Oracle Database Patching User Guide

High Level steps for applying the Patch

1. Check current version of Opatch Tool

2. Upgrade the Opatch utility

3. Take the backup of ORACLE_HOME

4. Shutdown the Database and Listener

5. Apply PSU patch on ORACLE_HOME

6. Execute Post installation Scripts (datapatch)

7. Startup the Database and Listener

8. Check the dba_registry_sqlpatch

Step 1:- Search and download patch from oracle support site.

Goto Dashboard and then Patches and Updates. Click on recommended patch advisor. Once window will open and it will ask for the product, release and the platform as shown below. Finally click on search.

Once you will click on search, it will show you all the recommended patch and Double check any superset patches is available.

Step 2:- OPatch Utility

Opatch utility is used to apply patch to the database so before applying the patch you must check whether the existing Opatch is supported for the patch or not.

Note:-Check Readme file

You must use the OPatch utility version 12.2.0.1.19 or later to apply this patch. Oracle recommends that you use the latest released OPatch version for 12.2, which is available for download from My Oracle Support patch 6880880 by selecting the

12.2.0.1.0 release.

So for this patch Opatch version should be 12.2.0.1.19 or later . You can check the Opatch version using below command

[oracle@ranesh db_1]$ $ORACLE_HOME/OPatch/opatch version OPatch Version: 12.2.0.1.6

OPatch succeeded.

Note:- So we need to download latest version of OPatch Utility

Step 3:- Download latest version of OPatch Utility Link to Download:-

https://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=6880880

select a release and Platform and download move to $ORACLE_HOME location

[oracle@ranesh db_1]$ mv OPatch OPatch_bkp

[oracle@ranesh db_1]$ unzip p6880880_122010_Linux-x86-64.zip

[oracle@ranesh OPatch]$ ./opatch version

OPatch Version: 12.2.0.1.19

Step 4:- Once OPatch is verified proceed start apply Patch 30886680: DATABASE APR 2020 RELEASE UPDATE 12.2.0.1.200414

Go to downloaded patch Location and unzip the patch

[oracle@ranesh oracle]$ mkdir patches

[oracle@ranesh patches]$ ls

p30886680_122010_Linux-x86-64.zip

[oracle@ranesh patches]$ unzip p30886680_122010_Linux-x86-64.zip

[oracle@ranesh patches]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHwithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.19

PREREQ session

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

Central Inventory : /u01/app/oraInventory

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

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2020-04-18_15-27-22PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" failed.

The details are:

There is a prereq/overlay cycle exist in being applied patches.

Summary of Conflict Analysis:

There are no patches that can be applied now.

OPatch succeeded.

export PATH=$PATH:$ORACLE_HOME/OPatch

[oracle@ranesh 30886680]$ $ORACLE_HOME/OPatch/opatch prereq CheckActiveFilesAndExecutables -ph ./

Oracle Interim Patch Installer version 12.2.0.1.19

PREREQ session

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

Central Inventory : /u01/app/oraInventory

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

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2020-04-18_15-31-54PM_1.log

Invoking prereq "checkactivefilesandexecutables"

Prereq "checkActiveFilesAndExecutables" for patch 30886680 passed.

OPatch succeeded.

Step 5:- Make sure you have a good backup of database using Rman Tool. Schedule Rman backup before patching for rollback plan.

[oracle@ranesh OPatch]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Apr 17 18:26:08 2020

connected to target database: PROD (DBID=449664842)

RMAN> backup database;

Starting backup at 17-APR-20

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=72 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/prod1/system01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/prod1/sysaux01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/prod1/undotbs01.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/prod1/users01.dbf

channel ORA_DISK_1: starting piece 1 at 17-APR-20

channel ORA_DISK_1: finished piece 1 at 17-APR-20

piece handle=/u01/app/oracle/fast_recovery_area/prod1/PROD1/backupset/2020_04_17/o1_mf_nnndf_TAG20200417T182618_h9m9vmvp_.bkp tag=TAG20200417T182618 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56

Finished backup at 17-APR-20

Starting Control File and SPFILE Autobackup at 17-APR-20

piece handle=/u01/app/oracle/fast_recovery_area/prod1/PROD1/autobackup/2020_04_17/o1_mf_s_1037989635_h9m9xcsh_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 17-APR-20

Step 6:- Shutdown database and the listener.

Step 7:- Take Backup of $ORACLE_HOME oraInventory (Rollback plan)

[oracle@ranesh db_1]$ tar -cvf oracle_home_apr18.tar $ORACLE_HOME

[oracle@ranesh db_1]$ ls

bin hs opmn schagent.conf

ccr install oracle_home_apr18.tar scheduler

[oracle@ranesh app]$ cp -R oraInventory oraInventory_bkp

[oracle@ranesh app]$ ls

oracle oraInventory oraInventory_bkp

Step 8:- Go to the patch folder and apply the patch.

Before applying patch export the path of OPATCH folder

[oracle@ranesh 30886680]$]$ cd /u01/app/oracle/patches/30886680/

[oracle@ranesh 30886680]$ export PATH=/u01/app/oracle/product/12.2.0/dbhome_2/OPatch:$PATH

[oracle@ranesh 30886680]$ opatch version OPatch Version: 12.2.0.1.19

OPatch succeeded.

[oracle@ranesh 30886680]$ opatch apply

Oracle Interim Patch Installer version 12.2.0.1.19

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

Central Inventory : /u01/app/oraInventory

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

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2020-04-18_15-43-47PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 30886680

Do you want to proceed? [y|n]

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/12.2.0.1/db_1')

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

User Responded with: Y

Backing up files...

Applying interim patch '30886680' to OH '/u01/app/oracle/product/12.2.0.1/db_1'

ApplySession: Optional component(s) [ oracle.swd.oui, 12.2.0.1.0 ] , [ oracle.oid.client, 12.2.0.1.0 ] , [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.rdbms.drdaas, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] not present in the Oracle Home or a higher version is found.

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

Patching component oracle.rdbms, 12.2.0.1.0...

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

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

Patching component oracle.ctx, 12.2.0.1.0...

Patching component oracle.ctx.rsf, 12.2.0.1.0...

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

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

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

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

Patching component oracle.rdbms.rsf.ic, 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.tfa, 12.2.0.1.0...

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

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

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

Patching component oracle.rdbms.crs, 12.2.0.1.0...

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

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

Patching component oracle.sqlplus, 12.2.0.1.0...

Patching component oracle.ons, 12.2.0.1.0...

Patching component oracle.oraolap, 12.2.0.1.0...

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

Patching component oracle.xdk, 12.2.0.1.0...

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

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

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

Patching component oracle.nlsrtl.rsf, 12.2.0.1.0...

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

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

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

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

Patching component oracle.rdbms.oci, 12.2.0.1.0...

Patching component oracle.install.deinstalltool, 12.2.0.1.0...

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

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

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

Patching component oracle.jdk, 1.8.0.91.0...

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

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

Patching component oracle.rdbms.oci, 12.2.0.1.0...

Patching component oracle.install.deinstalltool, 12.2.0.1.0...

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

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

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

Patching component oracle.jdk, 1.8.0.91.0...

Patch 30886680 successfully applied.

Log file location: /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2020-04-18_16-20-57PM_1.log

OPatch succeeded.

Step 9:-Check and verify the inventory for the latest applied patch. It will show you the latest applied patch

[oracle@ranesh 30886680]$ opatch lsinventory|grep "Patch description"

Patch description: "Database Apr 2020 Release Update : 12.2.0.1.200414 (30886680)"

[oracle@ranesh 30886680]$ opatch lsinventory

Oracle Interim Patch Installer version 12.2.0.1.19

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

Central Inventory : /u01/app/oraInventory

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

OPatch version : 12.2.0.1.19

OUI version : 12.2.0.1.4

Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2020-04-18_16-34-40PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/lsinv/lsinventory2020-04-18_16-34-40PM.txt

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

Local Machine Information::

Hostname: ranesh.12r2.com

ARU platform id: 226

ARU platform description:: Linux x86-64

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 30886680 : applied on Sat Apr 18 16:26:47 IST 2020

Unique Patch ID: 23474251

Patch description: "Database Apr 2020 Release Update : 12.2.0.1.200414 (30886680)"

Created on 1 Apr 2020, 01:36:23 hrs PST8PDT

Bugs fixed:

30533198, 30312568, 30086166, 28951026, 28435902, 8480838, 8932139

12763598, 13554903, 14221306, 14690846, 15931756, 16002385, 16438495

27691920, 27691939, 27693416, 27693713, 27695063, 27698953, 27700466

27704237, 27709046, 27710072, 27719000, 27726780, 27729678, 27739006

27740424, 27748954, 27751755, 27757567, 27757888, 27758544, 27758653

27758972, 27759077, 27769361, 27793533, 27799032, 27801337, 27818389

27819881, 27824540, 27824543, 27825241, 27828794, 27829295, 27833672

27834551, 27834569, 27835925, 27839353, 27846298, 27846499, 27847259

27850112, 27855490, 27861226, 27873412, 27882176, 27886087, 27897759

27898015, 27902561, 27909478, 27927431, 27929287, 27931299, 27935493

27940876, 27945870, 27951817, 27959048, 27959594, 27966472, 27967484

27983174, 27986817, 27994325, 27995215, 27995248, 27997875, 27998003

27999073, 27999638, 28000269, 28022101, 28023081, 28023399, 28023482

28024793, 28025414, 28026866, 28033429, 28040776, 28043157, 28045903

28067846, 28071549, 28072383, 28072567, 28074713, 28079127, 28090453

28098160, 28099662, 28108003, 28111583, 28120036, 28120951, 28124631

28125947, 28129791, 28140658, 28157786, 28164480, 28165439, 28171079

28174827, 28180464, 28181021, 28184554, 28188330, 28190796, 28194173

28199085, 28201419, 28209985, 28215510, 28218832, 28220398, 28223871

28226179, 28229360, 28236305, 28238264, 28242712, 28250929, 28256164

28271119, 28276054, 28279837, 28281094, 28282606, 28290434, 28294563

28302049, 28305001, 28305362, 28309406, 28319114, 28320399, 28330714

29343086, 29343861, 29347943, 29353821, 29372069, 29372460, 29375355

29375984, 29376346, 29378913, 29379978, 29383695, 29388020, 29398488

29399336, 29405462, 29409149, 29409455, 29426241, 29434301, 29436454

29437712, 29450812, 29452251, 29454978, 29464779, 29483626, 29483672

29483723, 29483771, 29500257, 29500963, 29504682, 29511611, 29524985

29530515, 29536342, 29538631, 29542449, 29542580, 29548592, 29549071

29580394, 29614575, 29621961, 29625065, 29626154, 29629430, 29633753

29637526, 29645349, 29651520, 29656843, 29667994, 29676089, 29678163

29685137, 29687220, 29690625, 29703195, 29707896, 29719146, 29724063

29726695, 29767177, 29782211, 29791152, 29794462, 29807964, 29813494

29817278, 29825525, 29836659, 29841687, 29846645, 29853485, 29865188

29869404, 29875459, 29876358, 29881050, 29884958, 29893132, 29902311

29914449, 29944035, 29944660, 29951620, 29961353, 29962927, 29962939

29991257, 30018017, 30031027, 30064268, 30076253, 30078934, 30088912

30098251, 30099302, 30114477, 30120608, 30125995, 30131286, 30147928

30150731, 30163243, 30164714, 30173113, 30177597, 30179644, 30189023

30196358, 30200758, 30215130, 30218044, 30218317, 30223712, 30239480

30241567, 30244787, 30246179, 30247305, 30252098, 30252156, 30253255

30265523, 30272329, 30281591, 30282501, 30283932, 30305880, 30312094

30342878, 30364613, 30365745, 30374739, 30384152, 30402386, 30403763

30408515, 30413137, 30416034, 30431274, 30441687, 30453442, 30458593

30460922, 30475115, 30485255, 30496957, 30497057, 30501574, 30503943

30509277, 30510527, 30582500, 30613937, 30635302, 30654409, 30671813

30741263, 30803210, 30815852, 30968208

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

OPatch succeeded.

Step10: Postpatch steps (Start listener and database).

Step11:- Execute Datapatch for update the patch details in Database side.

[oracle@ranesh 30886680]$ cd /u01/app/oracle/product/12.2.0.1/db_1/OPatch

[oracle@ranesh OPatch]$ ./datapatch –verbose

SQL Patching tool version 12.2.0.1.0 Production on Sat Apr 18 16:43:45 2020

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_24264_2020_04_18_16_43_45/sqlpatch_invocation.log

Connecting to database...OK

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Bundle series DBRU:

ID 200414 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...

Installation queue:

Nothing to roll back

The following patches will be applied:

30886680 (DATABASE APR 2020 RELEASE UPDATE 12.2.0.1.200414)

Installing patches...

Patch installation complete. Total patches installed: 1

Validating logfiles...

Patch 30886680 apply: SUCCESS

logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30886680/23474251/30886680_apply_PROD_2020Apr18_16_44_17.log (no errors)

SQL Patching tool complete on Sat Apr 18 16:47:32 2020

Step 12:- After installing the SQL portion of the patch, some packages could become INVALID. This will get recompiled upon access or you can run utlrp.sql to get them back into a VALID state.

SQL> SELECT owner, object_name, object_type FROM all_objects WHERE status ='INVALID';

no rows selected

Step13:- Check patch information in Database level

SQL> select description, action, to_char(action_time,'DD/MM/RR HH24:MI:SS') action_date, ' ' version from dba_registry_sqlpatch;

DESCRIPTION

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

ACTION ACTION_DATE V

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

DATABASE APR 2020 RELEASE UPDATE 12.2.0.1.200414

APPLY 18/04/20 16:47:32

******Note:- Every on Share this below screenshot after apply the patch****

Oracle Apps DBA

Saturday, April 18, 2020