Description
In this article, we are going to see steps used to apply the latest Oracle 19c Database Release Update 19.9.0.0.201020 (Patch 31771877) The Environment is a single instance database.
Review readme file on Patch 31771877 – Database Release Update 19.9.0.0.201020 Download patch p31 771877_190000_Linux-x86-64.zip Make sure the opatch version is minimum 12.2.0.1.19
Step:-1 Download the Patch from oracle support
p31771877_190000_Linux-x86-64.zip
p6880880_200000_Linux-x86-64.zip
Step:-2 Copied the patch to DB Server
[oratest@oracle ~]$ mkdir patch [oratest@oracle ~]$ ls [oratest@oracle ~]$ cd patch/ [oratest@oracle ~]$ ls -lrth
-rwxrwxr-x. 1 oratest oratest 116M Oct 21 10:55-x86-64.zip
-rwxrwxr-x. 1 oratest oratest 1.6G Oct 21 11:23 p6880880_210000_Linux-x86-64.zip
Step:-3 Upgrade Opatch Tool from 12.2.0.1.17 to 12.2.0.1.
[oratest@oracle ~]$ cd $ORACLE_HOME [oratest@oracle ~]$ ls [oratest@oracle ~]$ ./opatch version OPatch Version: 12.2.0.1.17 OPatch succeeded [oratest@oracle ~]$ cd /home/oratest/patch [oratest@oracle ~]$ ls [oratest@oracle ~]$cp p6880880_200000_Linux-x86-64.zip /u01/app/oracle/product/19.0.0/dbhome_1 [oratest@oracle ~]$ cd /app/oracle/product/19.0.0/dbhome_1 [oratest@oracle ~]$pwd /u01/app/oracle/product/19.0.0/dbhome_1
[oratest@oracle ~]$ mv OPatch/ OPatch_BKP
[oratest@oracle ~]$ unzip p6880880_200000_Linux-x86-64.zip
Archive: p6880880_200000_Linux-x86-64.zip
creating: OPatch/
inflating: OPatch/emdpatch.pl
creating: OPatch/oracle_common/
creating: OPatch/oracle_common/modules/
inflating: OPatch/oracle_common/modules/com.oracle.glcm.common-logging_1.6.5.0.jar
inflating: OPatch/oracle_common/modules/common-logging-config.jar
creating: OPatch/oplan/
[oratest@oracle]$ cd OPatch
[oratest@oracle OPatch]$ ./opatch version
OPatch Version: 12.2.0.1.27
OPatch succeeded.Step:-4 Check Patches status before applying using the below query
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;
Identifying Invalid Objects before patching
SQL> select owner,object_type,object_name,status from dba_objects
where status='INVALID'; no row selected.
Step:-5 Apply RU patch on ORACLE_HOME 19c
[oracle@oraem01 soft]$ unzip p31771877_190000_Linux-x86-64.zip [oracle@oraem01 soft]$ 31771877 [oratest@oracle 31771877]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 [oratest@oracle 31771877]$
export PATH=/u01/app/oracle/product/19.0.0/dbhome_1/OPatch:$PATH
Step – 6:- Apply opatch
[oratest@oracle 31771877]$ opatch apply Oracle Interim Patch Installer version 12.2.0.1.27 Copyright (c) 2021, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19.0.0/dbhome_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc OPatch version : 12.2.0.1.27 OUI version : 12.2.0.7.0 Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/
opatch/opatch2021-10-21_12-48-42PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 31771877 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.0.0/dbhome_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying interim patch '31771877' to OH '/u01/app/oracle/product/19.0.0/dbhome_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.awm, 19.0.0.0.0 ] ,
[ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] ,
[ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] ,
[ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.assistants.usm, 19.0.0.0.0 ] ,
[ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.sqlj, 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.rsf, 19.0.0.0.0... Patching component oracle.rdbms, 19.0.0.0.0... Patching component oracle.rdbms.util, 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.buildtools.rsf, 19.0.0.0.0... Patching component oracle.ctx, 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.dbtoolslistener, 19.0.0.0.0... Patching component oracle.ldap.rsf, 19.0.0.0.0... Patching component oracle.network.rsf, 19.0.0.0.0... Patching component oracle.oracore.rsf, 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.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.usm.deconfig, 19.0.0.0.0... Patching component oracle.rdbms.crs, 19.0.0.0.0... Patching component oracle.oraolap, 19.0.0.0.0... Patching component oracle.precomp.rsf, 19.0.0.0.0... Patching component oracle.javavm.client, 19.0.0.0.0... Patching component oracle.precomp.common.core, 19.0.0.0.0... Patching component oracle.network.client, 19.0.0.0.0... Patching component oracle.rdbms.scheduler, 19.0.0.0.0... Patching component oracle.ctx.atg, 19.0.0.0.0... Patching component oracle.marvel, 19.0.0.0.0... Patching component oracle.oraolap.dbscripts, 19.0.0.0.0... Patching component oracle.ovm, 19.0.0.0.0... Patching component oracle.bali.ice, 11.1.1.7.0... Patching component oracle.ctx.rsf, 19.0.0.0.0... Patching component oracle.rdbms.lbac, 19.0.0.0.0... Patching component oracle.rdbms.oci, 19.0.0.0.0... Patching component oracle.odbc, 19.0.0.0.0... Patching component oracle.rdbms.rman, 19.0.0.0.0... Patching component oracle.nlsrtl.rsf, 19.0.0.0.0... Patching component oracle.rdbms.install.plugins, 19.0.0.0.0... Patching component oracle.rdbms.drdaas, 19.0.0.0.0... Patching component oracle.ldap.rsf.ic, 19.0.0.0.0... Patching component oracle.ldap.security.osdt, 19.0.0.0 Patching component oracle.dbdev, 19.0.0.0.0... Patching component oracle.rdbms.install.common, 19.0.0.0.0... Patching component oracle.sdo.locator, 19.0.0.0.0... Patching component oracle.duma, 19.0.0.0.0... Patching component oracle.sqlplus.ic, 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.rdbms.rsf.ic, 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 31771877 successfully applied. Sub-set patch [29517242] has become inactive due to the application of a super-set
patch [31771877]. Please refer to Doc ID 2161861.1 for any possible further required actions. Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/
opatch2021-10-21_12-48-42PM_1.log OPatch succeeded.
Step – 7 – Run the Below Commands
SQL> select owner from all_objects where status='INVALID'; no rows selected
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-10-21 13:20:37
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 2021-10-21 13:20:41
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.
SQL> select owner from all_objects where status='INVALID';
no rows selected
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
