記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

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com