记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