[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