eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Streams流复制的异常检测 | Blog首页 | 我家有儿初长成-恩墨三月记 »

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
----------- ---------------------- ---------------------- -------------------------


历史上的今天...
    >> 2015-11-08文章:
    >> 2011-11-08文章:
    >> 2009-11-08文章:
    >> 2006-11-08文章:
    >> 2005-11-08文章:
           离开UT
    >> 2004-11-08文章:
           滚滚红尘
           月光-王心凌
           Rattle and Burn
           九月的高跟鞋
           Lemon Tree(柠檬树)
           纯真年代
           推荐第一首歌:同步过冬
           上海滩

无觅

By eygle on 2007-11-08 15:41 | Comments (3) | Advanced | 1660 |

3 Comments

谢谢大师了,又学会了一招:-)
昨晚发现,做完后还要Stop,Start应用进程。不然的话Apply进程会停留在Aborted状态。
就是想知道如何进行“冲突处理”,书上讲的看得不是太懂。

是事先的解决方法,不是这样事后的方法,

print_transaction 是哪里来的?


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com