source db – gclone1
target db – london

I setup a simple streams replication scenario where i am trying to replicate two tables scott.emp and scott.test from gclone1@ggsource.localdomain to london@ggtarget.localdomain.

after replication i found that the changes in source db tables are not getting replicated to target db table. on target db when i checked dba_apply_error tables i found ORA-26687 error message.

on target db
col QUEUE_NAME for a20
col ERROR_MESSAGE for a90
select APPLY_NAME,QUEUE_NAME,ERROR_NUMBER,ERROR_MESSAGE,ERROR_CREATION_TIME from dba_apply_error;

APPLY_NAME           QUEUE_NAME           ERROR_NUMBER ERROR_MESSAGE
-------------------- -------------------- ------------ ------------------------------------------------------------------------------------------
ERROR_CRE
---------
APPLY$_GCLONE1_3     GCLONE1$APPQ                26687 ORA-26687: no instantiation SCN provided for "SCOTT"."EMP" in source database "GCLONE1"
05-APR-20

APPLY$_GCLONE1_3     GCLONE1$APPQ                26687 ORA-26687: no instantiation SCN provided for "SCOTT"."EMP" in source database "GCLONE1"
05-APR-20
on source db

Run the below command as a stream admin user in the Source DB, You will get the SCN for the corresponding table. you need to use them in Destination DB.

SYS@ gclone1>conn stream_user/stream_user
col TABLE_OWNER for a20
col TABLE_NAME for a20
SELECT * FROM DBA_CAPTURE_PREPARED_TABLES;

TABLE_OWNER          TABLE_NAME                  SCN TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME
-------------------- -------------------- ---------- --------- -------- -------- -------- --------
SCOTT                EMP                     3472541 05-APR-20 IMPLICIT IMPLICIT IMPLICIT NO
SCOTT                TEST                    3492026 05-APR-20 IMPLICIT IMPLICIT IMPLICIT NO
on target db

Enter instantiation_scn(iscn) that you got from the source.

SYS@ london>conn stream_user/stream_user
Connected.
STREAM_USER@ london>BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (
source_object_name => 'SCOTT.TEST',
source_database_name => 'gclone1',
instantiation_scn => 3492026);
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

STREAM_USER@ london>BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (
source_object_name => 'SCOTT.EMP',
source_database_name => 'gclone1',
instantiation_scn => 3472541);
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

Now start the APPLY process using below command. and also check the status of the process. you can see that status chnaged from aborted to enabled.

STREAM_USER@ london>SELECT APPLY_NAME,QUEUE_NAME,RULE_SET_NAME,NEGATIVE_RULE_SET_NAME,STATUS FROM DBA_APPLY;

APPLY_NAME           QUEUE_NAME
-------------------- --------------------
RULE_SET_NAME
--------------------------------------------------------------------------------------------------------------------------------
NEGATIVE_RULE_SET_NAME                                                                                                           STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
APPLY$_GCLONE1_3     GCLONE1$APPQ
RULESET$_4
                                                                                                                                 ABORTED


STREAM_USER@ london>exec dbms_apply_adm.start_apply(apply_name=>'APPLY$_GCLONE1_3');

PL/SQL procedure successfully completed.

STREAM_USER@ london>SELECT APPLY_NAME,QUEUE_NAME,RULE_SET_NAME,NEGATIVE_RULE_SET_NAME,STATUS FROM DBA_APPLY;

APPLY_NAME           QUEUE_NAME
-------------------- --------------------
RULE_SET_NAME
--------------------------------------------------------------------------------------------------------------------------------
NEGATIVE_RULE_SET_NAME                                                                                                           STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
APPLY$_GCLONE1_3     GCLONE1$APPQ
RULESET$_4
                                                                                                                                 ENABLED

Now all chnages in source db tables are getting replicated to target db tables.