Years ago, when I worked more with Oracle Replication than I do now, I knew more or less every command I needed to handle in-doubt transactions (as a result of failed two phase commit). On one of our development server something went wrong with distributed transactions between Oracle10g and Transparent Gateway for MS SQL Server.

First, I tried to force rollback of transaction listed in dba_2pc_pending:

SQL> rollback force '19.27.91915';

where 19.27.91915 is LOCAL_TRAN_ID from dba_2pc_pending.

I guess the problem was with TG at MS SQL Server side, because Oracle occasionally tried unsuccessfully to execute rollback on MS SQL Server. At the end, developers simply turned off Oracle TG Listener. As a result I got plenty of error messages in alert.log like this:

Fri Sep 26 12:12:38 2008
Errors in file F:\oracle\admin\oraDB\bdump\orcl_reco_1736.trc:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: Message 2063 not found; No message file for product=RDBMS, facility=ORA; arguments: [2] [ lines] [TGATEWAY] []

I knew that Oracle has a package procedure that can permanently purge such transactions…., here it is: if you know that database is permanently lost and you can not rollback or commit, then you can use dbms_transaction.purge_lost_db_entry to remove failed transaction.

SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;

LOCAL_TRAN_ID          STATE            TRAN_COMMENT
---------------------- ---------------- ------------------------------
19.27.91915            forced rollback

SQL> connect / as sysdba
S
QL> execute dbms_transaction.purge_lost_db_entry('19.27.91915');

SQL> select local_tran_id, state, tran_comment from dba_2pc_pending;

no rows selected