Solution to ORA-01591 errors

Abstract:
 
A ORA-01591 error is reported when accessing specific rows of some tables
 
Select * from BF_INCOME_EXPENSES_T
 
Where account_id = 36816153
 
And user_id = 39964213
 
And city_code = '000000'
 

ORA-01591: locking allocated transactions that have been problematic to handle 72.0.1608712 hangs

SQL> select count (*) from UNITELE. BI_MQSYNC_SOURCE_CONTROL_T1;

ORA-01591: locking allocated transactions that have been problematic to handle 72.0.1608712 hangs
 
As this table is a key business table, some foreground services are affected.
 
Keywords: ORA-01591 DBA_2PC_PENDING Distributed Transaction

1. Fault Analysis
 
First of all, when a ORA error occurs, we cannot know what each ORA error means. Therefore, you can check the incorrect cause and action in the Oracle online document to get a preliminary idea of this error.
 
01591,000 00, "lock held by in-doubt distributed transaction % s"
 
// * Cause: Trying to access resource that is locked by a dead two-phase commit
 
// Transaction that is in prepared state.
 
// * Action: DBA shocould query the pending_trans $ and related tables, and attempt
 
// To repair network connection (s) to coordinator and commit point.
 
// If timely repair is not possible, DBA shocould contact DBA at commit
 
// Point if known or end user for correct outcome, or use heuristic
 
// Default if given to issue a heuristic commit or abort command
 
// Finalize the local portion of the distributed transaction.
 

Oracle's description of ORA-01591 errors is "lock held by in-doubt distributed transaction % s, caused by the locks held by distributed transactions. Through the error cause, we can see that 'trying to access resource that is locked by a dead two-phase commit transaction that is in prepared State'. This error is caused by access to a stage 2 in the prepared state. this is caused by the lock held by the firm.
 
The following describes distributed transactions.
 
A distributed transaction, in simple terms, refers to a transaction being executed locally and remotely. The local transaction needs to wait for confirmation that the remote transaction is complete before proceeding to the next local operation. For example, if a row of records in the remote database is updated through dblink, if the network is abnormal during execution or other events cause the local database to be unable to know the execution status of the remote database, in this case, an in doublt error occurs. In this case, dba intervention is required, and the process must be performed in multiple situations.
 
The Two-Phase Commit mechanism of distributed transactions goes through three phases:
 
1. prepare phase:
 
1.1 determine which database is a commit point site. (Note: the database with the highest commit_point_strength value in the parameter file is the commit point site)
 
1.2 The Global Coordinator requires that all vertices (except the commit point site) be prepared for commit or rollback. In this case, the table of the distributed transaction is locked.
 
1.3 all distributed transaction nodes inform the global coordinator of its scn.
 
1.4 The Global Coordinator takes the largest scn of each point as the scn of the distributed transaction.
 
So far, all the points have completed the preparation work, and we start to enter the commit phase stage. At this time, the transactions at all points except the commit point site are in doubt State until the commit phase stage ends.
 
2. commit phase:
2.1 Global Coordinator uploads the maximum scn to the commit point site and requires its commit.
2.2 commit point: Try commit or rollback. Release the Distributed Transaction lock.
2.3 commit point indicates that the Global Coordinator has been commit.
2.4 Global Coordinator notifies all vertices of distributed transactions for commit.
 
3. forget phase:
3.1 The participating points notify the commit point site that they have completed the commit, and the commit point site will be able to forget the transaction (forget.
3.2 commit point site clears distributed transaction information on a remote database.
3.3 commit point site notifies Global Coordinator to clear local distributed transaction information.
3.4 Global Coordinator clears distributed transaction information.
 
For more information about distributed transactions, see oracle online documentation.
 
The current distributed transaction is in the prepared Phase of the Two-Phase Commit mechanism. At this stage, the transaction has been locked in the table. Now we want to access these tables, but the transaction is not finished and the lock is always held, the ORA-01591 is reported as a result of resource access failure. (It should be pointed out that the lock held by the distributed firm blocks read operations because oralce does not know which version of data to display.) if the transaction is terminated, the corresponding lock will be released, this will solve this problem. We know there are two ways to end a transaction: commit and rollback. Now we try to end this transaction:
 
Commit force '72. 0.1608712 ';
 
ORA-02058: no prepared transaction found with ID 72.0.1608712
 

No transaction in the prepared state is reported. Because the transaction is a distributed transaction, we first think of the attempt of dba_2pc_pending.
 

SQL> select * from dba_2pc_pending;
 
No rows selected
 
This attempt does not find any information, so we cannot end the distributed transaction with commit force. Now we can check whether the transaction exists. Through the actual error, we can clearly see that the transaction number is 72.0.1608712, the transaction is located in the No. 0 transaction slot of the No. 72 rollback segment and the serial number is 1608712. In this case, query a base table x $ ktuxe and check whether the transaction exists in the No. 72 rollback segment.
 
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN,/* Transaction ID */
 
2 KTUXESTA Status,
 
3 KTUXECFL Flags
 
4 FROM x $ ktuxe
 
5 WHERE ktuxesta! = 'Inactivity'
 
6 AND ktuxeusn = 72;
 

KTUXEUSNKTUXESLTKTUXESQN STATUSFLAGS
 
----------------------------------------------------------------------
 
7201608712 PREPAREDSCO | COL | REV | DEAD
 
Through the base table x $ ktuxe, we can see that this transaction exists and is in the prepared state.
 

At this point, we basically understand the cause of this problem: when a Distributed Transaction dies, the lock held by the transaction has not been released because the transaction has not been properly terminated, therefore, when accessing the resources involved in this transaction, the application cannot lock the resource, so it reports ORA-01591. Because it is a distributed transaction, when the transaction information cannot be queried in dba_2pc_pending, we cannot end the transaction through commit or rollback.
 
Therefore, our current task is to simulate this distributed transaction. Because dba_2pc_pending tries to rely on the pending_trans $ table and the transaction is associated with the session, we need to manually insert data to the pending_trans $ and pending_sessions $ tables.
 

2. troubleshooting
 

SQL> alter system disable distributed recovery;
 

The system has been changed.
 

SQL> insert into pending_trans $ (
 
2LOCAL_TRAN_ID,
 
3GLOBAL_TRAN_FMT,
 
4GLOBAL_ORACLE_ID,
 
5 STATE,
 
6 STATUS,
 
7SESSION_VECTOR,
 
8RECO_VECTOR,
 
9 TYPE #,
 
10FAIL_TIME,
 
11RECO_TIME)
 
12 values ('72. 0.1608712 ',

13306206,
 
14 'xxxxxxx. 12345.1.2.3 ',
 
15 'prepared', 'P ',
 
16 hextoraw ('20140901 '),

17 hextoraw ('20140901 '),
 
180, sysdate, sysdate );
 

One row has been created.
 

SQL> insert into pending_sessions $
 
2 values ('72. 0.1608712 ',
 
31, hextoraw ('05004f003a1500000104 '),
 
4 'C', 0, 30258592 ,'',
 
5146
 
6 );
 

One row has been created.
 

SQL> commit;
 

Submitted.
 
SQL> alter system enable distributed recovery;
 

The system has been changed.
 

At this point, the query dba_2pc_pending finds that the transaction already exists, and the status is the prepared State we have simulated.
 
SQL> select * from dba_2pc_pending;
 
LOCAL_TRAN_IDGLOBAL_TRAN_ID statemix a TRAN_COMMENTFAIL_TIMEFORCE_TIME RETRY_TIME OS _USER
 
OS _TERMINAL HOSTDB_USER
 
COMMIT #
 
----------------
 
72.0.1608712XXXXXXX.12345.1.2.3 prepared no12-august 1-0812-11-08
 

At this point, we end the transaction.
 
SQL> COMMIT FORCE '72. 0.1608712 ';
 

Submitted.
 

Query dba_2pc_pending again and find that the transaction is in the forced commit state, and the transaction has ended.
 
SQL> select * from dba_2pc_pending;
 

LOCAL_TRAN_IDGLOBAL_TRAN_ID statemix a TRAN_COMMENTFAIL_TIMEFORCE_TIME RETRY_TIME OS _USER
 
OS _TERMINALHOSTDB_USER COMMIT #
 
----------------
 
72.0.1608712XXXXXXX.12345.1.2.3 forced commitno12-september 12-11-08-12-11-08
 

Query transaction information through x $ kutxe and find that the transaction has released the rollback segment and the transaction has ended.
 
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN,/* Transaction ID */
 
2 KTUXESTA Status,
 
3 KTUXECFL Flags
 
4 FROM x $ ktuxe
 
5 WHERE ktuxesta! = 'Inactivity'
 
6AND ktuxeusn = 72;
 

Unselected row
 

In this case, we need to clear the residual information of distributed transactions in dba_2pc_pending.
 
SQL> alter session set "_ smu_debug_mode" = 4;-set the rollback segment in the manual management mode at the session level. If this parameter is not set, in automatic rollback segment management mode, an error is reported when transaction information is cleared.
 
The session has been changed.
 

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('72. 0.1608712 ');-Clear transaction information with dbms package
 

The PL/SQL process is successfully completed.
 

SQL> select * from dba_2pc_pending;
 

Unselected row
 

Test Access business table
 
SQL> select count (*) from UNITELE. BI_MQSYNC_SOURCE_CONTROL_T1;
 

COUNT (*)
 
----------
 
367
 
Solve the problem.
 

In fact, I also encountered a similar problem in another customer, at that time also reported ORA-01591, but in dba_2pc_pending can find the transaction in the prepared State, at this time only need to commit force to end this transaction, and clear the transaction information. For the above case, I suspect that the developer directly cleared the distributed transaction information, but the transaction is not completed, resulting in the lock resources not released to report the ORA-01591.

Summary: ORA-01591 errors are generally caused by distributed transactions, the main cause of the failure of distributed transactions is the sudden interruption of the network between databases, resulting in inconsistent transaction information in the two databases, therefore, there will be residual distributed transaction information. At this point, you must perform different processing for different transaction states. In addition, you can query metalink in case of a difficult problem. metalink Document: NOTE: 401302.1
[@ More @]