ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > rename导致的library_cache_pin

rename导致的library_cache_pin

原创 Linux操作系统 作者：redhouser 时间：2013-01-10 16:23:38 0 删除 编辑

1，现象：
执行rename a to b时,如果a不存在时，系统挂起。

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as bnet@DEV95

SQL> alter table payee_act_cor rename to tmp_payee_act_cor2;
 
alter table payee_act_cor rename to tmp_payee_act_cor2
 
ORA-00942: table or view does not exist
 
SQL> rename payee_act_cor to tmp_payee_act_cor2;
 
rename payee_act_cor to tmp_payee_act_cor2
 
ORA-04021: timeout occurred while waiting to lock object bnet.PAYEE_ACT_COR
 
SQL> desc payee_act_cor
Object payee_act_cor does not exist.
 

 --在另外一个会话中查询：
SQL> select * from v$lock where sid=866;
 
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
3DB300F4 3DB30210        866 TX       589827      66756          6          0         31          0


SQL> select sid,event,blocking_session from v$session where sid=866;
 
       SID EVENT               BLOCKING_SESSION
---------- ------------------- ----------------
       866 library cache pin                866
--〉该session在等待自己

SQL> select saddr,sid,serial#,paddr
  2  from v$session where sid=866;
 
SADDR           SID    SERIAL# PADDR
-------- ---------- ---------- --------
3E17FFC0        866         46 3E45029C
SQL> select * from v$process where addr='3E45029C';
 
ADDR     PID SPID   USERNAME     SERIAL# TERMINAL   PROGRAM            TRACEID  BACKGROUND LATCHWAIT LATCHSPIN PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
-------- --- ------ --------- ---------- ---------- ------------------ -------- ---------- --------- --------- ------------ ------------- ---------------- -----------
3E45029C  80 11501  oracle             4 UNKNOWN    oraclebnet@bnet95                                            758961       1188465           458752     4072049


2，查看等待事件
[oracle@bnet95 bdump]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 28 13:15:34 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug hanganalyze 10
Cycle 1: (0/866)
Hang Analysis in /u01/app/oracle/product/10.2.0/db_1/admin/bnet/udump/bnet_ora_399.trc
SQL>

跟踪会话对应跟踪文件为bnet_ora_399.trc，其中有如下信息：
==============
HANG ANALYSIS:
==============
 
Cycle 1 : :
    <0/866/46/0x3e45029c/11501/library cache pin>


存在cycle的会话对应的跟踪文件为bnet_ora_11501.trc，其中有如下信息：
    O/S info: user: mh0575, term: BJ-MH0575-01, ospid: 3688:1080, machine: DMS\BJ-MH0575-01
              program: PlSqlDev.exe
    client info: 22.11.99.99
    application name: PL/SQL Developer, hash value=1190136663
    action name: Command Window - New, hash value=254318129
    waiting for 'library cache pin' blocking sess=0x(nil) seq=170 wait_time=0 seconds since wait started=86
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
    Dumping Session Wait History
     for 'library cache pin' count=1 wait_time=2929085
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
     for 'library cache pin' count=1 wait_time=2934476
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
     for 'library cache pin' count=1 wait_time=2933380
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
     for 'library cache pin' count=1 wait_time=2938016
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
     for 'library cache pin' count=1 wait_time=2929823
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
     for 'library cache pin' count=1 wait_time=2929885
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
     for 'library cache pin' count=1 wait_time=2929833
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
     for 'library cache pin' count=1 wait_time=2929839
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
     for 'library cache pin' count=1 wait_time=2929857
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d
     for 'library cache pin' count=1 wait_time=2927294
                handle address=383a8d24, pin address=3ab903f0, 100*mode+namespace=12d


3，重新启动后问题依然存在。

4，在internet上搜索“rename,library cache pin”，该问题可能与bug有关。验证如下：
首先关闭supplemental_log，验证问题是否存在。

SQL> SELECT log_mode,
  2         force_logging,
  3         platform_name,
  4         supplemental_log_data_min slog_min,
  5         supplemental_log_data_pk slog_pk,
  6         supplemental_log_data_ui slog_ui,
  7         supplemental_log_data_fk slog_fk,
  8         supplemental_log_data_all slog_all
  9    FROM v$database;
 
LOG_MODE     FORCE_LOGGING PLATFORM_NAME      SLOG_MIN SLOG_PK SLOG_UI SLOG_FK SLOG_ALL
------------ ------------- ------------------ -------- ------- ------- ------- --------
NOARCHIVELOG NO            Linux IA (32-bit)  YES      NO      NO      NO      NO


[oracle@bnet95 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 28 14:46:20 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  1220336 bytes
Variable Size             150995216 bytes
Database Buffers          364904448 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> alter database drop supplemental log data;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT log_mode,
  2         force_logging,
  3         platform_name,
  4         supplemental_log_data_min slog_min,
  5         supplemental_log_data_pk slog_pk,
  6         supplemental_log_data_ui slog_ui,
  7         supplemental_log_data_fk slog_fk,
  8         supplemental_log_data_all slog_all
  9    FROM v$database;
 
 LOG_MODE     FORCE_LOGGING PLATFORM_NAME      SLOG_MIN SLOG_PK SLOG_UI SLOG_FK SLOG_ALL
------------ ------------- ------------------ -------- ------- ------- ------- --------
NOARCHIVELOG NO            Linux IA (32-bit)  NO       NO      NO      NO      NO
 

SQL> alter table payee_act_cor rename to tmp_payee_act_cor2;
 
alter table payee_act_cor rename to tmp_payee_act_cor2
 
ORA-00942: table or view does not exist
 
SQL> rename payee_act_cor to tmp_payee_act_cor2;
 
rename payee_act_cor to tmp_payee_act_cor2
 
ORA-04043: object PAYEE_ACT_COR does not exist
--〉问题消失

5，恢复supplemental_log_data_min后验证，问题仍然存在，完全符合bug描述：
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount 
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                  1220336 bytes
Variable Size             155189520 bytes
Database Buffers          360710144 bytes
Redo Buffers                7168000 bytes
Database mounted.
SQL> alter database add supplemental log data;

Database altered.

SQL> alter database open;

Database altered.

SQL> SELECT log_mode,
  2         force_logging,
  3         platform_name,
  4         supplemental_log_data_min slog_min,
  5         supplemental_log_data_pk slog_pk,
  6         supplemental_log_data_ui slog_ui,
  7         supplemental_log_data_fk slog_fk,
  8         supplemental_log_data_all slog_all
  9    FROM v$database;
 
LOG_MODE     FORCE_LOGGING PLATFORM_NAME     SLOG_MIN SLOG_PK SLOG_UI SLOG_FK SLOG_ALL
------------ ------------- ----------------- -------- ------- ------- ------- --------
NOARCHIVELOG NO            Linux IA (32-bit) YES      NO      NO      NO      NO
 
SQL> alter table payee_act_cor rename to tmp_payee_act_cor2;
 
alter table payee_act_cor rename to tmp_payee_act_cor2
 
ORA-00942: table or view does not exist
 
SQL> rename payee_act_cor to tmp_payee_act_cor2;
---〉挂起
ctrl+c
ORA-01013: user requested cancel of current operation
 
SQL> create table abc(x int);
 
Table created
 
SQL> reanem abc to tmp_abc;
 
reanem abc to tmp_abc
 
ORA-00900: invalid SQL statement
 
SQL> rename abc to tmp_abc;
 
Table renamed
 
SQL> rename abc to tmp_abc;
 
---〉挂起
ctrl+c
 
ORA-01013: user requested cancel of current operation

 

附件，相关bug说明：
---------------------
Rename hangs Indefinately On 'library cache pin' [ID 744914.1]  
--------------------------------------------------------------------------------
  Modified 17-NOV-2008     Type PROBLEM     Status PUBLISHED  

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4
This problem can occur on any platform.

Symptoms
RENAME to a none-existent object hangs indefinitly on waitevent 'library cache pin' when error ORA-4043 is expected.

    ORA-04043: object does not exist

Example :

SQL> create table tst_hang (c1 number);
SQL> rename tst_hang to hang;

        Table renamed.

SQL> rename tst_hang to hang;
        ---> hangs

From the SYSTEM STATE dump :

waiting for 'library cache pin' blocking sess=0x0 seq=1001 wait_time=0 seconds since wait started=96
handle address=70000001708c9a0, pin address=700000018671590, 100*mode+namespace=12d


Cause
The issue is related to unpublished bug 4908122: HANG ON RENAME OF NONEXISTANT OBJECT WITH SUPPLEMENAL LOGGING ON

Solution
Unpublished bug 4908122 will be included in patchset 10.2.0.5 and is fixed in Oracle11g.

Hence an upgrade to one of these versions is recommended.

Workaround :

Abort the statement as it is incorrect anyway.

 

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/18922393/viewspace-752478/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： rename导致的library_cache_pin
下一篇： 关于v$db_object_cache
请登录后发表评论 登录
全部评论
redhouser

注册时间：2011-05-26

  • 博文量
    211
  • 访问量
    876669

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593