Oracle Patch 30901889安装

一、MOS

ORA-04031 “shared pool”,“unknown object”,“sga heap(2,0)”,“KCRAHT Context & Hash” Errors when Shared Pool Top Blocking Session Held by RMS0 (Doc ID 2722160.1)

APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS
1. The following errors occurred in the alert log and generated a incident trace file:

ORA-04031: unable to allocate 16777320 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","KCRAHT Context & Hash")
2. The Call Stack Trace in the same incident trace file shows:

.... skdstdst <- ksedst1 <- ksedst <- ksm_4031_dump <- $cold_ksmasg <- kghallocpdb_swcb <- 224 <- kgh_invoke_alloc_cb <- $cold_kghnospc <- kghalo <- kcrahtini <- kcrabie <- ksbcti <- ksbabs <- ksbrdp <- opirip <- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main <- main_opd_entry ....
 

CAUSE
The reported errors and the symptoms closely matches Bug 27062903 - ORA-04031: "SHARED POOL","UNKNOWN OBJECT","SGA HEAP(1,0)","KCRAHT FREE BLOCKS" and is closed as duplicate of
Base Bug 30901889 - LATCH : SHARED POOL TOP BLOCKING SESSION HELD BY RMS0 AS PER ASH

This bug is identified by the following symptoms:

- this is a RAC configuration with more than one instance running.
- the oracle version is 12.2 or later
- the database performance suddenly becomes slow
- many processes are waiting for 'latch: shared pool'.

SOLUTION
1. Upgrade to the fixed future release of 21.1 where the fix for Bug Patch 30901889 is included

- OR -

2. Apply Patch 30901889 if available for your version and platform.
复制

二、安装前检查

1、Ensure that the Oracle home on which you are installing the patch or from which you are rolling back the patch is Oracle Database 19 Release 19.9.0.0.201020DBRU.

[oracle@enmo1 OPatch]$ ./opatch lspatches

31667096;SPLIT PARTITION FAILED WITH ERROR ORA-600 [KKPAPDIPOBJNUM1]

31772784;OCW RELEASE UPDATE 19.9.0.0.0 (31772784)

31771877;Database Release Update : 19.9.0.0.201020 (31771877)
复制

2、Ensure that 19 Release 19.9.0.0.201020DBRU Patch Set Update (PSU) 31771877 is already applied on the Oracle Database.

[oracle@enmo1 OPatch]$ ./opatch lspatches

31667096;SPLIT PARTITION FAILED WITH ERROR ORA-600 [KKPAPDIPOBJNUM1]

31772784;OCW RELEASE UPDATE 19.9.0.0.0 (31772784)

31771877;Database Release Update : 19.9.0.0.201020 (31771877)
复制

3、Ensure that you have OPatch 19 Release 12.2.0.1.19 or higher. Oracle recommends that you use the latest version available for 19 Release 12.2.0.1.19.

[oracle@enmo1 OPatch]$ ./opatch -version

Oracle Interim Patch Installer version 12.2.0.1.25

Copyright (c) 2022, Oracle Corporation. All rights reserved.
复制

4、Ensure that you set (as the home user) the ORACLE_HOME environment variable to the Oracle home.

Cat .bash_profile

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/db_1
复制

5、Ensure that the $PATH definition has the following executables: make, ar, ld and nm

[oracle@enmo1 ~]$ which make

/usr/bin/make

[oracle@enmo1 ~]$ which ar

/usr/bin/ar

[oracle@enmo1 ~]$ which ld

/usr/bin/ld

[oracle@enmo1 ~]$ which nm

/usr/bin/nm

Cat .bash_profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/bin:/u01/app/oracle/product/19.3.0/db_1/suptools/oratop
复制

6、Determine whether any currently installed interim patches conflict with this patch 30901889

[oracle@enmo1 30901889]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.25

Copyright (c) 2022, Oracle Corporation. All rights reserved.

 

PREREQ session

 

Oracle Home    : /u01/app/oracle/product/19.3.0/db_1

Central Inventory : /u01/app/oraInventory

  from      : /u01/app/oracle/product/19.3.0/db_1/oraInst.loc

OPatch version  : 12.2.0.1.25

OUI version    : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatch/opatch2022-06-09_14-39-53PM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.
OPatch succeeded.
复制

二、补丁安装

1、停服务

oracle@enmo1 30901889]$ su -

Password: 

Last login: Thu Jun 9 15:13:58 CST 2022

Last failed login: Thu Jun 9 15:25:49 CST 2022 on pts/0

There were 2 failed login attempts since the last successful login.

[root@enmo1 ~]# crsctl stop crs

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'enmo1'

CRS-2673: Attempting to stop 'ora.crsd' on 'enmo1'

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'enmo1'

CRS-2673: Attempting to stop 'ora.qosmserver' on 'enmo1'

CRS-2673: Attempting to stop 'ora.orcl.db' on 'enmo1'

CRS-2673: Attempting to stop 'ora.chad' on 'enmo1'

CRS-2677: Stop of 'ora.orcl.db' on 'enmo1' succeeded

CRS-33673: Attempting to stop resource group 'ora.asmgroup' on server 'enmo1'

CRS-2673: Attempting to stop 'ora.OCRDG.dg' on 'enmo1'

CRS-2673: Attempting to stop 'ora.DATA01.dg' on 'enmo1'

CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'enmo1'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'enmo1'

CRS-2673: Attempting to stop 'ora.cvu' on 'enmo1'

CRS-2677: Stop of 'ora.DATA01.dg' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.OCRDG.dg' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'enmo1'

CRS-2677: Stop of 'ora.cvu' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.enmo1.vip' on 'enmo1'

CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.scan1.vip' on 'enmo1'

CRS-2677: Stop of 'ora.asm' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'enmo1'

CRS-2677: Stop of 'ora.enmo1.vip' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.scan1.vip' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.qosmserver' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.chad' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.asmnet1.asmnetwork' on 'enmo1'

CRS-2677: Stop of 'ora.asmnet1.asmnetwork' on 'enmo1' succeeded

CRS-33677: Stop of resource group 'ora.asmgroup' on server 'enmo1' succeeded.

CRS-2672: Attempting to start 'ora.qosmserver' on 'enmo2'

CRS-2672: Attempting to start 'ora.scan1.vip' on 'enmo2'

CRS-2672: Attempting to start 'ora.cvu' on 'enmo2'

CRS-2672: Attempting to start 'ora.enmo1.vip' on 'enmo2'

CRS-2676: Start of 'ora.enmo1.vip' on 'enmo2' succeeded

CRS-2676: Start of 'ora.cvu' on 'enmo2' succeeded

CRS-2676: Start of 'ora.scan1.vip' on 'enmo2' succeeded

CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on 'enmo2'

CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on 'enmo2' succeeded

CRS-2676: Start of 'ora.qosmserver' on 'enmo2' succeeded

CRS-2673: Attempting to stop 'ora.ons' on 'enmo1'

CRS-2677: Stop of 'ora.ons' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.net1.network' on 'enmo1'

CRS-2677: Stop of 'ora.net1.network' on 'enmo1' succeeded

CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'enmo1' has completed

CRS-2677: Stop of 'ora.crsd' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'enmo1'

CRS-2673: Attempting to stop 'ora.crf' on 'enmo1'

CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'enmo1'

CRS-2673: Attempting to stop 'ora.mdnsd' on 'enmo1'

CRS-2677: Stop of 'ora.crf' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.drivers.acfs' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.mdnsd' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.asm' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'enmo1'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.ctssd' on 'enmo1'

CRS-2673: Attempting to stop 'ora.evmd' on 'enmo1'

CRS-2677: Stop of 'ora.ctssd' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.evmd' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'enmo1'

CRS-2677: Stop of 'ora.cssd' on 'enmo1' succeeded

CRS-2673: Attempting to stop 'ora.gipcd' on 'enmo1'

CRS-2673: Attempting to stop 'ora.gpnpd' on 'enmo1'

CRS-2677: Stop of 'ora.gipcd' on 'enmo1' succeeded

CRS-2677: Stop of 'ora.gpnpd' on 'enmo1' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'enmo1' has completed

CRS-4133: Oracle High Availability Services has been stopped.
复制

2、安装

[root@enmo1 ~]# su - oracle

Last login: Thu Jun 9 15:13:59 CST 2022

[oracle@enmo1 ~]$ cd /u01/soft/30901889/

[oracle@enmo1 30901889]$ ls

etc files README.txt

[oracle@enmo1 30901889]$ opatch apply -local

Oracle Interim Patch Installer version 12.2.0.1.25

Copyright (c) 2022, Oracle Corporation. All rights reserved.

 

 

Oracle Home    : /u01/app/oracle/product/19.3.0/db_1

Central Inventory : /u01/app/oraInventory

  from      : /u01/app/oracle/product/19.3.0/db_1/oraInst.loc

OPatch version  : 12.2.0.1.25

OUI version    : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatch/opatch2022-06-09_15-29-13PM_1.log

 

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:  30901889 

 

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

 

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/u01/app/oracle/product/19.3.0/db_1')

 

 

Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files...

Applying interim patch '30901889' to OH '/u01/app/oracle/product/19.3.0/db_1'

 

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 30901889 successfully applied.

Log file location: /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatch/opatch2022-06-09_15-29-13PM_1.log

 

OPatch succeeded.
复制

3、验证

[oracle@enmo1 30901889]$ opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.25

Copyright (c) 2022, Oracle Corporation. All rights reserved.


Oracle Home    : /u01/app/oracle/product/19.3.0/db_1

Central Inventory : /u01/app/oraInventory

  from      : /u01/app/oracle/product/19.3.0/db_1/oraInst.loc

OPatch version  : 12.2.0.1.25

OUI version    : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatch/opatch2022-06-09_15-31-35PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/19.3.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2022-06-09_15-31-35PM.txt

\--------------------------------------------------------------------------------

Local Machine Information::

Hostname: enmo1

ARU platform id: 226

ARU platform description:: Linux x86-64


Installed Top-level Products (1): 


Oracle Database 19c                          19.0.0.0.0

There are 1 products installed in this Oracle Home.


Interim patches (4) :

 

Patch 30901889   : applied on Thu Jun 09 15:29:42 CST 2022

Unique Patch ID: 23946555

Patch description: "LATCH  SHARED POOL TOP BLOCKING SESSION HELD BY RMS0 AS PER ASH"

  Created on 3 Dec 2020, 08:27:38 hrs PST8PDT

  Bugs fixed:

   30901889

  This patch overlays patches:

   31771877, 29517242, 31771877

  This patch needs patches:

   31771877, 29517242, 31771877

  as prerequisites
复制