关于为何无法访问in-doubt transcationa锁住的表

lory

version:10.2.0.5
od:winows 2008 64 bit
当有表被in-doubt transaction锁住，其他任何人都无法访问，会报
ORA-01591: lock held by in-doubt distributed transaction 6.28.1674

为何不能访问呢，文档里面是说
NOTE: Reads are blocked because, until the transaction is resolved,Oracledoes not assume which version of the data to display for a query user.

为何不能从undo里面恢复数据给read看呢

Maclean Liu(刘相兵

ODM FINDING:

How to PURGE the DISTRIBUTED transaction in PREPARED state, when COMMIT
or ROLLBACK FORCE hangs ?, where we have entries for both Distributed
transaction and dba_2pc entries.
ORA-01591: lock held by in-doubt distributed transaction 44.88.85589
The row exist from dba_2pc_pending & Rollback segment
SQL> SELECT LOCAL_TRAN_ID,STATE FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID STATE
----------------- -----------
44.88.85589 prepared
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE'
AND ktuxeusn= 44; /* <== Replace this value with your txn undo seg#
Which is displayed in the first part of
the transaction ID */
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
---------- ---------- ---------- ---------------- ------------------------
44 88 85589 PREPARED SCO|COL|REV|DEAD
SQL> Commit force 44.88.85589;
SQL> rollback force 44.88.85589;




ORA-01591 处理过程

故障处理：
1.        检查系统基表：
Select KTUXEUSN,KTUXESLT,KTUXESQN
from X$KTUXE
where KTUXESTA='PREPARED'
and KTUXECFL like '%REV%';

共有7条记录显示
确定系统存在问题
2.        处理故障
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(  '140.7.1173914', /* <== Replace this with your local tran id */
       306206,                  /*                                         */
       'XXXXXXX.12345.1.2.3',   /*  These values can be used without any    */
       'prepared','P',          /*  modification. Most of the values are   */
       hextoraw( '00000001' ),  /*  constant.                              */
       hextoraw( '00000000' ),  /*                                         */
       0, sysdate, sysdate );

    insert into pending_sessions$
    values(  '140.7.1173914',/* <==Replace only this with your local tran id */
        1, hextoraw('05004F003A1500000104'),
        'C', 0, 30258592, '',
       146
     );
commit;

alter system disable distributed recovery; (需要双实例上执行)
commit force '140.7.1173914 ';
alter system enable distributed recovery; (需要双实例上执行)
exec dbms_transaction.purge_lost_db_entry('140.7.1173914 ');
commit;

分别处理了7条记录

3.        检查基表信息
Select KTUXEUSN,KTUXESLT,KTUXESQN
from X$KTUXE
where KTUXESTA='PREPARED'
and KTUXECFL like '%REV%';

no rows selected

建议
把基表检查列入日常巡检，及时处理

Maclean Liu(刘相兵

NOTE: Reads are blocked because, until the transaction is resolved,Oracle does not assume which version of the data to display for a query user.


==> 读一致性 需要确认构建哪一个版本的cr , 可能是由于in-doubt transaction  的相关undo 造成了oracle不知道该回滚到哪一个scn 造成了该问题，具体需要测试