分布式错误 ORA-02068: following severe error from dblink_name

最新推荐文章于 2021-08-28 12:27:39 发布

原创最新推荐文章于 2021-08-28 12:27:39 发布 · 6.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#database #transactions #sql #interface #crash #oracle

Oracle Management 专栏收录该内容

40 篇文章

订阅专栏

本文详细介绍了Oracle中分布式事务出现错误时的排查步骤及处理方法，包括使用SQL语句识别事务ID、清理未完成的分布式事务等关键操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

早上刚打开邮件，就传来生产库的一条ORA错误邮件，内容如下

Error 2068 trapped in 2PC on transaction 21.42.327552. Cleaning up.
Error stack returned to user:
ORA-02068: following severe error from dblink
ORA-03113: end-of-file on communication channel

Two-Phase Commit (2PC)
The two phase-commit mechanism is used to ensure data integrity in a distributed transaction. It is automatically used during all distributed transactions and coordinates either the commit or roll back of all the changes in the transaction as a single, self-contained unit.

In short, there are three phases for the 2PC:

    PREPARE: The initiating node ask each of its referenced nodes to promise to perform a commit or rollback when told to do so. The preparing node will flush the redo log buffer to the online redo log. It converts locks to in-doubt transaction locks on the data blocks and passes its highest SCN value to the initiating node.
    COMMIT: The initiating node commits and writes to its redo log the committed SCN. The Data Block locks are released.
    FORGET: Pending transactions tables are related database views are cleared (dba_2pc_pending/dba_2pc_neighbors)

All the above phases take place quickly and transparently to the application where the transaction originated.

NOTE: A crash during the PREPARE Phase results in a ROLLBACK A crash during the COMMIT Phase results in either COMMIT or ROLLBACK

读了一下oracle support中How to Purge a Distributed Transaction from a Database [ID 159377.1]的内容

处理的办法如下

How to Purge a Distributed Transaction from a Database [ID 159377.1]
Applies to:
Oracle Server - Enterprise Edition - Version: 7.3.4.0 to 11.1.0.6 - Release: 7.3.4 to 11.1
Information in this document applies to any platform.
Goal
How to Purge a Distributed Transaction from a Database
Solution

If the remote database cannot be accessed, a failed distributed transaction 
cannot be committed or rolled back and so must be purged 
from the list of pending transactions.


1. Identify the id of the transaction:

SQL> column global_tran_id format a25
SQL> column database format a22
SQL> column global_name format a22
SQL> SELECT * from global_name;
SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;
SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;


2. Purge the transaction:

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('<transaction_id>');
SQL> COMMIT;

3. Confirm that the transaction has been purged:

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID,to_char(FAIL_TIME,'dd-mon-yyyy HH24:MI:SS'),STATE, MIXED FROM DBA_2PC_PENDING;
SQL> SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS;