记DG备库CPU消耗达到瓶颈的修复

DG standby 不定时CPU消耗达到瓶颈，重启数据库后问题解除吗，由于备库未对外提供任何服务，理论上不应该出现该问题

问题描述：
DG standby 不定时CPU消耗达到瓶颈，重启数据库后问题解除吗，由于备库未对外提供任何服务，理论上不应该出现该问题

解决步骤：

在CPU消耗达到瓶颈时查看等待事件

SELECT Sid, Event, P1text, P1, P2text, P2, P3text, P3
FROM V$session_Wait
WHERE Event NOT LIKE '%SQL%'
AND Event NOT LIKE '%rdbms%'
AND Event NOT LIKE '%mon%'
ORDER BY Event;

根据top观察出消耗cpu100%的进程，查询得到的sid果然是1346

select a.sid, b.spid, a.serial#

  from v$session a, v$process b

 where a.paddr = b.addr

   and b.spid = '19034'

 

问题已经定位，是新的会话连接到数据库后library cache: mutex X事件致使数据库hang住

library cache: mutex X是11g时用来替换之前的library cache latch，主要作用是在hash bucket中定位handle时使用。

期初怀疑是数据库内存自动管理，数据库pga，sga在备库执行recover时来回收缩频率过多导致，修改成了手动管理

 

后期观察发现问题仍然存在~

 

dump出该回话的trace信息

exec dbms_system.set_ev(1346,43,10046,12,'');

执行一个SQL

exec dbms_system.set_ev(1346,43,0,0,'');

 

查询当前session的trace文件SQL

select d.value || 'http://www.linuxidc.com/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||

       p.spid || '.trc' trace_file_name

  from (select p.spid

          from sys.v$mystat m, sys.v$session s, sys.v$process p

         where m.statistic# = 1

           and s.sid = m.sid

           and p.addr = s.paddr) p,

       (select t.instance

          from sys.v$thread t, sys.v$parameter v

         where v.name = 'thread'

           and (v.value = 0 or t.thread# = to_number(v.value))) i,

       (select value from sys.v$parameter where name = 'user_dump_dest') d 

/

观察trace

 

该等待一直有，会话一直hang住，查询数据库发现在等待library cache lock，数据库没有任何业务，，dg的归档应用也正常

 

查看官方，发现有关11glibrary cache: mutex 的bug还真不少，主要涉及的应该是如下两个：

9530750 High waits for ‘library cache: mutex X’ for cursor Build lock

10145558 Selects on library cache V$/X$ views cause “library cache: mutex X” waits

解决方法:
为数据库打上相应的补丁包，p14727315_112020_Linux-x86-64.zip是11.2.0.2版本最后一个补丁包psu9
打补丁过程记录如下：
下载opatch和补丁包
p6880880_112000_Linux-x86-64.zip
p14727315_112020_Linux-x86-64.zip
 
解压下载后的两个zip包
[Oracle@54-Oracle-Fog-Backup ~]$ cp OPatch/ $ORACLE_HOME/ -r
[oracle@54-Oracle-Fog-Backup ~]$ cd $ORACLE_HOME
[oracle@54-Oracle-Fog-Backup dbhome_1]$ cd OPatch/
[oracle@54-Oracle-Fog-Backup OPatch]$ ls
crs  emdpatch.pl  jlib  opatch      opatchdiag      opatch.ini  opatchprereqs  README.txt
docs  fmw          ocm  opatch.bat  opatchdiag.bat  opatch.pl  oplan

[oracle@54-Oracle-Fog-Backup OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.
Oracle Home      : /opt/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /opt/app/oraInventory
  from          : /opt/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version      : 11.2.0.2.0
Log file location : /opt/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2015-09-17_14-02-19PM_1.log
Lsinventory Output file location : /opt/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2015-09-17_14-02-19PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home
--------------------------------------------------------------------------------
OPatch succeeded.
 
[oracle@54-Oracle-Fog-Backup OPatch]$ ./opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/oracle/14727315/
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

PREREQ session