Description:-
This article we are going to see steps used to apply the latest Oracle 21c Database Release Update Patch 34160444: DATABASE RELEASE UPDATE 21.7.0.0.0
The environment is single instance database. (Multi Tenant Database)
Note:-
1)Review readme file on Patch 34160444: DATABASE RELEASE UPDATE 21.7.0.0.0
2)Download patch p34160444_210000_Linux-x86-64.zip
3)Make sure the opatch version is minimum 12.2.0.1.30
Step:-1 Download the Patch from oracle support
p34160444_210000_Linux-x86-64.zip
p6880880_210000_Linux-x86-64.zip
Step:-2 Copied the patch to DB Server
[oracle@oracle21c oracle21c]$ ls -lrt
total 1104268
-rwxrw-rw-. 1 oracle oinstall 124211908 Sep 21 19:08 p6880880_210000_Linux-x86-64.zip
-rwxrw-rw-. 1 oracle oinstall 1006554472 Sep 21 19:22 p34160444_210000_Linux-x86-64.zip
Step:-3 Upgrade Opatch Tool from 12.2.0.1.26 to 12.2.0.1.32
[oracle@oracle21c ~]$ . .bash_profile
[oracle@oracle21c ~]$ cd oracle21c/
[oracle@oracle21c oracle21c]$ cp p6880880_210000_Linux-x86-64.zip $ORACLE_HOME
[oracle@oracle21c oracle21c]$ cd $ORACLE_HOME
[oracle@oracle21c 21.0.0.0]$ cd OPatch[oracle@oracle21c OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.26
OPatch succeeded.
[oracle@oracle21c OPatch]$ cd ..
[oracle@oracle21c 21.0.0.0]$ mv OPatch OPatch_bkp
[oracle@oracle21c 21.0.0.0]$ unzip p6880880_210000_Linux-x86-64.zip
[oracle@oracle21c 21.0.0.0]$ cd OPatch
[oracle@oracle21c OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.32
OPatch succeeded.
Step:-4 Check Patches status before apply using below query
Before proceeding a oracle 21c patching check the component status and invalids
Previous patching details:-
SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN comments FORMAT A30
COLUMN description FORMAT A60
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
SELECT TO_CHAR(action_time, ‘YYYY-MM-DD’) AS action_time,action,status,
description,patch_id FROM sys.dba_registry_sqlpatch ORDER by action_time;
Component check:-
col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;
Identifying Invalid Objects before patching:-
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = ‘INVALID’
ORDER BY owner, object_type, object_name;
Step 5:- Shutdown Database and Listener
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.3.0.0.0
[oracle@oracle21c OPatch]$ lsnrctl stop
LSNRCTL for Linux: Version 21.0.0.0.0 – Production on 21-SEP-2022 19:53:15
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521)))
The command completed successfully
Step:-6 Take Backup of ORACLE_HOME and Database (Rollback plan)
cd $ORACLE_HOME
tar -cvf oracle_home_21SEP_2022.tar $ORACLE_HOME
Connect with rman take full backup
Step:-7 Apply RU patch on ORACLE_HOME 21c
[oracle@oracle21c ~]$ cd oracle21c/
[oracle@oracle21c oracle21c]$ ls -lrt
total 1104268
-rwxrw-rw-. 1 oracle oinstall 124211908 Sep 21 19:08 p6880880_210000_Linux-x86-64.zip
-rwxrw-rw-. 1 oracle oinstall 1006554472 Sep 21 19:22 p34160444_210000_Linux-x86-64.zip
[oracle@oracle21c oracle21c]$ unzip p34160444_210000_Linux-x86-64.zip
[oracle@oracle21c oracle21c]$ ls -lrt
total 1104464
drwxr-xr-x. 4 oracle oinstall 63 Jul 1 16:17 34160444
-rw-rw-r–. 1 oracle oinstall 200396 Jul 20 03:12 PatchSearch.xml
-rwxrw-rw-. 1 oracle oinstall 124211908 Sep 21 19:08 p6880880_210000_Linux-x86-64.zip
-rwxrw-rw-. 1 oracle oinstall 1006554472 Sep 21 19:22 p34160444_210000_Linux-x86-64.zip
[oracle@oracle21c oracle21c]$ cd 34160444/
[oracle@oracle21c ~]$ export ORACLE_HOME=/u01/app/oracle/product/21.0.0.0
[oracle@oracle21c ~]$ export PATH=/u01/app/oracle/product/21.0.0.0/OPatch:$PATH
[oracle@oracle21c ~]$ opatch version
OPatch Version: 12.2.0.1.32
OPatch succeeded.
[oracle@oracle21c 34160444]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.32
Copyright (c) 2022, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/21.0.0.0
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/21.0.0.0/oraInst.loc
OPatch version : 12.2.0.1.32
OUI version : 12.2.0.9.0
Log file location : /u01/app/oracle/product/21.0.0.0/cfgtoollogs/opatch/opatch2022-09-21_20-01-00PM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 34160444
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/21.0.0.0’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying interim patch ‘34160444’ to OH ‘/u01/app/oracle/product/21.0.0.0’
ApplySession: Optional component(s) [ oracle.network.gsm, 21.0.0.0.0 ] , [ oracle.python, 21.0.0.0.0 ] , [ oracle.tfa, 21.0.0.0.0 ] , [ oracle.network.cman, 21.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 21.0.0.0.0 ] , [ oracle.sdo.companion, 21.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 21.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 21.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 21.0.0.0.0 ] , [ oracle.duma, 21.0.0.0.0 ] , [ oracle.oraolap.mgmt, 21.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 21.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 21.0.0.0.0 ] , [ oracle.rdbms.ic, 21.0.0.0.0 ] , [ oracle.rdbms.tg4ifxm, 21.0.0.0.0 ] , [ oracle.net.cman, 21.0.0.0.0 ] , [ oracle.ons.cclient, 21.0.0.0.0 ] , [ oracle.jdk, 1.8.0.271.00 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.network.rsf, 21.0.0.0.0…
Patching component oracle.rdbms, 21.0.0.0.0…
Patching component oracle.rdbms.util, 21.0.0.0.0…
Patching component oracle.rdbms.rsf, 21.0.0.0.0…
Patching component oracle.dbjava.ic, 21.0.0.0.0…
Patching component oracle.dbjava.jdbc, 21.0.0.0.0…
Patching component oracle.dbjava.ucp, 21.0.0.0.0…
Patching component oracle.has.common.cvu, 21.0.0.0.0…
Patching component oracle.javavm.server, 21.0.0.0.0…
Patching component oracle.javavm.server.core, 21.0.0.0.0…
Patching component oracle.ldap.owm, 21.0.0.0.0…
Patching component oracle.ldap.rsf, 21.0.0.0.0…
Patching component oracle.ldap.rsf.ic, 21.0.0.0.0…
Patching component oracle.ldap.security.osdt, 21.0.0.0.0…
Patching component oracle.nlsrtl.rsf, 21.0.0.0.0…
Patching component oracle.oraml.server, 21.0.0.0.0…
Patching component oracle.rdbms.crs, 21.0.0.0.0…
Patching component oracle.rdbms.dbscripts, 21.0.0.0.0…
Patching component oracle.rdbms.deconfig, 21.0.0.0.0…
Patching component oracle.rdbms.install.plugins, 21.0.0.0.0…
Patching component oracle.rdbms.rman, 21.0.0.0.0…
Patching component oracle.rdbms.rsf.ic, 21.0.0.0.0…
Patching component oracle.sdo, 21.0.0.0.0…
Patching component oracle.sdo.locator.jrf, 21.0.0.0.0…
Patching component oracle.sqlplus, 21.0.0.0.0…
Patching component oracle.tfa.db, 21.0.0.0.0…
Patching component oracle.rdbms.lbac, 21.0.0.0.0…
Patching component oracle.ctx, 21.0.0.0.0…
Patching component oracle.sqlplus.ic, 21.0.0.0.0…
Patching component oracle.xdk, 21.0.0.0.0…
Patching component oracle.assistants.deconfig, 21.0.0.0.0…
Patching component oracle.ctx.rsf, 21.0.0.0.0…
Patching component oracle.oracore.rsf, 21.0.0.0.0…
Patching component oracle.ldap.ssl, 21.0.0.0.0…
Patching component oracle.rdbms.hsodbc, 21.0.0.0.0…
Patching component oracle.assistants.acf, 21.0.0.0.0…
Patching component oracle.xdk.parser.java, 21.0.0.0.0…
Patching component oracle.rdbms.hs_common, 21.0.0.0.0…
Patching component oracle.odbc, 21.0.0.0.0…
Patching component oracle.network.listener, 21.0.0.0.0…
Patching component oracle.ctx.atg, 21.0.0.0.0…
Patching component oracle.ons, 21.0.0.0.0…
Patching component oracle.ons.ic, 21.0.0.0.0…
Patching component oracle.assistants.server, 21.0.0.0.0…
Patching component oracle.rdbms.oci, 21.0.0.0.0…
Patching component oracle.precomp.rsf, 21.0.0.0.0…
Patching component oracle.rdbms.dv, 21.0.0.0.0…
Patching component oracle.ovm, 21.0.0.0.0…
Patching component oracle.rdbms.scheduler, 21.0.0.0.0…
Patching component oracle.sdo.locator, 21.0.0.0.0…
Patching component oracle.xdk.rsf, 21.0.0.0.0…
Patching component oracle.precomp.common, 21.0.0.0.0…
Patching component oracle.precomp.lang, 21.0.0.0.0…
Patching component oracle.jdk, 1.8.0.291.09…
Patch 34160444 successfully applied.
Log file location: /u01/app/oracle/product/21.0.0.0/cfgtoollogs/opatch/opatch2022-09-21_20-01-00PM_1.log
OPatch succeeded.
Step:-8 Startup the Database and Listener
[oracle@oracle21c ~]$ sql
SQL*Plus: Release 21.0.0.0.0 – Production on Wed Sep 21 20:13:21 2022
Version 21.7.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 2415916864 bytes
Fixed Size 9688896 bytes
Variable Size 855638016 bytes
Database Buffers 1258291200 bytes
Redo Buffers 292298752 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 – Production
Version 21.7.0.0.0
[oracle@oracle21c ~]$ lsnrctl start
LSNRCTL for Linux: Version 21.0.0.0.0 – Production on 21-SEP-2022 20:14:47
Copyright (c) 1991, 2021, Oracle. All rights reserved.
Starting /u01/app/oracle/product/21.0.0.0/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 21.0.0.0.0 – Production
System parameter file is /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle21c/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 21.0.0.0.0 – Production
Start Date 21-SEP-2022 20:14:47
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/homes/OraDB21Home1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle21c/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary…
Service “oradbwr” has 1 instance(s).
Instance “oradbwr”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
Step:-9 Execute post patch steps and run datapatch command
[oracle@oracle21c ~]$ cd $ORACLE_HOME/OPatch
[oracle@oracle21c OPatch]$ ./datapatch -verbose
SQL Patching tool version 21.7.0.0.0 Production on Wed Sep 21 20:16:38 2022
Copyright (c) 2012, 2022, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/sqlpatch_13020_2022_09_21_20_16_38/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:
21.7.0.0.0 Release_Update 220628175910: Installed
PDB CDB$ROOT:
No release update patches installed
PDB ORADBWRPDB1:
No release update patches installed
PDB PDB$SEED:
No release update patches installed
Adding patches to installation queue and performing prereq checks…done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED ORADBWRPDB1
No interim patches need to be rolled back
Patch 34160444 (Database Release Update : 21.7.0.0.220719 (34160444)):
Apply from 21.1.0.0.0 Feature Release to 21.7.0.0.0 Release_Update 220628175910
No interim patches need to be applied
Installing patches…
Patch installation complete. Total patches installed: 3
Validating logfiles…done
Patch 34160444 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/34160444/24850944/34160444_apply_ORADBWR_CDBROOT_2022Sep21_20_17_03.log (no errors)
Patch 34160444 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/34160444/24850944/34160444_apply_ORADBWR_PDBSEED_2022Sep21_20_24_15.log (no errors)
Patch 34160444 apply (pdb ORADBWRPDB1): SUCCESS
logfile: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/34160444/24850944/34160444_apply_ORADBWR_ORADBWRPDB1_2022Sep21_20_24_15.log (no errors)
SQL Patching tool complete on Wed Sep 21 20:29:49 2022
Step:-10 After applying RU patch,Check the DBA_REGISTRY_SQLPATCH
Recompile invalid Objects:-
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_BGN 2022-09-21 20:31:15
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-09-21 20:31:19
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.
Check patches applied in oracle home:-
[oracle@oracle21c OPatch]$ opatch lspatches
34160444;Database Release Update : 21.7.0.0.220719 (34160444)
OPatch succeeded.
Check datapatch verbose return in DB level:-
SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN comments FORMAT A30
COLUMN description FORMAT A60
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
SELECT TO_CHAR(action_time, ‘YYYY-MM-DD’) AS action_time,action,status,
description,patch_id FROM sys.dba_registry_sqlpatch ORDER by action_time;
Component check:-
col comp_id for a10
col version for a11
col status for a10
col comp_name for a37
select comp_id,comp_name,version,status from dba_registry;
Connect with me on:-
Telegram App:https://t.me/oracledbwr
LinkedIn:https://www.linkedin.com/in/hariprasathdba
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page : https://www.facebook.com/dbahariprasath/?
Twitter : https://twitter.com/hariprasathdba
