Apply the Latest DB RU Patch_19.25 36912597 for Oracle Database 19C
Posted by Mir Sayeed Hassan on February 4th, 2025
Apply the Latest DB RU Patch_19.25 36912597 for Oracle Database 19C
Check the status of Database
SQL> select instance_name, version, open_mode from V$database, v$instance;
INSTANCE_NAME VERSION OPEN_MODE
---------------- ------------------------------------
ora19cdb 19.0.0.0.0 READ WRITE
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------------------------ 2 PDB$SEED READ ONLY NO 3 TEST_PDB READ WRITE NO 4 D2T_PDB READ WRITE NO 5 PAY_SOURCE_PDB READ WRITE NO 6 PAYTEST READ WRITE NO 7 POST_TDB READ WRITE NO 8 TEST READ WRITE NO 9 OUTSOURCE READ WRITE NO 10 PAYTEST125 READ WRITE NO 11 SBTEST READ WRITE NO 12 LOCAL_UPGRADE READ WRITE NO 13 ARC_TEST READ WRITE NO
Before apply this main patch, Verify the latest OPatch applied or not., If not download and apply the latest OPatch, Fallow the below link to apply the OPatch.
Apply the latest OPatch 6880880 (12.2.0.1.45) issued on Jan-2025 on 19C Database
Download the Main Patch from below link
https://support.oracle.com/epmos/faces/PatchSearchResults?_adf.ctrl-state=ym4c1dobw_120&_afrLoop=189225645173189
Transfer the downloaded Patch into Server
[oracle@ora19ctdb ~]$ cd /u01/source/Patch_19.25/
[oracle@ora19ctdb PATCH_19.25]$ ls p36912597_190000_Linux-x86-64.zip
[root@ora19ctdb source]# chown -R oracle:oinstall PATCH_19.25/ [root@ora19ctdb source]# su - oracle
[oracle@ora19ctdb PATCH_19.25]$ unzip p36912597_190000_Linux-x86-64.zip
[oracle@ora19ctdb PATCH_19.25]$ ls 36912597 p36912597_190000_Linux-x86-64.zip PatchSearch.xml
Verify the Patch Conflict
[oracle@ora19ctdb ~]$ cd /u01/source/PATCH_19.25/36912597/ [oracle@ora19ctdb 36912597]$ /u01/app/oracle/product/19c/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.45 Copyright (c) 2025, 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.45 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2025-02-04_15-53-39PM_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@ora19ctdb admin]$ lsnrctl stop LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-FEB-2025 15:56:14 Copyright (c) 1991, 2022, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19ctdb)(PORT=1251))) The command completed successfully
Start Apply the Main Patch
[oracle@oemdb 36912597]$ [oracle@ora19ctdb 36912597]$ /u01/app/oracle/product/19c/db_1/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.45 Copyright (c) 2025, 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.45 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2025-02-04_15-57-01PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 36912597 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 '36912597' to OH '/u01/app/oracle/product/19c/db_1' ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.crypto.rsf, 19.0.0.0.0 ] , [ oracle.pg4appc, 19.0.0.0.0 ] , [ oracle.pg4mq, 19.0.0.0.0 ] , [ oracle.precomp.companion, 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.net.cman, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.ldap.ztk, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] , [ oracle.jdk, 1.8.0.391.11 ] 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.rsf, 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.rsf, 19.0.0.0.0... Patching component oracle.javavm.server, 19.0.0.0.0... Patching component oracle.xdk.xquery, 19.0.0.0.0... Patching component oracle.ctx.rsf, 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.nlsrtl.rsf.lbuilder, 19.0.0.0.0... Patching component oracle.rdbms.rat, 19.0.0.0.0... Patching component oracle.ldap.rsf.ic, 19.0.0.0.0... Patching component oracle.rdbms.dv, 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.ldap.client, 19.0.0.0.0... Patching component oracle.install.deinstalltool, 19.0.0.0.0... Patching component oracle.rdbms.rman, 19.0.0.0.0... Patching component oracle.oraolap.api, 19.0.0.0.0... Patching component oracle.dbtoolslistener, 19.0.0.0.0... Patching component oracle.rdbms.drdaas, 19.0.0.0.0... Patching component oracle.rdbms.hs_common, 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.rdbms.dm, 19.0.0.0.0... Patching component oracle.ldap.ssl, 19.0.0.0.0... Patching component oracle.xdk.parser.java, 19.0.0.0.0... Patching component oracle.odbc, 19.0.0.0.0... Patching component oracle.network.listener, 19.0.0.0.0... Patching component oracle.ctx.atg, 19.0.0.0.0... Patching component oracle.rdbms.install.common, 19.0.0.0.0... Patching component oracle.rdbms.hsodbc, 19.0.0.0.0... Patching component oracle.network.aso, 19.0.0.0.0... Patching component oracle.rdbms.locator, 19.0.0.0.0... Patching component oracle.rdbms.install.plugins, 19.0.0.0.0... Patching component oracle.nlsrtl.rsf, 19.0.0.0.0... Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0... Patching component oracle.nlsrtl.rsf.ic, 19.0.0.0.0... Patching component oracle.oraolap.dbscripts, 19.0.0.0.0... Patching component oracle.network.client, 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 36912597 successfully applied. Sub-set patch [33806152] has become inactive due to the application of a super-set patch [36912597]. 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/opatch2025-02-04_15-57-01PM_1.log OPatch succeeded.
Verify the patch applied
[oracle@ora19ctdb OPatch]$ ./opatch lspatches 36912597;Database Release Update : 19.25.0.0.241015 (36912597) 33815596;OCW RELEASE UPDATE 19.15.0.0.0 (33815596) OPatch succeeded.
[oracle@ora19ctdb OPatch]$ ./opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.45 Copyright (c) 2025, 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.45 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2025-02-04_16-06-40PM_1.log Lsinventory Output file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/lsinv/lsinventory2025-02-04_16-06-40PM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: ora19ctdb 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 36912597 : applied on Tue Feb 04 16:00:14 IRST 2025 Unique Patch ID: 25871884 Patch description: "Database Release Update : 19.25.0.0.241015 (36912597)" Created on 11 Oct 2024, 06:47:24 hrs UTC Bugs fixed: 10121473, 10123661, 1297945, 14570574, 14735102, 15878434, 15931756 15959416, 16662822, 16664572, 16750494, 17275499, 17395507, 17428816 17468475, 17562236, 17777718, 18534283, 18697534, 18943905, 19080742 OPatch succeeded.
or
[oracle@ora19ctdb OPatch]$ ./opatch lsinv | grep applied Patch 36912597 : applied on Tue Feb 04 16:00:14 IRST 2025 Patch 33815596 : applied on Tue Aug 09 16:56:24 IRDT 2022
Bring back the Oracle Database & Listener, Verify the POST UPDATE THE PATCH
[oracle@ora19ctdb OPatch]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 4 16:07:49 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1.0737E+10 bytes Fixed Size 13943384 bytes Variable Size 7398752256 bytes Database Buffers 3305111552 bytes Redo Buffers 19607552 bytes Database mounted. Database opened.
SYS> alter pluggable database all open; Pluggable database altered.
Start the listener.
[oracle@ora19ctdb ~]$ lsnrctl start
Apply the Main Patch for all pluggable database
[oracle@ora19ctdb OPatch]$ ./datapatch -verbose SQL Patching tool version 19.25.0.0.0 Production on Tue Feb 4 16:10:19 2025 Copyright (c) 2012, 2024, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_13816_2025_02_04_16_10_19/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.25.0.0.0 Release_Update 241010184253: Installed PDB ARC_TEST: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 27-JUN-23 02.00.07.445516 PM PDB CDB$ROOT: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 20-JUN-23 03.06.24.139422 PM PDB D2T_PDB: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 20-JUN-23 03.06.37.366931 PM PDB PAY_SOURCE_PDB: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 20-JUN-23 03.06.43.981429 PM PDB PAYTEST: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 20-JUN-23 03.06.50.548261 PM PDB PAYTEST125: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 27-JUN-23 02.00.07.445516 PM PDB PDB$SEED: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 27-JUN-23 02.00.07.445516 PM PDB POST_TDB: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 21-JUN-23 05.05.27.735121 PM PDB OUTSOURCE: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 20-JUN-23 03.06.37.366931 PM PDB SBTEST: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 27-JUN-23 02.00.07.445516 PM PDB LOCAL_UPGRADE: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 27-JUN-23 02.00.07.445516 PM PDB TEST_PDB: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 20-JUN-23 03.06.30.727397 PM PDB TEST: Applied 19.15.0.0.0 Release_Update 220331125408 successfully on 27-JUN-23 01.51.13.474918 PM Adding patches to installation queue and performing prereq checks...done Installation queue: For the following PDBs: CDB$ROOT PDB$SEED TEST_PDB D2T_PDB PAY_SOURCE_PDB PAYTEST POST_TDB TEST OUTSOURCE PAYTEST125 SBTEST LOCAL_UPGRADE ARC_TEST No interim patches need to be rolled back Patch 36912597 (Database Release Update : 19.25.0.0.241015 (36912597)): Apply from 19.15.0.0.0 Release_Update 220331125408 to 19.25.0.0.0 Release_Update 241010184253 No interim patches need to be applied Installing patches... Patch installation complete. Total patches installed: 13 Validating logfiles...done Patch 36912597 apply (pdb CDB$ROOT): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_CDBROOT_2025Feb04_16_14_26.log (no errors) Patch 36912597 apply (pdb PDB$SEED): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_PDBSEED_2025Feb04_16_19_31.log (no errors) Patch 36912597 apply (pdb TEST_PDB): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_TEST_PDB_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb D2T_PDB): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_D2T_PDB_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb PAY_SOURCE_PDB): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_PAY_SOURCE_PDB_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb PAYTEST): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_PAYTEST_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb POST_TDB): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_POST_TDB_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb TEST): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_TEST_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb OUTSOURCE): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_OUTSOURCE_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb PAYTEST125): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_PAYTEST125_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb SBTEST): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_SBTEST_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb LOCAL_UPGRADE): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_LOCAL_UPGRADE_2025Feb04_16_19_30.log (no errors) Patch 36912597 apply (pdb ARC_TEST): SUCCESS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/36912597/25871884/36912597_apply_PEPTEST_ARC_TEST_2025Feb04_16_19_30.log (no errors) Automatic recompilation incomplete; run utlrp.sql to revalidate. PDBs: ARC_TEST D2T_PDB PAY_SOURCE_PDB PAYTEST PAYTEST125 PDB$SEED POST_TDB OUTSOURCE SBTEST LOCAL_UPGRADE TEST_PDB TEST SQL Patching tool complete on Tue Feb 4 16:28:11 2025
Check the Invalid Objects on Both Container and Pluggable Database
SQL> select count(*) from all_objects where status='INVALID' group by owner; COUNT(*) ---------- 113 191 40 1 1 2 8 7 rows selected.
Run the below script to re-compile invalid objects
SQL> @?/rdbms/admin/utlrp.sql; Session altered. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2025-02-04 16:29:07 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2025-02-04 16:29:11 DOC> The following query reports the number of invalid objects. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of exceptions caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC> Note: Typical compilation errors (due to coding errors) are not DOC> logged into this table: they go into DBA_ERRORS instead. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed.
Verify the Invalid Objects
SQL> select count(*) from all_objects where status='INVALID' group by owner; no rows selected
Login to the each and every Pluggable database and check the same invalid objects and re-compile.
SQL> alter session set container=TEST_PDB; Session altered.
SQL> select count(*) from all_objects where status='INVALID' group by owner; COUNT(*) ---------- 241 4 163 43 2 1 1 7 14 9 rows selected.
SQL> @?/rdbms/admin/utlrp.sql; Session altered. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2025-02-04 16:30:40 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2025-02-04 16:31:04 DOC> The following query reports the number of invalid objects. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of exceptions caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC> Note: Typical compilation errors (due to coding errors) are not DOC> logged into this table: they go into DBA_ERRORS instead. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed.
Verify the Invalid Object on Pluggable Database.
SQL> select count(*) from all_objects where status='INVALID' group by owner; no rows selected
Finally verify applied patch by query.
SQL> SET LINESIZE 300 SET PAGESIZE 800 SET SERVEROUT ON SET LONG 2000000 COLUMN action_time FORMAT A11 COLUMN action FORMAT A12 COLUMN patch_type FORMAT A10 COLUMN description FORMAT A22 COLUMN status FORMAT A12 COLUMN version FORMAT A12 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_TIME PATCH_ID PATCH_TYPE ACTION DESCRIPTION SOURCE_VERSION TARGET_VERSION ---------- ----------- ---------- ---------- ------------ ---------------------- --------------- --------------- 3 2022-08-09 29517242 RU APPLY Database Release Updat 19.1.0.0.0 19.3.0.0.0 e : 19.3.0.0.190416 (2 9517242) 3 2023-06-20 33806152 RU APPLY Database Release Updat 19.3.0.0.0 19.15.0.0.0 e : 19.15.0.0.220419 ( 33806152) 3 2025-02-04 36912597 RU APPLY Database Release Updat 19.15.0.0.0 19.25.0.0.0 e : 19.25.0.0.241015 ( 36912597)