[20190415]ora-02049错误.txt

[20190415]ora-02049错误.txt --//前几天遇到的问题,这几天探究latch,没有马上解决彻底,今天在看看, --//很古老的旧系统(192.168.xxx.xx)出现问题,ora-02049错误. ORA-02049: time-out: distributed transaction waiting for lock $  oerr ora 2049 02049, 00000, "timeout: distributed transaction waiting for lock" // *Cause: exceeded INIT.ORA distributed_lock_timeout seconds waiting for lock. // *Action: treat as a deadlock --//当作1个死锁,什么意思. 1.环境: SYS@orcl> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ---------------------------------------------------------------- x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi SYS@orcl> select * from DBA_2PC_PENDING; LOCAL_TRAN_ID GLOBAL_TRAN_ID                        STATE      MIXED   ADVICE  TRAN_COMMENT FAIL_TIME          FORCE_TIME RETRY_TIME        OS_USER       OS_TERMINAL   HOST                    DB_USER COMMIT# ------------- ------------------------------------- ---------- ------- ------- ------------ ------------------ ---------- ----------------- ------------- ------------- ----------------------- ------- ----------- 10.40.544086  1000.A02F73E8DA45D2C8FF2B6C348158B393 prepared      no                        2015-3-31 17:26:39            2019-3-27 7:41:04 Administrator PC-ZXSSGYS    WORKGROUP\PC-ZXSSGYS            12660075699 45.95.4537    1000.C36C893F479A009F75F05132E4FD3F45 prepared      no                        2015-3-31 17:46:46            2019-3-27 7:41:04 Administrator GXRMYYBAO1-PC WORKGROUP\GXRMYYBAO1-PC         12660231947 --//奇怪FAIL_TIME是2015-3-31 17:26:39,RETRY_TIME时间是2019-3-27 7:41:04.难道这么久没有人访问对应记录吗?或者再执行DML时才会报错. --//忘记问一下操作人员2019-3-27 7:41:04执行什么DML操作了. SYS@orcl> select * from DBA_2PC_NEIGHBORS ; LOCAL_TRAN_ID          IN_ DATABASE DBUSER_OWNER I DBID SESS# BRANCH ---------------------- --- -------- ------------ - ---- ----- -------------------------------- 45.95.4537             in  orcl     XXXYYY       N orcl     1 6273FAC251C618479219637D5C2790F9 10.40.544086           in  orcl     XXXYYY       N orcl     1 7F0D54DCF83BFA4195B749C59D0B99D5 2.解决方法: --//解决方法如下,以sys用户执行: set transaction use rollback segment SYSTEM; commit force '&&x'; alter system enable distributed recovery; exec dbms_transaction.purge_lost_db_entry( '&&x'); commit; --//X 分别带入10.40.544086, 45.95.4537. set transaction use rollback segment SYSTEM; commit force '10.40.544086'; alter system enable distributed recovery; exec dbms_transaction.purge_lost_db_entry( '10.40.544086'); --//执行结果如下: Transaction set. SYS@orcl> commit force '10.40.544086' * ERROR at line 1: ORA-02058: no prepared transaction found with ID 10.40.544086 SYS@orcl> System altered. SYS@orcl> PL/SQL procedure successfully completed. SYS@orcl> commit; Commit complete. set transaction use rollback segment SYSTEM; Transaction set. commit force '45.95.4537'; commit force '45.95.4537' * ERROR at line 1: ORA-02058: no prepared transaction found with ID 45.95.4537 alter system enable distributed recovery; System altered. exec dbms_transaction.purge_lost_db_entry( '45.95.4537'); PL/SQL procedure successfully completed. SYS@orcl> commit; Commit complete. --//执行完成,再次查询: select * from DBA_2PC_PENDING; select * from DBA_2PC_NEIGHBORS ; --//已经没有显示.以前遇到的都是:ORA-01591: lock held by in-doubt distributed transaction 285.27.35251.第1次遇到这样的情况. --//打电话,叫用户执行相关操作,已经不再报错. --//我看了网上一些链接,查看死锁的进程,我这里根本看不到死锁以及阻塞的情况. SELECT   S.USERNAME,  DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,  O.OWNER,  O.OBJECT_NAME,  O.OBJECT_TYPE,  S.SID,  S.SERIAL#,  S.TERMINAL,  S.MACHINE,  S.PROGRAM,  S.OSUSER   FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O  WHERE L.SID = S.SID    AND L.ID1 = O.OBJECT_ID(+)    AND S.USERNAME IS NOT NULL; --//仅仅做一个记录. 3.一些探究: SYS@book> @ slottoxid.sql 45 95 4537 2D005F00B9110000 --//脚本很简单,转换16进制,大小头对调就ok了. --//比如 : 4537=0x11b9 ,后4位就是 0xb9110000. SYS@orcl> select xid,start_scn,commit_timestamp,operation,table_name,row_id,undo_sql from FLASHBACK_TRANSACTION_QUERY where xid=hextoraw('2D005F00B9110000'); XID                 START_SCN COMMIT_TIMESTAMP    OPERATION TABLE_NAME ROW_ID              UNDO_SQL ---------------- ------------ ------------------- --------- ---------- ------------------- ------------------------------------------------------------ 2D005F00B9110000  12660231946 2019-04-15 16:01:18 INSERT    SYSLOG     AAA24EAAiAACBFuAA6  delete from "XXXYYY"."SYSLOG" where ROWID = 'AAA24EAAiAACBFu                                                                                            AA6'; 2D005F00B9110000  12660231946 2019-04-15 16:01:18 UPDATE    FLOWDISINF AAA22ZAAiAACCmvAAB  update "XXXYYY"."FLOWDISINFECTCONTAINERLIST" set "STATUS" =                                                             ECTCONTAIN                     '0', "CHECKID" = NULL, "CHECKDATE" = NULL where ROWID = 'AAA                                                             ERLIST                         22ZAAiAACCmvAAB'; 2D005F00B9110000  12660231946 2019-04-15 16:01:18 UPDATE    FLOWDISINF AAA22ZAAiAACCmvAAA  update "XXXYYY"."FLOWDISINFECTCONTAINERLIST" set "STATUS" =                                                             ECTCONTAIN                     '0', "CHECKID" = NULL, "CHECKDATE" = NULL where ROWID = 'AAA                                                             ERLIST                         22ZAAiAACCmvAAA'; 2D005F00B9110000  12660231946 2019-04-15 16:01:18 UPDATE    CONTAINER  AAA21vAAiAAAAEKAAH  update "XXXYYY"."CONTAINER" set "CONTAINERID" = 'BCA070BE-17                                                                                            D3-4E62-8940-7E20471088F2', "CONTAINERNAME" = '手术一区00006                                                                                            ', "BARCODE" = '1290184', "CONTAINERIMAGE" = NULL, "WASHTYPE                                                                                            " = '-1', "ISDISABLED" = '0', "MODIFIER" = 'E6C8B618-6282-41                                                                                            49-8D21-FFB9FB6E88E4', "MODIFYTIME" = TO_DATE('2015-03-31 17                                                                                            :43:42', 'YYYY-MM-DD HH24:MI:SS'), "WASHTYPENOW" = '0', "DEV                                                                                            ICELOGID" = '6DD86D9C-FE25-4A89-9C17-A4D1A1735E3B', "STATUS"                                                                                             = '0', "REMARK" = NULL, "FRECYCLEID" = 'A596601D-5862-498A-                                                                                            AF0D-EDE3F938361C', "WASHDATE" = TO_DATE('2015-03-31 17:43:4                                                                                            2', 'YYYY-MM-DD HH24:MI:SS'), "DEFAULTCOLOR" = '0', "PACKAGE                                                                                            BARCODE" = NULL, "FPACKAGETYPE" = NULL, "PINYIN" = 'SSYQ0000                                                                                            6', "CONTAINERTYPE" = NULL, "FDISINFECTID" = '6DD86D9C-FE25-                                                                                            4A89-9C17-A4D1A1735E3B', "ISDISINFECTONLY" = '0' where ROWID                                                                                             = 'AAA21vAAiAAAAEKAAH'; 2D005F00B9110000  12660231946 2019-04-15 16:01:18 BEGIN --//START_SCN=12660231946,与查询select * from DBA_2PC_PENDING;的COMMIT# = 12660231947 相差1. --//昏!开始忘记记录操作前的FLASHBACK_TRANSACTION_QUERY视图的输出了. --//当前的scn如下,难道我执行的脚本提交2015-3-31 17:46:46的事务吗? 开句玩笑,我提交了4年前的2个事务. SYS@orcl> select current_scn from v$database;  CURRENT_SCN ------------  27650907754