I breifly read the Oracle document about this topic, and I have successfuly added a new table to the replication by taking the following steps:
1. Create the table at source and destination database.
create table scott.strm_tab2 (
id number,
name varchar2(20),
constraint id_pk primary key(id)
);
2. Turn on supplemental logging for the table at source
ALTER TABLE scott.strm_tab2 ADD SUPPLEMENTAL LOG GROUP id_pk1 (id) ALWAYS;
3. At source database, stop the capture process and propagation process
++ CAPTURE PROCESSES IN DATABASE ++
CAPTURE_NAME : STRMADMIN_CAPTURE
QUEUE_OWNER : STRMADMIN
QUEUE : STREAMS_QUEUE
CAPTURE_TYPE : LOCAL
STATUS : DISABLED
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 : 07-dec-2008 14:18:13
++ PROPAGATIONS IN DATABASE ++
PROPAGATION_NAME : STRMADMIN_PROPAGATE
SOURCE_QUEUE_OWNER : STRMADMIN
SOURCE_QUEUE_NAME : STREAMS_QUEUE
SRC GLOBAL NAME : TEST10G.WORLD
DESTINATION_QUEUE_OWNER : STRMADMIN
DESTINATION_QUEUE_NAME : STREAMS_QUEUE
DESTINATION_DBLINK : TEST02DB.WORLD
QUEUE_TO_QUEUE : FALSE
STATUS : ABORTED
ERROR_DATE :
ERROR_MESSAGE :
-----------------
4. At destionation database stop the apply process
++ APPLY INFORMATION ++
APPLY_NAME : STRMADMIN_APPLY
QUEUE_OWNER : STRMADMIN
QUEUE_NAME : STREAMS_QUEUE
APPLY_CAPTURED : YES
STATUS : DISABLED
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 : 10138270879363
STATUS : DISABLED
STATUS_CHANGE_TIME : 07-dec-2008 14:20:47
ERROR_NUMBER :
ERROR_MESSAGE :
-----------------
5. At destination database, add rules for an apply process
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.STRM_TAB2',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_APPLY',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'TEST10G.world');
END;
/
6. At source database, add rules for propagation process
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'SCOTT.STRM_TAB2',
streams_name => 'STRMADMIN_PROPAGATE',
source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
destination_queue_name => 'STRMADMIN.STREAMS_QUEUE@TEST02DB.world',
include_dml => true,
include_ddl => true,
source_database => 'TEST10G.world');
END;
/
7. At source database, add rules for capture process
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'SCOTT.STRM_TAB2',
streams_type => 'CAPTURE',
streams_name => 'STRMADMIN_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_QUEUE',
include_dml => true,
include_ddl => true,
source_database => 'TEST10G.world');
END;
/
8. At destination database, set the instantiation SCN for the table
8.1 Execute the following to get SCN at source
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;
/
8.2 Execute the following with the SCN obtained in 8.1
connect strmadmin/strmadmin@TEST02DB
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCOTT.STRM_TAB2',
source_database_name => 'TEST10G.world',
instantiation_scn => &iscn);
END;
/
9. At source database start capture process
10. At source database start propagation process
11. At destinationdatabase start apply
12. Test the replciation --- OK DML and DDL
It is still not completely clear to me what was wrong for the steps I have taken yesterday. But it looks like we must stop capture, propagation and apply processes if we add a rule to the rule sets they are using and the order matters - From the doc: "If you perform administrative steps in the wrong order, you can lost LCRs"
Savepoint Funny
5 weeks ago
