ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-03113错误解决一例

ORA-03113错误解决一例

原创 Linux操作系统 作者:Dodd 时间:2008-02-19 10:12:16 0 删除 编辑

 

大家知道,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                   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--

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7220098/viewspace-175853/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-22

  • 博文量
    46
  • 访问量
    159088