Oracle Streams Setup between two database

OverviewDatabase Version: 10.1.0.5.0Windows XP sp2Database Name and TNS nameDB1, db1 (source)DB2, db2 (target)Set up below parameters on both databases (db1, db2)1. Enable ARCHIVELOG MODE on both . Create Stream administrator UserSource Database: DB1SQL> conn sys@db1 as sysdbaEnter password:Connected.SQL> create user strmadmin identified by strmadmin;User created.SQL> grant connect, resource, dba to strmadmin;Grant succeeded.SQL> begin dbms_streams_auth.grant_admin_privilege2 (grantee => 'strmadmin',3 grant_privileges => true);4 end;5 /PL/SQL procedure successfully completed.SQL> grant select_catalog_role, select any dictionary to strmadmin;Grant succeeded.Target Database: DB2SQL> conn sys@db2 as sysdbaEnter password:Connected.SQL> create user strmadmin identified by strmadmin;User created.SQL> grant connect, resource, dba to strmadmin;Grant succeeded.SQL> begin dbms_streams_auth.grant_admin_privilege2 (grantee => 'strmadmin',3 grant_privileges => true);4 end;5 /PL/SQL procedure successfully completed.SQL> grant select_catalog_role, select any dictionary to strmadmin;Grant succeeded.3. Setup INIT parametersSource Database: DB1SQL> conn sys@db1 as sysdbaEnter password:Connected.SQL> alter system set global_names=true;System altered.SQL> alter system set streams_pool_size = 100 m;System altered.Target Database: DB2SQL> conn sys@db2 as sysdbaEnter password:Connected.SQL> alter system set global_names=true;System altered.SQL> alter system set streams_pool_size = 100 m;System altered.4. Create Database Link Target Database: DB1SQL> conn strmadmin/strmadmin@db1Connected.SQL> create database link db22 connect to strmadmin3 identified by strmadmin4 using 'DB2';Database link created.Source Database: DB2SQL> conn strmadmin/strmadmin@db2Connected.SQL> create database link db12 connect to strmadmin3 identified by strmadmin4 using 'DB1';Database link created.5. Setup Source and Destination queuesSource Database: DB1SQL> conn strmadmin/strmadmin@db1Connected.SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();PL/SQL procedure successfully completed.Target Database: DB2SQL> conn strmadmin/strmadmin@db2Connected.SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();PL/SQL procedure successfully completed.6. Setup Schema for streamsSchema: SCOTTTable: TajNOTE: Unlock scott schema because in 10g scott schema is locked by defaultSource Database: DB1SQL> conn sys@db1 as sysdbaEnter password:Connected.SQL> alter user scott account unlock identified by tiger;User altered.SQL> conn scott/tiger@db1Connected.SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);Table created.Target Database: DB2SQL> conn sys@db2 as sysdbaEnter password:Connected.SQL> alter user scott account unlock identified by tiger;User altered.SQL> conn scott/tiger@db2Connected.SQL> create table TAJ ( no number primary key,name varchar2(20),ddate date);Table created.7. Setup Supplemental logging at the source databaseSource Database: DB1SQL> conn scott/tiger@db1Connected.SQL> alter table taj2 add supplemental log data (primary key,unique) columns;Table altered. 8. Configure capture process at the source databaseSource Database: DB1SQL> conn strmadmin/strmadmin@db1Connected.SQL> begin dbms_streams_adm.add_table_rules2 ( table_name => 'scott.taj',3 streams_type => 'capture',4 streams_name => 'capture_stream',5 queue_name=> 'strmadmin.streams_queue',6 include_dml => true,7 include_ddl => true,8 inclusion_rule => true);9 end;10 /PL/SQL procedure successfully completed.9. Configure the propagation processSource Database: DB1SQL> conn strmadmin/strmadmin@db1Connected.SQL> begin dbms_streams_adm.add_table_propagation_rules2 ( table_name => 'scott.taj',3 streams_name => 'DB1_TO_DB2',4 source_queue_name => 'strmadmin.streams_queue',5 destination_queue_name => 'strmadmin.streams_queue@DB2',6 include_dml => true,7 include_ddl => true,8 source_database => 'DB1',9 inclusion_rule => true);10 end;11 /PL/SQL procedure successfully completed.10. Set the instantiation system change number (SCN)Source Database: DB1SQL> CONN STRMADMIN/STRMADMIN@DB1Connected.SQL> declare2 source_scn number;3 begin4 source_scn := dbms_flashback.get_system_change_number();5 dbms_apply_adm.set_table_instantiation_scn@DB26 ( source_object_name => 'scott.taj',7 source_database_name => 'DB1',8 instantiation_scn => source_scn);9 end;10 /PL/SQL procedure successfully completed.11. Configure the apply process at the destination databaseTarget Database: DB2SQL> conn strmadmin/strmadmin@db2Connected.SQL> begin dbms_streams_adm.add_table_rules2 ( table_name => 'scott.taj',3 streams_type => 'apply',4 streams_name => 'apply_stream',5 queue_name => 'strmadmin.streams_queue',6 include_dml => true,7 include_ddl => true,8 source_database => 'DB1',9 inclusion_rule => true);10 end;11 /PL/SQL procedure successfully completed.12. Start the capture and apply processesSource Database: DB1SQL> conn strmadmin/strmadmin@db1Connected.SQL> begin dbms_capture_adm.start_capture2 ( capture_name => 'capture_stream');3 end;4 /PL/SQL procedure successfully completed.Target Database: DB2SQL> conn strmadmin/strmadmin@db2Connected.SQL> begin dbms_apply_adm.set_parameter2 ( apply_name => 'apply_stream',3 parameter => 'disable_on_error',4 value => 'n');5 end;6 /PL/SQL procedure successfully completed.SQL> begin2 dbms_apply_adm.start_apply3 ( apply_name => 'apply_stream');4 end;5 /PL/SQL procedure successfully completed.NOTE: Stream replication environment is ready, just needed to test it.SQL> conn scott/tiger@db1Connected.SQL> --DDL operationSQL> alter table taj add (flag char(1));Table altered.SQL> --DML operationSQL> begin2 insert into taj values (1,'first_entry',sysdate,1);3 commit;4 end;5 /PL/SQL procedure successfully completed.SQL> conn scott/tiger@db2Connected.SQL> --TEST DDL operationSQL> desc tajName Null? Type----------------------------------------- -------- ----------------------------NO NOT NULL NUMBERNAME VARCHAR2(20)DDATE DATEFLAG CHAR(1)SQL> --TEST DML operationSQL> select * from taj;NO NAME DDATE F---------- -------------------- --------- -1 first_entry 24-JAN-08 1Reference: http://www.oracle.com/technology/oramag/oracle/04-nov/o64streams.html