資料庫日誌中出現啟動JOB程式的TIMED OUT資訊

在資料庫的alert檔案中發現了錯誤資訊。

 

 

詳細錯誤資訊如下：

Tue Nov 17 17:02:02 2009
Timed out trying to start process J000.
Tue Nov 17 17:04:08 2009
Timed out trying to start process J000.
Tue Nov 17 17:06:14 2009
Timed out trying to start process J000.
Tue Nov 17 17:08:19 2009
Timed out trying to start process J000.
Tue Nov 17 17:10:30 2009
Timed out trying to start process J000.
Tue Nov 17 17:12:36 2009
Timed out trying to start process J000.
Tue Nov 17 17:14:42 2009
Timed out trying to start process J000.
Tue Nov 17 17:16:47 2009
Timed out trying to start process J000.
Tue Nov 17 17:18:53 2009
Timed out trying to start process J000.
Tue Nov 17 17:20:59 2009
Timed out trying to start process J000.

從上面的資訊看，問題發生在啟動JOB程式的時候，不過資料庫沒有其他任何的錯誤資訊，無法透過其他的資訊來判斷是什麼問題導致了這個錯誤的產生。

由於沒有其他的資訊，只好以上面的資訊為關鍵字查詢了metalink，結果也沒有找到任何有價值的資訊。

由於從資料庫和作業系統中都沒有找到導致問題的原因，本打算放棄，結果在檢查其他的資料庫的時候意外的發現了線索：

Tue Nov 17 17:06:13 2009
Errors in file /data/oracle/admin/jiangsu/bdump/jiangsu_j000_16007.trc:
ORA-12012: error on auto execute of job 101
ORA-12048: error encountered while refreshing materialized view "NDMAIN"."CAT_PRODUCT"
ORA-04052: error occurred when looking up remote object SELE.SYS@DATA01.US.ORACLE.COM
ORA-00604: error occurred at recursive SQL level 3
ORA-02068: following severe error from DATA01
ORA-00020: maximum number of processes (300) exceeded
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Tue Nov 17 17:08:18 2009
Errors in file /data/oracle/admin/jiangsu/bdump/jiangsu_j000_16009.trc:
ORA-12012: error on auto execute of job 101
ORA-12048: error encountered while refreshing materialized view "NDMAIN"."CAT_PRODUCT"
ORA-04052: error occurred when looking up remote object SELE.SYS@DATA01.US.ORACLE.COM
ORA-00604: error occurred at recursive SQL level 3
ORA-02068: following severe error from DATA01
ORA-00020: maximum number of processes (300) exceeded
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
Tue Nov 17 17:12:18 2009
Errors in file /data/oracle/admin/jiangsu/bdump/jiangsu_j000_16017.trc:
ORA-12012: error on auto execute of job 101
ORA-12048: error encountered while refreshing materialized view "NDMAIN"."CAT_PRODUCT"
ORA-04052: error occurred when looking up remote object SELE.SYS@DATA01.US.ORACLE.COM
ORA-00604: error occurred at recursive SQL level 3
ORA-02068: following severe error from DATA01
ORA-00020: maximum number of processes (300) exceeded
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

這個資料庫透過資料庫鏈訪問的物件就是前面出錯的物件，而這裡可以輕易的發現導致問題的原因是由於目標資料庫的啟動程式已經超過了初始化引數設定。

根據這個錯誤的時間，可以確定，導致本機上JOB程式啟動失敗的原因肯定也是由於processes超過了300。

雖然目標資料庫的訪問很小，但是忙時程式超過300的可能性還是很大的，顯然這個初始化引數的設定偏小。

沒想到本機的資訊都無法定位的問題，卻透過其他伺服器上的資訊得到解決。