Creating a Simple Two-Way Streams Replciation Environment

Before starting this game, my environment is as follows:

• Two 10g databases on my PC: TEST10G and TEST02DB
• One-way Streams replication has been set up for the tables: dept and strm_tab2 in scott schema
• replciation from TEST10G to TEST02DB.

By the end of game, my new enviroment should be:

• Two databases: TEST10G and TEST02DB
• One-way Streams replication for table dept and strm_tab2 in scott schema, from TEST10G to TEST02DB
• Two-way Streams replication for table strm_tab3 in denis schema, between TEST10G and TEST02DB
  

My experimenting steps are as follows:
(ref: Oracle document: Creating a New Streams Multiple-Source Environment )

Step 0 -- Stop current capture, propagation and apply processes


Step 1 -- Create table denis.strm_tab3 at TEST10G

sys@TEST10G> select * from denis.strm_tab3;

ID FIRST_NAME LAST_NAME
---------- -------------------- ------------
1 Denis Sun
2 Tom Kyte
3 Jack Smith


Step 2 -- Create database link between TEST10G and TEST02DB

connect STRMADMIN/STRMADMIN@test02db

CREATE DATABASE LINK TEST10G.world connect to
STRMADMIN identified by STRMADMIN using 'TEST10G.world';
select * from global_name@TEST10G.world;

Note: DB link from TEST10G to TEST02DB already exists.

Step 3 -- Specifying an Unconditional Supplemental Log Group for Primary Key Column(s) at TEST10G

ALTER TABLE denis.strm_tab3 ADD SUPPLEMENTAL LOG GROUP tab3_id_pk1 (id) ALWAYS;


Step 4 -- Performing the following steps at TEST10G


-- create queues

connect strmadmin/strmadmin

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_Q_SRC',
queue_table =>'STREAMS_Q_SRC',
queue_user => 'STRMADMIN');
END;strm_tab3;
/

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_Q_DST',
queue_table =>'STREAMS_Q_DST',
queue_user => 'STRMADMIN');
END;
/


-- Add table rules to caputure process


BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'DENIS.STRM_TAB3',
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE_2',
queue_name => 'STRMADMIN.STREAMS_Q_SRC',
include_dml => true,strm_tab3;
include_ddl => true,
source_database => 'TEST10G.world');
END;
/


-- Add rule to propagation

connect strmadmin/strmadmin
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'DENIS.STRM_TAB3',
streams_name => 'STRMADMIN_PROPAGATE_2',
source_queue_name => 'STRMADMIN.STREAMS_Q_SRC',
destination_queue_name => 'STRMADMIN.STREAMS_Q_DST@TEST02DB.world',
include_dml => true,
include_ddl => true,
source_database => 'TEST10G.world');
END;
/


-- Add apply rules for the table

conn strmadmin/strmadmin

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'DENIS.STRM_TAB3',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY_2',
queue_name => 'STRMADMIN.STREAMS_Q_DST',
include_dml => true,
include_ddl => true,
source_database => 'TEST02DB.world');
END;
/

-- apply user
conn strmadmin/strmadmin

BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'STRMADMIN_APPLY_2',
apply_user => 'DENIS');
END;
/

Step 5 -- Performing the following steps at TEST02DB


-- create q
connect strmadmin/strmadmin

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_Q_SRC',
queue_table =>'STREAMS_Q_SRC',
queue_user => 'STRMADMIN');
END;
/

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_name => 'STREAMS_Q_DST',
queue_table =>'STREAMS_Q_DST',
queue_user => 'STRMADMIN');
END;
/


-- Add table rules to caputure process

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'DENIS.STRM_TAB3',
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_Q_SRC',
include_dml => true,
include_ddl => true,
source_database => 'TEST02DB.world');
END;
/


-- Add rule to propagation

connect strmadmin/strmadmin
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'DENIS.STRM_TAB3',
streams_name => 'STRMADMstrm_tab3;IN_PROPAGATE_2',
source_queue_name => 'STRMADMIN.STREAMS_Q_SRC',
destination_queue_name => 'STRMADMIN.STREAMS_Q_DST@TEST10G.world',
include_dml => true,
include_ddl => true,
source_database => 'TEST02DB.world');
END;
/


-- Add apply rules for the table

conn strmadmin/strmadmin

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'DENIS.STRM_TAB3',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY_2',
queue_name => 'STRMADMIN.STREAMS_Q_DST',
include_dml => true,
include_ddl => true,
source_database => 'TEST10G.world');
END;
/
-- apply user
conn strmadmin/strmadmin

BEGIN
DBMS_APPLY_ADM.ALTER_APPLY(
apply_name => 'STRMADMIN_APPLY_2',
apply_user => 'DENIS');
END;
/



Step 6 Export and import

$ exp denis/oracle file=tab3.dmp tables=strm_tab3
Export: Release 10.2.0.1.0 - Production on Fri Dec 12 20:45:29 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table STRM_TAB3 3 rows exported
Export terminated successfully without warnings.

---------------------------------------------

$ imp denis/oracle@TEST02DB file=tab3.dmp tables=strm_tab3
Import: Release 10.2.0.1.0 - Production on Fri Dec 12 20:49:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing DENIS's objects into DENIS
. importing DENIS's objects into DENIS
. . importing table "STRM_TAB3" 3 rows imported
Import terminated successfully without warnings.



Step 7 -- Setting Instantiation SCNs

At TEST10G
~~~~~~~~~~

connect STRMADMIN/STRMADMIN@TEST02DB
set serveroutput on
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' iscn);
END;
/


connect strmadmin/strmadmin@TEST10G
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'DENIS.STRM_TAB3',
source_database_name => 'TEST02DB.world',
instantiation_scn => &iscn);
END;
/



At TEST02DB
~~~~~~~~~

connect STRMADMIN/STRMADMIN@TEST10G
set serveroutput on
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' iscn);
END;
/


connect strmadmin/strmadmin@TEST02DB
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'DENIS.STRM_TAB3',
source_database_name => 'TEST10G.world',
instantiation_scn => &iscn);
END;
/



Step 8 -- Configure conflict resolution

(skip this step for future experimenting, check this doc for reference )


Step 9 -- Start apply process, propagation and capture


Note:

Oracle recommends that you use only one capture process for each source database. I have two capture at TEST10G.



Step 10 - Verifying the replication.

Working as expected