Adding a new table to an existing Oracle Streams Replication environment - A failed attempt

Today I assigned myself a task, which is to add a new table to an existing streams replication. The source database is TEST10G and the destination database is TEST02DB. They both reside in my notebook pc.

The following are all the test steps for today, the task does not succeed.

1. Apply create table DDL on both databases:

create table denis.strm_tab1 (
id number,
name varchar2(20),
constraint id_pk primary key(id)
);


2. Check current capture, propagation processes on the source database

++ CAPTURE PROCESSES IN DATABASE ++
CAPTURE_NAME                  : STRMADMIN_CAPTURE
QUEUE_OWNER                   : STRMADMIN
QUEUE                         : STREAMS_QUEUE
CAPTURE_TYPE                  : LOCAL
STATUS                        : ENABLED
RULE_SET_OWNER                : SYS
RSN                           : RULESET$_17
NEGATIVE_RULE_SET_OWNER       :
RSN2                          :
CHECKPOINT_RETENTION_TIME     : 60
VERSION                       : 10.2.0.1.0
LOGFILE_ASSIGNMENT            : IMPLICIT
ERROR_NUMBER                  :
STATUS_CHANGE_TIME            : 30-nov-2008 21:07:57
ERROR_MESSAGE                 :
-----------------

++ CAPTURE PROCESS SOURCE INFORMATION ++
CAPTURE_NAME                  : STRMADMIN_CAPTURE
CAPTURE_TYPE                  : LOCAL
SOURCE_DATABASE               : TEST10G.WORLD
FIRST_SCN                     : 10138270589233
START_SCN                     : 10138270589233
CAPTURED_SCN                  : 10138270859114
APPLIED_SCN                   : 10138270859114
LAST_ENQUEUED_SCN             : 10138270865525
REQUIRED_CHECKPOINT_SCN       : 10138270830473
MAX_CHECKPOINT_SCN            : 10138270859114
SOURCE_DBID                   : 917147433
SOURCE_RESETLOGS_SCN          : 534907
SOURCE_RESETLOGS_TIME         : 665622764
LOGMINER_ID                   : 1
-----------------

++ PROPAGATIONS IN DATABASE ++
PROPAGATION_NAME              : STRMADMIN_PROPAGATE
SOURCE_QUEUE_OWNER            : STRMADMIN
SOURCE_QUEUE_NAME             : STREAMS_QUEUE
SRC GLOBAL NAME               : TEST10G.WORLDInstantiating
DESTINATION_QUEUE_OWNER       : STRMADMIN
DESTINATION_QUEUE_NAME        : STREAMS_QUEUE
DESTINATION_DBLINK            : TEST02DB.WORLD
QUEUE_TO_QUEUE                : FALSE
STATUS                        : ENABLED
ERROR_DATE                    :
ERROR_MESSAGE                 :

3. Check current apply process on the destioination database

++ APPLY INFORMATION ++
APPLY_NAME                    : STRMADMIN_APPLY
QUEUE_OWNER                   : STRMADMIN
QUEUE_NAME                    : STREAMS_QUEUE
APPLY_CAPTURED                : YES
STATUS                        : ENABLED
APPLY_USER                    : SCOTT
APPLY_TAG                     : 00
RULE_SET_OWNER                : STRMADMIN
RULE_SET_NAME                 : RULESET$_35
NEGATIVE_RULE_SET_OWNER       :
NEGATIVE_RULE_SET_NAME        :
APPLY_DATABASE_LINK           :
-----------------

++  APPLY PROCESS INFORMATION ++
APPLY_NAME                    : STRMADMIN_APPLY
MAX_APPLIED_MESSAGE_NUMBER    :
STATUS                        : ENABLED
STATUS_CHANGE_TIME            : 03-dec-2008 15:26:13
ERROR_NUMBER                  :
ERROR_MESSAGE                 :
-----------------

4. Execute the following steps at source database
4.1 Turn on supplementapply_vt.sqlal logging for STRM_TAB1 table
ALTER TABLE denis.strm_tab1 ADD SUPPLEMENTAL LOG GROUP id_pk1 (id) ALWAYS;

4.2 Add capture rules the table STRM_TAB1 at the source database:

conn / as sysdba
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'DENIS.STRM_TAB1',
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'TEST10G.world');
END;
/

4.3 Add propagation rules for the table STRM_TAB1 at the source database.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'DENIS.STRM_TAB1',
streams_name => 'STRMADMIN_PROPAGATE',
source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@TEST02DB.world',
include_dml => true,
include_ddl => true, Adding to a Streams Replication Environment
source_database => 'TEST10G.world');
END;
/


5. Execute the following steps at the destination database
5.1 Add apply rules for the table at the destination database

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'DENIS.STRM_TAB1',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'TEST10G.world');
END;
/


5.2 Grant privs to scott

Note: The apply user in existing repliction is scott. It may be better to
have different capture, propagation and apply for different schema, will
explore this later

denis@TEST02DB> grant all on strm_tab1 to scott;

Grant succeeded.


6. Instantiating

6.1 Execute the following to get SCN

connect STRMADMIN/STRMADMIN@TEST10G
set serveroutput on
DECLARE Adding to a Streams Replication Environment
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;
/

6.2 Execute the following with the SCN obtained in 6.1

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


7. Test to see if the replication work.
Insert a row into denis.strm_tab1 at source to see what happen at the dest.
Results: At the dest the row did not get inserted.
Verified that the replication for scott.dept still works fine.

8. Troubleshoot:

Execute the health check script at source and destination and review the report. Fixed the instantiating error, however, it is still not working
Tried stop and start capture, propagation and apply process, does not help.


9. Next step: Read documentation, try to fix the problem tommorrow if possible.

Oracle Doc: Adding to a Streams Replication Environment