How to demonstrate Oracle In-Doubt Transactions

An 'in doubt transaction' occurs in Oracle when you update both the local and a remote table via a db link but then the remote databases fails.

Here is the example.

Using databases TEST1 and TEST2, create a table on each. Also create a database link from TEST1 to TEST2.

conn user1/pwd1@TEST1
create table loc_tab (afld varchar2(10));
create database link alink connect to user2 identified by pwd2 using 'TEST2';

conn user2/pwd2@TEST2
create table rem_tab (afld varchar2(10));

Then connect to TEST1 and insert a row into both.

conn user1/pwd1@TEST1
insert into loc_tab values ('A');
insert into rem_tab@alink values ('B');

The connect another session to SYS as SYSDBA on TEST2 and abort the database.

shutdown abort

Back on TEST1, commit the transaction. You will get an error.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02054: transaction 6.21.2068 in-doubt
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from ALINK

Select * from dba_2pc_pending to see the in doubt transaction on TEST1.
Startup TEST2 and you'll see that the in doubt transaction remains.

The options for dealing with this are COMMIT FORCE or ROLLBACK FORCE. Because we are creating standby databases from TEST1 and don't want the users getting ORA-01591 errors, we will roll back the error.

SQL> select local_tran_id from dba_2pc_pending WHERE state not like 'forced%';
LOCAL_TRAN_ID
----------------------
6.21.2068

SQL> rollback force '6.21.2068';
Rollback complete.

** note the WHERE Clause to avoid seeing other in doubt transaction that have already been forced back recently.

If you choose to COMMIT FORCE '6.21.2068', then it will only commit the local part of the transaction.

I did find that when I restarted TEST2, before performing a 'commit force' on TEST1, the command hung? Not sure about that. I didn't hang on later tests.


Another thing you can do is pretend the failure on TEST2 occurred.
This is done by not aborting TEST2, but instead committing with a comment.

COMMIT COMMENT 'ORA-2PC-CRASH-TEST-7';

Where the 7 can be a number between 1 and 10. The setting of this requires some thinking about. I've listed the values and what they mean at the end of this note.

The result however is the same. You still have to rollback force or commit force the in doubt transaction, but you'll get an error when you do.

Finally there are options to auto fix in doubt transactions based on some criteria.

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

Doc ID 100664.1 from Oracle will give you more detailed information on this matter, and it also has links to other related documents.


COMMIT COMMENT Options.

1 - Crash commit point after collect
2 - Crash non-commit-point site after collect
3 - Crash before prepare (non-commit-point site)
4 - Crash after prepare (non-commit-point site)
5 - Crash commit point site before commit
6 - Crash commit point site after commit
7 - Crash non-commit-point site before commit
8 - Crash non-commit-point site after commit
9 - Crash commit point site before forget
10 - Crash non-commit-point site before forget