今天一个朋友遇到的问题,告警日志中出现如下信息:
ORA-1591:lockheldbyin-doubtdistributedtransaction1.92.66874
查询dba_2pc的表,发现没有分布式事务信息:
selectlocal_tran_id,statefromdba_2pc_pending where local_tran_id='1.92.66874';
no rowsselected
但是去查实际的rollback segment信息,却发现有prepared状态的分布式事务存在:
SELECTKTUXEUSN,KTUXESLT,KTUXESQN,/* Transaction ID */
KTUXESTA Status,KTUXECFL FlagsFROM x$ktuxeWHERE ktuxesta!='INACTIVE'AND ktuxeusn=1;
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ---------------- ------------------------
1 92 66874 PREPARED SCO|COL|REV|DEAD
我们无法做commit force或者rollback force:
rollbackforce'1.92.66874';
ORA-02058:no preparedtransactionfoundwithID1.92.66874
遇到这种情况,只能制造虚假数据,再模拟清理未提交的分布式事务;
alter system disable distributed recovery;
INSERT INTO PENDING_TRANS$
(LOCAL_TRAN_ID,
GLOBAL_TRAN_FMT,
GLOBAL_ORACLE_ID,
STATE,
STATUS,
SESSION_VECTOR,
RECO_VECTOR,
TYPE#,
FAIL_TIME,
RECO_TIME)
VALUES
('1.92.66874', /* <== 此处为你的local tran id */
306206, /* */
'XXXXXXX.12345.1.2.3', /* 这些值不必更改, */
'prepared',
'P', /* 是静态参数,可以直接 */
HEXTORAW('00000001'), /* 在这个sql中使用 */
HEXTORAW('00000000'), /* */
0,
SYSDATE,
SYSDATE);
INSERT INTO PENDING_SESSIONS$
VALUES
('1.92.66874', /* <==此处为你的local tran id */
1,
HEXTORAW('05004F003A1500000104'),
'C',
0,
30258592,
'',
146);
commit;
-----------------------------------------------------------------------------------------
QQ:49904358
http://space.itpub.net/14941137/spacelist-blog