Library cache pin问题的处理过程

本文是《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;
   
3. 
   
4. #杀掉Job进程
   
5. SELECT SPID FROM V$PROCESS WHERE ADDR IN (
   
6. SELECT PADDR FROM V$SESSION WHERE SID = 75);
   
7. 
   
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);
   
4. 
   
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-2109979/