Apply the Latest DB RU Patch_19.18 (34765931) on Oracle Database 19C
Posted by Mir Sayeed Hassan on May 1st, 2023
Apply the Latest DB RU Patch_19.18 (34765931) on Oracle Database 19C
Check the status of Database
[oracle@db-stag ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 1 16:53:37 2023 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.17.0.0.0 SQL> select instance_name, version, open_mode from V$database, v$instance; INSTANCE_NAME VERSION OPEN_MODE ---------------- --------------------------- DBSTAGE 19.0.0.0.0 READ WRITE
Download the Patch from below link
https://support.oracle.com/epmos/faces/PatchSearchResults?_adf.ctrl-state=ym4c1dobw_120&_afrLoop=189225645173189
Create a directory & place the downloaded Patch into it.
[oracle@db-stag ~]$ cd /u01/source/PATCH_19_18/ [oracle@db-stag PATCH_19_18]$ ls p34765931_190000_Linux-x86-64.zip
Unzip the DB Patch
[oracle@db-stag PATCH_19_18]$ unzip p34765931_190000_Linux-x86-64.zip
[oracle@db-stag PATCH_19_18]$ ls 34765931 p34765931_190000_Linux-x86-64.zip PatchSearch.xml
[oracle@db-stag PATCH_19_18]$ cd 34765931/
[oracle@db-stag 34765931]$ ls custom etc files README.html README.txt
Verify the Patch Conflict.
[oracle@db-stag 34765931]$ /u01/app/oracle/product/19c/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/19c/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19c/db_1/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2023-05-01_15-55-40PM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded.
Shutdown database & listener
SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down.
[oracle@db-stag 34765931]$ lsnrctl stop LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-MAY-2023 16:58:42 Copyright (c) 1991, 2022, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully
Start Apply the Patch
[oracle@db-stag 34765931]$ /u01/app/oracle/product/19c/db_1/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19c/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19c/db_1/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2023-05-01_15-59-13PM_1.log Verifying environment and performing prerequisite checks... -------------------------------------------------------------------------------- Start OOP by Prereq process. Launch OOP... Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19c/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19c/db_1/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2023-05-01_16-01-40PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 34765931 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/19c/db_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying interim patch '34765931' to OH '/u01/app/oracle/product/19c/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.sdo.companion, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 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.slax.rsf, 19.0.0.0.0... Patching component oracle.ordim.jai, 19.0.0.0.0... Patching component oracle.bali.jewt, 11.1.1.6.0... Patching component oracle.bali.ewt, 11.1.1.6.0... Patching component oracle.help.ohj, 11.1.1.7.0... Patching component oracle.rdbms.locator, 19.0.0.0.0... Patching component oracle.perlint.expat, 2.0.1.0.4... Patching component oracle.rdbms.util, 19.0.0.0.0... Patching component oracle.rdbms, 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.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.ldap.rsf.ic, 19.0.0.0.0... Patching component oracle.network.listener, 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.hs_common, 19.0.0.0.0... Patching component oracle.network.client, 19.0.0.0.0... Patching component oracle.sdo.locator, 19.0.0.0.0... Patching component oracle.xdk.rsf, 19.0.0.0.0... Patching component oracle.rdbms.drdaas, 19.0.0.0.0... Patching component oracle.oraolap, 19.0.0.0.0... Patching component oracle.rdbms.dv, 19.0.0.0.0... Patching component oracle.javavm.server, 19.0.0.0.0... Patching component oracle.ldap.ssl, 19.0.0.0.0... Patching component oracle.ctx.atg, 19.0.0.0.0... Patching component oracle.rdbms.hsodbc, 19.0.0.0.0... Patching component oracle.xdk, 19.0.0.0.0... Patching component oracle.mgw.common, 19.0.0.0.0... Patching component oracle.oraolap.dbscripts, 19.0.0.0.0... Patching component oracle.xdk.xquery, 19.0.0.0.0... Patching component oracle.rdbms.rman, 19.0.0.0.0... Patching component oracle.odbc, 19.0.0.0.0... Patching component oracle.rdbms.install.plugins, 19.0.0.0.0... Patching component oracle.oraolap.api, 19.0.0.0.0... Patching component oracle.rdbms.scheduler, 19.0.0.0.0... Patching component oracle.rdbms.install.common, 19.0.0.0.0... Patching component oracle.xdk.parser.java, 19.0.0.0.0... Patching component oracle.ovm, 19.0.0.0.0... Patching component oracle.ctx.rsf, 19.0.0.0.0... Patching component oracle.ldap.client, 19.0.0.0.0... Patching component oracle.install.deinstalltool, 19.0.0.0.0... Patching component oracle.dbtoolslistener, 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 34765931 successfully applied. Sub-set patch [34419443] has become inactive due to the application of a super-set patch [34765 931]. Please refer to Doc ID 2161861.1 for any possible further required actions. Log file location: /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2023-05-01_16-01-4 0PM_1.log OPatch succeeded.
Verify the patch applied
[oracle@db-stag PATCH_19_18]$ cd $ORACLE_HOME/OPatch [oracle@db-stag OPatch]$ ./opatch lspatches 34765931;DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931) 33815596;OCW RELEASE UPDATE 19.15.0.0.0 (33815596) OPatch succeeded.
OR
[oracle@db-stag OPatch]$ ./opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.37 Copyright (c) 2023, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19c/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19c/db_1/oraInst.loc OPatch version : 12.2.0.1.37 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2023-05-01_16-17-18PM_1.log Lsinventory Output file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/lsinv/lsinventory2023-05-01_16-17-18PM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: db-stag ARU platform id: 226 ARU platform description:: Linux x86-64 Installed Top-level Products (1): Oracle Database 19c 19.0.0.0.0 There are 1 products installed in this Oracle Home. Interim patches (2) : Patch 34765931 : applied on Mon May 01 16:09:06 IRST 2023 Unique Patch ID: 25098466 Patch description: "DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)" Created on 27 Jan 2023, 11:25:14 hrs UTC Bugs fixed: 10123661, 1297945, 14570574, 14735102, 15931756, 15959416, 16662822 16664572, 16750494, 17275499, 17395507, 17428816, 17468475, 17562236 17777718, 18534283, 18697534, 18943905, 19080742, 19138896, 19147630 19630878, 19697993, 19884953, 19958239, 20007421, 20083476, 20289608 20313356, 20319830, 20479545, 20661314, 20721183, 20867658, 20922160 21119541, 21232786, 21245711, 21374587, 21528318, 21533734, 21629064 OPatch succeeded.
Check the list of DB Patches are applied init it.
[oracle@db-stag OPatch]$ ./opatch lsinv | grep applied Patch 34765931 : applied on Mon May 01 16:09:06 IRST 2023 Patch 33815596 : applied on Tue Aug 09 16:56:24 IRDT 2022
Bring back the Oracle Database & Listener, Verify the POST UPDATE THE PATCH
SQL> startup ORACLE instance started. Total System Global Area 2432693416 bytes Fixed Size 9172136 bytes Variable Size 1526726656 bytes Database Buffers 889192448 bytes Redo Buffers 7602176 bytes Database mounted. Database opened.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ---------------------------------------------- 2 PDB$SEED READ ONLY NO 3 STAG1 MOUNTED 4 TESTSTAG2 MOUNTED 5 CARDSTAG MOUNTED 6 XSTAG3 MOUNTED 7 XESTAG4 MOUNTED 8 TESTSTAG2_KUBER MOUNTED
Open the Pluggable Databases
SQL> alter pluggable database all open; Pluggable database altered.
Check the Status of ALL PDBS
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ---------------------------- ---------- ---------- 2 PDB$SEED READ ONLY NO 3 STAG1 READ WRITE NO 4 TESTSTAG2 READ WRITE NO 5 CARDSTAG READ WRITE NO 6 XSTAG3 READ WRITE NO 7 XESTAG4 READ WRITE NO 8 TESTSTAG2_KUBER READ WRITE NO
Verify by Query level.
SQL> select CON_ID, TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time, PATCH_ID, PATCH_TYPE, ACTION, DESCRIPTION, SOURCE_VERSION, TARGET_VERSION from CDB_REGISTRY_SQLPATCH order by CON_ID, action_time, patch_id; CON_ID ACTION_TIM PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION ---------- ---------- ---------- ------------------------- ------------------------------------------------------------------------------- 6 2022-08-09 29517242 RU APPLY Database Release Update : 19.3.0.0.190416 (29517242) 19.1.0.0.0 19.3.0.0.0 6 2023-02-20 34419443 RU APPLY Database Release Update : 19.17.0.0.221018 (34419443) 19.3.0.0.0 19.17.0.0.0
To verify the individual PDB Database., Connect to each of them & verify
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ----------------------------------------- 6 XSTAG3 READ WRITE NO
Therefore the DB Patch are applied in Container., Need to apply for All PDB Database as fallow.
[oracle@db-stag admin]$ cd $ORACLE_HOME/OPatch
[oracle@db-stag OPatch]$ ./datapatch -verbose SQL Patching tool version 19.18.0.0.0 Production on Mon May 1 17:23:35 2023 Copyright (c) 2012, 2023, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_1943_2023_05_01_17_23_35/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.18.0.0.0 Release_Update 230127005551: Installed PDB TESTSTAG2: Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 20-FEB-23 07.21.56.479431 PM PDB TESTSTAG2_KUBER: Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 20-FEB-23 07.21.56.479431 PM PDB CARDSTAG: Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 20-FEB-23 07.22.04.916619 PM PDB CDB$ROOT: Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 20-FEB-23 07.21.30.269349 PM PDB STAG1: Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 20-FEB-23 07.21.47.888159 PM PDB PDB$SEED: Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 20-FEB-23 07.21.39.283941 PM PDB XSTAG3: Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 20-FEB-23 07.22.14.715265 PM PDB XESTAG4: Applied 19.17.0.0.0 Release_Update 220924224051 successfully on 20-FEB-23 07.22.23.472597 PM Adding patches to installation queue and performing prereq checks...done Installation queue: For the following PDBs: CDB$ROOT PDB$SEED STAG1 TESTSTAG2 CARDSTAG XSTAG3 XESTAG4 TESTSTAG2_KUBER No interim patches need to be rolled back Patch 34765931 (DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)): Apply from 19.17.0.0.0 Release_Update 220924224051 to 19.18.0.0.0 Release_Update 230127005551 No interim patches need to be applied Installing patches... Patch installation complete. Total patches installed: 8 Validating logfiles...done Patch 34765931 apply (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_PEPSTAGE_CDBROOT_2023May01_17_27_34.log (no errors) Patch 34765931 apply (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_PEPSTAGE_PDBSEED_2023May01_17_30_27.log (no errors) Patch 34765931 apply (pdb STAG1): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_PEPSTAGE_STAG1_2023May01_17_30_27.log (no errors) Patch 34765931 apply (pdb TESTSTAG2): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_PEPSTAGE_TESTSTAG2_2023May01_17_30_27.log (no errors) Patch 34765931 apply (pdb CARDSTAG): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_PEPSTAGE_CARDSTAG_2023May01_17_30_27.log (no errors) Patch 34765931 apply (pdb XSTAG3): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_PEPSTAGE_XSTAG3_2023May01_17_30_27.log (no errors) Patch 34765931 apply (pdb XESTAG4): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_PEPSTAGE_XESTAG4_2023May01_17_30_27.log (no errors) Patch 34765931 apply (pdb TESTSTAG2_KUBER): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_PEPSTAGE_TESTSTAG2_KUBER_2023May01_17_30_27.log (no errors) SQL Patching tool complete on Mon May 1 17:35:18 2023
Hence applied for all PDB DB’s & veified.
- Now check the Patch allied for All PDB'S DB'S & INVALID Objects of Container & all pluggable databases. - Incase if the Invalid objects does not exist., you can skip & this process.
SQL> select count(*) from all_objects where status='INVALID' group by owner; no rows selected
Connect to Pluggable Database & check the INVALID Objects
SQL> alter session set container=STAG1;
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- --------------------------------------- 3 STAG1 READ WRITE NO
Verify the Patch applied on PDB DB
SQL> select CON_ID, TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time, PATCH_ID, PATCH_TYPE, ACTION, DESCRIPTION, SOURCE_VERSION, TARGET_VERSION from CDB_REGISTRY_SQLPATCH order by CON_ID, action_time, patch_id; CON_ID ACTION_TIM PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION -------------------- ---------- ---------- --------------- ----------------------------------------------------------------------------------------------- 6 2022-08-09 29517242 RU APPLY Database Release Update : 19.3.0.0.190416 (29517242) 19.1.0.0.0 19.3.0.0.0 6 2023-02-20 34419443 RU APPLY Database Release Update : 19.17.0.0.221018 (34419443) 19.3.0.0.0 19.17.0.0.0 6 2023-05-01 34765931 RU APPLY DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931) 19.17.0.0.0 19.18.0.0.0
Here., there was an INVALID Objects was exist init.
SQL> select count(*) from all_objects where status='INVALID' group by owner; COUNT(*) ---------- 4 2 1
Run the below script to validate all the INVALID Objects in Database
SQL> @?/rdbms/admin/utlrp.sql; Session altered.
Verify
SQL> select count(*) from all_objects where status='INVALID' group by owner; no rows selected.