大家知道，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;
参考信息/更多阅读：
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
Note:126069.1
Manually Resolving In-Doubt Transactions: Different Scenarios
--The End--
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7220098/viewspace-175853/ ，如需转载，请注明出处，否则将追究法律责任。
