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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7220098/viewspace-175853/