一则ora-03113错误的解决

一客户反应在alert log中经常出现0ra-03113错误：

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

查看对应的trc文件，发现如下内容：

ERROR, tran=19.17.68930, session#=1, ose=0:
ORA-03113: end-of-file on communication channel
*** 2008-09-10 19:44:07.628
ERROR, tran=29.1.44284, session#=1, ose=0:
ORA-03113: end-of-file on communication channel
*** 2008-09-11 00:26:54.122
ERROR, tran=52.27.193, session#=1, ose=0:
ORA-03113: end-of-file on communication channel
*** 2008-09-11 09:01:44.147
ERROR, tran=9.22.2176, session#=1, ose=0:
ORA-03113: end-of-file on communication channel
*** 2008-09-11 16:48:32.073
ERROR, tran=19.40.146101, session#=1, ose=0:
ORA-03113: end-of-file on communication channel

也就是说是因为这些transaction错误导致了ORA-03113的发生。

查询dba_2pc_neighbors及dba_2pc_pending表:

SQL> select local_tran_id,in_out,database from dba_2pc_neighbors;

LOCAL_TRAN_ID          IN_ DATABASE
———————- — ———————————————-
19.40.146101           in
17.14.8483             in
19.17.68930            in
19.40.146101           out DBLK3.REGRESS.RDBMS.DEV.US. oracle .COM
17.14.8483             out DBLK1.REGRESS.RDBMS.DEV.US. oracle .COM
17.14.8483             out DBLK3.REGRESS.RDBMS.DEV.US. oracle .COM
19.17.68930            out DBLK1.REGRESS.RDBMS.DEV.US. oracle .COM

 SQL>select LOCAL_TRAN_ID,STATE,mixed,HOST from dba_2pc_pending;
 
 LOCAL_TRAN_ID          STATE            MIX HOST
———————- —————- — ——————————
52.27.193              collecting       no  WDE\IT15798A
9.22.2176              collecting       no  WDE\IT15614A
17.14.8483             collecting       no  WDE\IT15904Z
19.31.38686            collecting       no  WDE\IT15614A
19.17.68930            collecting       no  WDE\IT15614A
29.1.44284             collecting       no  WDE\IT15614A
19.40.146101           forced rollback  no  rw2

发现有很多分布式事务出现问题，举个例子，local_tran_id=19.40.146101,这个事务是从DBLK3取数据,然后在rw2进行相关处理，此时如果出现异常，比如用户人为强制结束这个事务就会造成事务异常中断，从而出现ora-03113错误。

解决方法：

1.禁用分布式恢复

SQL> ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

System altered.
 
2.清空in-doubt transaction entry：

SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(’1.60.1257421′);

PL/SQL procedure successfully completed.

3.然后启用分布式恢复：

SQL> ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

 参考文档：http://www.eygle.com/dodd/oracle-dba/#001789