ORA-01591: lock held by in-doubt distributed transaction 1.21.17

Following are the error messages that inform applications when there are problems with distributed transactions.

ORA-02050: transaction ID rolled back,
some remote dbs may be in-doubt
ORA-02053: transaction ID committed,
some remote dbs may be in-doubt
ORA-02054: transaction ID in-doubt

Manual fix the in-doubt transactions cases:
1. In-doubt transaction locks data that is required by other transactions. This situation occurs when the ORA-01591 error message.
2. An in-doubt transaction prevents the extent of an undo segment from being used by other transactions. The first portion of the local transaction ID of an in-doubt distributed transaction corresponds to the ID of the undo segment, as listed by the data dictionary view DBA_2PC_PENDING.

Example of handling the In-Doubt Transactions
In this example, we are taking 3 servers HR, SALES, and Warehouse.

Firstly, Identify the transaction is committed or rollback

Major we will identify which Server is the Global Coordinator, commit point Site, Database server, and Client in transactions. This information will help us to identify the status of the transaction is committed or rollback.

Terms used in Distributed Environment:
Clients: Clients refer information from another database node.
Database Server: Database Server which hosts a database from which a client requests data.
Local Coordinator: A node that refers to another node for data to complete its transaction.
Global coordinator: Node from which transaction is originating.
Commit Point: The system always chooses one node to be the commit point site.

Following are the steps to handle the in-doubt transaction in Oracle

1. Getting the following error alert:

ORA-01591: lock held by in-doubt distributed transaction 1.21.17

2. Trace the DBA_2PC_PENDING
WAREHOUSE Server: Fire the following command on the WAREHOUSE Server with transaction ID in error.

SELECT * FROM DBA_2PC_PENDING WHERE LOCAL_TRAN_ID = '1.21.17';

Column Name            Value
---------------------- --------------------------------------
LOCAL_TRAN_ID          1.21.17
GLOBAL_TRAN_ID         SALES.EXAMPLE.COM.55d1c563.1.93.29
STATE                  prepared
MIXED                  no
ADVICE
TRAN_COMMENT           Sales/New Order/Trans_type 10B
FAIL_TIME              31-MAY-91
FORCE_TIME
RETRY_TIME             31-MAY-91
OS_USER                SWILLIAMS
OS_TERMINAL            TWA139:
HOST                   system1
DB_USER                SWILLIAMS
COMMIT#

Note:
1. Global ID and local ID is different, So it is not the global coordinator
2. The State column is prepared means the server waits for the coordinator to send a commit or roll-back request.
3. Comment or advice column: information can help you decide whether to commit or roll back but it is blank in my case.

3. Trace the session tree, and query the DBA_2PC_NEIGHBORS view on each node.
Warehouse: Fire the following command to trace the session tree on the warehouse server. IN_OUT and INTERFACE COLUMNS help identify the session tree and commit point site.

SELECT * FROM DBA_2PC_NEIGHBORS WHERE LOCAL_TRAN_ID = '1.21.17' ORDER BY SESS#, IN_OUT;

Column Name             Value
---------------------- --------------------
LOCAL_TRAN_ID          1.21.17
IN_OUT                 in
DATABASE               SALES.EXAMPLE.COM
DBUSER_OWNER           SWILLIAMS
INTERFACE              N
DBID                   000003F4
SESS#                  1
BRANCH                 0100

Note:
IN_OUT column:
IN – Your node is a server of another node.
Lists the name of the client database that is connected to your node.
Lists the local account for the database link connection that corresponds to the in-doubt transaction.
OUT -Your node is a client of other servers.
Lists the name of the database link that connects to the remote node.
Lists the owner of the database link for the in-doubt transaction.
INTERFACE column tells whether the local node or a subordinate node is the commit point site. C means commit site.

4. Try another query on another SERVER/NODE to check the status of pending transactions.
SALES Server: Run the following query on the SALES server.

SELECT * FROM DBA_2PC_PENDING WHERE GLOBAL_TRAN_ID = 'SALES.EXAMPLE.COM.55d1c563.1.93.29';

Column Name            Value
---------------------- --------------------------------------
LOCAL_TRAN_ID          1.90.29
GLOBAL_TRAN_ID         SALES.EXAMPLE.COM.55d1c563.1.93.29
STATE                  prepared
MIXED                  no
ADVICE
TRAN_COMMENT           Sales/New Order/Trans_type 10B
FAIL_TIME              31-MAY-91
FORCE_TIME
RETRY_TIME             31-MAY-91
OS_USER                SWILLIAMS
OS_TERMINAL            TWA139:
HOST                   system1
DB_USER                SWILLIAMS
COMMIT#

4.1 Fire this on Sale SERVER with DBA_2PC_NEIGHBORS

SELECT * FROM DBA_2PC_NEIGHBORS WHERE GLOBAL_TRAN_ID = 'SALES.EXAMPLE.COM.55d1c563.1.93.29' ORDER BY SESS#, IN_OUT;

Warehouse:
Column Name             Value
---------------------- ----------------------
LOCAL_TRAN_ID          1.93.29
IN_OUT                 OUT
DATABASE               WAREHOUSE.EXAMPLE.COM
DBUSER_OWNER           SWILLIAMS
INTERFACE              N
DBID                   55d1c563
SESS#                  1
BRANCH                 1

HQ: 
Column Name            Value
---------------------- -----------------
LOCAL_TRAN_ID          1.93.29
IN_OUT                 OUT
DATABASE               HQ.EXAMPLE.COM
DBUSER_OWNER           ALLEN
INTERFACE              C
DBID                   00000390
SESS#                  1
BRANCH                 1

Note:
1. The First Query shows us that Sales is the global coordinator because it has having same local or global tran ID.
2. The second query shows us Two outbound connections are established from this node, but no inbound connections. sales is not the server of another node.
3. HQ Server is the commit point site because the INTERFACE column has a C value.

5. Checking the Status of Pending Transactions at HQ

SELECT * FROM DBA_2PC_PENDING@hq.example.com WHERE GLOBAL_TRAN_ID = 'SALES.EXAMPLE.COM.55d1c563.1.93.29';

Column Name            Value
---------------------- --------------------------------------
LOCAL_TRAN_ID          1.45.13
GLOBAL_TRAN_ID         SALES.EXAMPLE.COM.55d1c563.1.93.29
STATE                  COMMIT
MIXED                  NO
ACTION
TRAN_COMMENT           Sales/New Order/Trans_type 10B
FAIL_TIME              31-MAY-91
FORCE_TIME
RETRY_TIME             31-MAY-91
OS_USER                SWILLIAMS
OS_TERMINAL            TWA139:
HOST                   SYSTEM1
DB_USER                SWILLIAMS
COMMIT#                129314

Note:
STATE Column show us that its commit value.
COMMIT# column show the commit id.

6. The STATE Column shows us that the Commit Site performed the Commit operation it’s means we need to commit the transaction on other nodes.
Manually do commit operations on other nodes:

SQL> CONNECT SYS@sales.example.com AS SYSDBA
SQL> COMMIT FORCE 'SALES.EXAMPLE.COM.55d1c563.1.93.29';

SQL> CONNECT SYS@warehouse.example.com AS SYSDBA
SQL> COMMIT FORCE 'SALES.EXAMPLE.COM.55d1c563.1.93.29';

To understand Distributed Database, Please ClickDistributed Database