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

5. 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.

6. 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;
      /
   

7. 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;
      /
   
   

8. 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.

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
   
   

10. 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;
    /