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

March 28, 2003

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

  2. 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;
       /
    
  3. 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;
       /
    
    
  4. 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.

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







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers