Ref ID: How To Resolve Stranded DBA_2PC_PENDING Entries [ID 401302.1]
Issue: We had network outage and there were some distributed transactions with dblinks hangs. Causing issue when trying to update some rows of the base table.
Problem Discription:
====================
As a result of a failed commit of a distributed transaction, some entries canbe left in dba_2pc views, i.e. dba_2pc_pending and dba_2pc_neighbors. The RECO process checks these views to recover the failed txn. However, in some cases RECO cannot perform the recovery. One cause is that all sites involved in thetransaction not being accessible at the same time. Another cause is dba_2pc views being inconsistent with the transaction table.
Solutions:
==========
Case:1 Dba_2pc entries without a corresponding transaction
==========================================================
In this case dba_2pc views show distributed transactions but there are no txnsin reality.
If the state of the transaction is committed, rollback forced or commit forced then this is normal and it can be cleaned up using dbms_transaction.purge_lost_db_entry.
If the state of the transaction is PREPARED and there is no entry in the transaction table for it then this entry can be cleaned up manually as follows:
set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$ where local_tran_id = ;
delete from sys.pending_sessions$ where local_tran_id = ;
delete from sys.pending_sub_sessions$ where local_tran_id = ;
commit;
For Example:
————
The following query reports a dist. txn. in prepared state
select local_tran_id, state from dba_2pc_pending;
LOCAL_TRAN_ID STATE
———————- —————-
1.92.66874 prepared
Given that a transaction id is composed of ‘1.92.66874’ is located in rollback segment# 1. To find out the list of active transactions in that rollback segment, use:
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!=’INACTIVE’
AND ktuxeusn= 1; <== this is the rollback segment#
no rows selected
It is not possible to rollback force or commit force this transaction.
SQL> rollback force ‘1.92.66874’;
ORA-02058: no prepared transaction found with ID 1.92.66874
Hence, we have to manually cleanup that transaction:
set transaction use rollback segment SYSTEM;
delete from sys.pending_trans$where local_tran_id = ‘1.92.66874’;
delete from sys.pending_sessions$ where local_tran_id = ‘1.92.66874’;
delete from sys.pending_sub_sessions$ where local_tran_id = ‘1.92.66874’;
commit;
Case:2 Distributed transaction without corresponding dba_2pc entries
====================================================================
In this case dba_2pc views are empty but users are receiving distributed txnrelated errors, e.g. ORA-2054, ORA-1591. Normally such a case should not appearand if it is reproducible a bug should be filed. Here is the list of severalalternative solutions that can be used in this case:
a. Perform incomplete recovery
b. Truncate the objects referenced by that transaction and import them
c. Use _corrupted_rollback_segments parameter to drop that rollback segment
d. Insert dummy entries into dba_2pc views and either commit or rollback force the distributed transaction
We will discuss only solution (d) here, as first 3 solutions can be found on Backup/Recovery Guide.
In the 4th solution a dummy entry is inserted intothe dictionary so that the transaction can be manually committed or rolled back.
Note that RECO will not be able to process this txn and distributed txn recovery should be disabled before using this method. Furthermore, please take a BACKUPof your database before using this method.The following example describes how to diagnose and resolve this case. Supposethat users are receiving
ORA-1591: lock held by in-doubt distributed transaction 1.92.66874
and the following query returns no rows:
SQL> select local_tran_id, state from dba_2pc_pending where local_tran_id=’1.92.66874′;
no rows selected
Furthermore querying the rollback segment shows that 1.92.66874 remains in prepared state,
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!=’INACTIVE’
AND ktuxeusn= 1; /* <== Replace this value with your txn undo seg#
Which is displayed in the first part of
the transaction ID */
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
———- ———- ———- —————- ————————
1 92 66874 PREPARED SCO|COL|REV|DEAD
Trying to manually commit or rollback this transaction,
SQl> commit force ‘1.92.66874’;
ORA-02058: no prepared transaction found with ID 1.92.66874
raises ORA-02058 since dba_2pc views are empty. In order to use commit force or rollback force a dummy record should be inserted into pending_trans$ as follows:
alter system disable distributed recovery;
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( ‘1.92.66874’, /* <== Replace this with your local tran id */
306206, /* */
‘XXXXXXX.12345.1.2.3’, /* These values can be used without any */
‘prepared’,’P’, /* modification. Most of the values are */
hextoraw( ‘00000001’ ), /* constant. */
hextoraw( ‘00000000’ ), /* */
0, sysdate, sysdate );
insert into pending_sessions$
values( ‘1.92.66874’,/* <==Replace only this with your local tran id */
1, hextoraw(‘05004F003A1500000104’),
‘C’, 0, 30258592, ”,
146
);
commit;
commit force ‘1.92.66874’
If commit force raises an error then note the errormessage and execute the following:
delete from pending_trans$ where local_tran_id=’1.92.66874′;
delete from pending_sessions$ where local_tran_id=’1.92.66874′;
commit;
alter system enable distributed recovery;
Otherwise run purge the dummy entry from the dictionary, using
alter system enable distributed recovery;
connect / as sysdba
COMMIT;
alter system set “_smu_debug_mode” = 4; /* if automatic undo management is being used */
commit; /* this is to prevent the ORA-01453 in purge_lost_db_entry call */
exec dbms_transaction.purge_lost_db_entry( ‘1.92.66874’ )
Case:3 How to PURGE the DISTRIBUTED transaction in PREPARED state, when COMMITor ROLLBACK FORCE hangs ?, where we have entries for both Distributedtransaction and dba_2pc entries.
==========================================================================
ISSUE DESCRIPTION
=================
ORA-01591: lock held by in-doubt distributed transaction 44.88.85589 The row exist from dba_2pc_pending & Rollback segment.
SQL> SELECT LOCAL_TRAN_ID,STATE FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID STATE
—————– ———–
44.88.85589 prepared
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!=’INACTIVE’
AND ktuxeusn= 44; /* <== Replace this value with your txn undo seg#
Which is displayed in the first part of
the transaction ID */
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
———- ———- ———- —————- ————————
44 88 85589 PREPARED SCO|COL|REV|DEAD
SQL> Commit force 44.88.85589;
SQL> rollback force 44.88.85589;
Executing COMMIT or ROLLBACK FORCE hangs
The wait event is “”free global transaction table entry”
Purging the transaction should fail with below error:
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘44.88.85589’);
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(‘44.88.85589’); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at “SYS.DBMS_TRANSACTION”, line 94
ORA-06512: at line 1
Solution:
——–
You have to implement both the solution :
2.1 Dba_2pc entries without a corresponding transaction
2.2 Distributed transaction without corresponding dba_2pc entries
1.
delete from sys.pending_trans$ where local_tran_id = ‘44.88.85589’;
delete from sys.pending_sessions$ where local_tran_id = ‘44.88.85589’;
delete from sys.pending_sub_sessions$ where local_tran_id =’44.88.85589′;
commit;
2. Now insert the dummy record as explained in section:
2.2 Distributed transaction without corresponding dba_2pc entries
commit;
3. Commit force ‘44.88.85589’
4. Purge the transaction:
exec dbms_transaction.purge_lost_db_entry(‘44.88.85589’);
Metalink Noteid: How To Resolve Stranded DBA_2PC_PENDING Entries [ID 401302.1]