Step by Step applying PSU patch in Oracle 12c on Windows Environment

In this tutorial we have a discussion with an example how to apply Windows Bundle Patch (BP) or PSU Patch. To install the BP 12.2.0.1.201020 patch, the Oracle home must have the 12.2.0.1.0.

Oracle database have some vulnerability as well as have some bug for that reason oracle provide Windows Bundle Patch (BP)/ PSU Patch every four months' interval which fix the bug and security vulnerability. So oracle database administrator should focus on this matter and have to apply patch after oracle release for avoiding any unexpected circumstance. In this article we only discuss with an example windows bundle patch (BP) patch apply for single database environment.

Patch installation steps divide into three section

1. Pre-installation 

2. Installation

3. Post Installation

1. Pre-installation 

To apply the patch, we must need to check opatch version where must use the OPatch utility version 12.2.0.1.7 or later to apply this patch. 

Before installing the patch, we must take backup oracle home and need to go through the README.html file very carefully where all patch applies related instruction given.

Step 1: Check database version

Sqlplus / as sysdba

SQL> select banner from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

PL/SQL Release 12.2.0.1.0 - Production

Step 2: Check Opatch version

Go to opatch location: $ORACLE_HOME/OPatch

E:\app\masud\virtual\product\12.2.0\dbhome_1\OPatch>opatch.bat version

OPatch Version: 12.2.0.1.23

OPatch succeeded.

Step 3: Check last applied patch using lsinventory command

Go to the Opatch directory: $ORACLE_HOME/Opatch

E:\app\masud\virtual\product\12.2.0\dbhome_1\OPatch>opatch.bat lsinventory

Oracle Interim Patch Installer version 12.2.0.1.23

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

Oracle Home: E:\app\masud.sarker\virtual\product\12.2.0\dbhome_1

Central Inventory: C:\Program Files\Oracle\Inventory

   from:

OPatch version: 12.2.0.1.23

OUI version: 12.2.0.1.4

Log file location: E:\app\masud\virtual\product\12.2.0\dbhome_1\cfgtoollogs\opatch\opatch2020-12-08_16-58-10PM_1.log

Lsinventory Output file location: E:\app\masud\virtual\product\12.2.0\dbhome_1\cfgtoollogs\opatch\lsinv\lsinventory2020-12-08_16-58-10PM.txt

--------------------------------------------------------------------------------

Local Machine Information::

Hostname: ITPC158.southeastbank.com.bd

ARU platform id: 233

ARU platform description:: Microsoft Windows (64-bit AMD)

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

OPatch succeeded.

From above output we found no patch applied before, now we are going to apply PSU/ Windows Bundle Patch (BP) 

2. Patch Installation 

Below steps for single instance database environment

Step 1: download the latest Release Update patch set from http://support.oracle.com/ and unzip patch in same server which database server we want to apply patch 

Step 2: Shutdown database and listener 

Sqlplus / as sysdba

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down. 

# Stop Listener:

lsnrctl stop  -- stop listener

We check the windows service to make sure oracle database and listener service are down.

Step 3: Apply Opatch

Set oracle home directory

Ex. set $ORACLE_HOME=E:\app\masud\virtual\product\12.2.0\dbhome_1

Go to opatch location ($ORACLE_HOME/OPatch)

Apply the patch using below command

Note: (E:\app\masud\Patch\p31654782_122010_MSWIN-x86-64\31654782 is patch unzip location)

E:\app\masud\virtual\product\12.2.0\dbhome_1\OPatch>opatch.bat apply E:\app\masud\Patch\p31654782_122010_MSWIN-x86-64\31654782

Oracle Interim Patch Installer version 12.2.0.1.23

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

Oracle Home: E:\app\masud\virtual\product\12.2.0\dbhome_1

Central Inventory: C:\Program Files\Oracle\Inventory

   from:

OPatch version: 12.2.0.1.23

OUI version: 12.2.0.1.4

Log file location: E:\app\masud\virtual\product\12.2.0\dbhome_1\cfgtoollogs\opatch\opatch2020-12-08_17-23-31PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   31654782

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 = 'E:\app\masud\virtual\product\12.2.0\dbhome_1')

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

User Responded with: Y

Backing up files...

Applying interim patch '31654782' to OH 'E:\app\masud\virtual\product\12.2.0\dbhome_1'

ApplySession: Optional component(s) [ oracle.usm, 12.2.0.1.0 ] , [ oracle.rdbms.ic, 12.2.0.1.0 ] , [ oracle.has.cfs, 12.2.0.1.0 ] , [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.wlm.dbwlm, 12.2.0.1.0 ] , [ oracle.has.cvu, 12.2.0.1.0 ] , [ oracle.tomcat.crs, 12.2.0.1.0 ] , [ oracle.rdbms.tg4db2, 12.2.0.1.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.precomp.rsf, 12.2.0.1.0...

Patching component oracle.tfa, 12.2.0.1.0...

Patching component oracle.assistants.server, 12.2.0.1.0...

Patching component oracle.ntrdbms.admin, 12.2.0.1.0...

Patching component oracle.xdk.parser.java, 12.2.0.1.0...

Patching component oracle.ldap.rsf.ic, 12.2.0.1.0...

Patching component oracle.xdk, 12.2.0.1.0...

Patching component oracle.ntrdbms.oraconfig, 12.2.0.1.0...

Patching component oracle.network.client, 12.2.0.1.0...

Patching component oracle.rdbms.install.plugins, 12.2.0.1.0...

Patching component oracle.precomp.common, 12.2.0.1.0...

Patching component oracle.network.aso, 12.2.0.1.0...

Patching component oracle.precomp.lang, 12.2.0.1.0...

Patching component oracle.has.common.cvu, 12.2.0.1.0...

Patching component oracle.clrintg.ode_net_2, 12.2.0.1.0...

Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...

Patching component oracle.has.common, 12.2.0.1.0...

Patching component oracle.sdo, 12.2.0.1.0...

Patching component oracle.sdo.locator, 12.2.0.1.0...

Patching component oracle.oracore.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...

Patching component oracle.oraolap, 12.2.0.1.0...

Patching component oracle.rdbms.dv, 12.2.0.1.0...

Patching component oracle.assistants.deconfig, 12.2.0.1.0...

Patching component oracle.xdk.rsf, 12.2.0.1.0...

Patching component oracle.ntoledb, 12.2.0.1.0...

Patching component oracle.rdbms.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.lbac, 12.2.0.1.0...

Patching component oracle.has.deconfig, 12.2.0.1.0...

Patching component oracle.rdbms.util, 12.2.0.1.0...

Patching component oracle.has.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.deconfig, 12.2.0.1.0...

Patching component oracle.sdo.locator.jrf, 12.2.0.1.0...

Patching component oracle.aspnet_2, 12.2.0.1.0...

Patching component oracle.has.db, 12.2.0.1.0...

Patching component oracle.sqlplus.ic, 12.2.0.1.0...

Patching component oracle.ctx, 12.2.0.1.0...

Patching component oracle.rdbms.rman, 12.2.0.1.0...

Patching component oracle.ldap.client, 12.2.0.1.0...

Patching component oracle.ntoramts, 12.2.0.1.0...

Patching component oracle.assistants.acf, 12.2.0.1.0...

Patching component oracle.network.rsf, 12.2.0.1.0...

Patching component oracle.usm.deconfig, 12.2.0.1.0...

Patching component oracle.rdbms.olap, 12.2.0.1.0...

Patching component oracle.rdbms.scheduler, 12.2.0.1.0...

Patching component oracle.odbc.ic, 12.2.0.1.0...

Patching component oracle.ntoledb.odp_net_2, 12.2.0.1.0...

Patching component oracle.rdbms, 12.2.0.1.0...

Patching component oracle.ldap.rsf, 12.2.0.1.0...

Patch 31654782 successfully applied.

Log file location: E:\app\masud\virtual\product\12.2.0\dbhome_1\cfgtoollogs\opatch\opatch2020-12-08_17-23-31PM_1.log

OPatch succeeded.

Step 4: Check the applied patch 

E:\app\masud\virtual\product\12.2.0\dbhome_1\OPatch>opatch.bat lsinventory

Oracle Interim Patch Installer version 12.2.0.1.23

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

Oracle Home: E:\app\masud.sarker\virtual\product\12.2.0\dbhome_1

Central Inventory: C:\Program Files\Oracle\Inventory

   from:

OPatch version: 12.2.0.1.23

OUI version: 12.2.0.1.4

Log file location: E:\app\masud\virtual\product\12.2.0\dbhome_1\cfgtoollogs\opatch\opatch2020-12-08_17-37-24PM_1.log

Lsinventory Output file location: E:\app\masud\virtual\product\12.2.0\dbhome_1\cfgtoollogs\opatch\lsinv\lsinventory2020-12-08_17-37-24PM.txt

--------------------------------------------------------------------------------

Local Machine Information::

Hostname: ITPC158.southeastbank.com.bd

ARU platform id: 233

ARU platform description:: Microsoft Windows (64-bit AMD)

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  31654782     : applied on Tue Dec 08 17:29:42 BDT 2020

Unique Patch ID:  23831760

Patch description:  "Windows Database Bundle Patch : 12.2.0.1.201020 (31654782)"

   Created on 24 Sep 2020, 08:01:11 hrs PST8PDT

   Bugs fixed: 

# Check applied patch status by query

SQL> select PATCH_ID,VERSION,to_char(ACTION_TIME,'DD-MON-YY'), action,BUNDLE_ID from sys.dba_registry_sqlpatch;

  PATCH_ID VERSION              TO_CHAR(ACTION_TIM ACTION           BUNDLE_ID

---------- -------------------- ------------------ --------------- ----------

  31654782 12.2.0.1             08-DEC-20          APPLY               201020

3. Patch Post-Installation Step

Startup the database and listener using below command

Sqlplus / as sysdba

SQL> startup

ORACLE instance started.

Total System Global Area 2533359616 bytes

Fixed Size                  8750336 bytes

Variable Size             687868672 bytes

Database Buffers         1828716544 bytes

Redo Buffers                8024064 bytes

Database mounted.

Database opened.

# Startup listener

lsnrctl start  

Run the datapatch which is the patching tool to complete the post-patch SQL actions for RDBMS patches

Go to the location $ORACLE_HOME/OPatch

./datapatch.bat -verbose

Note: Please applied patch first in TEST/UAT environment after successfully applied in TEST then applied in production environment. 

Done!