alert日志報(bào)錯(cuò)信息：
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會(huì)自動(dòng)處理分布事務(wù)，保證分布事務(wù)的一致性，所有站點(diǎn)全部提交或全部回滾。一般情況下，處理過(guò)程在很短的時(shí)間內(nèi)完成，根本無(wú)法察覺(jué)到。但是，如果在commit或rollback的時(shí)候，出現(xiàn)了連接中斷或某個(gè)數(shù)據(jù)庫(kù)站點(diǎn)CRASH的情況，則提交操作可能會(huì)無(wú)法繼續(xù)，此時(shí)DBA_2PC_PENDING和DBA_2PC_NEIGHBORS中會(huì)包含尚未解決的分布事務(wù)。

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

解決過(guò)程：

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中斷開(kāi)
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: 自動(dòng)撤消模式中的回退段操作非法
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: 自動(dòng)撤消模式中的回退段操作非法
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;

會(huì)話已更改。

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

PL/SQL 過(guò)程已成功完成。

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中斷開(kāi)
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錯(cuò)誤是Oracle數(shù)據(jù)庫(kù)常見(jiàn)的錯(cuò)誤，導(dǎo)致這個(gè)錯(cuò)誤的原因比較復(fù)雜，各種各樣的原因?？赡苁蔷W(wǎng)絡(luò)中斷引起的、也可能是數(shù)據(jù)庫(kù)本身出現(xiàn)了問(wèn)題。

下面就一個(gè)案例，分析一下ORA-03113錯(cuò)誤。

故障現(xiàn)象：

開(kāi)始alert文件提示錯(cuò)誤：

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)

然后時(shí)不時(shí)的會(huì)提示下面錯(cuò)誤：

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ù)庫(kù)只有這些錯(cuò)誤提示，其余狀態(tài)均正常。

從錯(cuò)誤提示看，應(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，本機(jī)數(shù)據(jù)庫(kù)數(shù)據(jù)流向?yàn)?FONT face="Times New Roman">in，遠(yuǎn)端smsdbn數(shù)據(jù)庫(kù)流向?yàn)?FONT face="Times New Roman">out。

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

下面嘗試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

上述錯(cuò)誤的原因是由于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