TA的每日心情 | 开心
2023-8-9 11:05 |
|---|
|
楼主 |
发表于 2023-12-31 11:09:07
|
显示全部楼层
How To Resolve Stranded DBA_2PC_PENDING Entries (Doc ID 401302.1)
Decide whether to commit or rollback the transaction, after considering the
impact this will have on data integrity.
Identify the id of the transaction by querying DBA_2PC_PENDING and looking at
the LOCAL_TRAN_ID or GLOBAL_TRAN_ID columns, then issue one of the following
statements:
SQL>COMMIT FORCE '<transaction_id>';
or
SQL>ROLLBACK FORCE '<transaction_id>';
If the command causes an ORA-02058 error to occur, it may mean that the remote
database cannot be accessed. In this case, check whether the database link to
the remote database exists and whether the remote database is shutdown.
If the remote database no longer exists then the transaction will have to be
purged from the list of pending distributed transactions.
For instructions on how to purge a distributed transaction from the database, reference:
PURPOSE
The purpose of this bulletin is to assist support analysts in understanding and
resolving the stranded dba_2pc_entries.
SCOPE
The intended audience are support analysts having good experience on distributed
databases.
DETAILS
Contents
1. Problem Description
2. Solutions
2.1 Dba_2pc entries without a corresponding transaction
2.2 Distributed transaction without corresponding dba_2pc entries
2.3 How to PURGE the DISTRIBUTED transaction in PREPARED state, when COMMIT
or ROLLBACK FORCE hangs ?, where we have entries for both Distributed
transaction and dba_2pc entries.
1. Problem Description:
=======================
As a result of a failed commit of a distributed transaction, some entries can
be 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 the
transaction not being accessible at the same time. Another cause is dba_2pc
views being inconsistent with the transaction table, which is the topic of
this article. This cause can further be classified as follows:
1. dba_2pc views have entries for a non-existent distributed transaction
2. There is a distributed transaction for which there are no entries in
dba_2pc views
3. How to PURGE the DISTRIBUTED transaction in PREPARED state, when COMMIT
or ROLLBACK FORCE hangs ?, where we have entries for both Distributed
transaction and dba_2pc entries.
Solutions to each subclass is provided in the rest of the article.
2. Solutions:
2.1 Dba_2pc entries without a corresponding transaction
In this case dba_2pc views show distributed transactions but there are no txns
in 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
However, 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;
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 triple,
'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.
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;
2.2 Distributed transaction without corresponding dba_2pc entries
In this case dba_2pc views are empty but users are receiving distributed txn
related errors, e.g. ORA-2054, ORA-1591. Normally such a case should not appear
and if it is reproducible a bug should be filed. Here is the list of several
alternative 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
The first three solutions are discussed in Backup and Recovery manuals and in
the notes referred above. In the 4th solution a dummy entry is inserted into
the 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 BACKUP
of your database before using this method.
The stranded entries is the cause of ORA-01591 so we need to
clear the stranded entries by purging them using
Please be aware the usage of _corrupted_rollback_segments can lead to logical corruption on the database and require the database to be recreated. This is documented on Internal notes 179952.1 _OFFLINE_ROLLBACK_SEGMENTS/_CORRUPTED_ROLLBACK_SEGMENTS and AUM Undo Segments
and TECH: Summary For Forcing The Database Open With `_ALLOW_RESETLOGS_CORRUPTION` with Automatic Undo Management (Doc ID 283945.1)[This section is not visible to customers.]
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('transanction_id');
The following example describes how to diagnose and resolve this case. Suppose
that users are receiving
ORA-1591: lock held by in-doubt distributed transaction 1.92.66874
and the following query returns no rows:
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
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:
Please note that the below steps only applies to REV transactions. ( SCO|COL|REV|DEAD). If the transaction causing the problem is not marked REV, then most likely its cleanup is being blocked by some other prepared transaction. For this case, first resolve all the DEAD|REV prepared transactions.
Important:
The solution depends on the type of transaction being recovered - EXTDTX or non EXTDTX (Oracle Managed Transactions).
For each of the transactions, a different formatid needs to be inserted into the GLOBAL_TRAN_FMT the 2nd column of table pending_trans$ table.
GLOBAL_TRAN_FMT value is 306206 for Oracle-managed transaction and 48801 for Welogic.
o For EXTDTX transactions that has the flag set to "SCO|COL|REV|DEAD|EXTDTX"
Example:
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
507 18 31224 PREPARED SCO|COL|REV|DEAD|EXTDTX
o For Oracle Managed Transactions that has the flag set to "SCO|COL|REV|DEAD"
Example:
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS
526 4 28122 PREPARED SCO|COL|REV|DEAD
a. If the transaction is Oracle Managed Transaction ( Non EXTDX) use below steps to insert GLOBAL_TRAN_FMT value of 306206 :
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';
b. If the transaction is EXTDX use below steps to find the right formatid to insert into GLOBAL_TRAN_FMT :
How to find the right formatid:
• Find an example entry into the pending tables, in the alert log or smon trace, for example:
• RIC1P005(3):DISTRIB TRAN 0000bea1.065C358C40D8D6C34A4C
• RIC1P005(3): is local tran 507.18.31224 (hex=1fb.12.79f8)
• RIC1P005(3): insert pending prepared tran, scn=3025244795 (hex=0x00000000b451927b)
• The first field in the global transaction id, in this case "0000bea1", is the formatid.
• Convert it to decimal to get the right value for the GLOBAL_TRAN_FMT column. --> formatid translates to 48801
Here in the above example, EXTDTX formatid "0000bea1" is translated to 48801 which signifies WebLogic. Similarly it could translate to different values corresponding to Tuxedo, Microsoft DTC, Websphere etc.
Now you may use below steps to insert GLOBAL_TRAN_FMT value of 48801 :
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 */
48801, /* */
'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;
Use following query to retrieve the value for such _smu_debug_mod parameter:
col Parameter for a20
col "Session Value" for a20
col "Instance Value" for a20
SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm = '_smu_debug_mode'
/
-- set it temporarily to 4:
alter system set "_smu_debug_mode" = 4; /* if automatic undo management
is being used */
-- in 9.2x alter session can be used instead.
commit; /* this is to prevent the ORA-01453 in purge_lost_db_entry call */
exec dbms_transaction.purge_lost_db_entry( '1.92.66874' );
SQL> commit;
SQL> alter system set "_smu_debug_mode" = <original value>;
SQL> commit;
2.3 How to PURGE the DISTRIBUTED transaction in PREPARED state, when COMMIT
or ROLLBACK FORCE hangs ?, where we have entries for both Distributed
transaction and dba_2pc entries.
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');
Note:126069.1 Manually Resolving In-Doubt Transactions: Different Scenario's |
|
发表于 2023-12-29 11:40:26