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.09aad
is local tran 1.60.1257421 (hex=
insert pending collecting tran, scn=8914343855672 (hex=81b
然后时不时的会提示下面错误：
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.09aad
SQL> select * from DBA_2PC_NEIGHBORS;
LOCAL_TRAN_ID IN_ DATABASE DBUSER_OWNER I DBID SESS# BRANCH
---------------------- --- --------------- ------------------------------ ----------------- ---------- --------------------
1.
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--
Categories
Oracle DBA留言 (1)
学习了！
Posted by: mengzhaoliang at February 19, 2008 9:51 PM
