ORA-02019的處理

今天早上過來，檢視測試庫的告警日誌發現，一直不斷的報錯:ORA-02019，具體如下：

alert輸出如下：

Errors in file /oracle/admin/UAT/bdump/uat_reco_16233.trc:
ORA-02019: connection description for remote database not found
Mon Feb 13 10:18:46 CST 2012
Errors in file /oracle/admin/UAT/bdump/uat_reco_16233.trc:
ORA-02019: connection description for remote database not found
Mon Feb 13 10:19:34 CST 2012
Errors in file /oracle/admin/UAT/bdump/uat_reco_16233.trc:
ORA-02019: connection description for remote database not found
Mon Feb 13 10:20:47 CST 2012
Errors in file /oracle/admin/UAT/bdump/uat_reco_16233.trc:
ORA-02019: connection description for remote database not found
Mon Feb 13 10:22:35 CST 2012
Errors in file /oracle/admin/UAT/bdump/uat_reco_16233.trc:
ORA-02019: connection description for remote database not found
Mon Feb 13 10:25:17 CST 2012
Errors in file /oracle/admin/UAT/bdump/uat_reco_16233.trc:
ORA-02019: connection description for remote database not found

對應的trace檔案內容如下：

[oracle@testdb ~]$ more /oracle/admin/UAT/bdump/uat_reco_16233.trc
/oracle/admin/UAT/bdump/uat_reco_16233.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0/db_1
System name:    Linux
Node name:      testdb
Release:        2.6.18-128.el5
Version:        #1 SMP Wed Dec 17 11:41:38 EST 2008
Machine:        x86_64
Instance name: UAT
Redo thread mounted by this instance: 1
Oracle process number: 5
Unix process pid: 16233, image: oracle@testdb (RECO)

*** SERVICE NAME:(SYS$BACKGROUND) 2012-02-13 10:18:13.429
*** SESSION ID:(1096.3) 2012-02-13 10:18:13.429
*** 2012-02-13 10:18:13.429
ERROR, tran=7.27.49932, session#=1, se=0:
ORA-02019: connection description for remote database not found
*** 2012-02-13 10:18:46.453
初步判斷以為是db-link問題，到資料庫裡查詢，並沒有這個db-link，仔細觀察trace檔案可以發現，這是個oracle的後臺程式（RECOVER程式），會話ID是1096，進一步觀察不難發現，應該是oracle的RECOVER程式要恢復一個transaction，其事務號：7.27.49932，而需要被恢復的資料庫連線名不正確，只要資料庫開啟狀態，該程式就會一直嘗試進行恢復，所以alert中會不斷的出現該錯誤，最後查詢metalink，有如下解釋：

When a failure occurs during commit processing, automatic recovery will

consistently resolve the results at all sites involved in the

transaction. However, if the remote database is destroyed or

recreated before recovery completes, then the entries used to

control recovery in DBA_2PC_PENDING and associated tables will never

be removed, and recovery will periodically retry. Procedure

purge_lost_db_entry allows removal of such transactions from the local site.

查詢對應檢視發現：

SELECT LOCAL_TRAN_ID,global_tran_id,STATE, MIXED,to_char(FAIL_TIME,'yyyy-mm-dd hh24:mi:ss')FAIL_TIME,
        to_char(RETRY_TIME,'yyyy-mm-dd hh24:mi:ss')Retry_Time
FROM DBA_2PC_PENDING;

7.27.49932 UAT.bb70863a.7.27.49932 collecting no 2011-09-29 18:23:40 2012-02-13 10:58:13

有問題的db-link 就是上面紅色部分，該事務第1次失敗時間是：2011-09-29 18:23:40 

最近的嘗試時間是：2012-02-13 10:58:13

此問題不影響測試資料庫的正常執行，可以忽略。

如需要解決，不再報錯，可以強制移掉此事務來解決。方法如下：

execute sys.dbms_transaction.purge_lost_db_entry('');

執行完上面的語句後再觀察alert，這個錯誤終於消失了。

[oracle@testdb ~]$ tail -f /oracle/admin/UAT/bdump/alert_UAT.log
ORA-02019: connection description for remote database not found
Mon Feb 13 10:47:29 CST 2012
Thread 1 advanced to log sequence 9006 (LGWR switch)
  Current log# 3 seq# 9006 mem# 0: /oracle/oradata/UAT/redo03.log
Mon Feb 13 10:47:37 CST 2012
Thread 1 advanced to log sequence 9007 (LGWR switch)
  Current log# 1 seq# 9007 mem# 0: /oracle/oradata/UAT/redo01.log
Mon Feb 13 10:58:13 CST 2012
Errors in file /oracle/admin/UAT/bdump/uat_reco_16233.trc:
ORA-02019: connection description for remote database not found
Mon Feb 13 11:08:30 CST 2012
ALTER SYSTEM ARCHIVE LOG
Mon Feb 13 11:08:30 CST 2012
Thread 1 advanced to log sequence 9008 (LGWR switch)
  Current log# 5 seq# 9008 mem# 0: /oracle/oradata/UAT/redo05.log
Mon Feb 13 11:09:14 CST 2012
Thread 1 advanced to log sequence 9009 (LGWR switch)
  Current log# 6 seq# 9009 mem# 0: /oracle/oradata/UAT/redo06.log

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25618347/viewspace-716114/，如需轉載，請註明出處，否則將追究法律責任。