Apply Patching On Oracle 19c Database Release Update 19.7.1.0.200714: In this article We are going Apply Patching On Oracle 19c Database Release Update 19.6.2.0.200714 (Patch 31204483).
For this activity you can follow Oracle Database 12c Release Update Known Issues (Doc ID 2663985.1)
Description:-
In this article we are going to see steps used to apply the latest Oracle 19c Database Release Update Revision 19.7.1.0.200714 (Patch 31204483). The environment is single instance non-cdb database.
Note That:-
1)Review readme file on Patch 31204483 – Database Release Update Revision 19.7.1.0.200714
2)Download patch p31204483_190000_Linux-x86-64.zip , p6880880_190000_Linux-x86-64.zip
3)Make sure the opatch version is minimum 12.2.0.1.19.
Step:-1 Download the Patch from oracle support
p31204483_190000_Linux-x86-64.zip
p6880880_190000_Linux-x86-64.zipStep:-2 Copied the patch to DB Server
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ ls -ltrh
total 10G
-rwxrwxr-x. 1 oracle oinstall 113M Dec 16 22:39 p6880880_190000_Linux-x86-64.zip
drwxrwxr-x. 3 oracle oinstall 4.0K Dec 21 12:23 backup
drwxrwxr-x. 4 oracle oinstall 40 Dec 21 19:54 app
-rwxrwxr-x. 1 oracle oinstall 1.3G Dec 21 22:30 p31204483_190000_Linux-x86-64.zip
[oracle@vm-3 u01]$Step:-3 Upgrade Opatch Tool from 12.2.0.1.17 to 12.2.0.1.21
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vm-3 u01]$ export PATH=/u01/app/oracle/product/19.0.0/dbhome_1/Opatch:$PATH
[oracle@vm-3 u01]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ opatch version
OPatch Version: 12.2.0.1.17
OPatch succeeded.
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ cp p6880880_190000_Linux-x86-64.zip /u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ cd /u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vm-3 dbhome_1]$
[oracle@vm-3 dbhome_1]$ mv OPatch/ OPatch_bkp
[oracle@vm-3 dbhome_1]$
[oracle@vm-3 dbhome_1]$ ls
addnode ctx dmu inventory md oracore perl root.sh slax usm
apex cv drdaas javavm mgw oraInst.loc plsql root.sh.old sqldeveloper utl
assistants data dv jdbc network ord precomp root.sh.old.1 sqlj wwg
bin dbjava env.ora jdk nls ords QOpatch root.sh.old.2 sqlpatch xdk
cfgtoollogs dbs has jlib odbc oss R root.sh.old.3 sqlplus
clone deinstall hs ldap olap oui racg runInstaller srvm
crs demo install lib OPatch_bkp owm rdbms schagent.conf suptools
css diagnostics instantclient log opmn p6880880_190000_Linux-x86-64.zip relnotes sdk ucp
[oracle@vm-3 dbhome_1]$
[oracle@vm-3 dbhome_1]$ unzip p6880880_190000_Linux-x86-64.zip
[oracle@vm-3 dbhome_1]$
[oracle@vm-3 dbhome_1]$ opatch version
OPatch Version: 12.2.0.1.21
OPatch succeeded.
[oracle@vm-3 dbhome_1]$
[oracle@vm-3 dbhome_1]$ cd /u01
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ unzip p31204483_190000_Linux-x86-64.zip
[oracle@vm-3 u01]$Step:-4 Check Patches status before apply using below query
SQL> SET LINESIZE 500
SQL> SET PAGESIZE 1000
SQL> SET SERVEROUT ON
SQL> SET LONG 2000000
SQL> COLUMN action_time FORMAT A12
SQL> COLUMN action FORMAT A10
SQL> COLUMN comments FORMAT A30
SQL> COLUMN description FORMAT A60
SQL> COLUMN namespace FORMAT A20
SQL> COLUMN status FORMAT A10
SQL>
SQL> SELECT TO_CHAR(action_time, ‘YYYY-MM-DD’) AS action_time, action, status, description, patch_id
SQL> FROM sys.dba_registry_sqlpatch
SQL> ORDER by action_time;
ACTION_TIME ACTION STATUS DESCRIPTION PATCH_ID
------------ ---------- ---------- ---------------------------------------------------------------------- ----------
2020-12-21 APPLY SUCCESS Database Release Update : 19.3.0.0.190416 (29517242) 29517242
2020-12-22 APPLY SUCCESS Database Release Update Revision : 19.6.2.0.200714 (31212138) 31212138
SQL>
SQL>
SQL> col comp_id for a10
SQL> col version for a11
SQL> col status for a10
SQL> col comp_name for a37
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------------- ----------- ----------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
SQL>Step:-5 Identifying Invalid Objects before patching
SQL> 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;
no rows selected
SQL>Step:-6 Connect with RMAN and take full backup
Step:-7 Shutdown Database and Listener
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> !lsnrctl stop LISTENER
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-DEC-2020 16:44:36
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vm-3.localdomain)(PORT=1521)))
The command completed successfully
SQL>Step:-8 Take Backup of ORACLE_HOME and Database (Rollback plan)
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ cd $ORACLE_HOME
[oracle@vm-3 dbhome_1]$
[oracle@vm-3 dbhome_1]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vm-3 dbhome_1]$
[oracle@vm-3 dbhome_1]$ tar -cvf oracle_home_22_DEC_2020.tar $ORACLE_HOMEStep:-9 Apply RU patch on ORACLE_HOME 12c
[oracle@vm-3 u01]$ ls -ltrh
total 16G
drwxr-xr-x. 5 oracle oinstall 81 Jul 7 17:23 31204483
-rw-rw-r--. 1 oracle oinstall 327K Jul 7 17:50 PatchSearch.xml
-rwxrwxr-x. 1 oracle oinstall 3.3G Dec 16 22:11 linuxx64_12201_database.zip
-rwxrwxr-x. 1 oracle oinstall 2.9G Dec 16 22:39 LINUX.X64_193000_db_home.zip
-rwxrwxr-x. 1 oracle oinstall 1.3G Dec 16 22:49 p31281355_190000_Linux-x86-64.zip
drwxrwxr-x. 3 oracle oinstall 4.0K Dec 21 12:23 backup
drwxrwxr-x. 4 oracle oinstall 40 Dec 21 19:54 app
-rwxrwxr-x. 1 oracle oinstall 1.3G Dec 21 22:35 p31204483_190000_Linux-x86-64.zip
-rw-r--r--. 1 oracle oinstall 7.0G Dec 22 11:36 oracle_home_22_DEC_2020.tar
[oracle@vm-3 u01]$
[oracle@vm-3 u01]$ cd 31204483
[oracle@vm-3 31204483]$ pwd
/u01/31204483
[oracle@vm-3 31204483]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.21
Copyright (c) 2020, 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.21
OUI version : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-12-22_16-46-00PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 31204483
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 '31204483' 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.assistants.asm, 19.0.0.0.0 ] , [ oracle.assistants.usm, 19.0.0.0.0 ] , [ oracle.options.olap.awm, 19.0.0.0.0 ] , [ oracle.xdk.parser.java.jaxb2, 19.0.0.0.0 ] , [ oracle.oraolap.mgmt, 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.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, 19.0.0.0.0...
Patching component oracle.rdbms.rsf, 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.ldap.rsf, 19.0.0.0.0...
Patching component oracle.network.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.ldap.rsf.ic, 19.0.0.0.0...
Patching component oracle.dbdev, 19.0.0.0.0...
Patching component oracle.rdbms.rman, 19.0.0.0.0...
Patching component oracle.rdbms.install.common, 19.0.0.0.0...
Patching component oracle.dbjava.ic, 19.0.0.0.0...
Patching component oracle.odbc, 19.0.0.0.0...
Patching component oracle.marvel, 19.0.0.0.0...
Patching component oracle.sdo.locator, 19.0.0.0.0...
Patching component oracle.rdbms.lbac, 19.0.0.0.0...
Patching component oracle.rdbms.drdaas, 19.0.0.0.0...
Patching component oracle.bali.ice, 11.1.1.7.0...
Patching component oracle.ldap.security.osdt, 19.0.0.0.0...
Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...
Patching component oracle.install.deinstalltool, 19.0.0.0.0...
Patching component oracle.dbjava.jdbc, 19.0.0.0.0...
Patching component oracle.duma, 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.ovm, 19.0.0.0.0...
Patching component oracle.ldap.owm, 19.0.0.0.0...
Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...
Patching component oracle.ctx.rsf, 19.0.0.0.0...
Patching component oracle.xdk, 19.0.0.0.0...
Patching component oracle.ons, 19.0.0.0.0...
Patching component oracle.sqlplus.ic, 19.0.0.0.0...
Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...
Patching component oracle.rdbms.crs, 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.xdk.parser.java, 19.0.0.0.0...
Patching component oracle.rdbms.oci, 19.0.0.0.0...
Patching component oracle.oraolap, 19.0.0.0.0...
Patching component oracle.xdk.rsf, 19.0.0.0.0...
Patching component oracle.rdbms.dv, 19.0.0.0.0...
Patching component oracle.dbjava.ucp, 19.0.0.0.0...
Patching component oracle.network.listener, 19.0.0.0.0...
Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...
Patching component oracle.ordim.rdbms, 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 31204483 successfully applied.
Sub-set patch [31212138] has become inactive due to the application of a super-set patch [31204483].
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/opatch2020-12-22_16-46-00PM_1.log
OPatch succeeded.
[oracle@vm-3 31204483]$Step:-10 Startup the Database and Listener
SQL> startup
ORACLE instance started.
Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 880803840 bytes
Database Buffers 180355072 bytes
Redo Buffers 3674112 bytes
Database mounted.
Database opened.
SQL>
SQL> !lsnrctl start LISTENER
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-DEC-2020 17:34:35
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/vm-3/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm-3.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=vm-3.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 22-DEC-2020 17:34:35
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/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/vm-3/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vm-3.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
SQL>Step:-11 Execute post patch steps and run datapatch command
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ cd $ORACLE_HOME/OPatch
[oracle@vm-3 OPatch]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/OPatch
[oracle@vm-3 OPatch]$
[oracle@vm-3 OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.7.1.0.0 Production on Tue Dec 22 17:36:06 2020
Copyright (c) 2012, 2020, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_2662_2020_12_22_17_36_06/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
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.7.1.0.0 Release_Update_Revision 200703213817: Installed
SQL registry:
Applied 19.6.2.0.0 Release_Update_Revision 200614162643 successfully on 22-DEC-20 01.15.10.381153 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
No interim patches need to be rolled back
Patch 31212138 (Database Release Update Revision : 19.6.2.0.200714 (31212138)):
Rollback from 19.6.2.0.0 Release_Update_Revision 200614162643 to 19.6.0.0.0 Release_Update 191217155004
Patch 31204483 (Database Release Update Revision : 19.7.1.0.200714 (31204483)):
Apply from 19.6.0.0.0 Release_Update 191217155004 to 19.7.1.0.0 Release_Update_Revision 200703213817
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 2
Validating logfiles...done
Patch 31212138 rollback: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31212138/23677476/31212138_rollback_ORCL_2020Dec22_17_38_29.log (no errors)
Patch 31204483 apply: SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31204483/23680268/31204483_apply_ORCL_2020Dec22_17_38_45.log (no errors)
SQL Patching tool complete on Tue Dec 22 17:51:58 2020
[oracle@vm-3 OPatch]$Step:-12 After applying RU patch, Check the DBA_REGISTRY_SQLPATCH
[oracle@node-2 ~]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Dec 20 18:36:18 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set lines 400 pages 400
SQL> col comp_id for a10
SQL> col version for a11
SQL> col status for a10
SQL> col comp_name for a37
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------------- ----------- ----------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
SQL>
SQL> SET LINESIZE 500
SQL> SET PAGESIZE 1000
SQL> SET SERVEROUT ON
SQL> set long 999999
SQL> COLUMN action_time FORMAT A12
SQL> COLUMN action FORMAT A10
SQL> COLUMN comments FORMAT A30
SQL> COLUMN description FORMAT A70
SQL> COLUMN namespace FORMAT A20
SQL> COLUMN status FORMAT A10
SQL> 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;
ACTION_TIME ACTION STATUS DESCRIPTION PATCH_ID
------------ ---------- ---------- ---------------------------------------------------------------------- ----------
2020-12-21 APPLY SUCCESS Database Release Update : 19.3.0.0.190416 (29517242) 29517242
2020-12-22 ROLLBACK SUCCESS Database Release Update Revision : 19.6.2.0.200714 (31212138) 31212138
2020-12-22 APPLY SUCCESS Database Release Update Revision : 19.7.1.0.200714 (31204483) 31204483
2020-12-22 APPLY SUCCESS Database Release Update Revision : 19.6.2.0.200714 (31212138) 31212138
SQL>Step:-13 Check opatch lsinventory and list of patches applied in ORACLE_HOME
[oracle@vm-3 ~]$
[oracle@vm-3 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@vm-3 ~]$ export PATH=/u01/app/oracle/product/19.0.0/dbhome_1/Opatch:$PATH
[oracle@vm-3 ~]$ export PATH=$ORACLE_HOME/OPatch:$PATH
[oracle@vm-3 ~]$ opatch lspatches
31204483;Database Release Update Revision : 19.7.1.0.200714 (31204483)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
OPatch succeeded.
[oracle@vm-3 ~]$Step:-14 Check invalid objects after the execution of datapatch :
SQL>
SQL> COLUMN object_name FORMAT A30
SQL> SELECT owner,
SQL> object_type,
SQL> object_name,
SQL> status
SQL> FROM dba_objects
SQL> WHERE status = 'INVALID'
SQL> ORDER BY owner, object_type, object_name;
no rows selected
SQL>Step:-15 Any databases if that have invalid objects after the execution of datapatch should have utlrp.sql run to revalidate those objects.
SQL>
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-12-22 18:33:57
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 2020-12-22 18:34: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.
SQL>
SQL>
SQL> COLUMN object_name FORMAT A30
SQL> SELECT owner,
SQL> object_type,
SQL> object_name,
SQL> status
SQL> FROM dba_objects
SQL> WHERE status = 'INVALID'
SQL> ORDER BY owner, object_type, object_name;
no rows selected
SQL>I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.