Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Hutchison DG standby database CPU consumption reached bottleneck repair     - How to ensure that the Internet will not be attacked (Linux)

- Linux Basic Course: tar command description (Linux)

- Actual custom yum repository ---- gem commands commonly used parameters (Linux)

- OpenSSH server configuration file for each Common Definition (Server)

- KVM usb passthrough configuration (Linux)

- Linux Shell Understanding and Learning (Linux)

- Installation image conversion tool on your Ubuntu Converseen 0.8.1 (Linux)

- 5 interesting Linux command line tips (Linux)

- Oracle users to automatically increase the partition table (Database)

- Advanced permissions Linux file system settings (Linux)

- Tab set to four spaces in Vim (Linux)

- Oracle capability i / o (stress test database server i / o performance) (Database)

- Linux RHCS basic maintenance commands (Linux)

- Python Django direct implementation of sql statement (Programming)

- LAMP environment to build Apache, MySQL, PHP under Ubuntu (Server)

- Ubuntu 14.04 installed NVIDIA graphics driver (Linux)

- Using Android Studio integrated development environment to build Android (Linux)

- ogg Oracle to SQL Server 2005 to achieve synchronization (Database)

- The need to avoid a gap of InnoDB lock (Database)

- Android float ball and boot from the start (Programming)

 
         
  Hutchison DG standby database CPU consumption reached bottleneck repair
     
  Add Date : 2018-11-21      
         
         
         
  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
     
         
         
         
  More:      
 
- Linux operating system boot manager -GRUB (Linux)
- CentOS modify yum update source (Linux)
- Nodejs nano library to handle couchdb: need a timeout (Database)
- Ubuntu 15.04 / CentOS 7.0 to set custom boot (Linux)
- Security implementation of disk data protection under Linux (Linux)
- Installation JDK 1.8 under CentOS7 (Linux)
- Apache2.4.7 make an error [exports.lo] Error 1 Solution (Server)
- Linux Tutorial: Open multiple tabs in the GNOME terminal in Ubuntu 15.04 (Linux)
- Oracle 12CIN-memory in table spaces (Database)
- Tomcat configuration memory and remote debug port (Server)
- Ruby and Python simple comparison (Programming)
- VMware Workstation virtual machine startup error: Could not open / dev / vmmon in CentOS 6 (Linux)
- How to add and delete bookmarks in Ubuntu (Linux)
- Oracle create user authorization and in PLSQL (Database)
- Grub2: Save Your bootloader (Linux)
- ORA-01000 Solution (Database)
- Bash difference in single quotes and double quotes (Programming)
- Linux hybrid character device (Linux)
- B-tree - ideas and implementation of C language code (Programming)
- MySQL appears data too long for column 'name' at row 1 to solve (Database)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.