streams流复制ORA-01403错误解决一则

在Streams复制中，很多时候会遇到ORA-01403: no data found的错误（定义冲突解决方案很重要）：

SQL> select apply_name,LOCAL_TRANSACTION_ID,SOURCE_TRANSACTION_ID,ERROR_MESSAGE
  2  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

通过print_transaction可以找到相关事务的出错原因：

SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> EXEC print_transaction('5.27.1273')

----- Local Transaction ID: 5.27.1273
----- Source Database: TEST201.EYGLE.COM
----Error in Message: 1
----Error Number: 1403
----Message Text: ORA-01403: no data found

--message: 1
type name: SYS.LCR$_ROW_RECORD
source database: TEST201.EYGLE.COM
owner: SCOTT
object: DEPT
is tag null: Y
command_type: UPDATE
old(1): DEPTNO
50
old(2): LOC
CHINA
new(1): LOC
CHINA

如果能够定位错误数据，可以手工纠正一下：

SQL> connect scott/tiger
Connected.
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 EYGLE          BEIJING
        10 ACCOUNTING    NEW YORK
        20 RESEARCH      DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS    BOSTON

SQL> update dept set loc='CHINA' where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

然后再次执行原事务，该事务就可以被正常执行：

SQL> BEGIN
  2  DBMS_APPLY_ADM.EXECUTE_ERROR(
  3  LOCAL_TRANSACTION_ID => '5.27.1273',
  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
  2  from dba_apply_error
  3  /

APPLY_NAME  LOCAL_TRANSACTION_ID  SOURCE_TRANSACTION_ID  ERROR_MESSAGE
----------- ---------------------- ---------------------- -------------------------