Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to deal with ORACLE suspense distributed transaction problem". The content of this article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to deal with ORACLE suspense distributed transaction problem".

Distributed transactions are used when data consistency operations are required across multiple Oracle databases.

For example:

Insert into Tunable logically remoteable DB;-- remote database inserts insert into Tunable local;-- Local database inserts commit

Transactions distributed on both local and remote db operate at the same time, which constitutes a distributed transaction.

Distributed transactions use Two-Phase Commit commit mechanism to ensure that all sub-transactions distributed in each node can be committed or rolled back atomically. Under this mechanism, the transaction process is divided into three phases:

PREPARE: the node initiating the distributed transaction notifies each associated node to prepare for commit or rollback. Each associated node will do three things at this time: refresh the redo information to the redo log, convert the held lock into a suspense transaction lock, and synchronize with the largest SCN number in each node.

COMMIT: write to commited SCN and release lock resources

FORGET: suspense transaction tables and associated database view information cleanup

Because distributed transactions involve operations between multiple databases, there are occasional anomalies (such as system or network outages) that lead to exceptions in the above three phases, which are on one or more nodes. Generate incomplete "suspense distributed transactions".

In most cases, when this problem occurs, the Oracle will be automatically repaired by the Reco process, and the Oracle database will record the information related to distributed transactions in multiple views such as dba_2pc_pending and dba_2pc_neighbors. In fact, the reco process will also do automatic repair based on this information.

The Reco process attempts to connect to other nodes to obtain distributed transaction information, then attempts to repair the failed transaction and deletes the records in the corresponding transaction.

However, in some cases (for example, the node cannot be accessed properly or the data recorded in the transaction table is incomplete), the Reco process cannot complete the work properly and an exception will be thrown. For distributed transactions, the corresponding exception code interval is ORA-02040-ORA-02099, and the error message can be found in the alert log.

For example:

ORA-02054: transaction in-doubtThe transaction is neither committed or rolled back locally, and we have lost communication with the global coordinator.

At this time, manual processing is often needed for intervention.

Here are three common distributed transaction problem scenarios:

There is data in the dba_2pc view, but the distributed transaction no longer exists

Distributed transactions exist, but there is no data in the dba_2pc view

Both transaction and view data are available, but hang resides when executing commit force or rollback force

There will be a prompt by reporting an error, such as:

ORA-01591: the 10.20.360 of lock held by in-doubt distributed transaction 10.20.360 is the distributed transaction ID that we need to check.

Scenario 1: there is data in the dba_2pc view, but the distributed transaction no longer exists

If the view has data, check the status of the data first

Select * from dba_2pc_pending where local_tran_id='10.20.360'

Mainly look at the state field.

If the transaction is already in the state of committed, rollback forced or commit forced, the transaction has been completed, but during the FORGET phase, the information in the database dictionary is not cleared in time. At this point, we can complete the processing by calling the statement of oracle to clean up the missing transaction information:

Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY ('10.20.360')

If the transaction is in PREPARED state, but there are no active transactions in the transaction table:

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, / * Transaction ID * / KTUXESTA Status,KTUXECFL FlagsFROM x$ktuxeWHERE ktuxesta please replace the rollback segment number here (xid=usn.slot. (sqn+1))-there are no active transactions

At this point, you need to manually clean up the information of the lost transaction.

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

Scenario 2: distributed transactions exist, but there is no data in the dba_2pc view

When you encounter errors such as ORA-2054, ORA-1591, etc., check that the dba_2pc view is not recorded. This scenario is not common and only occurs in a few extreme cases.

First confirm the phenomenon, check the x$ktuxe and dba_2pc_pending views respectively, and the query statement is the same as scenario 1.

In this case, whether commit force or rollback force is executed, an exception is thrown directly:

Commit force '10.20.360; ORA-02058: no prepared transaction found with ID 10.20.360

At this point, we need to fill in the base table data corresponding to the view, and then execute rollback force.

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 ('10.20.360, / *

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.