Change data capture implementation in Oracle Data warehouses - Part 2 - Oracle Streams implementation - Page 3

Create a Database Link in strmadmin@local.world to point to the strmadmin user in remote.world database.
```
CREATE DATABASE LINK remote.world 
CONNECT TO strmadmin 
IDENTIFIED BY strmadmin 
USING 'remote.world';
```
NOTE: Alternatively you can login as SYS user and create a public database link.

Login to strmadmin at local.world and remote.world respectively and create a Streams Queue as follows:

/* Refer to PART-1 of the article series for DBMS_STREAMS_ADM description  */
BEGIN
 DBMS_STREAMS_ADM.SET_UP_QUEUE(
   queue_user => 'STRMADMIN');
END;
   /

Create the Capture, propagate and Apply rules in the respective streams administrator schemas.

Login to destination streams administrator schema and create the Apply process

/* In this example we create DML apply rules on DEMO.DEPt and DEMO.EMP tables 
respectively. We can also create DML /DDL rules for the entire DEMO schema using 
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES procedure */
BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name => '"DEMO"."DEPT"', 
   streams_type => 'APPLY', 
   streams_name => 'STRMADMIN_LOCAL', 
   queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
   include_dml => true, 
   include_ddl => false, 
   source_database => 'LOCAL.WORLD');

/* Add Apply rules for DEMO.DEPT */
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name => '"DEMO"."EMP"', 
   streams_type => 'APPLY', 
   streams_name => 'STRMADMIN_LOCAL', 
   queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
   include_dml => true, 
   include_ddl => false, 
   source_database => 'LOCAL.WORLD');
END;
/

Login to source streams administrator schema and create the Capture and Propagation process

/* Add capture rules to DEMO.DEPT and DEMO.EMP */
BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name => '"DEMO"."DEPT"', 
   streams_type => 'CAPTURE', 
   streams_name => 'STRMADMIN_CAPTURE', 
   queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
   include_dml => true, 
   include_ddl => false, 
   source_database => 'LOCAL.WORLD');

 DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name => '"DEMO"."EMP"', 
   streams_type => 'CAPTURE', 
   streams_name => 'STRMADMIN_CAPTURE', 
   queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
   include_dml => true, 
   include_ddl => false, 
   source_database => 'LOCAL.WORLD');
END;
/
/* Add propagation rules to DEMO.DEPT and DEMO.EMP */
BEGIN
 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name => '"DEMO"."DEPT"', 
   streams_name => 'STRMADMIN_PROPAGATE', 
   source_queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
   destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@REMOTE.WORLD', 
   include_dml => true, 
   include_ddl => false, 
   source_database => 'LOCAL.WORLD');

 DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
   table_name => '"DEMO"."EMP"', 
   streams_name => 'STRMADMIN_PROPAGATE', 
   source_queue_name => '"STRMADMIN"."STREAMS_QUEUE"', 
   destination_queue_name => '"STRMADMIN"."STREAMS_QUEUE"@REMOTE.WORLD', 
   include_dml => true, 
   include_ddl => false, 
   source_database => 'LOCAL.WORLD');
END;
   /

Grant appropriate privileges to the Objects / schema for which the apply rules have been setup, to apply the DDL or DML changes.

If DEMO.DEPT and DEMO.EMP already exist in DEMO@remote.world then grant SELECT, INSERT and UPDATE privileges to the Streams administrator. Otherwise, with respect to this example, export the tables from the source database as specified in step 9.
Set up the instantiation SCNs for the tables.

In addition to using supplied PL/SQL packages, you can also use Export/Import Oracle utility to set up Instantiation SCNs for database objects.

NOTE: In Oracle streams the tables identified for capturing and applying changes should have primary keys defined on them.

From the command line do the following:
```
exp USERID="STRMADMIN"@local.world TABLES="DEMO"."DEPT", "DEMO"."EMP" FILE=tables.dmp 
GRANTS=Y ROWS=Y LOG=exportTables.log  OBJECT_CONSISTENT=Y INDEXES=Y

imp USERID="STRMADMIN"@remote.world FULL=Y CONSTRAINTS=Y FILE=tables.dmp 
IGNORE=Y GRANTS=Y ROWS=Y COMMIT=Y LOG=importTables.log STREAMS_CONFIGURATION=Y 
STREAMS_INSTANTIATION=Y
```

Now to start the processes at the respective databases.

/*  Start Apply process at the destination database.
  Login to STRMADMIN@REMOTE.WORLD*/
DECLARE
 v_started number;
BEGIN
  SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
    FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_LOCAL';

  if (v_started = 0) then
    DBMS_APPLY_ADM.START_APPLY(apply_name  => 'STRMADMIN_LOCAL');
  end if;
END;
/

/*  Start Capture process at the destination database 
Login to STRMADMIN@LOCAL.WORLD */
DECLARE
 v_started number;
BEGIN
  SELECT decode(status, 'ENABLED', 1, 0) INTO v_started
    FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'STRMADMIN_CAPTURE';

  if (v_started = 0) then
    DBMS_CAPTURE_ADM.START_CAPTURE(capture_name  => 'STRMADMIN_CAPTURE');
  end if;
END;
/