Procedure to ROLLBACK FORCE pending in-doubt transaction

Below is the procedure necessary to force the rollback of a failed distributed transaction, known as an “in-doubt transaction”. Oracle uses a two phase commit (2PC) mechanism to commit changes locally and remotely in a distributed transaction.

The following error is normally associated with an in doubt transaction. Here, a network “glitch” has caused the ORA-03113.

ORA-03113: end-of-file on communication channel

In addition to the ORA-03113 is ORA-02050, stating that the local in-doubt transaction has been rolled back automatically, this can be seen in the local database instance alert log:

Error 3113 trapped in 2PC on transaction 70.31.1376339. Cleaning up.

Error stack returned to user:

ORA-02050: transaction 70.31.1376339 rolled back, some remote DBs may be in-doubt

ORA-03113: end-of-file on communication channel

Thu Jun 09 12:28:32 2011

DISTRIB TRAN REMDB.WORLD.f9784a67.3.9.924681

  is local tran 70.31.1376339 (hex=46.1f.150053)

  insert pending collecting tran, scn=6187118039 (hex=1.70c7edd7)

However, this is not always the case, as the transaction is seen as pending in the “pending two phase commit” view (DBA_2PC_PENDING)

SQL> select LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, FAIL_TIME, RETRY_TIME from dba_2pc_pending;

LOCAL_TRAN_ID GLOBAL_TRAN_ID                  STATE     FAIL_TIME RETRY_TIME

------------- ------------------------------- --------- --------- ----------

70.31.1376339 REMDB.WORLD.f9784a67.3.9.924681 collecting 09-JUN-11 09-JUN-11

If the state of the transaction is “prepared”, it is possible to force rollback the transaction by appending the transaction id to the command as follows (as sysdba):

SQL> ROLLBACK FORCE '70.31.1376339';

If the state of the transaction is “collecting”, you will suffer the following error:

SQL> ROLLBACK FORCE '70.31.1376339';

ROLLBACK FORCE '70.31.1376339'

*

ERROR at line 1:

ORA-02058: no prepared transaction found with ID 70.31.1376339

In this case, you need to execute the following procedure in the DBMS_TRANSACTION package to clear.

SQL> execute dbms_transaction.purge_lost_db_entry('70.31.1376339')

PL/SQL procedure successfully completed.

Rerun the query against DBA_2PC_PENDING to confirm the pending local transaction has gone.

SQL>  select LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, FAIL_TIME, RETRY_TIME from dba_2pc_pending;

no rows selected

Also check DBA_2PC_NEIGHBORS to confirm the pending remote transaction has gone.

SQL> select database,local_tran_id,dbid,sess#,branch from dba_2pc_neighbors;

no rows selected

Further information may be found at http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_txnman006.htm#ADMIN12266