ORA-4031導致CJQ程式出現ORA-1003錯誤
客戶資料庫出現ORA-4031錯誤,隨後出現了大量的ORA-1003和ORA-604錯誤。
資料庫版本為10.2.0.3 RAC for HP-UX,詳細的報錯資訊為:
Mon Jul 16 15:30:30 2012
Errors in file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_2389.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared
pool","select name,online$,contents...","sql
area","tmp")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 32 bytes of shared memory ("shared
pool","select name,online$,contents...","sql
area","tmp")
Mon Jul 16 15:30:32 2012
Errors in file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_2878.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 144 bytes of shared memory ("shared
pool","select
name,online$,contents...","Typecheck","coldef:
qcopCreateCol")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 144 bytes of shared memory ("shared
pool","select
name,online$,contents...","Typecheck","coldef:
qcopCreateCol")
Mon Jul 16 15:30:32 2012
Errors in file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_10030.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 144 bytes of shared memory ("shared
pool","select
name,online$,contents...","Typecheck","coldef:
qcopCreateCol")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 144 bytes of shared memory ("shared
pool","select
name,online$,contents...","Typecheck","coldef:
qcopCreateCol")
Mon Jul 16 15:30:39 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 32 bytes of shared memory ("shared
pool","select o.owner#,o.name,o.nam...","sql
area","kobjn : kkdcchs")
Mon Jul 16 15:30:40 2012
Trace dumping is performing id=[cdmp_20120716153040]
Mon Jul 16 15:32:19 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:33:59 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:35:39 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:37:19 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:39:00 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:40:40 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQ
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: err
Mon Jul 16 15:41:59 2012
Thread 2 advanced to log sequence 61522
Current log# 7 seq# 61522 mem# 0: +ORCL_CTL/orcl/onlinelog/group_7.263.611598065
Mon Jul 16 15:42:20 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:44:00 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:45:40 2012
Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQ
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at
recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: err
可以看得,開始還是比較“正常”的ORA-4031錯誤,但是隨著CJQ程式出現了ORA-4031的錯誤,資料庫開始每1分40秒報一次ORA-1003和ORA-604的錯誤。這裡CJQ程式似乎陷入了一個死迴圈中,持續不停的報錯。查詢MOS發現,在9.2中Oracle存在類似的BUG:Bug 3289063 ORA-1003 every 5 seconds after CJQ hits ORA-4031。這個bug已經在9.2.0.5和10.1.0.2中被FIXED,但是這個bug的現象和當前的錯誤完全一致,從這點上看,這個問題顯然在10.2.0.3中仍然存在。
Oracle給出的解決方案也很簡單,重啟CJQ程式既可。除了在作業系統去kill -9殺掉cjq0對應的程式之外,直接透過alter system set job_queue_processes=0,然後在設定回原值,也有可能解決該問題。此外,儘量避免ORA-4031錯誤的產生是最根本的問題解決之道。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-736197/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ASM例項出現ORA-4031錯誤導致例項崩潰ASM
- 11.2.0.3 ASM例項出現ORA-4031錯誤導致資料庫歸檔失敗ASM資料庫
- INTERVAL分割槽插入大量資料導致ORA-4031錯誤
- Oracle GoldenGate導致IMP出現ORACLE 32588錯誤OracleGo
- 刪除應用程式對映會導致OWA出現404錯誤
- 什麼會導致HTTP出現429請求過多錯誤?HTTP
- 一次錯誤使用 go-cache 導致出現的線上問題Go
- DBCA建庫導致已有資料庫出現ORA-27140錯誤資料庫
- 多餘索引導致explain錯誤索引AI
- 11.2.0.3 ASM例項出現ORA-4031導致資料庫歸檔失敗ASM資料庫
- impdp時parallel=4導致的錯誤Parallel
- 什麼會導致HTTP代理出現400錯誤請求HTTP
- GetDlgItem() 出現錯誤Git
- 版本不當導致的exp出錯
- merge語句導致的ORA錯誤分析
- shared_pool——解決ORA-4031錯誤
- JS · \r\n被轉義導致出錯JS
- xml中有特殊字元,導致解析時出錯XML字元
- 10.2出現reliable message資訊導致程式hang死
- Opencv出現detecMultiScale錯誤OpenCV
- Lombok 的@ToString導致的Maven編譯錯誤LombokMaven編譯
- ORA-04031錯誤導致當機案例分析
- 如何解決url傳參導致錯誤問題
- 修復svn hook導致的字符集錯誤Hook
- 錯誤思維導向致IT專案問題多
- sys密碼修改導致的RMAN-00571錯誤密碼
- goldengate命令輸入錯誤導致的血案2Go
- 編譯過程導致ORA-4068錯誤編譯
- CHAR型別函式索引導致結果錯誤型別函式索引
- SqlServer NBU備份出現錯誤程式碼2SQLServer
- 並行程式出現ORA-27090錯誤並行行程
- 程式出錯時如何找到錯誤?
- [譯] RxJS: 避免因濫用 switchMap 而導致錯誤JS
- 升級Xcode10導致的編譯錯誤XCode編譯
- autotrace 和explain plan for可能導致執行計劃錯誤AI
- 修改記憶體導致Ora-27100錯誤記憶體
- 資料庫升級導致ORA-918錯誤資料庫
- 執行計劃錯誤導致系統負載高負載