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