STRMADMIN_APPLY ORA-26687: no instantiation SCN p

作者：

l、流复制目标数据库出现错误如下：

STRMADMIN_APPLY  ORA-26687: no instantiation SCN provided

for "BASECONF"."PUB_USER" in source database "BILLBJ.CHINACACHE"

2、查看apply_error等相关信息

SQL> desc dba_apply_error
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 APPLY_NAME                                         VARCHAR2(30)
 QUEUE_NAME                                NOT NULL VARCHAR2(30)
 QUEUE_OWNER                               NOT NULL VARCHAR2(30)
 LOCAL_TRANSACTION_ID                               VARCHAR2(22)
 SOURCE_DATABASE                                    VARCHAR2(128)
 SOURCE_TRANSACTION_ID                              VARCHAR2(22)
 SOURCE_COMMIT_SCN                                  NUMBER
 MESSAGE_NUMBER                                     NUMBER
 ERROR_NUMBER                                       NUMBER
 ERROR_MESSAGE                                      VARCHAR2(4000)
 RECIPIENT_ID                                       NUMBER
 RECIPIENT_NAME                                     VARCHAR2(30)
 MESSAGE_COUNT                                      NUMBER
 ERROR_CREATION_TIME                                DATE

select APPLY_NAME,LOCAL_TRANSACTION_ID,SOURCE_COMMIT_SCN,MESSAGE_NUMBER,ERROR_MESSAGE from dba_apply_error;
得到SOURCE_COMMIT_SCN 34327911302

3、然后在源库执行
SQL>
 SQL> begin
  2       => 'BASECONF.PUB_USER',
  3                                                         source_database_name => 'BILLBJ.CHINACACHE',
  4                                                         instantiation_scn    => 34327911302);
  5  end;
  6  /

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

问题解决。

我的系统是一个schema级别的复制，为什么这个表会出现没有初始化no instantiation SCN错误呢？

经过回忆，在早晨的时候出现apply disabled

 

1、登陆目标机器查看状态

sqlplus strmadmin/******

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jul 30 08:40:38 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

 

> select status from dba_apply;

STATUS
--------
DISABLED

 

> exec DBMS_APPLY_ADM.START_APPLY(apply_name  => 'STRMADMIN_APPLY');    
BEGIN DBMS_APPLY_ADM.START_APPLY(apply_name  => 'STRMADMIN_APPLY'); END;

*
ERROR at line 1:
ORA-26666: cannot alter STREAMS process STRMADMIN_APPLY
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 615
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 28
ORA-06512: at line 1

提示不能关闭，于是尝试进行关闭操作
> exec  DBMS_APPLY_ADM.STOP_APPLY(apply_name  => 'STRMADMIN_APPLY');  
BEGIN DBMS_APPLY_ADM.STOP_APPLY(apply_name  => 'STRMADMIN_APPLY'); END;

*
ERROR at line 1:
ORA-26672: timeout occurred while stopping STREAMS process STRMADMIN_APPLY
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 626
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 55
ORA-06512: at line 1

超时，于是进行强制关闭
> exec DBMS_APPLY_ADM.STOP_APPLY(apply_name  => 'STRMADMIN_APPLY',force => true);

PL/SQL procedure successfully completed.

 

> select * from dba_apply;

APPLY_NAME                     QUEUE_NAME                     QUEUE_OWNER                    APP
------------------------------ ------------------------------ ------------------------------ ---
RULE_SET_NAME                  RULE_SET_OWNER                 APPLY_USER
------------------------------ ------------------------------ ------------------------------
APPLY_DATABASE_LINK
----------------------------------------------------------------------------------------------------
APPLY_TAG
----------------------------------------------------------------------------------------------------
DDL_HANDLER
--------------------------------------------------------------------------------------------------
PRECOMMIT_HANDLER
--------------------------------------------------------------------------------------------------
MESSAGE_HANDLER
--------------------------------------------------------------------------------------------------
STATUS   MAX_APPLIED_MESSAGE_NUMBER NEGATIVE_RULE_SET_NAME         NEGATIVE_RULE_SET_OWNER
-------- -------------------------- ------------------------------ ------------------------------
STATUS_CH ERROR_NUMBER
--------- ------------
ERROR_MESSAGE
----------------------------------------------------------------------------------------------------
STRMADMIN_APPLY                STREAMS_QUEUE                  STRMADMIN                      YES
RULESET$_57                    STRMADMIN                      BASECONF

00

 

ABORTED
30-JUL-08         1013
ORA-01013: user requested cancel of current operation

启动
> DECLARE                                                               
  2      v_started number;                                                  
  3  BEGIN                                                                 
  4      SELECT decode(status, 'ENABLED', 1, 0) INTO v_started              
  5      FROM DBA_APPLY WHERE APPLY_NAME = 'STRMADMIN_APPLY';               
  6      if (v_started = 0) then                                            
  7        DBMS_APPLY_ADM.START_APPLY(apply_name  => 'STRMADMIN_APPLY');    
  8      end if;                                                            
  9  END;
 10  /

PL/SQL procedure successfully completed.

> exit

 

Subject:	Bug 5205636 - Apply fails with ORA-26687 while dropping a table with referential constraints
	:	Note:5205636.8	Type:	PATCH
	Last Revision Date:	09-MAY-2008	Status:	PUBLISHED

 Click for details of sections in this note.

Bug 5205636  Apply fails with ORA-26687 while dropping a table with referential constraints

 This note gives a brief overview of bug 5205636.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions < 11
Versions confirmed as being affected	10.2.0.3
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

10.2.0.4 (Server Patch Set) 11.1.0.6 (Base Release)

Symptoms:	Related To:
Error May Occur ORA-26687	Streams / Logical Standby

Description

After dropping a table T2 (using recycle bin) that has a foreign key reference 
to another table T1 and then dropping table T1, apply throws -
   ORA-26687: No instantiation SCN provided


Workaround:
  Manually drop the tables on the Apply site and delete the error for the apply.