Edi Yanto (何 萬 新)

ORA-01591: lock held by in-doubt distributed transaction

Posted on: May 31, 2011


If you have rows on the view DBA_2PC_PENDING, do the below actions (Just enter the TRANSACTION ID, got from the ORA-1591 error):

Connect as SYS or SYSDBA

DELETE FROM pending_trans$ WHERE local_tran_id = ‘&TRANSACTION_ID’;
DELETE FROM pending_sessions$ WHERE local_tran_id = ‘&TRANSACTION_ID’;

If you don’t have rows on the view DBA_2PC_PENDING, do the below actions (insert dummy rows into SYS.PENDING_TRANS$, and SYS.PENDING_SESSIONS$ tables to correct this, and just enter the TRANSACTION ID, got from the ORA-1591 error):

Connect AS SYS OR SYSDBA

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 (‘&TRANSACTION_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 (‘&TRANSACTION_ID’,
1,
HEXTORAW (‘05004F003A1500000104’),
‘C’,
0,
30258592,
”,
146);
COMMIT;
ROLLBACK FORCE ‘&TRANSACTION_ID’;    /* or commit force */
COMMIT;
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
EXEC dbms_transaction.purge_lost_db_entry( ‘&TRANSACTION_ID’ );
COMMIT;
DELETE FROM pending_trans$ WHERE local_tran_id = ‘&TRANSACTION_ID’;
DELETE FROM pending_sessions$ WHERE local_tran_id = ‘&TRANSACTION_ID’;
COMMIT;

Determining When to Use DBMS_TRANSACTION

The following tables indicates what the various states indicate about the distributed transaction what the administrator’s action should be:

STATE Column State of Global Transaction State of Local Transaction Normal Action Alternative Action

Collecting

Rolled back

Rolled back

None

PURGE_LOST_DB_ENTRY (only if autorecovery cannot resolve transaction)

Committed

Committed

Committed

None

PURGE_LOST_DB_ENTRY (only if autorecovery cannot resolve transaction)

Prepared

Unknown

Prepared

None

Force commit or rollback

Forced commit

Unknown

Committed

None

PURGE_LOST_DB_ENTRY (only if autorecovery cannot resolve transaction)

Forced rollback

Unknown

Rolled back

None

PURGE_LOST_DB_ENTRY (only if autorecovery cannot resolve transaction)

Forced commit

Mixed

Committed

Manually remove inconsistencies then use PURGE_MIXED

Forced rollback

Mixed

Rolled back

Manually remove inconsistencies then use PURGE_MIXED

4 Responses to "ORA-01591: lock held by in-doubt distributed transaction"

thanks for sharing it a very useful post

Like

You ROCK! Thanx man, saved me…

Like

Great ,Thanks for the quote it helped us

Like

excelent post. just miss sys.pending_sub_sessions$;

Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

May 2011
M T W T F S S
« Apr   Jan »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: