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
- Oracle Critical Database Patch ID for October 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for July 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for January 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for October 2023 along with enabled Download Link
