Library cache pin問題的處理過程

hooca發表於2016-05-31
本文是《DBA手記1》的讀書筆記，相關文章：ORA-600(17069)錯誤的解決過程

1. 故障的確定：
ORA-00600 Error的通用處理 

2. 故障的分析
1）根據已經確認的問題是library cache pin，加上若能在trace檔案中找出SID，可用以下語句確認

點選(此處)摺疊或開啟

  1. SELECT EVENT,P1TEXT,P1RAW,P2TEXT,P2RAW,STATE
  2. FROM V$SESSION_WAIT WHERE SID=75;

查詢結果的EVENT就是library cache pin

2）確認Job呼叫的儲存過程狀態

點選(此處)摺疊或開啟

  1. SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
  2. FROM DBA_OBJECTS
  3. WHERE OWNER = :1
  4. AND OBJECT_NAME = :2;
此時該儲存過程的STATUS為INVALID

可先關閉該Job

點選(此處)摺疊或開啟

  1. EXEC DBMS_JOB.BROKEN(63,TRUE);
  2. COMMIT;

  4. #殺掉Job程式
  5. SELECT SPID FROM V$PROCESS WHERE ADDR IN (
  6. SELECT PADDR FROM V$SESSION WHERE SID = 75);

  8. HOST kill -9 <程式ID>;
3）找出儲存過程依賴物件

點選(此處)摺疊或開啟

  1. SELECT * FROM V$ACESS
  2. WHERE (OWNER, OBJECT) IN
  3. (SELECT REFERENCED_OWNER, REFERENCED_NAME FROM DBA_DEPENDENCIES
  4. WHERE OWNER = :1 AND NAME = :2);
4）根據找出的SID，查詢詳細的會話資訊

點選(此處)摺疊或開啟

  1. SELECT SID, SERIAL#, USERNAME, PROGRAM, TERMINAL
  2. FROM V$SESSION WHERE SID = :1;
5）找出該會話執行的SQL語句

點選(此處)摺疊或開啟

  1. SELECT SQL_TEXT FROM V$SQL
  2. WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID = :1);
6）找出該語句的等待時間

點選(此處)摺疊或開啟

  1. SELECT EVENT, P1TEXT, P1, P2TEXT, P2, P3TEXT, P3, SECONDS_IN_WAIT
  2. FROM V$SESSION_WAIT WHERE SID = :1;

3. 故障解決

1)殺死以上查到的程式

點選(此處)摺疊或開啟

  1. #殺掉Job程式
  2. SELECT SPID FROM V$PROCESS WHERE ADDR IN (
  3. SELECT PADDR FROM V$SESSION WHERE SID = :1);

  5. HOST kill -9 <程式ID>;
2）重新編譯過程

點選(此處)摺疊或開啟

  1. ALTER PROCEDURE <> COMPILE

3) 重新恢復Job

點選(此處)摺疊或開啟

  1. EXEC DBMS_JOB.BROKEN(63， FALSE);
  2. COMMIT;

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

相關文章