Adding a table to a single source Streams environment

Adding a table to an existing single source Streams replication environment while the table is active.

Source database (S): ORCL.ORACLEDB1.KRISH.COM

Target database (T): MYDEV.ORACLEDB2.KRISH.COM

Source & Target Schema: SCOTT
Adding Table: DEPT

Steps to add table “DEPT” which exists at ORCL.ORACLEDB1.KRISH.COM to the Streams environment without stopping the existing apply/propagation/capture:

Note: Ensure that you have setup the streams for replication for the first time and created a streams administrator before executing following scripts.

1. Add apply rules on the table(T)
2. Add propagation rules on the table(S)
3. Configure supplemental logging for the table (S)
4. Add capture rules on the table (S)
5. Set Instantiation of table from Source(S)
6. Export, Import of table(Data) from Source(S) to Target (T)
7. Grant privileges to strmadmin user(T)

1 Add apply rules on the table(T)

Target(MYDEV.ORACLEDB2.KRISH.COM):

connect strmadmin/strmpwd@MYDEV.ORACLEDB2.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV,
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/

2 Add propagation rules on the table(S)

Source(ORCL.ORACLEDB1.KRISH.COM):


-- This script needs to be run as "strmadmin"

connect strmadmin/strmpwd@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.DEPT',
streams_name => 'STREAMS_PROPAGATE',
source_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
destination_queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV@MYDEV.ORACLEDB2.KRISH.COM',
include_dml => true,
include_ddl => true,
source_database => 'ORCL.ORACLEDB1.KRISH.COM',
inclusion_rule => true);
END;
/

3 Configure supplemental logging for the table (S)

connect strmadmin/strmpwd@ORCL.ORACLEDB1.KRISH.COM

alter table SCOTT.DEPT add supplemental log data (primary key, unique, foreign key, all) columns;

4 Add capture rules on the table (S)

Source(ORCL.ORACLEDB1.KRISH.COM):

-- This script needs to be run as "strmadmin"

connect strmadmin/strmpwd@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.DEPT',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'strmadmin.STREAMS_QUEUE_ORCL_MYDEV',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
END;
/

5 Set Instantiation of the table from Source (S)

--This script needs to be run as "strmadmin".

Source(ORCL.ORACLEDB1.KRISH.COM):

connect strmadmin/strmpwd@ORCL.ORACLEDB1.KRISH.COM

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION('SCOTT.DEPT');
END;
/

6 Export, import of table (Data) from Source(S) to Target (T)

Source(ORCL.ORACLEDB1.KRISH.COM):

$exp parfile=DEPT.par

DEPT.par
USERID=system/pwd@ORCL.ORACLEDB1.KRISH.COM
FILE=DEPT.dmp
LOG= DEPT.log
ROWS=Y
GRANTS=Y
INDEXES=Y
TRIGGERS=N
CONSTRAINTS=Y
CONSISTENT=Y
STATISTICS=NONE
TABLES= SCOTT.DEPT

ftp the DEPT.dmp file to target site

Target(MYDEV.ORACLEDB2.KRISH.COM):

$imp parfile=imp_DEPT.par

imp_DEPT.par
USERID=system/pwd@MYDEV.ORACLEDB2.KRISH.COM
FILE=DEPT.dmp
LOG= DEPT.log
ROWS=Y
IGNORE=Y
STREAMS_INSTANTIATION=Y
FULL=Y

7 Grant privileges to strmadmin user(T)

Target(MYDEV.ORACLEDB2.KRISH.COM):
-- grant all permissions on DEPT to streams administrator
conn SCOTT/tiger@MYDEV.ORACLEDB2.KRISH.COM

GRANT ALL ON SCOTT.DEPT to strmadmin;

Hub and Spoke Streams Replication Setup between 3 Databases

Hub-n-Spoke replication has typically a single Hub database to which all the other Spoke databases will be replicating. The changes (DML & DDL) originated on the Hub will be replicated to all the Spoke databases.

Publishing the document with example soon.