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

文章由LinuxBoy分享于2019-03-31 04:03:01热评（588）

记DG备库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 || '/' || 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

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-04-14PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.

[oracle@54-Oracle-Fog-Backup OPatch]$ ./opatch napply -oh $ORACLE_HOME -local /home/oracle/14727315/
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-07-06PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 13696224 13923804 14275621 14727315
Do you want to proceed? [y|n]
Y
User Responded with: Y
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/opt/app/oracle/product/11.2.0/dbhome_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '13696224' to OH '/opt/app/oracle/product/11.2.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.network.cman, 11.2.0.2.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.rdbms.rsf, 11.2.0.2.0...
Patching component oracle.rdbms, 11.2.0.2.0...
Patching component oracle.sysman.console.db, 11.2.0.2.0...

[oracle3@svr7-168 14727315]$ cd $ORACLE_HOME/rdbms/admin

sqlplus "/as sysdba"

SQL> startup

SQL> @catbundle.sql psu apply

SQL> quit

SQL>select * from dba_registry_history;
打上补丁后后期不在出现该异常

推荐文章：

ORA-21561、ORA-15055、ORA-25253 导致DG备库无法应用

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