首页 > 数据库 > Oracle > [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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2641623/,如需转载,请注明出处,否则将追究法律责任。