处理Oracle的分布式事务故障
最近两天生产数据库一直在报ORA-24756错误，查了MOS上的文档，看到一篇类似的，说是BUG(Bug 19201866 - RECO reports ORA-24756 repeatedly into trace file (文档 ID 19201866.8))，但是HP-UX上的11.2.0.4没有解决需要升级到12.2版本，文档中还说重启实例也可以解决，但是不现实。
于是查报错时的trace文件都是如下的内容
1 2 3 4 | *** 2017-02-04 09:17:56.058ERROR, tran=42.13.2709343, ose=0:ORA-24756:...... |
看到tran=42.13.2709343，印象中是分布式事务，于是查DBA_2PC_PENDINGS视图
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SYS@db1> COL LOCAL_TRAN_ID FORMAT A13SYS@db1> COL GLOBAL_TRAN_ID FORMAT A90SYS@db1> COL STATE FORMAT A10SYS@db1> COL MIXED FORMAT A3SYS@db1> COL HOST FORMAT A10SYS@db1> COL COMMIT# FORMAT A15SYS@db1> SET LINESIZE 240SYS@db1> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE,FAIL_TIME,FORCE_TIME,RETRY_TIME, MIXED, HOST, COMMIT# 2 FROM DBA_2PC_PENDING 3 /LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE FAIL_TIME FORCE_TIME RETRY_TIME MIX HOST COMMIT#------------- ------------------------------------------------------------------------------------------ ---------- ----------------- ----------------- ----------------- --- ---------- ---------------42.13.2709343 1096044365.31302E3235352E3233322E32332E746D313438363137313036383638333230333633 collecting 20170204 09:17:55 20170206 01:12:41 no bosbpm4s 764631398601 |
看到LOCAL_TRAN_ID和trace文件中的tran一致，刚开始的设想是正确的。其中FAIL_TIME是20170204 09:17:55对应到后台的alert日志中，看到这个时间前后的报错信息
1 2 3 4 5 6 7 8 9 10 11 12 13 | Sat Feb 04 09:17:50 2017Error 22 trapped in 2PC on transaction 42.13.2709343. Cleaning up.Error stack returned to user:ORA-02050: transaction 42.13.2709343 rolled back, some remote DBs may be in-doubtORA-00022: invalid session ID; access deniedORA-02063: preceding line from LINK_DB2Sat Feb 04 09:17:56 2017DISTRIB TRAN 41544f4d.31302E3235352E3233322E32332E746D313438363137313036383638333230333633 is local tran 42.13.2709343 (hex=2a.0d.29575f) insert pending collecting tran, scn=764631398601 (hex=b2.079538c9)Sat Feb 04 09:17:56 2017Errors in file /Oracle11g/app/oracle/diag/rdbms/db1/db1/trace/db1_reco_23402.trc:ORA-24756: |
这种分布式事务故障可能会锁定数据导致其他事务报ORA-01591报错或者一直占用UNDO段不能被别的事务重用。需要手工处理这种故障。
1 2 3 4 5 6 7 8 9 10 11 12 13 | SYS@db1> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('42.13.2709343');PL/SQL procedure successfully completed.SYS@db1> COMMIT;COMMIT complete.SYS@db1> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE,FAIL_TIME,FORCE_TIME,RETRY_TIME, MIXED, HOST, COMMIT# 2 FROM DBA_2PC_PENDING 3 /no rows selected |
清理完毕。
管理分布式事务的官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_txnman.htm#ADMIN12252
MOS文档：How to Purge a Distributed Transaction from a Database (文档 ID 159377.1) ORA-30019 When Executing Dbms_transaction.Purge_lost_db_entry (文档 ID 290405.1)
相关推荐
spurity 2020-11-10
LeeLuffy 2020-10-16
DriveCar 2020-09-07
zjuwangleicn 2020-09-04
quniMdejiangyou 2020-08-15
gamestart0 2020-08-15
loviezhang 2020-08-08
gaozhennan 2020-08-03
mcvsyy 2020-08-02
happinessaflower 2020-07-29
花落花开春去秋来 2020-07-29
heniancheng 2020-07-26
牧场SZShepherd 2020-07-20
jakefei 2020-07-19
牧场SZShepherd 2020-07-19
qingjiuquan 2020-07-18
云之飞踢 2020-07-08
zhanbuquan 2020-07-04