[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

[20190415]ora-02049錯誤.txt

相關文章