Oracle Streams: Adding new site in existing environment

Part-I=How to setup Oracle Streams between two databases

Overview
Database Version: 10.1.0.5.0
Windows XP sp2
Database Name and TNS name
DB1, db1 (source)
DB2, db2 (target) (Existing Target DB)
DB3,db3 (new target database)

1. Enable ARCHIVELOG MODE on both database
Reference: http://dbataj.blogspot.com/2007/09/how-to-enable-archivelog-mode.html
2. Create Stream administrator User
New target database: DB3
SQL> conn sys@db3 as sysdba
Enter 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_privilege
2 ( 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@db1
Connected.

SQL> create database link DB3
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB3';

Database link created.

SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> create database link db1
2 connect to strmadmin
3 identified by strmadmin
4 using 'DB1';
Database link created.

5. Setup Destination queues

SQL> conn strmadmin/strmadmin@db3
Connected.

SQL> EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
PL/SQL procedure successfully completed.

6. Setup Schema for streams

SQL> conn sys@db3 as sysdba
Enter password:
Connected.
SQL> alter user scott account unlock identified by tiger;
User altered.

SQL> conn scott/tiger@db3
Connected.
SQL> create table taj (no number primary key, name varchar2(20), ddate date);
Table created.

7. Configure the propagation process
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
SQL> begin dbms_streams_adm.add_table_propagation_rules
2 ( table_name => 'scott.taj',
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 database
SQL> conn strmadmin/strmadmin@db2
Connected.
SQL> exec dbms_apply_adm.stop_apply('apply_stream');

PL/SQL procedure successfully completed.
9. Set the instantiation system change number (SCN)
Source Database: DB1
SQL> conn strmadmin/strmadmin@db1
Connected.
PREPARE instantiation system change number (SCN)
SQL> begin
2 dbms_capture_adm.prepare_table_instantiation( table_name => 'scott.taj');
3 end;
4 /
PL/SQL procedure successfully completed.

SQL> declare
2 source_scn number;
3 begin
4 source_scn := dbms_flashback.get_system_change_number();
5 dbms_apply_adm.set_table_instantiation_scn@DB3
6 ( source_object_name => 'scott.taj',
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@db2
Connected.
SQL> exec dbms_apply_adm.start_apply('apply_stream');

PL/SQL procedure successfully completed.

SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> begin dbms_streams_adm.add_table_rules
2 ( 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.

SQL> conn strmadmin/strmadmin@db3
Connected.
SQL> begin dbms_apply_adm.set_parameter
2 (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_apply
2 ( 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@db1
Connected.
SQL> alter table taj add (flag char(1));
Table altered.

SQL> desc taj
Name Null? Type
----------------------------------------- -------- ------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1)---new entry

SQL> conn scott/tiger@db2
Connected.
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1) ---new entry

SQL> conn scott/tiger@db3
Connected.
SQL> desc taj
Name Null? Type
----------------------------------------- -------- ------------------------
NO NOT NULL NUMBER
NAME VARCHAR2(20)
DDATE DATE
FLAG CHAR(1) ---new entry