流复制不同步出现ORA-26687

tsx197

问题：1）怎么让流复制恢复正常？
           2）ORA-26687：no instantiation SCN provided。。。。问题有办法快速一次解决4089条记录的办法吗？我现在是手机一条条清除的，感觉效率太差，这一星期还折腾不完。          3）如果用EXEC DBMS_APPLY_ADM.DELETE_ALL_ERRORS(apply_name => ‘APPLY_DEST’);解决“ORA-26687：no instantiation SCN。。。”问题会有什么不良影响？
背景：两台机器按刘大的贴子做的双向流复制（Schema级别）。上周五成功后在周末两天测试中都正常，昨天同事测试出异常：发现两边都无法同步到另一台机器。登录源、目标两台机器发现流复制配置都正常，但在告警和apply_error中有大量报错。具体情况如下：
证据1：登录源机查看
SQL> SELECT CAPTURE_NAME,QUEUE_NAME,RULE_SET_NAME,NEGATIVE_RULE_SET_NAME,STATUS FROM DBA_CAPTURE;
CAPTURE_NAME                   QUEUE_NAME
------------------------------ ------------------------------
RULE_SET_NAME                  NEGATIVE_RULE_SET_NAME         STATUS
------------------------------ ------------------------------ --------
CAPTURES_SRC                   CAPTURE_SRC
RULESET$_3                                                     ENABLED

证据2：登录源机查看
SQL>SELECT CAPTURE_NAME,ERROR_NUMBER,ERROR_MESSAGE STATUS FROM DBA_CAPTURE;
CAPTURE_NAME  ERROR_NUMBER                                              STATUS
------------------ ----------------------------------------------------------------------------------------------

证据3：登录目标机查看
SQL> SELECT apply_name, apply_captured, status FROM dba_apply;
APPLY_NAME APPLY_ STATUS
---------------------- ------ ----------------
APPLY_DEST YES ENABLED

证明4：登录目标机查看
SQL> select APPLY_NAME,LOCAL_TRANSACTION_ID,SOURCE_COMMIT_SCN,MESSAGE_NUMBER,ERROR_MESSAGE from dba_apply_error;
APPLYS_DEST        14.0.491        6426162079        1        ORA-26687: no instantiation SCN provided for "TEST"."CUSTOMERS" in source database "HOST.COM.CN"                                                           
.
.
.
.
.
.
此处一共有4089条记录

证据5：登录目标机查看告警日志有大量报错
ORA-24010 encountered when generating server alert SMG-4120
ORA-24010 encountered when generating server alert SMG-4121
ORA-24010 encountered when generating server alert SMG-4121
.
.
.
.
.
.
太多不一一贴出

附录：我手动解决ORA-26687: no instantiation SCN provided for "XXX" in source database “XXX”的办法
在源机操作：
begin
DBMS_APPLY_ADM.set_table_instantiation_scn(
   source_object_name=> 'TEST.CUSTOMERS',
   source_database_name => 'HOST.COM.CN',
   instantiation_scn => 6426162079);
end;
/

在目标库执行即可
BEGIN
  DBMS_APPLY_ADM.EXECUTE_ERROR(
   local_transaction_id => '14.0.491',
   execute_as_user => FALSE,
   user_procedure => NULL);
END;
/

[ 本帖最后由 tsx197 于 2012-7-3 09:19 编辑 ]

Maclean Liu(刘相兵

分别在 source和target 运行以下脚本 并上传为附件


http://www.oracledatabase12g.com ... -10g-release-2.html

tsx197

已上传在附件，请查收
刘大，你看我下面的步骤能恢复啵？
1、分别在源和目标机关闭流复制
目标库：exec dbms_capture_adm.stop_capture (capture_name=>'captures_dest ');
        exec dbms_apply_adm.stop_apply (apply_name=> 'applys_dest ');


源库：exec dbms_capture_adm.stop_capture (capture_name=>'captures_src');
　　  exec dbms_apply_adm.stop_apply (apply_name=> 'applys_src');

2、重新导入导出实例化，不带数据（因上周已导过一次，两库数据相差不是太大）
exp test/testLiu
file=/data_backup/test_20120703.dmp log=/data_backup/test_20120703.log OBJECT_CONSISTENT=Y STATISTICS = NONE ROWS=NO buffer=2121269248 full=y

imp test/testLiu FULL=Y object_consistent=y buffer=2121269248
file=/oradata/test_20120703.dmp log=/oradata/test_20120703.log COMMIT=Y  STREAMS_INSTANTIATION=Y IGNORE=Y


3、重新在目标机配置SCN的实例化
declare v_scn number;begin
v_scn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_schema_instantiation_scn@HOST.COM.CN(source_schema_name => 'TEST',source_database_name => 'guest.com.cn',instantiation_scn => v_scn,recursive => true);
end;
/

4、分别对源和目标机开启流复制
目标库：exec dbms_apply_adm.start_apply (apply_name=> 'applys_dest');
　　    exec dbms_capture_adm.start_capture (capture_name=>'captures_dest');
源库：exec dbms_apply_adm.start_apply (apply_name=> 'applys_src');
　　  exec dbms_capture_adm.start_capture (capture_name=>'captures_src');

[ 本帖最后由 tsx197 于 2012-7-4 05:57 编辑 ]

Maclean Liu(刘相兵

ORA-26687问题的解决请参考以下信息：



ORA-26687:  Instantiation SCN Not Set

Instantiated SCN for objects required to APPLY changes
For table DML:  Check view DBA_APPLY_INSTANTIATED_OBJECTS
For schema/database DDL: Check appropriate view
SYS.APPLY$_SOURCE_SCHEMA
SYS.APPLY$_SOURCE_DATABASE
Correct with:
Export/ Import
SET_TABLE_INSTANTIATION_SCN
SET_SCHEMA_INSTANTIATION_SCN
SET_DATABASE_INSTANTIATION_SCN


Exp/Imp is the preferred method of setting the instantation SCN because it automatically populates the TABLE level scns even when performing a SCHEMA or Database level import.

SET_SCHEMA_INSTANTIATION_SCN sets the instantiation scn for DDL that is performed in the individual schema.  DDL that creates new Tables at destination site will have the SCN set automatically at the apply site, when the table is created.  However, existing tables at the apply site must have the SCN set explicilty before DML will be able to be applied at the destination site.