Oracle Database 19c standalone patching involves applying patches and updates to an existing Oracle Database 19c installation that is not part of an Oracle Real Application Clusters (RAC) configuration.
High-Level steps for applying the patching
- Check current version of Opatch Tool
- Upgrade the Opatch utility
- Take the backup of ORACLE_HOME
- Shutdown the Database and Listener
- Apply PSU patch on ORACLE_HOME
- Execute Post installation Scripts (datapatch)
- Startup the Database and Listener
- Check the dba_registry_sqlpatch .
Check current version of Opatch Tool
[oracle@sql ~]$ $ORACLE_HOME/OPatch/opatch version OPatch Version: 12.2.0.1.17 OPatch succeeded.
Move the current OPatch tool as backup
[oracle@sql db_home]$ mv OPatch/ OPatch_old
unzip the new OPatch tool to the ORACLE_HOME location
[oracle@sql db_home]$ unzip /stage_soft/p6880880_190000_Linux-x86-64.zip
After unzip, check the updated version of OPatch tool
[oracle@sql db_home]$ OPatch/opatch version OPatch Version: 12.2.0.1.39 OPatch succeeded.
Download the necessary patch, ensuring that you’ve obtained the correct version that corresponds to your specific OS type and Oracle Database version.
Create a directory to unzip the patch.
mkdir /u01/patch cd /stage_soft/ unzip p35320081_190000_Linux-x86-64.zip -d /u01/patch/
cd /u01/patch/ [oracle@sql patch]$ ll total 1712 drwxr-xr-x. 5 oracle oinstall 81 Jul 15 19:54 35320081 -rw-rw-r--. 1 oracle oinstall 1749054 Jul 18 13:03 PatchSearch.xml [oracle@sql patch]$
Gather details about active instances, the listener, patch inventory, the oratab file, and create a backup of the relevant ORACLE_HOME.
[oracle@sql patch]$ mkdir prechecks [oracle@sql patch]$ [oracle@sql patch]$ [oracle@sql patch]$ cd prechecks/ [oracle@sql prechecks]$ [oracle@sql prechecks]$ ps -ef |grep pmon > /u01/patch/prechecks/current_instance.txt [oracle@sql prechecks]$ ps -ef |grep tns > /u01/patch/prechecks/current_tns.txt [oracle@sql prechecks]$ cat /etc/oratab > /u01/patch/prechecks/current_oratab.txt [oracle@sql prechecks]$ $ORACLE_HOME/OPatch/opatch lsinventory > /u01/patch/prechecks/current_lsinv.txt [oracle@sql prechecks]$ [oracle@sql prechecks]$ [oracle@sql prechecks]$ [oracle@sql prechecks]$ ls -lrta total 24 drwxr-xr-x. 4 oracle oinstall 62 Aug 21 14:58 .. -rw-r--r--. 1 oracle oinstall 71 Aug 21 14:58 current_instance.txt -rw-r--r--. 1 oracle oinstall 126 Aug 21 14:59 current_tns.txt -rw-r--r--. 1 oracle oinstall 785 Aug 21 14:59 current_oratab.txt drwxr-xr-x. 2 oracle oinstall 108 Aug 21 15:00 . -rw-r--r--. 1 oracle oinstall 8728 Aug 21 15:00 current_lsinv.txt [oracle@sql prechecks]$ [oracle@sql prechecks]$
Check for any potential conflicts between the currently installed interim patches and the patch you’re planning to install.
[oracle@sql 35320081]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.39 Copyright (c) 2023, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/19.3/db_home Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.3/db_home/oraInst.loc OPatch version : 12.2.0.1.39 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19.3/db_home/cfgtoollogs/opatch/opatch2023-08-21_15-27-06PM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded. [oracle@sql 35320081]$
Make sure to stop all services running from the Oracle home where the patching is taking place. This includes shutting down any databases and the listener that are associated with the same ORACLE_HOME as the patch being applied.
[oracle@sql 35320081]$ lsnrctl stop LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-AUG-2023 15:39:44 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sql.dbace.in)(PORT=1521))) The command completed successfully [oracle@sql 35320081]$ [oracle@sql 35320081]$ [oracle@sql 35320081]$ [oracle@sql 35320081]$ [oracle@sql 35320081]$ [oracle@sql 35320081]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 21 15:39:50 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> SQL> SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>
Check for the services are stopped.
[oracle@sql 35320081]$ ps -ef|grep pmon oracle 7831 3040 0 15:42 pts/0 00:00:00 grep --color=auto pmon [oracle@sql 35320081]$ [oracle@sql 35320081]$ [oracle@sql 35320081]$ ps -ef|grep tns root 23 2 0 14:03 ? 00:00:00 [netns] oracle 7837 3040 0 15:42 pts/0 00:00:00 grep --color=auto tns [oracle@sql 35320081]$ [oracle@sql 35320081]$ [oracle@sql 35320081]$ [oracle@sql 35320081]$ ps -ef|grep exp oracle 7845 3040 0 15:42 pts/0 00:00:00 grep --color=auto exp [oracle@sql 35320081]$ [oracle@sql 35320081]$ [oracle@sql 35320081]$ [oracle@sql 35320081]$ ps -ef|grep rman oracle 7852 3040 0 15:42 pts/0 00:00:00 grep --color=auto rman
Navigate to the directory where the patch is located, and then execute the opatch utility using the following commands:
cd /u01/patch/35320081
Start applying Patch
[oracle@sql 35320081]$ $ORACLE_HOME/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.39 Copyright (c) 2023, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19.3/db_home Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.3/db_home/oraInst.loc OPatch version : 12.2.0.1.39 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19.3/db_home/cfgtoollogs/opatch/opatch2023-08-21_16-01-18PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 35320081 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/oracle/product/19.3/db_home') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying interim patch '35320081' to OH '/u01/app/oracle/product/19.3/db_home' 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.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.options.olap.api, 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.rdbms, 19.0.0.0.0... Patching component oracle.rdbms.util, 19.0.0.0.0... Patching component oracle.rdbms.rsf, 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.blaslapack, 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.dbdev, 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.ldap.security.osdt, 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.odbc.ic, 19.0.0.0.0... Patching component oracle.ons, 19.0.0.0.0... Patching component oracle.ons.ic, 19.0.0.0.0... Patching component oracle.oracore.rsf, 19.0.0.0.0... Patching component oracle.perlint, 5.28.1.0.0... Patching component oracle.precomp.common.core, 19.0.0.0.0... Patching component oracle.precomp.rsf, 19.0.0.0.0... Patching component oracle.rdbms.crs, 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.rdbms.rsf.ic, 19.0.0.0.0... Patching component oracle.rdbms.scheduler, 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.xdk.parser.java, 19.0.0.0.0... Patching component oracle.ldap.ssl, 19.0.0.0.0... Patching component oracle.ctx.rsf, 19.0.0.0.0... Patching component oracle.rdbms.dv, 19.0.0.0.0... Patching component oracle.rdbms.drdaas, 19.0.0.0.0... Patching component oracle.network.client, 19.0.0.0.0... Patching component oracle.rdbms.hsodbc, 19.0.0.0.0... Patching component oracle.network.listener, 19.0.0.0.0... Patching component oracle.ldap.rsf.ic, 19.0.0.0.0... Patching component oracle.dbtoolslistener, 19.0.0.0.0... Patching component oracle.nlsrtl.rsf, 19.0.0.0.0... Patching component oracle.xdk.xquery, 19.0.0.0.0... Patching component oracle.rdbms.install.common, 19.0.0.0.0... Patching component oracle.ovm, 19.0.0.0.0... Patching component oracle.oraolap, 19.0.0.0.0... Patching component oracle.rdbms.rman, 19.0.0.0.0... Patching component oracle.install.deinstalltool, 19.0.0.0.0... Patching component oracle.rdbms.install.plugins, 19.0.0.0.0... Patching component oracle.rdbms.lbac, 19.0.0.0.0... Patching component oracle.sdo.locator, 19.0.0.0.0... Patching component oracle.oraolap.dbscripts, 19.0.0.0.0... Patching component oracle.oraolap.api, 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.rdbms.hs_common, 19.0.0.0.0... Patching component oracle.xdk, 19.0.0.0.0... Patching component oracle.xdk.rsf, 19.0.0.0.0... Patching component oracle.ldap.client, 19.0.0.0.0... Patching component oracle.mgw.common, 19.0.0.0.0... Patching component oracle.odbc, 19.0.0.0.0... Patching component oracle.precomp.lang, 19.0.0.0.0... Patching component oracle.precomp.common, 19.0.0.0.0... Patching component oracle.jdk, 1.8.0.201.0... Patch 35320081 successfully applied. Sub-set patch [29517242] has become inactive due to the application of a super-set patch [35320081]. Please refer to Doc ID 2161861.1 for any possible further required actions. Log file location: /u01/app/oracle/product/19.3/db_home/cfgtoollogs/opatch/opatch2023-08-21_16-01-18PM_1.log OPatch succeeded. [oracle@sql 35320081]$
After installing the patch:
Verify that the applied Oracle Home patch is registered in the inventory.
[oracle@sql 35320081]$ $ORACLE_HOME/OPatch/opatch lspatches 35320081;Database Release Update : 19.20.0.0.230718 (35320081) 29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399) OPatch succeeded. [oracle@sql 35320081]$
Start Oracle Service
[oracle@sql 35320081]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 21 16:22:24 2023 Version 19.20.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2415917856 bytes Fixed Size 8928032 bytes Variable Size 520093696 bytes Database Buffers 1879048192 bytes Redo Buffers 7847936 bytes Database mounted. Database opened. SQL> SQL> SQL> SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.20.0.0.0
RunPerform the necessary post-patch procedures and execute the “datapatch” command.
[oracle@sql 35320081]$ cd $ORACLE_HOME/OPatch
[oracle@sql OPatch]$
[oracle@sql OPatch]$
[oracle@sql OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.20.0.0.0 Production on Mon Aug 21 16:25:43 2023
Copyright (c) 2012, 2023, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_13350_2023_08_21_16_25_43/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
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.20.0.0.0 Release_Update 230715022800: Installed
SQL registry:
Applied 19.3.0.0.0 Release_Update 190410122720 successfully on 21-AUG-23 08.58.21.281567 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
No interim patches need to be rolled back
Patch 35320081 (Database Release Update : 19.20.0.0.230718 (35320081)):
Apply from 19.3.0.0.0 Release_Update 190410122720 to 19.20.0.0.0 Release_Update 230715022800
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 35320081 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/35320081/25314491/35320081_apply_TEST_2023Aug21_16_26_19.log (no errors)
SQL Patching tool complete on Mon Aug 21 16:33:05 2023
[oracle@sql OPatch]$
Validate the patches applied to the database
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; PATCH_ID ACTION STATUS DESCRIPTION ACTION_TIME ---------- ---------- ---------- ---------------------------------------- -------------------- 29517242 APPLY SUCCESS Database Release Update : 19.3.0.0.19041 21-AUG-23 08.58.21.2 6 (29517242) 81567 AM 35320081 APPLY SUCCESS Database Release Update : 19.20.0.0.2307 21-AUG-23 04.33.05.4 18 (35320081) 02900 PM
Patching is done, start the Listener and services
[oracle@sql OPatch]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-AUG-2023 16:40:19 Copyright (c) 1991, 2023, Oracle. All rights reserved. Starting /u01/app/oracle/product/19.3/db_home/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19.3/db_home/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/sql/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sql.dbace.in)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sql.dbace.in)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 21-AUG-2023 16:40:19 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.3/db_home/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/sql/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sql.dbace.in)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully [oracle@sql OPatch]$
Rollback
Check existing patches
Shutdown Database and Listener
Rollback patch
$ORACLE_HOME/OPatch/opatch rollback -id 35320081
Start the Database and Listener
Rollback SQL changes (datapatch)
$ORACLE_HOME/OPatch/datapatch -verbose
Check Registry Status
sqlplus / as sysdba SET LINESIZE 500 SET PAGESIZE 1000 SET SERVEROUT ON SET LONG 2000000 COLUMN action_time FORMAT A25 COLUMN action FORMAT A10 COLUMN bundle_series FORMAT A4 COLUMN comments FORMAT A30 COLUMN description FORMAT A40 COLUMN namespace FORMAT A20 COLUMN status FORMAT A10 COLUMN version FORMAT A10 SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time, action, status, description, version, patch_id, bundle_series FROM sys.dba_registry_sqlpatch ORDER by action_time;