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

Steps to handle the In-Doubt Transactions ORA-01591 in Distributed Database

One of the application support guy asked me for help. He received the following error.

Error starting at line 3 in command:

update scv_cv_publish_xml set status = 'A' where status = 'V' and overworking_status='A'

Error report:
SQL Error: ORA-01591: lock held by in-doubt distributed transaction 63.8.92859
ORA-01591 – “lock held by in-doubt distributed transaction %s”
Cause: Trying to access a resource that is locked by a dead two-phase commit transaction that is in a prepared state.
Action: DBA should query the pending_trans$ and related tables, and attempt to repair network connection(s) to the coordinator and commit point. If timely repair is not possible, DBA should contact DBA at commit point if known or end user for correct outcome, or use heuristic default if given to issue a heuristic commit or abort command to finalize the local portion of the distributed transaction.

Solution

1. Login to database and check the transaction.

SQL> select local_tran_id, state from dba_2pc_pending where local_tran_id='63.8.92859';

LOCAL_TRAN_ID  STATE
-------------  ---------
3.8.92859      prepared

SQL> select a.sql_text, s.osuser, s.username from v$transaction t, v$session s, v$sqlarea a where s.taddr = t.addr and a.address = s.prev_sql_addr and t.xidusn = 63 and t.xidslot = 8 and t.xidsqn = 92859;
no rows selected

2. How To Resolve DBA_2PC_PENDING Entries [ID 401302.1].

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */   KTUXESTA Status, KTUXECFL Flags FROM x$ktuxe  WHERE ktuxesta != 'INACTIVE'   AND ktuxeusn= 63;

KTUXEUSN KTUXESLT KTUXESQN STATUS   FLAGS
-------- -------- -------- -------- ------------------------
63       8        92859    PREPARED SCO|COL|REV|DEAD|EXTDTX

SQL> rollback force '63.8.92859';
Rollback complete.
SQL> commit;
Commit complete.
SQL> select local_tran_id, state from dba_2pc_pending where local_tran_id='63.8.92859';

LOCAL_TRAN_ID  STATE
-------------  --------------- 
63.8.92859     forced rollback

3. Check with the application team, if still get error then follow the following steps

SQL> delete from sys.pending_trans$ where local_tran_id='63.8.92859';
1 row deleted.

sys@avac> delete from sys.pending_sessions$ where local_tran_id ='63.8.92859';
1 row deleted.

sys@avac> delete from sys.pending_sub_sessions$ where local_tran_id = '63.8.92859';
0 rows deleted.

sys@avac> commit;
Commit complete.

sys@avac> select local_tran_id, state from dba_2pc_pending where local_tran_id='63.8.92859';
no rows selected