Steps to handle the In-Doubt Transactions ORA-01591 in Distributed Database

Handle the In-Doubt Transactions

Following are the error messages 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 extents 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 server HR, SALES and Warehouse.

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

Terms used in Distributed Environment:
Clients: Client which refer information from another database node.
Database Server: Database Server which host a database from which a client request data.
Local Coordinator: A node that refer to another node for data to complete its transaction.
Global coordinator: Node from which transaction is originating.
Commit Point: System always choose one node to be commit point site.

Following are the steps to handle the in-doubt transaction

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 WAREHOUSE Server with transaction id got 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. State column is prepared means server wait for the coordinator to send commit or roll-back request.
3. Comment or advice column: information can helps you decide whether to commit or roll-back but its blank in my case.

3. Trace the session tree, query the DBA_2PC_NEIGHBORS view on each node.
Warehouse: Fire the following command to trace the session tree on warehouse server. IN_OUT and INTERFACE COLUMNS helps for identified 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 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 both another query on other SERVER/NODE for checking the status of pending transactions.
SALES Server: Run following query on 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. First Query show us Sales is global coordinator because it is having same local or global tran id.
2. Second query show 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 INTERFACE column has 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. STATE Column show us that Commit Site performed the Commit operation it’s means we need to commit the transaction on other nodes.
Manually do commit operation 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';

For understand Distributed Database, Please ClickDistributed Database

Advertisements

One thought on “Steps to handle the In-Doubt Transactions ORA-01591 in Distributed Database

  1. Pingback: In-Doubt Transactions in Distributed Database | Smart way of Technology

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.