alert日志報錯信息：
Wed Jun 18 08:39:12 2008
Error 2068 trapped in 2PC on transaction 21.3.5270155. Cleaning up.
Wed Jun 18 08:39:12 2008
DISTRIB TRAN BIMS.888c7a47.21.3.5270155
  is local tran 21.3.5270155 (hex=15.03.506a8b)
  insert pending collecting tran, scn=26446089449 (hex=6.284f4ce9)
Wed Jun 18 08:43:21 2008
Thread 1 advanced to log sequence 242663
  Current log# 1 seq# 242663 mem# 0: /oraredo/redo01.log
Wed Jun 18 08:43:21 2008
ARC0: Evaluating archive   log 3 thread 1 sequence 242662
ARC0: Beginning to archive log 3 thread 1 sequence 242662
Creating archive destination LOG_ARCHIVE_DEST_1: '/archivelog/1_242662.arc'
ARC0: Completed archiving  log 3 thread 1 sequence 242662
Wed Jun 18 08:47:49 2008
Error stack returned to user:
ORA-02050: transaction 21.3.5270155 rolled back, some remote DBs may be in-doubt
ORA-02068: following severe error from RADIUSC
ORA-12570: TNS:packet reader failure
Wed Jun 18 08:48:46 2008

Wed Jun 18 09:03:51 2008
Error 2068 trapped in 2PC on transaction 7.7.9236646. Cleaning up.
Error stack returned to user:
ORA-02068: following severe error from RADIUSD
ORA-12570: TNS:packet reader failure
ORA-02068: following severe error from RADIUSC
ORA-12570: TNS:packet reader failure

原因分析：

DBA_2PC_PENDING

Oracle會自動處理分布事務(wù)，保證分布事務(wù)的一致性，所有站點全部提交或全部回滾。一般情況下，處理過程在很短的時間內(nèi)完成，根本無法察覺到。但是，如果在commit或rollback的時候，出現(xiàn)了連接中斷或某個數(shù)據(jù)庫站點CRASH的情況，則提交操作可能會無法繼續(xù)，此時DBA_2PC_PENDING和DBA_2PC_NEIGHBORS中會包含尚未解決的分布事務(wù)。

對于絕大多數(shù)情況，當(dāng)恢復(fù)連接或CRASH的數(shù)據(jù)庫重新啟動后，會自動解決分布式事務(wù)，不需要人工干預(yù)。只有分布事務(wù)鎖住的對象急需被訪問，鎖住的回滾段阻止了其他事務(wù)的使用，網(wǎng)絡(luò)故障或CRASH的數(shù)據(jù)庫的恢復(fù)需要很長的時間等情況出現(xiàn)時，才使用人工操作的方式來維護分布式事務(wù)。

解決過程：

SQL> conn /as sysdba
已連接。
SQL> select LOCAL_TRAN_ID,STATE,MIXED,ADVICE,FAIL_TIME,RETRY_TIME,HOST from dba_2pc_pending;

LOCAL_TRAN_ID   STATE            MIX A FAIL_TIME           RETRY_TIME
--------------- ---------------- --- - ------------------- -------------------
HOST
--------------------------------------------------------------------------------------------------------------------------------
21.3.5270155    collecting       no    2008-06-18 08:39:12 2008-06-18 09:41:34
bimsb

SQL> col LOCAL_TRAN_ID for a15
SQL> col IN_OUT for a10
SQL> col DATABASE for a10
SQL> col INTERFACE for a20
SQL> set line 132
SQL> SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, INTERFACE FROM DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID   IN_OUT     DATABASE   INTERFACE
--------------- ---------- ---------- --------------------
21.3.5270155    in                    N
21.3.5270155    out        RADIUSA    N
21.3.5270155    out        RADIUSB    N
21.3.5270155    out        RADIUSC    N
21.3.5270155    out        RADIUSD    N

SQL> COMMIT FORCE '21.3.5270155';
COMMIT FORCE '21.3.5270155'
*
ERROR 位于第 1 行:
ORA-02058: 未找到 ID21.3.5270155的準(zhǔn)備事務(wù)處理

SQL> COMMIT FORCE '21.3.5270155','26446089449';
COMMIT FORCE '21.3.5270155','26446089449'
*
ERROR 位于第 1 行:
ORA-02058: 未找到 ID21.3.5270155的準(zhǔn)備事務(wù)處理

SQL> COMMIT FORCE 'BIMS.888c7a47.21.3.5270155';
COMMIT FORCE 'BIMS.888c7a47.21.3.5270155'
*
ERROR 位于第 1 行:
ORA-02058: 未找到 IDBIMS.888c7a47.21.3.5270155的準(zhǔn)備事務(wù)處理

SQL> select count(*) from pending_trans$;

  COUNT(*)
----------
         1

SQL> select LOCAL_TRAN_ID,STATUS,STATE,TOP_DB_USER from pending_trans$;

LOCAL_TRAN_ID   S STATE            TOP_DB_USER
--------------- - ---------------- ------------------------------
21.3.5270155    P collecting       BILL

SQL> COMMIT FORCE '21.3.5270155';
COMMIT FORCE '21.3.5270155'
*
ERROR 位于第 1 行:
ORA-02058: 未找到 ID21.3.5270155的準(zhǔn)備事務(wù)處理

SQL> COMMIT FORCE '21.3.5270155';
COMMIT FORCE '21.3.5270155'
*
ERROR 位于第 1 行:
ORA-02058: 未找到 ID21.3.5270155的準(zhǔn)備事務(wù)處理

SQL> disc
從Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production中斷開
SQL> conn /as sysdba
已連接。
SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('21.3.5270155');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('21.3.5270155'); END;

*
ERROR 位于第 1 行:
ORA-30019: 自動撤消模式中的回退段操作非法
ORA-06512: 在"SYS.DBMS_TRANSACTION", line 65
ORA-06512: 在"SYS.DBMS_TRANSACTION", line 85
ORA-06512: 在line 1

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID
---------------
GLOBAL_TRAN_ID
------------------------------------------------------------------------------------------------------------------------------------
21.3.5270155
BIMS.888c7a47.21.3.5270155

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(''); END;

*
ERROR 位于第 1 行:
ORA-30019: 自動撤消模式中的回退段操作非法
ORA-06512: 在"SYS.DBMS_TRANSACTION", line 65
ORA-06512: 在"SYS.DBMS_TRANSACTION", line 85
ORA-06512: 在line 1

SQL> commit;

提交完成。

SQL> alter session set "_smu_debug_mode" = 4;

會話已更改。

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('21.3.5270155');

PL/SQL 過程已成功完成。

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;

未選定行

SQL> SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, INTERFACE FROM DBA_2PC_NEIGHBORS;

未選定行

SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;

未選定行

SQL> commit;

提交完成。

SQL> exit
從Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production中斷開
bimsb%

========metalink.oracle.com================
關(guān)鍵Doc ID:  Note:274321.1 其它相關(guān)：Doc ID:  Note:100664.1 Doc ID:  Note:401302.1
===========================================
Subject:  While Trying to Commit or Rollback a Pending Transaction Getting Errors ORA-02058,ORA-01453,ORA-06512
  Doc ID:  Note:274321.1 Type:  PROBLEM
  Last Revision Date:  08-JAN-2008 Status:  MODERATED

In this Document
  Symptoms
  Cause
  Solution
  References

--------------------------------------------------------------------------------

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2
This problem can occur on any platform.

Symptoms
While trying to commit or rollback a pending transaction getting error ora-02058...
Subsequently when trying to purge the pending transactions using the
procedure "dbms_transaction.purge_lost_db_entry" gives the following errors..

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386');

BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386'); END;
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108'); 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

Cause
If the remote database no longer exists then the transaction will have to be
purged from the list of pending distributed transactions.
The transaction to be deleted is in the prepared state...
Solution
If the command causes an ORA-02058 error to occur, it means 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.

Follow the instructions on how to purge a
distributed transaction from the database.
=================================
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> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;

2. Purge the transaction:

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

3. Confirm that the transaction has been purged:

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID FROM DBA_2PC_PENDING;

Step 2:
=====
If you get the following errors while purging transactions using "dbms_transaction.purge_lost_db_entry"

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386');

BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.23.2386'); END;
*
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1

Fix:
===
This problem is logged as
Bug.2191458 (unpublished) UNABLE TO EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY WITH AUTO UNDO MANAGEMENT and is worked by development.

Use the following workaround:

You have to use local_tran_id.....

Issue commit before alter system set "_smu_debug_mode" = 4;

Follow the steps,

SQL>commit;

SQL> alter session set "_smu_debug_mode" = 4;

SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

Step 3:
=====

When executing the following procedure(dbms_transaction.purge_lost_db_entry)
to delete entries from
dba_2pc_pending one encounters the following error:

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108'); ==>For example..
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('37.16.108'); 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

Fix:
===

The transaction to be deleted is in the prepared state and has to be either
force committed or rolled back

SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 prepared

SQL> rollback force '37.16.108'; ==>For example

Rollback complete.

SQL> select LOCAL_TRAN_ID,STATE from dba_2pc_pending;

LOCAL_TRAN_ID STATE
---------------------- ----------------
37.16.108 forced rollback

SQL>Commit;

SQL>alter system set "_smu_debug_mode" = 4;
Rollback complete.

SQL> exec dbms_transaction.purge_lost_db_entry('37.16.108'); ==>For example

References
Bug 2740481 - ORA-1591 WHEN INSERTING A ROW THAT HAS NO RELATION WITH THE IN-DOUBT TXN

============

其它參考：

http://blog./u/12960/showart_457785.html

另外： http://www./dodd/2008/02/ora03113.html

大家知道，ORA-03113錯誤是Oracle數(shù)據(jù)庫常見的錯誤，導(dǎo)致這個錯誤的原因比較復(fù)雜，各種各樣的原因?？赡苁蔷W(wǎng)絡(luò)中斷引起的、也可能是數(shù)據(jù)庫本身出現(xiàn)了問題。

下面就一個案例，分析一下ORA-03113錯誤。

故障現(xiàn)象：

開始alert文件提示錯誤：

Error 1013 trapped in 2PC on transaction 1.60.1257421. Cleaning up.

Error stack returned to user:

ORA-02050: transaction 1.60.1257421 rolled back, some remote DBs may be in-doubt

ORA-01013: user requested cancel of current operation

ORA-06553: PLS-103: Encountered the symbol "EXCEPTION" when expecting one of the following:

   begin case declare exit for function goto if loop mod null

   package pragma procedure raise return select separate type

   update while with <an identifier>

   <a double-quoted delimited-identifier> <a bind variable> <<

   form table call close current define delete fetch lock

Mon Feb 18 09:07:19 2008

DISTRIB TRAN SMSBOSS.09aad41c.1.60.1257421

  is local tran 1.60.1257421 (hex=01.3c.132fcd)

  insert pending collecting tran, scn=8914343855672 (hex=81b.884c8638)

然后時不時的會提示下面錯誤：

ERROR, tran=1.60.1257421, session#=1, ose=0:

ORA-03113: end-of-file on communication channel

*** 2008-02-18 09:45:25.919

ERROR, tran=1.60.1257421, session#=1, ose=0:

ORA-03113: end-of-file on communication channel

*** 2008-02-18 10:19:42.891

Oracle數(shù)據(jù)庫只有這些錯誤提示，其余狀態(tài)均正常。

從錯誤提示看，應(yīng)該是由于分布事務(wù)由于人為cancel中止，引起的事務(wù)失敗，下面查看相關(guān)信息：

SQL> select LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,HOST,COMMIT# from dba_2pc_pending;

LOCAL_TRAN_ID    GLOBAL_TRAN_ID                        STATE         MIX       HOST                              COMMIT#

---------------------- -------------------- ---------------- --- -------------------- ----------------

1.60.1257421               SMSBOSS.09aad41c.1.60.1257421  collecting       no      WORKGROUP\LIUQING  8914343855672

SQL> select * from DBA_2PC_NEIGHBORS;

LOCAL_TRAN_ID          IN_      DATABASE        DBUSER_OWNER                   I  DBID                  SESS# BRANCH

---------------------- --- --------------- ------------------------------ ----------------- ---------- --------------------

1.60.1257421                     in                                       BOSSMGR                             N                                  1            0000

1.60.1257421                      out      SMSDBN          BOSSMGR                              N      cc3ddb9b              1            4

select * from DBA_2PC_PENDING@smsdbn;

no rows selected

select * from DBA_2PC_NEIGHBORS@smsdbn;

no rows selected

dba_2pc_pending視圖記錄等待恢復(fù)的分布式事務(wù)的信息

dba_2pc_neighbors視圖記錄未決的分布式事務(wù)的輸入輸出連接信息

有上述信息分析原因，1.60.1257421事務(wù)的狀態(tài)為collecting，本機數(shù)據(jù)庫數(shù)據(jù)流向為in，遠(yuǎn)端smsdbn數(shù)據(jù)庫流向為out。

詢問業(yè)務(wù)人員，確實運行過這么個一存儲，中途手工中止了。并且是從smsdbn數(shù)據(jù)庫里select數(shù)據(jù)然后update本地數(shù)據(jù)庫。 這基本證實了我們的猜測。

下面嘗試force commit或者 force rollback此事務(wù)，

SQL> commit force '1.60.1257421';

commit force '1.60.1257421'

*

ERROR at line 1:

ORA-02058: no prepared transaction found with ID 1.60.1257421

上述錯誤的原因是由于collecting狀態(tài)的事務(wù)不需要commit/rollback force。

我們現(xiàn)在需要做的就是：

（1） Disable分布式恢復(fù)

SQL> ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

System altered.

（2）Puege(清空)in-doubt transaction entry：

SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.60.1257421');

PL/SQL procedure successfully completed.

（3）然后enable 分布式恢復(fù)：

SQL> ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

參考信息/更多閱讀：

https://metalink.oracle.com

Note:1012842.102

ORA-2019 ORA-2058 ORA-2068 ORA-2050: Failed Distributed Transactions

Note:100664.1

How to Troubleshoot Distributed Transactions

Note:274321.1

While Trying to Commit or Rollback a Pending Transaction Getting Errors ORA-02058,ORA-01453,ORA-06512

Note:126069.1

Manually Resolving In-Doubt Transactions: Different Scenarios