ORA-24042 ORA-12545 ORA-26714 bug（Bug 5623403)-CSDN博客

ORA-24042: no propagation schedule exists for QUEUE QBJMES_QUEUE and DESTINATION MES

记录处理 donwload streams复制报错：

ORA-26687 no instantiation SCN provided for in source database

在做DOWNLOADstreams复制，一切准备就绪后发现启动apply进程报错，这个错误应该很常见。

先看下我导出导入的句子

expdpbstyle_dev/bstyle_dev directory=exp_dir dumpfile=BSTYLE_20120518.DMPOBJECT_CONSISTENT=Y

STATISTICS = NONE;

impdpbstyle_dev/bstyle_dev directory=exp_dir dumpfile=BSTYLE_20120518.DMP IGNORE=Y COMMIT=Y STREAMS_INSTANTIATION=Y

导出和导入时候没注意观察日志，数据初始化后开启应用进程就报如下错误。

ORA-26687: noinstantiation SCN provided for in sourcedatabase

后来查看导入导入日志

Legacy Mode Active dueto the following parameters:

Legacy Mode Parameter:"object_consistent=TRUE" Location: Command Line, ignored.

Legacy Mode Parameter:"statistics=NONE" Location: Command Line, ignored.

由于IMP/EXP 和数据泵的语法有些差距这几个参数被忽略了，特别是导入时候STREAMS_INSTANTIATION=Y，这个参数就是来设置流基于用户的初始化SCN信息，没了这个信息，应用进程应用时候就会报错

ORA-26687: noinstantiation SCN provided for in sourcedatabase

解决方案有2中。

方案1

执行如下SQL；

setserveroutput ON

DECLARE

iscn NUMBER; -- Variable to holdinstantiation SCN value

BEGIN

iscn :=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER@portaloa();

DBMS_OUTPUT.PUT_LINE ('Instantiation SCNis: ' || iscn);

DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(

source_schema_name => 'user_DEV',

source_database_name => 'portaloa',

instantiation_scn => iscn,

recursive => TRUE);

END;

方案2

在导入的时候加入数据泵对应的STREAMS_INSTANTIATION=Y参数STREAMS_CONFIGURATION=YES

STREAMS_CONFIGURATION

Default: YES

Purpose

Specifies whether to import anyStreams metadata that may be present in the export dump file.

Syntax and Description

STREAMS_CONFIGURATION=[YES | NO]

Example

The following is anexample of using the STREAMS_CONFIGURATION parameter. You can create the expfull.dmp dump file used inthis example by running the example provided for the Export FULL parameter.See "FULL".

> impdp hr DIRECTORY=dpump_dir1DUMPFILE=expfull.dmp STREAMS_CONFIGURATION=NO

但是即便这个参数被忽略掉了，那么它的默认值是YES，复制时候为什么，还是识别不了，这个却是个疑点。

ORA-24042: no propagation schedule exists for QUEUE PROD_QUEUE and DESTINATION

3.10 创建传播进程

　　#以strmadmin身份，登录主数据库。
　　connect strmadmin/strmadmin
　　begin
　　dbms_streams_adm.add_schema_propagation_rules(
　　schema_name => 'admin',
　　streams_name => 'szxddb_to_szxtdb',
　　source_queue_name => 'strmadmin.szxddb_queue',
　　destination_queue_name =>'strmadmin.szxtdb_queue@szxtdb',
　　include_dml => true,
　　include_ddl => true,
　　include_tagged_lcr => false,
　　source_database => 'szxddb',
　　inclusion_rule => true);
　　end;
　　/
　　#修改propagation休眠时间为0，表示实时传播LCR。
　　begin
　　dbms_aqadm.alter_propagation_schedule(
　　queue_name => 'szxddb_queue',
　　destination => 'szxtdb',
　　latency => 0);
　　end;
　　/

报错：
SQL> run
1 begin
2 dbms_aqadm.alter_propagation_schedule(
3 queue_name => 'prod_queue',
4 destination => 'szxtdb',
5 latency =>0);
6* end;
begin
*
ERROR at line 1:
ORA-24042: no propagation schedule exists for QUEUE PROD_QUEUE and DESTINATION
SZXTDB
ORA-06512: at "SYS.DBMS_PRVTAQIP", line 1421
ORA-06512: at "SYS.DBMS_AQADM", line 971
ORA-06512: at line 2

注：跳过以上报错，继续以下可以正常进行,后续查询相关资料，执行以下PL/SQL可以

SQL> begin
2 dbms_aqadm.alter_propagation_schedule(
3 queue_name => 'prod_queue',
4 destination => 'szxtdb',
5 destination_queue => 'szxtdb_queue',
6 latency => 5);
7 end;
8 /

PL/SQL procedure successfully completed.

--------------------------

col PROPAGATION_NAME for a15

SQL> col SOURCE_QUEUE_NAME for a15

SQL> col QUEUE_TO_QUEUE for a6

SQL> col error_message for a52

SQL> select PROPAGATION_NAME,SOURCE_QUEUE_NAME,QUEUE_TO_QUEUE,error_message from dba_propagation;

PROPAGAT SOURCE_Q QUEUE_TO ERROR_MESSAGE

-------- -------- -------- ----------------------------------------------------

QBJMES_T QBJMES_Q TRUE ORA-12545: Connect failed because target host or object does not exist

O_MES UEUE

!oerr ora 12545

解决：

一将tnsnames.ora listener.ora 中主机名改为IP地址

二启动传播进程

begin

dbms_propagation_adm.start_propagation(

propagation_name => 'qbjmes_to_mes');

end;

SQL> select APPLY_NAME,QUEUE_NAME,STATUS,ERROR_MESSAGE from dba_apply;

APPLY_NAME QUEUE_NAME STATUS

------------------------------ ------------------------------ --------

ERROR_MESSAGE

--------------------------------------------------------------------------------

APPLY_MES_QBJ MES_QUEUE_QBJ ABORTED

ORA-26714: User error encountered while applying

!oerr ora 26714

解决：

SQL> select APPLY_NAME,STATUS,ERROR_MESSAGE from dba_apply;

Apply

Process

Name STATUS Error Message

---------- -------- --------------------

APPLY_MES_ ENABLED

APPLY_MES_ ABORTED ORA- 26714: User erro

QBJ r encountered while

applying

SQL> exec dbms_apply_adm.start_apply('APPLY_MES_QBJ');

再次查询状态不行的话执行下面的清除错误信息：

BEGIN
DBMS_APPLY_ADM.EXECUTE_ERROR(
LOCAL_TRANSACTION_ID => '4.5.308567', <-------- LOCAL_TRANSACT
EXECUTE_AS_USER => FALSE);
END;
/

方法：手工将目标数据库中的值，更新为错误事务中OLD的值，然后重新执行EXECUTE_ERROR即可

ORA-26714: User erro

r encountered while

applying

dba_apply_error 错误：

1 ORA-01422: exact fetch returns more than requested number of rows

解决：

SQL> select error_message from dba_apply;

ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-26714: User error encountered while applying

SQL> !oerr ora 26714
26714, 00000, "User error encountered while applying"
// *Cause: An error was encountered while applying.
// *Action: Query the dba_apply_error view to determine the error and
// take the appropriate action.
SQL> select ERROR_MESSAGE from dba_apply_error;

ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-01422: exact fetch returns more than requested number of rows

SQL> !oerr ora 01422
01422, 00000, "exact fetch returns more than requested number of rows"
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested

stream配置中，capture和propagation都正常，但applyABORTED。
ORA-01422: exact fetch returns more than requested number of rows
对这个错误不明白返回的数据比请求的数据多？
莫非表中存在重复数据，导致apply进程不能确定要更新哪个记录？

STREAM是基于LCRS的。每个LCRS对应一个操作。如果库里面的记录是两个完全一样的记录，这时库就不知道应该怎么进行操作的复制了。看来设置主键是很有必要的，一定要求对准备复制的表设置主键!

解决：

重新启动目标库应用。
SQL> exec dbms_apply_adm.start_apply('APPLY_STANDY');

PL/SQL procedure successfully completed

SQL> SELECT apply_name, apply_captured, status,ERROR_MESSAGE FROM dba_apply;

APPLY_NAME APPLY_CAPTURED STATUS ERROR_MESSAGE
------------- -------------- -------- --------------------------------------------------
APPLY_STANDY YES ENABLED

查看目标库，CONS_ID索引已经接收到
SQL> select INDEX_NAME from DBA_indexes where owner='TEST01';

INDEX_NAME
------------------------------
P_ID
CONS_ID

再次查看错误记录信息，原先的错误还有。
SQL> select apply_name,LOCAL_TRANSACTION_ID,SOURCE_TRANSACTION_ID,ERROR_MESSAGE from dba_apply_error;

APPLY_NAME LOCAL_TRANSACT SOURCE_TRANSA ERROR_MESSAGE
------------- ---------------- ------------- ---------------------------------------------------------------
APPLY_STANDY 4.5. 308567 2.2.624 ORA-01422: exact fetch returns more than requested number of rows

可以打印这个错误信息号，看到old，new值确实是我刚才修改前的数值
SQL> EXEC print_transaction('4.5. 308567');

----- Local Transaction ID: 4.5. 308567
----- Source Database: LISHZ
----Error Number: 1422
----Message Text: ORA- 01422: exact fetch returns more than requested number of rows

--message: 1
type name: SYS.LCR$_ROW_RECORD
source database: LISHZ
owner: TEST01
object: T7
is tag null: Y
command_type: UPDATE
old(1): ID
1
new(1): ID
2
--message: 2
type name: SYS.LCR$_ROW_RECORD
source database: LISHZ
owner: TEST01
object: T7
is tag null: Y
command_type: UPDATE
old(1): ID
2
new(1): ID
3

PL/SQL procedure successfully completed

再次执行原先的事务，
SQL>
SQL> BEGIN
2      DBMS_APPLY_ADM.EXECUTE_ERROR(
3      LOCAL_TRANSACTION_ID => '4.5. 308567',
4      EXECUTE_AS_USER => FALSE);
5      END;
6 /

PL/SQL procedure successfully completed

查看错误记录表，已经都正常了。
SQL> select apply_name,LOCAL_TRANSACTION_ID,SOURCE_TRANSACTION_ID,ERROR_MESSAGE from dba_apply_error;

APPLY_NAME    LOCAL_TRANSACTION_ID   SOURCE_TRANSA ERROR_MESSAGE
------------- ---------------------- ------------- ------------------------

再次查看t7表，记录已经和原库同步了。
SQL> select * from test01.t7;

        ID
----------
         2
         3

2 ORA-01403: no data found

解决：

select apply_name,LOCAL_TRANSACTION_ID,SOURCE_TRANSACTION_ID,ERROR_MESSAGE

from dba_apply_error;

APPLY_NAME LOCAL_TRANSACTION_ID SOURCE_TRANSACTION_ID ERROR_MESSAGE

----------- ---------------------- ---------------------- -------------------------

APP97_APPLY 5.27.1273 4.46.576 ORA- 01403: no data found

对复制管理员进行授权：

SQL> GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;

Grant succeeded

SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;

Grant succeeded

此后需要建立几个过程

CREATE OR REPLACE PROCEDURE print_any (DATA IN ANYDATA)

tn VARCHAR2 (61);

str VARCHAR2 (4000);

CHR VARCHAR2 (1000);

num NUMBER;

dat DATE;

rw RAW (4000);

res NUMBER;

BEGIN

IF DATA IS NULL

THEN

DBMS_OUTPUT.put_line ('NULL value');

RETURN;

END IF;

tn := DATA.gettypename ();

IF tn = 'SYS.VARCHAR2'

THEN

res := DATA.getvarchar2 (str);

DBMS_OUTPUT.put_line (SUBSTR (str, 0, 253));

ELSIF tn = 'SYS.CHAR'

THEN

res := DATA.getchar (CHR);

DBMS_OUTPUT.put_line (SUBSTR (CHR, 0, 253));

ELSIF tn = 'SYS.VARCHAR'

THEN

res := DATA.getvarchar (CHR);

DBMS_OUTPUT.put_line (CHR);

ELSIF tn = 'SYS.NUMBER'

THEN

res := DATA.getnumber (num);

DBMS_OUTPUT.put_line (num);

ELSIF tn = 'SYS.DATE'

THEN

res := DATA.getdate (dat);

DBMS_OUTPUT.put_line (dat);

ELSIF tn = 'SYS.RAW'

THEN

-- res := data.GETRAW(rw);

-- DBMS_OUTPUT.PUT_LINE(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253));

DBMS_OUTPUT.put_line ('BLOB Value');

ELSIF tn = 'SYS.BLOB'

THEN

DBMS_OUTPUT.put_line ('BLOB Found');

ELSE

DBMS_OUTPUT.put_line ('typename is ' || tn);

END IF;

END print_any;

CREATE OR REPLACE PROCEDURE print_lcr (lcr IN ANYDATA)

typenm VARCHAR2 (61);

ddllcr SYS.lcr$_ddl_record;

proclcr SYS.lcr$_procedure_record;

rowlcr SYS.lcr$_row_record;

res NUMBER;

newlist SYS.lcr$_row_list;

oldlist SYS.lcr$_row_list;

ddl_text CLOB;

ext_attr ANYDATA;

BEGIN

typenm := lcr.gettypename ();

DBMS_OUTPUT.put_line ('type name: ' || typenm);

IF (typenm = 'SYS.LCR$_DDL_RECORD')

THEN

res := lcr.getobject (ddllcr);

DBMS_OUTPUT.put_line ( 'source database: '

|| ddllcr.get_source_database_name

);

DBMS_OUTPUT.put_line ('owner: ' || ddllcr.get_object_owner);

DBMS_OUTPUT.put_line ('object: ' || ddllcr.get_object_name);

DBMS_OUTPUT.put_line ('is tag null: ' || ddllcr.is_null_tag);

DBMS_LOB.createtemporary (ddl_text, TRUE);

ddllcr.get_ddl_text (ddl_text);

DBMS_OUTPUT.put_line ('ddl: ' || ddl_text);

-- Print extra attributes in DDL LCR

ext_attr := ddllcr.get_extra_attribute ('serial#');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ('serial#: ' || ext_attr.accessnumber ());

END IF;

ext_attr := ddllcr.get_extra_attribute ('session#');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ('session#: ' || ext_attr.accessnumber ());

END IF;

ext_attr := ddllcr.get_extra_attribute ('thread#');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ('thread#: ' || ext_attr.accessnumber ());

END IF;

ext_attr := ddllcr.get_extra_attribute ('tx_name');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ( 'transaction name: '

|| ext_attr.accessvarchar2 ()

);

END IF;

ext_attr := ddllcr.get_extra_attribute ('username');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ('username: ' || ext_attr.accessvarchar2 ());

END IF;

DBMS_LOB.freetemporary (ddl_text);

ELSIF (typenm = 'SYS.LCR$_ROW_RECORD')

THEN

res := lcr.getobject (rowlcr);

DBMS_OUTPUT.put_line ( 'source database: '

|| rowlcr.get_source_database_name

);

DBMS_OUTPUT.put_line ('owner: ' || rowlcr.get_object_owner);

DBMS_OUTPUT.put_line ('object: ' || rowlcr.get_object_name);

DBMS_OUTPUT.put_line ('is tag null: ' || rowlcr.is_null_tag);

DBMS_OUTPUT.put_line ('command_type: ' || rowlcr.get_command_type);

oldlist := rowlcr.get_values ('old');

FOR i IN 1 .. oldlist.COUNT

LOOP

IF oldlist (i) IS NOT NULL

THEN

DBMS_OUTPUT.put_line ('old(' || i || '): '

|| oldlist (i).column_name

);

print_any (oldlist (i).DATA);

END IF;

END LOOP;

newlist := rowlcr.get_values ('new', 'n');

FOR i IN 1 .. newlist.COUNT

LOOP

IF newlist (i) IS NOT NULL

THEN

DBMS_OUTPUT.put_line ('new(' || i || '): '

|| newlist (i).column_name

);

print_any (newlist (i).DATA);

END IF;

END LOOP;

-- Print extra attributes in row LCR

ext_attr := rowlcr.get_extra_attribute ('row_id');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ('row_id: ' || ext_attr.accessurowid ());

END IF;

ext_attr := rowlcr.get_extra_attribute ('serial#');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ('serial#: ' || ext_attr.accessnumber ());

END IF;

ext_attr := rowlcr.get_extra_attribute ('session#');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ('session#: ' || ext_attr.accessnumber ());

END IF;

ext_attr := rowlcr.get_extra_attribute ('thread#');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ('thread#: ' || ext_attr.accessnumber ());

END IF;

ext_attr := rowlcr.get_extra_attribute ('tx_name');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ( 'transaction name: '

|| ext_attr.accessvarchar2 ()

);

END IF;

ext_attr := rowlcr.get_extra_attribute ('username');

IF (ext_attr IS NOT NULL)

THEN

DBMS_OUTPUT.put_line ('username: ' || ext_attr.accessvarchar2 ());

END IF;

ELSE

DBMS_OUTPUT.put_line ('Non-LCR Message with type ' || typenm);

END IF;

END print_lcr;

3 现在就可以使用print_errors来打印出详细的错误信息，

但是注意，如果错误事务非常多，那么这个过程可能会非常耗时：

CREATE OR REPLACE PROCEDURE print_errors

CURSOR c

SELECT local_transaction_id, source_database, message_number,

message_count, error_number, error_message

FROM dba_apply_error

ORDER BY source_database, source_commit_scn;

i NUMBER;

txnid VARCHAR2 (30);

SOURCE VARCHAR2 (128);

msgno NUMBER;

msgcnt NUMBER;

errnum NUMBER := 0;

errno NUMBER;

errmsg VARCHAR2 (255);

lcr ANYDATA;

r NUMBER;

BEGIN

FOR r IN c

LOOP

errnum := errnum + 1;

msgcnt := r.message_count;

txnid := r.local_transaction_id;

SOURCE := r.source_database;

msgno := r.message_number;

errno := r.error_number;

errmsg := r.error_message;

DBMS_OUTPUT.put_line

('*************************************************');

DBMS_OUTPUT.put_line ('----- ERROR #' || errnum);

DBMS_OUTPUT.put_line ('----- Local Transaction ID: ' || txnid);

DBMS_OUTPUT.put_line ('----- Source Database: ' || SOURCE);

DBMS_OUTPUT.put_line ('----Error in Message: ' || msgno);

DBMS_OUTPUT.put_line ('----Error Number: ' || errno);

DBMS_OUTPUT.put_line ('----Message Text: ' || errmsg);

FOR i IN 1 .. msgcnt

LOOP

DBMS_OUTPUT.put_line ('--message: ' || i);

lcr := DBMS_APPLY_ADM.get_error_message (i, txnid);

print_lcr (lcr);

END LOOP;

END print_errors;

3 最后创建一个print_transaction过程可以用来打印输出指定事务的详细信息：

CREATE OR REPLACE PROCEDURE print_transaction (ltxnid IN VARCHAR2)

i NUMBER;

txnid VARCHAR2 (30);

SOURCE VARCHAR2 (128);

msgno NUMBER;

msgcnt NUMBER;

errno NUMBER;

errmsg VARCHAR2 (128);

lcr ANYDATA;

BEGIN

SELECT local_transaction_id, source_database, message_number,

message_count, error_number, error_message

INTO txnid, SOURCE, msgno,

msgcnt, errno, errmsg

FROM dba_apply_error

WHERE local_transaction_id = ltxnid;

DBMS_OUTPUT.put_line ('----- Local Transaction ID: ' || txnid);

DBMS_OUTPUT.put_line ('----- Source Database: ' || SOURCE);

DBMS_OUTPUT.put_line ('----Error in Message: ' || msgno);

DBMS_OUTPUT.put_line ('----Error Number: ' || errno);

DBMS_OUTPUT.put_line ('----Message Text: ' || errmsg);

FOR i IN 1 .. msgcnt

LOOP

DBMS_OUTPUT.put_line ('--message: ' || i);

lcr := DBMS_APPLY_ADM.get_error_message (i, txnid); -- gets the LCR

print_lcr (lcr);

END LOOP;

END print_transaction;

4 现在来看看这个失败的事务：

SQL> SET SERVEROUTPUT ON SIZE 1000000

SQL> EXEC print_transaction('5.27.1273')

这几个过程在流复制的故障诊断中非常有用，记录于此。

---------------------------------

SQL> EXEC print_transaction('6.27.352')

----- Local Transaction ID: 6.27.352

----- Source Database: QBJMES

----Error in Message: 2

----Error Number: 942

----Message Text: ORA-00942: table or view does not exist

--message: 1

type name: SYS.LCR$_DDL_RECORD

source database: QBJMES

owner: QBJMES

object: MHZ2

is tag null: Y

ddl: ALTER TABLE "QBJMES"."MHZ2" RENAME TO "BIN$5bFfr/CTV8zgQBCsKgoXgw==$0"

--message: 2

type name: SYS.LCR$_DDL_RECORD

source database: QBJMES

owner: QBJMES

object: MHZ2

is tag null: Y

ddl: drop table mhz2 AS "BIN$5bFfr/CTV8zgQBCsKgoXgw==$0"

--message: 3

type name: SYS.LCR$_PROCEDURE_RECORD

Non-LCR Message with type SYS.LCR$_PROCEDURE_RECORD

3 ORA-00942: table or view does not exist（处理方式同2）

select APPLY_NAME,LOCAL_TRANSACTION_ID,SOURCE_COMMIT_SCN,

ERROR_MESSAGE from dba_apply_error;

APPLY_NAME LOCAL_TRANSACTION_ID SOURCE_COMMIT_SCN

------------------------------ ---------------------- -----------------

MESSAGE_NUMBER

ORA-00942: table or view does not exist

--------------------------------------------------------------------------------

APPLY_MES_QBJ 6.27.352 3668001

解决：

处理方式同2 查id---建存储过程----打印错误----清除错误。

4 ORA-26687: no instantiation SCN provided for "QBJMES"."" in source database "QBJMES"

SQL> select APPLY_NAME,QUEUE_NAME,ERROR_MESSAGE,SOURCE_COMMIT_SCN from DBA_APPLY_ERROR;

APPLY_NAME QUEUE_NAME

------------------------------ ------------------------------

ERROR_MESSAGE

--------------------------------------------------------------------------------

SOURCE_COMMIT_SCN

-----------------

APPLY_MES_QBJ MES_QUEUE_QBJ

ORA-26687: no instantiation SCN provided for "QBJMES"."" in source database "QBJMES"

!oerr ora 26687

解：

select APPLY_NAME,LOCAL_TRANSACTION_ID,SOURCE_COMMIT_SCN,MESSAGE_NUMBER,ERROR_MESSAGE from dba_apply_error;

源

begin

DBMS_APPLY_ADM.set_table_instantiation_scn(

source_object_name => 'qbjmes',

source_database_name => 'qbjmes',

instantiation_scn => 3556816);

end;

目标

BEGIN

DBMS_APPLY_ADM.EXECUTE_ERROR(

local_transaction_id => '10.31.322',

execute_as_user => FALSE,

user_procedure => NULL);

END;

2、非法的数据类型：Invalid data type for column "malformed redo"。malformed redo列，不知道这个列，google了一下，据说是个bug（Bug 5623403），可怜没有metalink账号。由于capture遭遇到不支持的数据类型，默认会自动终于capture，可以看到状态变为ABORTED。
alert日志：