sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle library cache pin/library cache lock导致整个系统hang住

时间:2016-10-19 21:35   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 运维DBA反映Oracle数据库HANG住.怀疑分析是由library cache pin/library cache lock导致.经过还原案例分析约束的启用与禁用是不会导致过程失效的。

客户一套系统因为library cache pin/library cache lock导致整个系统hang住了.全部业务中断.通过分析过.是由于人为导致编译了一个子的存储过程.导致父存储过程在执行时发现无效.自动进行编译.然而存储过程切一直在执行.最后导致了整个业务系统的hang住.

这个存储过程是一个接口.所有的应用都要调用.业务通过分析日志.切没有找到任何的alter procedure的语句.只发现了alter table *** disable/enable constraint语句.所以怀疑我们的分析结果.下面是一个测试结果.让业务方知道约束的启用与禁用是不会导致过程失效的。

测试环境:DB:RHEL 4.8 OS:10.2.0.4.12

1.创建测试环境:


www.oracleplus.net>grant dba to scott;

Grant succeeded.

www.oracleplus.net>create table scott.test2(id number,id2 varchar2(20));

Table created.

www.oracleplus.net>alter table scott.test2 add constraint pk_test2_id primary key(id);

Table altered.

www.oracleplus.net>create table scott.test3(id number,id2 varchar2(20));

Table created.

www.oracleplus.net>alter table scott.test3 add constraint for_test3_id foreign key(id) references scott.test2(id);

Table altered.

www.oracleplus.net>conn scott/oracle
Connected.
www.oracleplus.net>create or replace procedure test_proc
  2  is
  3  begin
  4  execute immediate 'select count(*) from scott.test2';
  5  execute immediate 'select count(*) from scott.test3';
  6  end;
  7  /

Procedure created.



create or replace procedure test_proc2 is
begin
scott.test_proc;
end;
/

2.禁用约束


www.oracleplus.net>alter table scott.test3 disable constraint for_test3_id;

Table altered.
www.oracleplus.net>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

www.oracleplus.net>select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name in ('TEST2','TEST3','TEST_PROC','TEST_PROC2') and owner='SCOTT';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         LAST_DDL_TIME       STATUS
------------------------------ ------------------------------ ------------------- ------------------- -------
SCOTT                          TEST2                          TABLE               2013-06-21 09:52:55 VALID
SCOTT                          TEST3                          TABLE               2013-06-21 10:09:09 VALID
SCOTT                          TEST_PROC                      PROCEDURE           2013-06-21 09:53:30 VALID
SCOTT                          TEST_PROC2                     PROCEDURE           2013-06-21 10:07:52 VALID
这里发现过程是有效的
再禁用另一个约束

www.oracleplus.net>alter table scott.test2 disable constraint pk_test2_id;

Table altered.

www.oracleplus.net>select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name in ('TEST2','TEST3','TEST_PROC','TEST_PROC2') and owner='SCOTT';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         LAST_DDL_TIME       STATUS
------------------------------ ------------------------------ ------------------- ------------------- -------
SCOTT                          TEST2                          TABLE               2013-06-21 10:10:08 VALID
SCOTT                          TEST3                          TABLE               2013-06-21 10:09:09 VALID
SCOTT                          TEST_PROC                      PROCEDURE           2013-06-21 09:53:30 VALID
SCOTT                          TEST_PROC2                     PROCEDURE           2013-06-21 10:07:52 VALID
两个过程都是有效的

3.启用约束


www.oracleplus.net>alter table scott.test2 enable constraint pk_test2_id;

Table altered.


www.oracleplus.net>alter table scott.test3 enable constraint for_test3_id;

Table altered.

www.oracleplus.net>select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name in ('TEST2','TEST3','TEST_PROC','TEST_PROC2') and owner='SCOTT';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         LAST_DDL_TIME       STATUS
------------------------------ ------------------------------ ------------------- ------------------- -------
SCOTT                          TEST2                          TABLE               2013-06-21 10:10:46 VALID
SCOTT                          TEST3                          TABLE               2013-06-21 10:11:11 VALID
SCOTT                          TEST_PROC                      PROCEDURE           2013-06-21 09:53:30 VALID
SCOTT                          TEST_PROC2                     PROCEDURE           2013-06-21 10:07:52 VALID
通过上面的测试可以发现.禁用约束并不会影响过程的有效性与last_ddl_time的值。

本文固定链接: http://www.htz.pw/2013/06/30/%e5%90%af%e7%94%a8%e4%b8%8e%e7%a6%81%e7%94%a8%e8%a1%a8%e7%9a%84%e7%ba%a6%e6%9d%9f%e6%98%af%e5%90%a6%e4%bc%9a%e5%af%bc%e8%87%b4%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e6%97%a0%e6%95%88.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle library cache pin/library cache lock导致整个系统hang住

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1002.html

Oracle研究中心

关键词:

Oracle约束的启用与禁用是否会导致过程失效的

library cache pin/library cache lock导致整个系统hang住分析