ORA-03113錯誤解決一例

大家知道，ORA-03113錯誤是Oracle資料庫常見的錯誤，導致這個錯誤的原因比較複雜，各種各樣的原因。可能是網路中斷引起的、也可能是資料庫本身出現了問題。

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

故障現象：

開始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

<<

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, se=0:

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

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

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

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

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

Oracle資料庫只有這些錯誤提示，其餘狀態均正常。

從錯誤提示看，應該是由於分佈事務由於人為cancel中止，引起的事務失敗，下面檢視相關資訊：

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檢視記錄等待恢復的分散式事務的資訊

dba_2pc_neighbors檢視記錄未決的分散式事務的輸入輸出連線資訊

有上述資訊分析原因，1.60.1257421事務的狀態為collecting，本機資料庫資料流向為in，遠端smsdbn資料庫流向為out。

詢問業務人員，確實執行過這麼個一儲存，中途手工中止了。並且是從smsdbn資料庫裡select資料然後update本地資料庫。這基本證實了我們的猜測。

下面嘗試force commit或者 force rollback此事務，

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狀態的事務不需要commit/rollback force。

我們現在需要做的就是：

（1） Disable分散式恢復

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 分散式恢復：

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

--The End--

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7220098/viewspace-175853/，如需轉載，請註明出處，否則將追究法律責任。

ORA-03113錯誤解決一例

相關文章