Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2154218
  • 博文数量: 157
  • 博客积分: 10047
  • 博客等级: 上将
  • 技术积分: 6757
  • 用 户 组: 普通用户
  • 注册时间: 2005-05-19 11:38
文章分类

全部博文(157)

文章存档

2011年(16)

2010年(50)

2009年(42)

2008年(49)

我的朋友

分类: Oracle

2008-07-30 15:29:56

作者:
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:

  • 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.
阅读(3310) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~