How can we remove-delete In-Doubt transactions?

In-Doubt transactions are the result of a failure during a distributed transaction and can be caused by any of the followings:

– A network failure between the involved databases
– A server crash
– An unhandled software error

In-Doubt transactions can occur between Oracle databases or Oracle and other-vendor databases.

When we have an in-doubt transaction we can perform the following query:

SQL> select * from dba_2pc_pending;

All the pending transactions are going to be listed, the number we are interested in is the LOCAL_TRAN_ID.

To clean an entry we use the following procedure (as sysdba):

SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('trans_id');

Note: Oracle recommend to wait for the RECO to recover pending transactions and does not recommend to perform this procedure unless:

* Total loss of the remote database
* Reconfiguration in software resulting in loss of two-phase commit capability
* Loss of information from an external transaction coordinator such as a TPMonitor

There are cases when the distributed transaction either commited or rollbacked on any of the nodes. To determined how to resolve the pending transaction you need to identify the remote DB (query the DBA_2PC_NEIGHBORS) and on every node check if any of the transactions have commited (check the COMMIT# column won’t be null on the DBA_2PC_PENDING view).

If any of the nodes have performed a commit then before executing the DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY procedure you should force a commit on all the participating nodes.

SQL> COMMIT FORCE '1.93.29';

Best regards.

Carlos Acosta Alamo

Advertisements
This entry was posted in Oracle Server and tagged , , , , , , , . Bookmark the permalink.

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