()

Upgrade Database (July 2021 Patch Apply 19c) 19.3 to 19.12 in Oracle

In this article, we are going to demonstrate the upgrade database from 19.3 to 19.12. To achieve it we will apply patch 32904851 of July 2021 (19.12.0.0.210720) on Oracle database version 19.3.0.0.0 which is multitenant architecture (CDB & PDB). Here it’s the current version of the database:

[oracle@DBsGuruN ~]$ . oraenv
ORACLE_SID = [oracle] ? gurudb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN ~]$ sqlplus -v

SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


Follow the high-level plan to apply patch 32904851 (19.12.0.0.210720).


  • Downdoad the require patches.
  • Validate version of current OPATCH utility and upgrade it if require.
  • Take the backup of ORACLE HOME and associated databases of same home.
  • Blackout in OEM, disable jobs in crontab & DBMS scheduler jobs, stop applications, etc. (if any applicable for all databases of same home).
  • Stop all sevices asociated to ORACLE HOME.
  • Apply patch on ORACLE HOME.
  • Validate applied patch in inventory.
  • Start databases only associated to ORACLE HOME.
  • Execute post patch (datapatch) on each database and validate.
  • Start all services asociated to ORACLE HOME.
  • Delete blackout in OEM, enable jobs in crontab & DBMS scheduler jobs, start applications, etc. (if any applicable for all databases of same home).
  • Go through read me doc of patch ID before patch apply.

1. Prerequisite

1.1: Make sure to download the correct patch specific to the database version and OS platform. Click here for Oracle Critical Patch IDs along with a download link for July 2021.

[oracle@DBsGuruN stage]$ mkdir -p patch_19.12.0.0.210720
[oracle@DBsGuruN stage]$ cd patch_19.12.0.0.210720



1.2: After download, transfer/copy downloaded patch to the desired directory. Here I am using  Linux 64 Bit and the database version is 19.3.0.0.0.

[oracle@DBsGuruN patch_19.12.0.0.210720]$ ls -lrt
total 1366980
-rw-r--r--. 1 oracle oinstall 1399784341 Jul 25 15:10 p32904851_190000_Linux-x86-64.zip
[oracle@DBsGuruN patch_19.12.0.0.210720]$ unzip -q p32904851_190000_Linux-x86-64.zip
[oracle@DBsGuruN patch_19.12.0.0.210720]$ ls -lrt
total 1367932
drwxr-xr-x. 5 oracle oinstall         81 Jul 20 14:51 32904851
-rw-rw-r--. 1 oracle oinstall     972397 Jul 23 18:41 PatchSearch.xml
-rw-r--r--. 1 oracle oinstall 1399784341 Jul 25 15:10 p32904851_190000_Linux-x86-64.zip
[oracle@DBsGuruN patch_19.12.0.0.210720]$


1.3: Capture information of running instances, listener, patch inventory, oratab, etc. of associated ORACLE_HOME.

[oracle@DBsGuruN patch_19.12.0.0.210720]$ mkdir prechecks
[oracle@DBsGuruN patch_19.12.0.0.210720]$ ls -ld prechecks/
drwxr-xr-x. 2 oracle oinstall 6 Jul 25 17:59 prechecks/
[oracle@DBsGuruN patch_19.12.0.0.210720]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@DBsGuruN patch_19.12.0.0.210720]$ ps -ef |grep pmon > /stage/patch_19.12.0.0.210720/prechecks/current_instance.txt
[oracle@DBsGuruN patch_19.12.0.0.210720]$ ps -ef |grep tns > /stage/patch_19.12.0.0.210720/prechecks/current_tns.txt
[oracle@DBsGuruN patch_19.12.0.0.210720]$ cat /etc/oratab > /stage/patch_19.12.0.0.210720/prechecks/current_oratab.txt
[oracle@DBsGuruN patch_19.12.0.0.210720]$ $ORACLE_HOME/OPatch/opatch lsinventory > /stage/patch_19.12.0.0.210720/prechecks/before_current_lsinventory.txt
[oracle@DBsGuruN patch_19.12.0.0.210720]$ cd prechecks/
[oracle@DBsGuruN prechecks]$ ls -lrt
total 24
-rw-r--r--. 1 oracle oinstall  143 Jul 25 18:01 current_instance.txt
-rw-r--r--. 1 oracle oinstall  252 Jul 25 18:01 current_tns.txt
-rw-r--r--. 1 oracle oinstall  832 Jul 25 18:01 current_oratab.txt
-rw-r--r--. 1 oracle oinstall 8733 Jul 25 18:01 before_current_lsinventory.txt


1.4: Validate the version of the utility OPATCH and upgrade it if needed, It should meet the minimum version as per readme doc of the specified patch ID. Click here for Step by Step OPATCH Upgrade in Oracle.

[oracle@DBsGuruN patch_19.12.0.0.210720]$ . oraenv
ORACLE_SID = [gurudb] ? gurudb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN patch_19.12.0.0.210720]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@DBsGuruN patch_19.12.0.0.210720]$ opatch version
OPatch Version: 12.2.0.1.25

OPatch succeeded.


1.5: Determine whether any currently installed interim patches conflict with the patch being installed.

[oracle@DBsGuruN patch_19.12.0.0.210720]$ cd 32904851/
[oracle@DBsGuruN 32904851]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.25
Copyright (c) 2021, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19.0.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.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.0.0/db_1/cfgtoollogs/opatch/opatch2021-07-25_18-52-55PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.


2. Backup Plan


2.1: It’s always recommended to have a backup before starting any major changes. So make sure you have a backup of ORACLE HOME as a backup plan if require to restore to the previous version.

[oracle@DBsGuruN prechecks]$ cd ../
[oracle@DBsGuruN patch_19.12.0.0.210720]$ mkdir backup_plan
[oracle@DBsGuruN patch_19.12.0.0.210720]$ cd backup_plan/
[oracle@DBsGuruN backup_plan]$ tar -cvf oracle_home_25Jul2021.tar $ORACLE_HOME
[oracle@DBsGuruN backup_plan]$ ls -lrt
total 542828
-rw-r-----. 1 oracle oinstall   3752960 Jul 25 18:09 GURUDB_0404s223_1_1
-rw-r-----. 1 oracle oinstall   3059200 Jul 25 18:09 GURUDB_0504s223_1_1
-rw-r-----. 1 oracle oinstall  18004480 Jul 25 18:09 GURUDB_0304s222_1_1
-rw-r-----. 1 oracle oinstall  41238528 Jul 25 18:09 GURUDB_0804s226_1_1
-rw-r-----. 1 oracle oinstall  62799872 Jul 25 18:09 GURUDB_0704s226_1_1
-rw-r-----. 1 oracle oinstall 240312320 Jul 25 18:10 GURUDB_0604s226_1_1
-rw-r-----. 1 oracle oinstall  41213952 Jul 25 18:10 GURUDB_0904s249_1_1
-rw-r-----. 1 oracle oinstall  62586880 Jul 25 18:10 GURUDB_0a04s249_1_1
-rw-r-----. 1 oracle oinstall   1073152 Jul 25 18:10 GURUDB_0c04s25d_1_1
-rw-r-----. 1 oracle oinstall  62537728 Jul 25 18:11 GURUDB_0b04s25d_1_1
-rw-r-----. 1 oracle oinstall      5632 Jul 25 18:11 GURUDB_0d04s25t_1_1
-rw-r-----. 1 oracle oinstall  19136512 Jul 25 18:11 GURUDBctl_0e04s25u
-rw-r-----. 1 oracle oinstall    114688 Jul 25 18:11 GURUDBSPF_0f04s260_1_1
-rw-r--r--. 1 oracle oinstall 570417225 Jul 25 18:43 oracle_home_25Jul2021.tar


2.2: Additionally database backup, refer to below RMAN scripts for full database along with archive log backup.

RMAN> run
{
ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT '/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_%U';
ALLOCATE CHANNEL CH2 DEVICE TYPE DISK FORMAT '/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_%U';
ALLOCATE CHANNEL CH3 DEVICE TYPE DISK FORMAT '/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_%U';
BACKUP tag 'UPGRADE_DB' FORCE AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
BACKUP CURRENT CONTROLFILE TAG 'UPGRADE_CTL' FORMAT '/stage/patch_19.12.0.0.210720/backup_plan/GURUDBctl_%u';
BACKUP SPFILE TAG 'UPGRADE_SPFILE' FORMAT '/stage/patch_19.12.0.0.210720/backup_plan/GURUDBSPF_%U';
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>

using target database control file instead of recovery catalog
allocated channel: CH1
channel CH1: SID=14 device type=DISK

allocated channel: CH2
channel CH2: SID=4 device type=DISK

allocated channel: CH3
channel CH3: SID=7 device type=DISK


Starting backup at 25-JUL-21
current log archived
channel CH1: starting compressed archived log backup set
channel CH1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=4 STAMP=1077443785
input archived log thread=1 sequence=2 RECID=5 STAMP=1078846637
input archived log thread=1 sequence=3 RECID=6 STAMP=1078848429
channel CH1: starting piece 1 at 25-JUL-21
channel CH2: starting compressed archived log backup set
channel CH2: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=1 STAMP=1077215630
input archived log thread=1 sequence=8 RECID=2 STAMP=1077215630
input archived log thread=1 sequence=9 RECID=3 STAMP=1077215630
channel CH2: starting piece 1 at 25-JUL-21
channel CH3: starting compressed archived log backup set
channel CH3: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=7 STAMP=1078852642
input archived log thread=1 sequence=5 RECID=8 STAMP=1078852967
input archived log thread=1 sequence=6 RECID=9 STAMP=1078855746
channel CH3: starting piece 1 at 25-JUL-21
channel CH2: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0404s223_1_1 tag=UPGRADE_DB comment=NONE
channel CH2: backup set complete, elapsed time: 00:00:01
channel CH3: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0504s223_1_1 tag=UPGRADE_DB comment=NONE
channel CH3: backup set complete, elapsed time: 00:00:01
channel CH1: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0304s222_1_1 tag=UPGRADE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-JUL-21

Starting backup at 25-JUL-21
channel CH1: starting compressed full datafile backup set
channel CH1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/DBSGURU/system01.dbf
input datafile file number=00004 name=/oradata/DBSGURU/undotbs01.dbf
channel CH1: starting piece 1 at 25-JUL-21
channel CH2: starting compressed full datafile backup set
channel CH2: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata/DBSGURU/sysaux01.dbf
input datafile file number=00007 name=/oradata/DBSGURU/users01.dbf
channel CH2: starting piece 1 at 25-JUL-21
channel CH3: starting compressed full datafile backup set
channel CH3: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/DBSGURU/dbsguru_pdb/sysaux01.dbf
channel CH3: starting piece 1 at 25-JUL-21
channel CH2: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0704s226_1_1 tag=UPGRADE_DB comment=NONE
channel CH2: backup set complete, elapsed time: 00:01:05
channel CH2: starting compressed full datafile backup set
channel CH2: specifying datafile(s) in backup set
input datafile file number=00006 name=/oradata/DBSGURU/pdbseed/sysaux01.dbf
channel CH2: starting piece 1 at 25-JUL-21
channel CH3: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0804s226_1_1 tag=UPGRADE_DB comment=NONE
channel CH3: backup set complete, elapsed time: 00:01:07
channel CH3: starting compressed full datafile backup set
channel CH3: specifying datafile(s) in backup set
input datafile file number=00005 name=/oradata/DBSGURU/pdbseed/system01.dbf
channel CH3: starting piece 1 at 25-JUL-21
channel CH1: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0604s226_1_1 tag=UPGRADE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:01:43
channel CH1: starting compressed full datafile backup set
channel CH1: specifying datafile(s) in backup set
input datafile file number=00009 name=/oradata/DBSGURU/dbsguru_pdb/system01.dbf
channel CH1: starting piece 1 at 25-JUL-21
channel CH2: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0904s249_1_1 tag=UPGRADE_DB comment=NONE
channel CH2: backup set complete, elapsed time: 00:00:36
channel CH2: starting compressed full datafile backup set
channel CH2: specifying datafile(s) in backup set
input datafile file number=00011 name=/oradata/DBSGURU/dbsguru_pdb/users01.dbf
channel CH2: starting piece 1 at 25-JUL-21
channel CH3: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0a04s249_1_1 tag=UPGRADE_DB comment=NONE
channel CH3: backup set complete, elapsed time: 00:00:35
channel CH2: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0c04s25d_1_1 tag=UPGRADE_DB comment=NONE
channel CH2: backup set complete, elapsed time: 00:00:07
channel CH1: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0b04s25d_1_1 tag=UPGRADE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:15
Finished backup at 25-JUL-21

Starting backup at 25-JUL-21
current log archived
channel CH1: starting compressed archived log backup set
channel CH1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=10 STAMP=1078855868
channel CH1: starting piece 1 at 25-JUL-21
channel CH1: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDB_0d04s25t_1_1 tag=UPGRADE_DB comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JUL-21

Starting backup at 25-JUL-21
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current control file in backup set
channel CH1: starting piece 1 at 25-JUL-21
channel CH1: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDBctl_0e04s25u tag=UPGRADE_CTL comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JUL-21

Starting backup at 25-JUL-21
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel CH1: starting piece 1 at 25-JUL-21
channel CH1: finished piece 1 at 25-JUL-21
piece handle=/stage/patch_19.12.0.0.210720/backup_plan/GURUDBSPF_0f04s260_1_1 tag=UPGRADE_SPFILE comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JUL-21

Starting Control File and SPFILE Autobackup at 25-JUL-21
piece handle=/oradata/DBSGURU/FRA/GURUDB/autobackup/2021_07_25/o1_mf_s_1078855873_jhtpz9t2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-JUL-21

released channel: CH1

released channel: CH2

released channel: CH3

RMAN>


3. Patch Install

3.1: Set environment for 19c Home.

[oracle@DBsGuruN patch_19.12.0.0.210720]$ . oraenv
ORACLE_SID = [gurudb] ? gurudb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN patch_19.12.0.0.210720]$ export PATH=$ORACLE_HOME/OPatch:$PATH


3.2: Make sure that you shut down all the services running from the ORACLE HOME where you are performing patch activity. Shutdown databases and Listener running associated from same ORACLE HOME where we are applying the patch

[oracle@DBsGuruN ~]$ ps -eaf | grep pmon
oracle     10979    3932  0 18:55 pts/0    00:00:00 grep --color=auto pmon
[oracle@DBsGuruN ~]$ ps -eaf | grep tns
root          16       2  0 17:14 ?        00:00:00 [netns]
oracle     10982    3932  0 18:55 pts/0    00:00:00 grep --color=auto tns
[oracle@DBsGuruN ~]$ ps -eaf | grep exp
oracle     10984    3932  0 18:55 pts/0    00:00:00 grep --color=auto exp
[oracle@DBsGuruN ~]$ ps -eaf | grep rman
oracle     10988    3932  0 18:55 pts/0    00:00:00 grep --color=auto rman


3.3: Set your current directory to the location where the patch is located and then run the opatch utility by entering the following commands:

[oracle@DBsGuruN patch_19.12.0.0.210720]$ cd 32904851/
[oracle@DBsGuruN 32904851]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.25
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.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.0.0/db_1/cfgtoollogs/opatch/opatch2021-07-25_18-56-05PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   32904851

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


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '32904851' to OH '/u01/app/oracle/product/19.0.0/db_1'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.perlint, 5.28.1.0.0...

Patching component oracle.rdbms.locator, 19.0.0.0.0...

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.buildtools.rsf, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.marvel, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.rhp.db, 19.0.0.0.0...

Patching component oracle.sdo, 19.0.0.0.0...

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

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.wwg.plsql, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

Patching component oracle.rdbms.drdaas, 19.0.0.0.0...

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

Patching component oracle.dbtoolslistener, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.rdbms.hs_common, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.oraolap.api, 19.0.0.0.0...

Patching component oracle.ons.ic, 19.0.0.0.0...

Patching component oracle.rdbms.install.common, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

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

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.rdbms.crs, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.xdk.xquery, 19.0.0.0.0...

Patching component oracle.ldap.client, 19.0.0.0.0...

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

Patching component oracle.ctx.rsf, 19.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.mgw.common, 19.0.0.0.0...

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

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.jdk, 1.8.0.201.0...
Patch 32904851 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [32904851].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/opatch/opatch2021-07-25_18-56-05PM_1.log

OPatch succeeded.


3.4: Validate applied patch on ORACLE HOME. Click here for the sample output.

[oracle@DBsGuruN 32904851]$ opatch lspatches
32904851;Database Release Update : 19.12.0.0.210720 (32904851)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
[oracle@DBsGuruN 32904851]$ opatch lsinventory > /stage/patch_19.12.0.0.210720/prechecks/after_apply_lsinventory.txt
OR
[oracle@DBsGuruN 32904851]$ opatch lsinventory


3.5: Validate that database version upgraded from 19.3.0.0.0 to 19.12.0.0.0.

[oracle@DBsGuruN 32904851]$ sqlplus -v

SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.12.0.0.0


4. Post Patch Apply


4.1: Start the services of patched ORACLE HOME.

[oracle@DBsGuruN 32904851]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 25 19:12:32 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  788526624 bytes
Fixed Size                  9139744 bytes
Variable Size             583008256 bytes
Database Buffers          192937984 bytes
Redo Buffers                3440640 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;  ==> Applicable only to Multitenant (CDB/PDB) used.

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DBSGURU_PDB                    READ WRITE NO
SQL>
[oracle@DBsGuruN OPatch]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-JUL-2021 20:30:44

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.0.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/DBsGuruN/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                25-JUL-2021 20:30:44
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/DBsGuruN/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@DBsGuruN OPatch]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 25-JUL-2021 20:31:05

Copyright (c) 1991, 2021, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                25-JUL-2021 20:30:44
Uptime                    0 days 0 hr. 0 min. 20 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/DBsGuruN/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN.dbsguru.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "GURUDB" has 1 instance(s).
  Instance "gurudb", status READY, has 1 handler(s) for this service...
Service "c40921a8bbf963f9e055515375a76b71" has 1 instance(s).
  Instance "gurudb", status READY, has 1 handler(s) for this service...
Service "dbsguruXDB" has 1 instance(s).
  Instance "gurudb", status READY, has 1 handler(s) for this service...
Service "dbsguru_pdb" has 1 instance(s).
  Instance "gurudb", status READY, has 1 handler(s) for this service...
The command completed successfully


4.2: Apply patch (datapatch) to databases.

[oracle@DBsGuruN backup_plan]$ cd $ORACLE_HOME/OPatch
[oracle@DBsGuruN OPatch]$ ls -lrt datapatch
-rwxr-x---. 1 oracle oinstall 589 Apr 20 15:10 datapatch
[oracle@DBsGuruN OPatch]$
[oracle@DBsGuruN OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.12.0.0.0 Production on Sun Jul 25 20:08:36 2021
Copyright (c) 2012, 2021, Oracle.  All rights reserved.

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

Connecting to database...OK
Gathering database info...done

Note:  Datapatch will only apply or rollback SQL fixes for PDBs
       that are in an open state, no patches will be applied to closed PDBs.
       Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
       (Doc ID 1585822.1)

Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
  No interim patches found

Current state of release update SQL patches:
  Binary registry:
    19.12.0.0.0 Release_Update 210716141810: Installed
  PDB CDB$ROOT:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 05-JUN-21 10.47.33.149238 PM
  PDB DBSGURU_PDB:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 05-JUN-21 10.55.14.729919 PM
  PDB PDB$SEED:
    Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 05-JUN-21 10.55.14.729919 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED DBSGURU_PDB
    No interim patches need to be rolled back
    Patch 32904851 (Database Release Update : 19.12.0.0.210720 (32904851)):
      Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.12.0.0.0 Release_Update 210716141810
    No interim patches need to be applied

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...done
Patch 32904851 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_GURUDB_CDBROOT_2021Jul25_20_08_57.log (no errors)
Patch 32904851 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_GURUDB_PDBSEED_2021Jul25_20_13_34.log (no errors)
Patch 32904851 apply (pdb DBSGURU_PDB): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32904851/24343243/32904851_apply_GURUDB_DBSGURU_PDB_2021Jul25_20_13_34.log (no errors)
SQL Patching tool complete on Sun Jul 25 20:20:56 2021


NOTE: Follow the same steps for all databases of patch home if applicable.

4.3: Validate applied patch in databases. Here we performed in a multitenant database (CDB, PDBs) so will validate in both CDB & all PDBs as require.

4.3.1: Validate in the ROOT container database (CDB$ROOT).

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> SET PAGES 55
SET LINESIZE 601
COLUMN ACTION_TIME FORMAT A21
COLUMN ACTION FORMAT A11
COLUMN STATUS FORMAT A11
COLUMN DESCRIPTION FORMAT A55
COLUMN VERSION FORMAT A11
COLUMN BUNDLE_SERIES FORMAT A11

SELECT TO_CHAR(ACTION_TIME, 'DD-MON-YYYY HH24:MI:SS') AS ACTION_TIME, PATCH_TYPE,
ACTION,STATUS,DESCRIPTION, SOURCE_VERSION,TARGET_VERSION, PATCH_ID FROM SYS.DBA_REGISTRY_SQLPATCH ORDER BY ACTION_TIME DESC;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2

ACTION_TIME           PATCH_TYPE ACTION      STATUS      DESCRIPTION                                             SOURCE_VERSION  TARGET_VERSION    PATCH_ID
--------------------- ---------- ----------- ----------- ------------------------------------------------------- --------------- --------------- ----------
25-JUL-2021 20:20:42  RU         APPLY       SUCCESS     Database Release Update : 19.12.0.0.210720 (32904851)   19.3.0.0.0      19.12.0.0.0       32904851
05-JUN-2021 22:47:33  RU         APPLY       SUCCESS     Database Release Update : 19.3.0.0.190416 (29517242)    19.1.0.0.0      19.3.0.0.0        29517242



4.3.2: Validate in a pluggable database (DBSGURU_PDB).

[oracle@DBsGuruN ~]$ sqlplus sys@DBSGURU_PDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 25 20:35:41 2021
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Sun Jul 25 2021 17:48:38 +05:30

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> show con_name

CON_NAME
------------------------------
DBSGURU_PDB
SQL> SET PAGES 55
SET LINESIZE 601
COLUMN ACTION_TIME FORMAT A21
COLUMN ACTION FORMAT A11
COLUMN STATUS FORMAT A11
COLUMN DESCRIPTION FORMAT A55
COLUMN VERSION FORMAT A11
COLUMN BUNDLE_SERIES FORMAT A11

SELECT TO_CHAR(ACTION_TIME, 'DD-MON-YYYY HH24:MI:SS') AS ACTION_TIME, PATCH_TYPE,
ACTION,STATUS,DESCRIPTION, SOURCE_VERSION,TARGET_VERSION, PATCH_ID FROM SYS.DBA_REGISTRY_SQLPATCH ORDER BY ACTION_TIME DESC;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2

ACTION_TIME           PATCH_TYPE ACTION      STATUS      DESCRIPTION                                             SOURCE_VERSION  TARGET_VERSION    PATCH_ID
--------------------- ---------- ----------- ----------- ------------------------------------------------------- --------------- --------------- ----------
25-JUL-2021 20:20:52  RU         APPLY       SUCCESS     Database Release Update : 19.12.0.0.210720 (32904851)   19.3.0.0.0      19.12.0.0.0       32904851
05-JUN-2021 22:55:14  RU         APPLY       SUCCESS     Database Release Update : 19.3.0.0.190416 (29517242)    19.1.0.0.0      19.3.0.0.0        29517242


Or here to go in one go to validate in all containers.

SQL> SET PAGES 55
SET LINESIZE 601
COLUMN ACTION_TIME FORMAT A21
COLUMN ACTION FORMAT A11
COLUMN STATUS FORMAT A11
SQL> SQL> SQL> SQL> SQL> COLUMN DESCRIPTION FORMAT A55
COLUMN VERSION FORMAT A11
COLUMN BUNDLE_SERIES FORMAT A11

SELECT CON_ID,TO_CHAR(ACTION_TIME, 'DD-MON-YYYY HH24:MI:SS') AS ACTION_TIME, PATCH_TYPE,
ACTION,STATUS,DESCRIPTION, SOURCE_VERSION,TARGET_VERSION, PATCH_ID FROM SYS.CDB_REGISTRY_SQLPATCH ORDER BY CON_ID,ACTION_TIME DESC;SQL> SQL> SQL> SQL>   2

    CON_ID ACTION_TIME           PATCH_TYPE ACTION      STATUS      DESCRIPTION                                             SOURCE_VERSION  TARGET_VERSION    PATCH_ID
---------- --------------------- ---------- ----------- ----------- ------------------------------------------------------- --------------- --------------- ----------
         1 25-JUL-2021 20:20:42  RU         APPLY       SUCCESS     Database Release Update : 19.12.0.0.210720 (32904851)   19.3.0.0.0      19.12.0.0.0       32904851
         1 05-JUN-2021 22:47:33  RU         APPLY       SUCCESS     Database Release Update : 19.3.0.0.190416 (29517242)    19.1.0.0.0      19.3.0.0.0        29517242
         3 25-JUL-2021 20:20:52  RU         APPLY       SUCCESS     Database Release Update : 19.12.0.0.210720 (32904851)   19.3.0.0.0      19.12.0.0.0       32904851
         3 05-JUN-2021 22:55:14  RU         APPLY       SUCCESS     Database Release Update : 19.3.0.0.190416 (29517242)    19.1.0.0.0      19.3.0.0.0        29517242



Click here for various options to validate applied patches in ORACLE HOME & DATABASE.

4.4: Final reboot databases.

SQL> shutdown immediate
SQL> startup
SQL> alter pluggable database all open;  ==> Applicable only to Multitenant (CDB/PDB) used.

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DBSGURU_PDB                    READ WRITE NO

Make sure all services are up & running from patched ORACLE HOME.

4.5: Delete blackout in OEM, Enable jobs in crontab & DBMS scheduler, etc. if applicable.

4.6: Handover to application checkout to start dependent application/jobs, if applicable.


Click here to get more patching-related articles.

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.

 

Related articles


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

Hello and welcome to DBsGuru,DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!Share Learn Grow!