Oracle Streams Setup between two database

OverviewDatabase Version: 10.1.0.5.0Windows XP sp2
Database Name and TNS nameDB1, db1 (source)DB2, db2 (target)
Set up below parameters on both databases (db1, db2)
1. Enable ARCHIVELOG MODE on both databaseReference: http://dbatry.blogspot.com/2007/09/how-to-enable-archivelog-mode.html
2. 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: tryNOTE: 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 try ( 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 try ( 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 try2 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.try',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.try',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.try',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.try',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 try add (flag char(1));
Table altered.
SQL> --DML operationSQL> begin2 insert into try 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 tryName Null? Type----------------------------------------- -------- ----------------------------
NO NOT NULL NUMBERNAME VARCHAR2(20)DDATE DATEFLAG CHAR(1)
SQL> --TEST DML operationSQL> select * from try;
NO NAME DDATE F---------- -------------------- --------- -1 first_entry 24-JAN-08 1
Reference: http://www.oracle.com/technology/oramag/oracle/04-nov/o64streams.html