当前页面位置: » 丰搜网 » 文档中心 » 详细内容
ora-01591故障处理
早晨到办公室听同事说表被锁了，一试，发现表中某字段为1111111的行都被锁了，select都不行。报错误ora-01591，打开toad的knowledge expert，描述很少，只是说由于分布式事务错误而造成锁定。询问同事，昨天通过一个存储过程调用另一个存储过程出了错误，而后者通过透明网关insert一些数据到sql server数据库。
立即想到打开oem，谁知道大失所望，进入锁，根本没发现相关的对象被锁定，开始有点郁闷。转而检查会话，该用户有5个会话，都是inactive，不管三七二十一，全部杀掉。结果依旧，并且锁也没有出现。远程登陆上主机，发现cpu和进程都正常，也没有发现透明网关进程挂死（之前曾发现tg4sql在无业务量时也会出现25%左右的cpu，挂死）。
突然想到看看alert.log，经过仔细搜索，终于发现：
wed nov 17 00:00:04 2004
errors in file d:\oracle\admin\xdcj\udump\xdcj_j006_3020.trc:
ora-12012: 自动执行作业 82 出错
ora-01591: 锁定已被有问题的分配事务处理6.5.887985挂起
ora-06512: 在line 6
这正是出错的地方，往前追溯：
tue nov 16 17:35:04 2004
error 28500 trapped in 2pc on transaction 6.5.887985. cleaning up.
error stack returned to user:
ora-02054: 事务处理6.5.887985有问题
ora-28500: 连接 oracle 到非 oracle 系统时返回此信息:
[transparent gateway for mssql]
ora-02063: 紧接着2 lines（源于zsmos_crm）
tue nov 16 17:35:04 2004
distrib tran qdcj.us.oracle.com.5ae32328.6.5.887985
is local tran 6.5.887985 (hex=06.05.d8cb1)
insert pending prepared tran, scn=6606197672830 (hex=602.2010cb7e)
tue nov 16 17:35:07 2004
errors in file d:\oracle\admin\xdcj\bdump\xdcj_reco_3024.trc:
ora-28500: connection from oracle to a non-oracle system returned this message:
[transparent gateway for mssql][microsoft][odbc sql server driver][sql server]用户 'recover' 登录失败。 (sql state: 28000; sql code: 18456)
ora-02063: preceding 2 lines from zsmos_crm
tue nov 16 17:35:12 2004
errors in file d:\oracle\admin\xdcj\bdump\xdcj_reco_3024.trc:
ora-28500: connection from oracle to a non-oracle system returned this message:
[transparent gateway for mssql][microsoft][odbc sql server driver][sql server]用户 'recover' 登录失败。 (sql state: 28000; sql code: 18456)
ora-02063: preceding 2 lines from zsmos_crm
这就是事发地点了。看来是昨天下午远程事务失败，但是又没有返回造成分布式事务挂死，从而锁定了行。终于找到了详细的错误ora-02054，进入toad一查，说是要等待或者提交该事务，可是怎么操作呢。还是打开官方文档搜索相关内容，在adminstrator guide中发现如下内容：
discovering problems with a two-phase commit
the user application that commits a distributed transaction is informed of a problem by one of the following error messages:
ora-02050: transaction id rolled back,
some remote dbs may be in-doubt
ora-02051: transaction id committed,
some remote dbs may be in-doubt
ora-02054: transaction id in-doubt
a robust application should save information about a transaction if it receives any of the above errors. this information can be used later if manual distributed transaction recovery is desired.
no action is required by the administrator of any node that has one or more in-doubt distributed transactions due to a.network or system failure. the automatic recovery features of oracle transparently complete any in-doubt transaction so that the same outcome occurs on all nodes of a session tree (that is, all commit or all roll back) after the.network or system failure is resolved.
