Old White explanation processing ora-01591

Database always reports errors:
Tue May 6 13:44:47 2008
SMON: About to recover undo segment 119
Oracle instance topcs2 (pid = 11)-error 1591 encountered while recovering tran
Saction (119, 18) on Object 2309045.
Errors in file/U2/Oracle/ora92/rdbms/log/topcs2_ora_2899.trc:
ORA-01591: Lock held by in-doubt Distributed Transaction 108.28.46269
SMON: Mark undo segment 119 as needs recovery

I asked Mr. Sun if the interface was Tuxedo. Mr. Sun said it was an application of tuxedo 6.5. Problems with this XA application cause ORA-1591 Problems
This is also a common problem. I will immediately find out if there is a dead transaction in the rollback segment No. 119:
Select ktuxeusn, ktuxeslt, ktuxesqn,
Ktuxesta status, ktuxecfl flags
From x $ ktuxe
Where ktuxesta! = 'Inactivity'
And ktuxeusn = 119;

The query result does not have any records. As soon as I saw that the original in-doubt distributed transaction was 108.28.46269, I immediately queried the 108 rollback segment
Is there a dead transaction:
Select ktuxeusn, ktuxeslt, ktuxesqn,
Ktuxesta status, ktuxecfl flags
From x $ ktuxe
Where ktuxesta! = 'Inactivity'
And ktuxeusn = 108;

Ktuxeusn ktuxeslt ktuxesqn Status flags
----------------------------------------------------------------------
108 28 46269 prepared SCO | Col | rev | dead

There is indeed a dead transaction in the prepared state. So I checked the pending_trans $ table immediately:
Select local_tran_id, global_tran_fmt, global_oracle_id,
Global_foreign_id, state, status, heuristic_dflt,
Session_vector, reco_vector,
Global_commit #
From pending_trans $;

Local_tran_id global_tran_fmt global_oracle_id global_foreign_id State status heuristic_dflt session_vector reco_vector global_commit #

70.6.108873 306206 topcs. c7dd20c6.70.6.108873 forced rollback P 00000001 00000001 1486865870
96.42.84009 306206 topcs. c7dd20c6.96.42.84009 forced rollback P 00000001 00000001 1286932454
85.10.101067 306206 topcs. c7dd20c6.85.10.101067 forced rollback P 00000001 00000001 1487167659
9.35.29156 306206 topcs. c7dd20c6.9.35.29156 collecting P 00000001 00000001 1672793351
80.8.132177 306206 topcs. c7dd20c6.80.8.132177 forced rollback P 00000001 00000001 1679427495
18.32.162778 306206 topcs. c7dd20c6.18.32.162778 forced commit P 00000001 00000001
64.21.136442 306206 topcs. c7dd20c6.64.21.136442 forced commit P 00000001 00000001
73.11.124822 306206 topcs. c7dd20c6.73.11.124822 forced rollback P 00000001 00000001 1731227073
63.29.148558 306206 topcs. c7dd20c6.63.29.148558 forced commit P 00000001 00000001

The transaction 108.28.46249 does not exist. It seems that a problem has occurred in the system dictionary table, and this distributed transaction cannot be automatically rolled back. First look at this
What is the transaction lock object:

Select name from OBJ $ where OBJ #= 2309045;
Name
---------------------------------------
Tptb_idx
The base table of this index is t_tptb. scan the entire table to see what will happen:
Select/* + full (a) */count (*) from t_tptb;
ORA-01591: Lock held by in-doubt Distributed Transaction 73.11.124822

This table is locked by another Distributed Transaction 73.11.124822. View the rollback segment of No. 73:

Select ktuxeusn, ktuxeslt, ktuxesqn,
Ktuxesta status, ktuxecfl flags
From x $ ktuxe
Where ktuxesta! = 'Inactivity'
And ktuxeusn = 73;

Ktuxeusn ktuxeslt ktuxesqn Status flags
----------------------------------------------------------------------
73 11 124822 prepared SCO | Col | rev | dead

An error occurred while executing rollback force 73.11.124822:

SQL> rollback force '73. 11.124822'
Error at line 1:
ORA-02058: No prepared transaction found with ID 73.11.124822

In pending_trans $, check that the current transaction status is not prepared. Therefore, you must first set the transaction status in pending_trans $
Prepared:
Update pending_trans $ set state = 'prepared ',
Status = 'P'
Where local_tran_id = '73. 11.124822 ';
Commit;

After executing this statement, execute rollback force '73. 11.124822 'again ';
SQL> rollback force '73. 11.124822 ';
Rollback force '73. 11.124822'
*
Error at line 1:
ORA-01591: Lock held by in-doubt Distributed Transaction 108.28.46269

It seems that this transaction is locked by the transaction we first found. One of the two transactions locks the table and the other locks the index. Therefore, you can only see if the transaction can be forcibly committed:
SQL> Update pending_trans $ set state = 'prepared ',
Status = 'P'
Where local_tran_id = '73. 11.124822 ';
SQL> commit;
SQL> commit force '73. 11.124822 ';
Using this command, the transaction is successfully forcibly committed. Next we will handle the transaction of the 108 rollback segment. Because this transaction lacks records in pending_trans $
Clear the transaction:
SQL> exec dbms_transaction.purge_lost_db_entry ('2017. 123 ')
Then insert related records manually:
SQL> alter system disable distributed recovery;
SQL> insert into pending_trans $ (
Local_tran_id,
Global_tran_fmt,
Global_oracle_id,
State,
Status,
Session_vector,
Reco_vector,
Type #,
Fail_time,
Reco_time)
Values ('2014. 108 ',
306206,
'Xxxxxxx. 12345.1.2.3 ',
'Prepared', 'P ',
Hextoraw ('20140901 '),
Hextoraw ('20140901 '),
0, sysdate, sysdate );

SQL> insert into pending_sessions $
Values ('2014. 108 ',
1, hextoraw ('05004f003a1500000104 '),
'C', 0, 30258592 ,'',
146
);

SQL> commit;

Then force submit again:
SQL> commit force '2017. 100 ';
Commit compelte.
After the application is submitted, Mr. Sun checks the application again and finds that the problem has been solved.

 

Topic: Additional instructions for ORA-1591

A few days ago quicksand ORA-1591 problem, because it is QQ dialogue, may for lack of experience in the ORA-1591 is not easy to understand, this post for further introduction to this problem. So refer to the instance to better understand the ORA-1591 problem.

ORA-01591: "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 shoshould query the pending_trans $ and related tables,
And attempt to repair network connection (s)
Coordinator and commit point. If timely repair is not
Possible, DBA shoshould 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 to finalize the local portion of
Distributed Transaction.
In general, the cause of ORA-1591 is the failure of distributed transactions, many reasons for failure, such as network problems, XA Resource Manager bug, etc., may cause failure. Once a distributed transaction fails, if a transaction in a local transaction is active, the data related to the transaction will be locked (no matter whether it is read or written ), if you access the data associated with this transaction, a ORA-1591 is reported. Under normal circumstances, the ORA-1591 can be automatically unlocked, SMON will check dba_2pc_pending within a certain period of time, find the transaction to be rolled back, and perform automatic recovery. There are several problems here. Due to the judgment of the Distributed Transaction timeout and the relationship between the reco processing cycle, generally, the automatic recovery time of the transaction is more than one minute, and the long time can reach 5-10 minutes. It may have a big impact on the production system. To speed up unlocking, you can use manual processing. In this case, you can use rollback force or commit force.

Sometimes, due to a fault in the recovery of distributed transactions, the data dictionary is inconsistent. In this case, the distributed transaction cannot be normally released and must be handled by manual intervention.

Analysis Method:

1. Check the status of distributed transactions:

Select local_tran_id, global_tran_id, state, mixed, host, commit #
From dba_2pc_pending
Where local_tran_id = 'error local transaction number'

2. Check other nodes related to distributed transactions:

Select local_tran_id, in_out, database, Interface
From dba_2pc_neighbors;

3. Check the local rollback segment:

Select ktuxeusn, ktuxeslt, ktuxesqn,/* transaction ID */
Ktuxesta status,
Ktuxecfl flags
From x $ ktuxe
Where ktuxesta! = 'Inactivity'
And ktuxeusn = rollback segment Encoding
The most common is that a record in the prepared state exists in dba_2pc_pending. An active transaction in the prepared State is also found in the rollback segment, but the transaction has a dead flag. In this case, you can perform commit force or rollback force based on the transaction number.
Another case is that there are records in dba_2pc_pending, but the status is different from the status in the rollback segment. In this case, manually modify sys. status value in pending_trans $, and then perform rollback force/commit force.
If no record is found in dba_2pc_pending, check SYS. pending_trans $, SYS. pending_sessions $, and SYS. pending_sub_sessions $. If the record is lost, you can manually insert the record and then perform rollback/commit force
(Refer to the post on liusha's question)
Insert into pending_trans $ (
Local_tran_id,
Global_tran_fmt,
Global_oracle_id,
State,
Status,
Session_vector,
Reco_vector,
Type #,
Fail_time,
Reco_time)
Values ('transid ',
306206, fixed value/
'Xxxxxxx. 12345.1.2.3 ', optional
'Prepared', 'P', indicating the prepared status
Hextoraw ('20140901'),/* constant .*/
Hextoraw ('20140901 '),/**/
0, sysdate, sysdate );

Insert into pending_sessions $
Values ('transaction number ',
1, hextoraw ('05004f003a1500000104 '),
'C', 0, 30258592 ,'',
146
);

Commit;
4. After rollback force/commit force is completed, if the record in dba_2pc_pending is not cleared or there is a record in dba_2pc_pending, but no record exists in the rollback segment. Use:
Alter session set "_ smu_debug_mode" = 4; // If 9i is used, you need to set it when using Aum. Otherwise, a commit; Exec dbms_transaction.purge_lost_db_entry ('transaction number') error occurs ') the actual situation is much more complicated. For details, refer to 401302.1.