eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 祝福大家新年好 2008梦想成真 | Blog首页 | RAC环境下根据sequence恢复指定日志 »

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 <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数据库只有这些错误提示,其余状态均正常。

 

从错误提示看,应该是由于分布事务由于人为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                   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.

 

2Puege(清空)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--


历史上的今天...
    >> 2009-02-07文章:
    >> 2007-02-07文章:
    >> 2006-02-07文章:
           DBWR enhancements In Oracle10g
    >> 2005-02-07文章:
           如何获得Oracle的隐含参数

无觅

By eygle on 2008-02-07 22:57 | Comments (0) | Case | 2702 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com