ORA-04031導致資料庫宕機問題分析
背景介紹
2014/6/5接渠道反饋,使用者資料庫意外宕機,後經過重啟伺服器Oracle資料庫恢複正常,使用者希望能夠排查原因,避免再次出現宕機事故,這種意外宕機原因排查是我們遠端經常遇到的案例,雖然宕機的原因有很多,但是排查的步驟基本一致,都一個固定的套路,接下來就介紹下如何一步步定位問題。
分析步驟
步驟一.查詢alert日誌,尋找錯誤資訊
本案例這種情況,是屬於事後分析,這種分析的方法只有一個,就是查看日誌,如果是作業系統,就要查看作業系統的日誌,是資料庫自然就要查看資料庫的日誌。我們知道資料庫有很多日誌,但是最關鍵的日誌就是alert日誌,如果是RAC環境可能會涉及到CRS的日誌,這裡是單機環境,我們就只需要關注alert日誌就可以,alert日誌的路徑和日誌名稱就不用太多介紹,作為DBA這點常識還是應該有,拿到alert日誌首先就是查看資料庫宕機時間段前後有什麼異常記錄,如下本案例在宕機前有如下錯誤提示資訊:
Errors infile d:\oracle\product\10.2.0\admin\oraxy\bdump\oraxy_cjq0_4340.trc:
ORA-00604:遞迴SQL 層級 1出現錯誤
ORA-04031:無法分配32 位元組的共用記憶體("shared pool","select job, nvl2(last_date, ...","sqlarea","tmp")
Wed Jun04 18:59:17 2014
Errors infile d:\oracle\product\10.2.0\admin\oraxy\bdump\oraxy_cjq0_4340.trc:
ORA-00604:遞迴SQL 層級 1出現錯誤
ORA-04031:無法分配32 位元組的共用記憶體("shared pool","select count(*) from sys.job...","sqlarea","tmp")
Wed Jun04 18:59:22 2014
Errors infile d:\oracle\product\10.2.0\admin\oraxy\bdump\oraxy_cjq0_4340.trc:
ORA-00604:遞迴SQL 層級 1出現錯誤
ORA-04031:無法分配32 位元組的共用記憶體("shared pool","select job, nvl2(last_date, ...","sqlarea","tmp")
Wed Jun04 18:59:22 2014
Errors infile d:\oracle\product\10.2.0\admin\oraxy\bdump\oraxy_cjq0_4340.trc:
ORA-00604:遞迴SQL 層級 1出現錯誤
ORA-04031:無法分配32 位元組的共用記憶體("shared pool","select count(*) from sys.job...","sqlarea","tmp")
Wed Jun04 18:59:27 2014
Errors infile d:\oracle\product\10.2.0\admin\oraxy\bdump\oraxy_cjq0_4340.trc:
ORA-00604:遞迴SQL 層級 1出現錯誤
ORA-04031:無法分配32 位元組的共用記憶體("shared pool","select job, nvl2(last_date, ...","sqlarea","tmp")
通過日誌資訊,可以初步定位到這次故障的起因是ora-00604和ora-04031引起,為了進一步準確定位,我們應該查看提示中的子追蹤檔案,如上面的d:\oracle\product\10.2.0\admin\oraxy\bdump\oraxy_cjq0_4340.trc,這裡面肯定包含了更多詳細的資訊。
Linux-6-64下安裝Oracle 12C筆記
在CentOS 6.4下安裝Oracle 11gR2(x64)
Oracle 11gR2 在VMWare虛擬機器中安裝步驟
Debian 下 安裝 Oracle 11g XE R2
步驟二.查看追蹤記錄檔,尋找更詳細的資訊
追蹤記錄檔這個查看就需要一些經驗和對問題的敏感度定位錯誤了,根據跟蹤對對象的不同,其日誌內容格式也不同,如下是我們這次跟蹤的關鍵資訊:
SO:000007FF493D38A0, type: 4, owner: 000007FF49005208, flag: INIT/-/-/0x00
(session) sid: 543 trans: 0000000000000000,creator: 000007FF49005208, flag: (51) USR/- BSY/-/-/-/-/-
DID: 0001-0016-00000003, short-termDID: 0000-0000-00000000
txn branch: 0000000000000000
oct: 0, prv: 0, sql:0000000000000000, psql: 0000000000000000, user: 0/SYS
last wait for 'SGA: allocation forcing component growth' blocking sess=0x0000000000000000 seq=30782wait_time=15629 seconds since wait started=0
=0, =0, =0
Dumping Session Wait History
for 'SGA: allocation forcing component growth'count=1 wait_time=15629
=0, =0, =0
for 'SGA: allocation forcing componentgrowth' count=1 wait_time=15006
=0, =0, =0
for 'latch: shared pool'count=1 wait_time=624
address=c96aed8, number=d6, tries=1
for 'latch: shared pool' count=1wait_time=1214
address=c96aed8, number=d6, tries=0
for 'latch: library cache' count=1wait_time=77
address=324ef0f0, number=d7, tries=0
for 'latch: shared pool' count=1wait_time=1369765
address=c96aed8, number=d6, tries=0
for 'rdbms ipc message' count=1wait_time=5007402
timeout=1f4, =0, =0
for 'rdbms ipc message' count=1wait_time=5006909
timeout=1f4, =0, =0
for 'rdbms ipc message' count=1wait_time=5007270
timeout=1f4, =0, =0
for 'rdbms ipc message' count=1wait_time=5004478
timeout=1f4, =0, =0
temporary object counter: 0
----------------------------------------
UOL used: 0 locks(used=1, free=4)
KGXAtomic Operation Log 000007FF35B23660
Mutex 0000000000000000(0, 0) idn 0 oper NONE
Cursor Parent uid 543 efd 10 whr 4 slp 0
oper=NONE pt1=000007FF2DD5ECA8pt2=000007FF2DD5ED10 pt3=000007FF2DD5F230
pt4=0000000000000000 u41=2 stt=0
KGXAtomic Operation Log 000007FF35B236A8
Mutex 000007FF2A744D18(0, 12) idn 0 oper NONE
Cursor Stat uid 543 efd 11 whr 2 slp 0
oper=NONE pt1=000007FF2A744BE8pt2=0000000000000000 pt3=0000000000000000
pt4=0000000000000000 u41=0 stt=0
KGXAtomic Operation Log 000007FF35B236F0
Mutex 0000000000000000(0, 0) idn 0 oper NONE
Library Cache uid 543 efd 0 whr 0 slp 0
更多詳情見請繼續閱讀下一頁的精彩內容: