Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

Apply the Latest DB RU Patch 34419443 on Oracle Database 19c

Posted by Mir Sayeed Hassan on December 6th, 2022

Apply the Latest DB RU Patch 34419443 onOracle Database 19c

Check the status of Database

SQL> select instance_name, version, open_mode from V$database, v$instance;

INSTANCE_NAME       VERSION       OPEN_MODE
---------------------------------------------
proddb           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

Transfer the downloaded Patch into Server

[oracle@PROD-DB ~]$ cd /u01/source/Patch_19.17/

[oracle@PROD-DB Patch_19.17]$ ls
p34419443_190000_Linux-x86-64.zip
[oracle@PROD-DB Patch_19.17]$ unzip p34419443_190000_Linux-x86-64.zip
[root@PROD-DB source]# chown -R oracle:oinstall Patch_19.17/
[oracle@PROD-DB ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 6 15:03:25 2022
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
[root@PROD-DB Patch_19.17]# unzip p34419443_190000_Linux-x86-64.zip
[oracle@PROD-DB Patch_19.17]$ ls
34419443 p34419443_190000_Linux-x86-64.zip PatchSearch.xml

Verify the Patch Conflict

[oracle@PROD-DB 34419443]$ /u01/app/oracle/product/19c/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.34
Copyright (c) 2022, 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.34
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2022-12-06_15-40-49PM_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@PROD-DB 34419443]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-DEC-2022 15:43:05
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

Start Apply the Patch

[oracle@PROD-DB 34419443]$ /u01/app/oracle/product/19c/db_1/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.34
Copyright (c) 2022, 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.34
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2022-12-06_15-43-46PM_1.log

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

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 '34419443' 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.options.olap.api, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.xdk.companion, 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.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.perlint, 5.28.1.0.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.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.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.precomp.common.core, 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.ssl, 19.0.0.0.0...
Patching component oracle.network.client, 19.0.0.0.0...
Patching component oracle.xdk, 19.0.0.0.0...
Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...
Patching component oracle.ldap.client, 19.0.0.0.0...
Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...
Patching component oracle.rdbms.install.common, 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.rdbms.scheduler, 19.0.0.0.0...
Patching component oracle.mgw.common, 19.0.0.0.0...
Patching component oracle.ctx.rsf, 19.0.0.0.0...
Patching component oracle.precomp.rsf, 19.0.0.0.0...
Patching component oracle.ctx.atg, 19.0.0.0.0...
Patching component oracle.sdo.locator, 19.0.0.0.0...
Patching component oracle.xdk.xquery, 19.0.0.0.0...
Patching component oracle.xdk.rsf, 19.0.0.0.0...
Patching component oracle.xdk.parser.java, 19.0.0.0.0...
Patching component oracle.install.deinstalltool, 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.rdbms.drdaas, 19.0.0.0.0...
Patching component oracle.rdbms.dv, 19.0.0.0.0...
Patching component oracle.dbtoolslistener, 19.0.0.0.0...
Patching component oracle.rdbms.rman, 19.0.0.0.0...
Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...
Patching component oracle.rdbms.lbac, 19.0.0.0.0...
Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...
Patching component oracle.oraolap, 19.0.0.0.0...
Patching component oracle.network.listener, 19.0.0.0.0...
Patching component oracle.odbc, 19.0.0.0.0...
Patching component oracle.ovm, 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 34419443 successfully applied.
Sub-set patch [29517242] has become inactive due to the application of a super-set patch [34419443].
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/opatch2022-12-06_15-43-46PM_1.log

OPatch succeeded.

Verify the patch applied

[oracle@PROD-DB OPatch]$ ./opatch lspatches

34419443;Database Release Update : 19.17.0.0.221018 (34419443)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.

[oracle@PROD-DB 34419443]$ /u01/app/oracle/product/19c/db_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.34
Copyright (c) 2022, 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.34
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/opatch2022-12-06_15-49-01PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/19c/db_1/cfgtoollogs/opatch/lsinv/lsinventory2022-12-06_15-49-01PM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: PROD-DB
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 34419443 : applied on Tue Dec 06 15:45:46 IRST 2022
Unique Patch ID: 24972075
Patch description: "Database Release Update : 19.17.0.0.221018 (34419443)"
Created on 14 Oct 2022, 05:25:14 hrs UTC
Bugs fixed:
31994298, 32070206, 30373419, 29179097, 32610271, 30398251, 29525467
24916492, 31788785, 31127969, 32893951, 33492066, 31555173, 28915561
----------- 
----------- (Patch number continue like this more)
31916096

Patch 29585399 : applied on Thu Apr 18 11:51:33 IRDT 2019
Unique Patch ID: 22840393
Patch description: "OCW RELEASE UPDATE 19.3.0.0.0 (29585399)"
Created on 9 Apr 2019, 19:12:47 hrs PST8PDT
Bugs fixed:
27222128, 27572040, 27604329, 27760043, 27877830, 28302580, 28470673
28621543, 28642469, 28699321, 28710663, 28755846, 28772816, 28785321
28800508, 28808652, 28815557, 28847541, 28847572, 28870496, 28871040
28874416, 28877252, 28881191, 28881848, 28888083, 28911140, 28925250
28925460, 28935956, 28940472, 3, 28942694, 28951332, 28963036, 28968779
28980448, 28995287, 29003207, 29003617, 29016294, 29018680, 29024876
29026154, 29027933, 29047127, 29052850, 29058476, 29111631, 29112455
29117337, 29123444, 29125708, 29125786, 29129476, 29131772, 29132456
29139727, 29146157, 29147849, 29149170, 29152603, 29152752, 29154631
29154636, 29154829, 29159216, 29159661, 29160462, 29161923, 29169540
29169739, 29170717, 29173618, 29181568, 29182920, 29183298, 29186091
29191827, 29201143, 29201695, 29209545, 29210577, 29210610, 29210624
29210683, 29213641, 29219627, 29224294, 29225861, 29229839, 29235934
29242906, 29243749, 29244495, 29244766, 29244968, 29248723, 29249583
29251564, 29255616, 29260224, 29261695, 29271019, 29273360, 29282090
29282666, 29285453, 29285621, 29290235, 29292232, 29293806, 29294753
29299830, 29307090, 29307109, 29311336, 29329675, 29330791, 29339299
29357821, 29360467, 29360775, 29367971, 29368725, 29379299, 29379381
29380527, 29381000, 29382296, 29391301, 29393649, 29402110, 29411931
29413360, 29457319, 29465047
-------------------------------------------------------------------------------
OPatch succeeded.

Bring back the Oracle Database & Listener, Verify the POST UPDATE THE PATCH

[oracle@PROD-DB ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 6 15:51:53 2022
Version 19.17.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 2.0670E+10 bytes
Fixed Size 13873520 bytes
Variable Size 2483027968 bytes
Database Buffers 1.8119E+10 bytes
Redo Buffers 53231616 bytes
Database mounted.
Database opened.
[oracle@PROD-DB ~]$ lsnrctl start

Post-installation

Open all pluggable database.

SQL> conn / as sysdba
Connected.
SQL> select name, open_mode from v$database;

NAME                  OPEN_MODE
--------------------------------
PROD-DB              READ WRITE
Open pluggable database for all

SQL> alter pluggable database all open;
Pluggable database altered.

datapatch -verbose

It loads modified SQL data and may take a while to complete, just be patient.

[oracle@PROD-DB OPatch]$ ./opatch lsinv | grep applied

Patch 34419443 : applied on Mon Feb 20 18:22:29 IRST 2023
Patch 33815596 : applied on Tue Aug 09 16:56:24 IRDT 2022

Apply to all Pluggable Database

[oracle@PROD-DB OPatch]$ ./datapatch -verbose

Check the INVALID Object in database

SQL> select count(*) from all_objects where status='INVALID' group by owner;

COUNT(*)
----------
113
191
40
1
1
2
8

7 rows selected.

Recompile Invalid Objects

We use utlrp.sql to recompile invalid objects in all databases, including CDB and all PDB.

SQL> @?/rdbms/admin/utlrp.sql;
Session altered.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-12-06 16:03:52

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 2022-12-06 16:04:03

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.

Re-Verify the INVALID DB Objects

SQL> select count(*) from all_objects where status='INVALID' group by owner;

no rows selected

Connect to each & every pluggable database & apply on it.

SQL> alter session set container=xpgcamo;

Session altered.

SQL> @?/rdbms/admin/utlrp.sql;

Session altered.

Finally Verifiy

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 
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
---------------------------------------------------------------------------------------------------------------------------------------------
1      2022-11-26    29517242    RU        APPLY   Database Release Update 19.1.0.0.0 19.3.0.0.0  e :       19.3.0.0.190416      (29517242)
1      2022-12-06    34419443    RU        APPLY   Database Release Update 19.3.0.0.0 19.17.0.0.0 e :       19.17.0.0.221018     (34419443)

=====Hence tested & verified in our test env=====