Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 28, 2003

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

By DatabaseJournal.com Staff

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


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date