unhandled exception while purging the dba_2pc_pending transaction

 A DBA_2PC unhandled exception while purging usually means Oracle is unable to clean up an in-doubt distributed transaction, even after COMMIT FORCE / ROLLBACK FORCE. This is a known and tricky DBA issue.

---

Problem

Error while executing:

EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('2.96.17311');

Typical Error Messages

• ORA-01403: no data found

• ORA-02062: distributed transaction %s rolled back

• ORA-00600 / ORA-00604

• OR "unhandled exception" without clear ORA code

---

Why This Happens

One or more of the following:

1. Transaction is already committed/rolled back, but metadata still exists

2. Transaction exists in pending_trans$ but not dba_2pc_pending

3. Oracle bug (common in 11g / early 12c)

4. Remote database link is broken or unreachable

5. Partial cleanup already happened → inconsistent state

---

Step-by-Step Resolution

---

✅ STEP 1: Reconfirm Transaction Existence

Run all of these:

SELECT * FROM dba_2pc_pending;
SELECT * FROM sys.pending_trans$;
SELECT * FROM sys.dba_2pc_neighbors;

👉 If transaction ID does NOT appear anywhere
➡️ DO NOT PURGE — it is already gone.

---

✅ STEP 2: Try PURGE with FULLY QUALIFIED CALL

BEGIN
  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(
    tran_id => '2.96.17311',
    ignore_corrupt => TRUE
  );
END;
/

Then:

COMMIT;

---

✅ STEP 3: Manually Remove Metadata (DBA-ONLY, CAREFUL)

⚠️ Last resort – take backup or snapshot first.

DELETE FROM sys.pending_trans$ WHERE local_tran_id = '2.96.17311';

DELETE FROM sys.dba_2pc_pending WHERE local_tran_id = '2.96.17311';

DELETE FROM sys.dba_2pc_neighbors WHERE local_tran_id = '2.96.17311';

COMMIT;

📌 This only removes metadata, not data changes.

---

✅ STEP 4: Clear Related Locks

SELECT s.sid, s.serial#, l.type, l.id1, l.id2
FROM v$lock l, v$session s
WHERE l.sid = s.sid
AND l.type = 'TX';

If still blocking:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

---

✅ STEP 5: Restart Distributed Transaction Services (Optional)

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

---

🔍 If Error Still Persists

Check Oracle bugs:

SELECT * FROM v$version;

Known problematic versions:

• 11.2.0.3

• Early 12.1

➡️ Oracle recommends database restart if metadata is corrupted.

---

🧠 Best Practice (Prevention)

• Avoid long-running distributed transactions

• Ensure DB links are stable

• Periodically monitor:

SELECT * FROM dba_2pc_pending;

---

✅ Final Recommendation

Situation	Action
Transaction visible & PREPARED	COMMIT FORCE / ROLLBACK FORCE
Not visible but lock exists	Manual SYS cleanup
Unhandled exception	Manual delete + commit
Critical prod system	Open Oracle SR

---