Home » RDBMS Server » Server Administration » ORA-01591: lock held by in-doubt distributed trans - I hope to help more people (Oracle 10g, cross plat form)
ORA-01591: lock held by in-doubt distributed trans - I hope to help more people [message #415270] Mon, 27 July 2009 00:19
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Dear all!
Today, I have got some error in alert_log file
Quote:

Sun Jul 26 10:15:57 2009
Error 2053 trapped in 2PC on transaction 21.19.82240. Cleaning up.
Error stack returned to user:
ORA-02054: transaction 21.19.82240 in-doubt
ORA-02053: transaction 131.32.485 committed, some remote DBs may be in-doubt
ORA-29740: evicted by member , group incarnation



Then, some user feedback to me they can not query - select count(1), insert, update.. from an object -
KETNOI_CATMO.
I checked the information in sys.pending_trans$, sys.pending_sessions$, and I realized one lost transaction
which had got state as PREPARED. Sorry if I can not post detail here, because I've resolved it as following:

-- The lost transaction is '21.19.82240'
1. Select * from sys.pending_trans$
LOCAL_TRAN_ID        GLOBAL_TRAN_ID       	STATE
-------------------- -------------------- 	--------------
21.19.82240          VNP.RDBMS.ORACLE.COM  	prepared


2. SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
    KTUXESTA Status,
    KTUXECFL Flags
    FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 21

3. -- Simulate a transaction to insert into pending_trans$
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( '21.19.82240',
    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 );

commit;

insert into pending_sessions$ values('21.19.82240',1,
    hextoraw('05004F003A1500000104'),'C',0,30258592,'',146);

commit;

4. -- Rollback the simulator transaction
rollback force '21.19.82240';


OKay, at the end, I released the lock from object KETNOI_CATMO, but...

In sys.pending_trans$, still existing 2 pending transaction
logvnp@VNP> col local_tran_id format a15
logvnp@VNP> col global_tran_id format a15
logvnp@VNP> col state format a9
logvnp@VNP> col host format a12
logvnp@VNP> select local_tran_id,
  2  global_tran_id,
  3  state,
  4  host,
  5  commit#
  6  from dba_2pc_pending;

LOCAL_TRAN_ID   GLOBAL_TRAN_ID  STATE     HOST         COMMIT#
--------------- --------------- --------- ------------ -------------
18.6.950447     XXXXXXX.12345.1 prepared
                .2.3

10.15.4693      XXXXXXX.12345.1 prepared
                .2.3


Oap, I released 1 lost transaction, but I've got 2 transactions at the end:(
Of course, they're fake, and they did not cause lock to my db, however, I must find the way to resolve them.
Seeking in metalink, I found a note as

How To Resolve Stranded DBA_2PC_PENDING Entries

Now, take carefull to read it. In memory, we have 3 ways, but 3 ways are really lost transaction. What have I got? The faking, no more.
I delete them from sys.pending_trans
sys@VNP> SELECT LOCAL_TRAN_ID,STATE FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID          STATE
---------------------- ----------------
18.6.950447            prepared
10.15.4693             prepared

sys@VNP> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,
  3  KTUXECFL Flags
  4  FROM x$ktuxe
  5  WHERE ktuxesta!='INACTIVE'
  6  AND ktuxeusn=18
  7  /

no rows selected

sys@VNP> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,
  3  KTUXECFL Flags
  4  FROM x$ktuxe
  5  WHERE ktuxesta!='INACTIVE'
  6  AND ktuxeusn=10
  7  /

no rows selected.

sys@VNP> select * from sys.pending_trans$
  2  /

LOCAL_TRAN_ID          GLOBAL_TRAN_FMT
---------------------- ---------------
GLOBAL_ORACLE_ID
----------------------------------------------------------------
GLOBAL_FOREIGN_ID
--------------------------------------------------------------------------------
--------------------
TRAN_COMMENT
--------------------------------------------------------------------------------
--------------------
STATE            S H SESSION_ RECO_VEC      TYPE# FAIL_TIME HEURISTIC RECO_TIME
---------------- - - -------- -------- ---------- --------- --------- ---------
TOP_DB_USER                    TOP_OS_USER
------------------------------ -------------------------------------------------
---------------
TOP_OS_HOST
--------------------------------------------------------------------------------
--------------------
TOP_OS_TERMINAL
--------------------------------------------------------------------------------
--------------------
GLOBAL_COMMIT#       SPARE1 SPARE2                             SPARE3 SPARE4
---------------- ---------- ------------------------------ ---------- ----------
--------------------
18.6.950447                     306206
XXXXXXX.12345.1.2.3


prepared         P   00000001 00000000          0 27-JUL-09           27-JUL-09





10.15.4693                      306206
XXXXXXX.12345.1.2.3


prepared         P   00000001 00000000          0 27-JUL-09           27-JUL-09






sys@VNP> delete from sys.pending_trans$ where local_tran_id='10.15.4693';

1 row deleted.

sys@VNP> select * from pending_trans$
  2  /

LOCAL_TRAN_ID          GLOBAL_TRAN_FMT
---------------------- ---------------
GLOBAL_ORACLE_ID
----------------------------------------------------------------
GLOBAL_FOREIGN_ID
--------------------------------------------------------------------------------
--------------------
TRAN_COMMENT
--------------------------------------------------------------------------------
--------------------
STATE            S H SESSION_ RECO_VEC      TYPE# FAIL_TIME HEURISTIC RECO_TIME
---------------- - - -------- -------- ---------- --------- --------- ---------
TOP_DB_USER                    TOP_OS_USER
------------------------------ -------------------------------------------------
---------------
TOP_OS_HOST
--------------------------------------------------------------------------------
--------------------
TOP_OS_TERMINAL
--------------------------------------------------------------------------------
--------------------
GLOBAL_COMMIT#       SPARE1 SPARE2                             SPARE3 SPARE4
---------------- ---------- ------------------------------ ---------- ----------
--------------------
18.6.950447                     306206
XXXXXXX.12345.1.2.3


prepared         P   00000001 00000000          0 27-JUL-09           27-JUL-09






sys@VNP> delete from sys.pending_trans$ where local_tran_id='18.6.950447';

1 row deleted.

sys@VNP> select * from pending_trans$
  2  /

no rows selected

sys@VNP> commit;

Commit complete.

sys@VNP> select * from pending_trans$
  2  /

no rows selected





[Updated on: Mon, 27 July 2009 00:56] by Moderator

Report message to a moderator

Previous Topic: Oracle 11g with compression feature
Next Topic: How to Creating dictionary managed tablespace in 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jul 03 01:56:40 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.