| |
Problem Description:
DG standby time to time CPU consumption reached a bottleneck, releasing it after restarting the database problem, since the standby database did not provide any service, in theory, it should not be the problem occurs
Solving steps:
When CPU consumption reached a bottleneck wait View events
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;
According to top was observed in the consumption cpu100% of the process, the resulting query is really 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'
The problem has been located, a new session to connect to the database library cache: mutex X event resulting database hang live
library cache: mutex X is 11g when used to replace the previous library cache latch, the main role is used for positioning the handle in the hash bucket.
Beginning suspected Database automatic memory management, database pga, sga when the standby database to perform recover back and forth due to excessive contraction frequency, is modified to manually manage
Late observed that the problem still exists ~
dump out the answer of trace information
exec dbms_system.set_ev (1346,43,10046,12, '');
Execute a SQL
exec dbms_system.set_ev (1346,43,0,0, '');
Query the current session of the SQL trace file
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
/
Observation of trace
The wait has been, has been hang live session, query the database was found waiting for library cache lock, the database does not have any business, dg archiving applications are normal
See official, was found about 11glibrary cache: mutex the bug really a lot, mainly involved should be the following two:
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
Solution:
Database marked with the corresponding patch, p14727315_112020_Linux-x86-64.zip is the last version 11.2.0.2 patch psu9
Patching recorded as follows:
Download the patch and opatch
p6880880_112000_Linux-x86-64.zip
p14727315_112020_Linux-x86-64.zip
Two zip unpack after downloading
[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 apply
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-04-34PM_1.log
[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;
Late after the patch does not appear abnormal |
|
|
|