Oracle Streams: Adding new site in existing environment

Part-I=How to setup Oracle Streams between two databases
OverviewDatabase Version: 10.1.0.5.0Windows XP sp2Database Name and TNS nameDB1, db1 (source)DB2, db2 (target) (Existing Target DB)DB3,db3 (new target database)
1. Enable ARCHIVELOG MODE on both databaseReference: http://dbatry.blogspot.com/2007/09/how-to-enable-archivelog-mode.html2. Create Stream administrator UserNew target database: DB3SQL> conn sys@db3 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.
3. Setup INIT parameters
SQL> alter system set global_names=true;System altered.
SQL> alter system set streams_pool_size =100m;System altered.
4. Create Database Link
SQL> conn strmadmin/strmadmin@db1Connected.
SQL> create database link DB32 connect to strmadmin3 identified by strmadmin4 using 'DB3';
Database link created.
SQL> conn strmadmin/strmadmin@db3Connected.SQL> create database link db12 connect to strmadmin3 identified by strmadmin4 using 'DB1';Database link created.
5. Setup Destination queues
SQL> conn strmadmin/strmadmin@db3Connected.
SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();PL/SQL procedure successfully completed.
6. Setup Schema for streams
SQL> conn sys@db3 as sysdbaEnter password:Connected.SQL> alter user scott account unlock identified by tiger;User altered.
SQL> conn scott/tiger@db3Connected.SQL> create table try (no number primary key, name varchar2(20), ddate date);Table created.
7. 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_DB3',4 source_queue_name => 'strmadmin.streams_queue',5 destination_queue_name => 'strmadmin.streams_queue@DB3',6 include_dml => true,7 include_ddl => true,8 source_database => 'DB1',9 inclusion_rule => true);10 end;11 /
PL/SQL procedure successfully completed.8. Stop Apply process at existing target databaseSQL> conn strmadmin/strmadmin@db2Connected.SQL> exec dbms_apply_adm.stop_apply('apply_stream');
PL/SQL procedure successfully completed.9. Set the instantiation system change number (SCN)Source Database: DB1SQL> conn strmadmin/strmadmin@db1Connected.PREPARE instantiation system change number (SCN)SQL> begin2 dbms_capture_adm.prepare_table_instantiation( table_name => 'scott.try');3 end;4 /PL/SQL procedure successfully completed.
SQL> declare2 source_scn number;3 begin4 source_scn := dbms_flashback.get_system_change_number();5 dbms_apply_adm.set_table_instantiation_scn@DB36 ( source_object_name => 'scott.try',7 source_database_name => 'DB1',8 instantiation_scn => source_scn);9 end;10 /
PL/SQL procedure successfully completed.
10. Configure the apply process at the destination database
SQL> conn strmadmin/strmadmin@db2Connected.SQL> exec dbms_apply_adm.start_apply('apply_stream');
PL/SQL procedure successfully completed.
SQL> conn strmadmin/strmadmin@db3Connected.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.
SQL> conn strmadmin/strmadmin@db3Connected.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> begin dbms_apply_adm.start_apply2 ( apply_name => 'apply_stream');3 end;4 /
PL/SQL procedure successfully completed.
NOTE: New site is added in Stream replication environment, just needed to test it.SQL> conn scott/tiger@db1Connected.SQL> alter table try add (flag char(1));Table altered.
SQL> desc tryName Null? Type----------------------------------------- -------- ------------------------NO NOT NULL NUMBERNAME VARCHAR2(20)DDATE DATEFLAG CHAR(1)---new entry
SQL> conn scott/tiger@db2Connected.SQL> desc tryName Null? Type----------------------------------------- -------- ------------------------NO NOT NULL NUMBERNAME VARCHAR2(20)DDATE DATE FLAG CHAR(1) ---new entry
SQL> conn scott/tiger@db3Connected.SQL> desc tryName Null? Type----------------------------------------- -------- ------------------------NO NOT NULL NUMBERNAME VARCHAR2(20)DDATE DATEFLAG CHAR(1) ---new entry