[20190415]ora-02049错误.txt

转载于 2019-04-17 11:18:27 发布 · 174 阅读

本文详细记录了一次解决Oracle数据库中ORA-02049错误的过程，该错误与分布式事务等待锁定超时有关。文章分析了错误原因，提供了具体的解决步骤，并通过查询相关视图定位了问题事务。

[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