Apply Patching On Oracle 12c Database Release Update 12.2.0.1.190416

Apply Patching On Oracle 12c Database Release Update 12.2.0.1.190416 : In this article We are going Apply Patching On Oracle 12c Database Release Update 12.2.0.1.190416 (Patch 29314339).

For this activity you can follow Oracle Database 12c Release Update Known Issues (Doc ID 2285557.1)

Description:-

This article we are going to see steps used to apply the latest Oracle 12c Database Release Update 12.2.0.1.190416 (Patch 29314339)

The environment is single instance non-cdb database.

Note:-

1)Review readme file on Patch 29314339 – Database Release Update 12.2.0.1.190416
2)Download patch p29314339_122010_Linux-x86-64.zip , p6880880_121010_Linux-x86-64.zip
3)Make sure the opatch version is minimum 12.2.0.1.7.

Step:-1 Download the Patch from oracle support

p29314339_122010_Linux-x86-64.zip


p6880880_121010_Linux-x86-64.zip

Step:-2 Copied the patch to DB Server

[oracle@node-2 u01]$ ls -ltrh
total 15G
drwxrwxr-x. 7 oracle oinstall 4.0K Jan 26  2017 database
-rwxrwxr-x. 1 oracle oinstall 3.3G Dec 16 23:23 linuxx64_12201_database.zip
drwxrwxr-x. 4 oracle oinstall   40 Dec 18 16:54 app
-rw-r--r--. 1 oracle oinstall 7.2G Dec 18 19:53 oracle_home_18_DEC_2020.tar
-rwxr-xr-x. 1 oracle oinstall 117M Dec 19 00:58 p6880880_121010_Linux-x86-64.zip
-rwxrwxr-x. 1 oracle oinstall 382M Dec 19 03:54 p29314339_122010_Linux-x86-64.zip
[oracle@node-2 u01]$

Step:-3 Upgrade Opatch Tool from 12.2.0.1.6 to 12.2.0.1.23

[oracle@node-2 u01]$
[oracle@node-2 u01]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.2.0.1/db_1
[oracle@node-2 u01]$
[oracle@node-2 u01]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
[oracle@node-2 u01]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/OPatch:$PATH
[oracle@node-2 u01]$
[oracle@node-2 u01]$ opatch version
OPatch Version: 12.2.0.1.6

OPatch succeeded.
[oracle@node-2 u01]$
[oracle@node-2 u01]$ 
[oracle@node-2 u01]$ cp p6880880_121010_Linux-x86-64.zip /u01/app/oracle/product/12.2.0.1/db_1
[oracle@node-2 u01]$ cd $ORACLE_HOME
[oracle@node-2 db_1]$ 
[oracle@node-2 db_1]$ mv OPatch/ OPatch_bkp
[oracle@node-2 db_1]$ 
[oracle@node-2 db_1]$ unzip p6880880_121010_Linux-x86-64.zip
[oracle@node-2 db_1]$ 
[oracle@node-2 db_1]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
[oracle@node-2 db_1]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/Opatch:$PATH
[oracle@node-2 db_1]$ opatch version
OPatch Version: 12.2.0.1.23

OPatch succeeded.
[oracle@node-2 dbhome_1]$ 
[oracle@node-2 dbhome_1]$ cd /u01
[oracle@node-2 u01]$ 
[oracle@node-2 u01]$ unzip p29314339_122010_Linux-x86-64.zip
[oracle@node-2 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-18   APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.170718                        26123830
2020-12-18   APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.170814                        26609817
2020-12-19   APPLY      SUCCESS    DATABASE APR 2018 RELEASE UPDATE 12.2.0.1.180417               27674384
2020-12-19   APPLY      SUCCESS    DATABASE JUL 2018 RELEASE UPDATE 12.2.0.1.180717               28163133
2020-12-19   APPLY      SUCCESS    DATABASE OCT 2018 RELEASE UPDATE 12.2.0.1.181016               28662603
2020-12-19   APPLY      SUCCESS    DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115               28822515
2020-12-19   APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.171017                        26710464
2020-12-19   APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.180116                        27105253

8 rows selected.

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         12.2.0.1.0  VALID
CATPROC    Oracle Database Packages and Types    12.2.0.1.0  VALID
JAVAVM     JServer JAVA Virtual Machine          12.2.0.1.0  VALID
XML        Oracle XDK                            12.2.0.1.0  VALID
CATJAVA    Oracle Database Java Packages         12.2.0.1.0  VALID
APS        OLAP Analytic Workspace               12.2.0.1.0  VALID
RAC        Oracle Real Application Clusters      12.2.0.1.0  OPTION OFF
XDB        Oracle XML Database                   12.2.0.1.0  VALID
OWM        Oracle Workspace Manager              12.2.0.1.0  VALID
CONTEXT    Oracle Text                           12.2.0.1.0  VALID
ORDIM      Oracle Multimedia                     12.2.0.1.0  VALID
SDO        Spatial                               12.2.0.1.0  VALID
XOQ        Oracle OLAP API                       12.2.0.1.0  VALID
OLS        Oracle Label Security                 12.2.0.1.0  VALID
DV         Oracle Database Vault                 12.2.0.1.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> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@node-2 u01]$
[oracle@node-2 u01]$ lsnrctl stop LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-DEC-2020 03:57:31

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node-2.localdomain)(PORT=1521)))
The command completed successfully
[oracle@node-2 u01]$

Step:-8 Take Backup of ORACLE_HOME and Database (Rollback plan)

[oracle@node-2 u01]$ cd $ORACLE_HOME
[oracle@node-2 dbhome_1]$ tar -cvf oracle_home_18_DEC_2020.tar $ORACLE_HOME

Step:-9 Apply RU patch on ORACLE_HOME 12c

[oracle@node-2 u01]$ ls -ltrh
total 15G
drwxrwxr-x. 7 oracle oinstall 4.0K Jan 26  2017 database
drwxr-xr-x. 4 oracle oinstall   67 Mar 24  2019 29314339
-rw-rw-r--. 1 oracle oinstall 194K Apr 16  2019 PatchSearch.xml
-rwxrwxr-x. 1 oracle oinstall 2.9G Dec 16 23:12 LINUX.X64_193000_db_home.zip
-rwxrwxr-x. 1 oracle oinstall 1.3G Dec 16 23:19 p31281355_190000_Linux-x86-64.zip
-rwxrwxr-x. 1 oracle oinstall 3.3G Dec 16 23:23 linuxx64_12201_database.zip
drwxrwxr-x. 4 oracle oinstall   40 Dec 18 16:54 app
-rw-r--r--. 1 oracle oinstall 7.2G Dec 18 19:53 oracle_home_18_DEC_2020.tar
-rwxr-xr-x. 1 oracle oinstall 117M Dec 19 00:58 p6880880_121010_Linux-x86-64.zip
-rwxrwxr-x. 1 oracle oinstall 382M Dec 19 03:54 p29314339_122010_Linux-x86-64.zip
[oracle@node-2 u01]$
[oracle@node-2 u01]$ cd 29314339
[oracle@node-2 29314339]$
[oracle@node-2 29314339]$ pwd
/u01/29314339
[oracle@node-2 29314339]$
[oracle@node-2 29314339]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.23
Copyright (c) 2020, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0.1/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0.1/db_1/oraInst.loc
OPatch version    : 12.2.0.1.23
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2020-12-19_03-58-11AM_1.log

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

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/12.2.0.1/db_1')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '29314339' to OH '/u01/app/oracle/product/12.2.0.1/db_1'
ApplySession: Optional component(s) [ oracle.has.crs, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.oid.client, 12.2.0.1.0 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms.util, 12.2.0.1.0...

Patching component oracle.precomp.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.rsf.ic, 12.2.0.1.0...

Patching component oracle.xdk.parser.java, 12.2.0.1.0...

Patching component oracle.xdk.rsf, 12.2.0.1.0...

Patching component oracle.xdk, 12.2.0.1.0...

Patching component oracle.precomp.lang, 12.2.0.1.0...

Patching component oracle.oracore.rsf, 12.2.0.1.0...

Patching component oracle.precomp.common, 12.2.0.1.0...

Patching component oracle.rdbms.install.plugins, 12.2.0.1.0...

Patching component oracle.ldap.rsf.ic, 12.2.0.1.0...

Patching component oracle.ctx, 12.2.0.1.0...

Patching component oracle.rdbms.dv, 12.2.0.1.0...

Patching component oracle.rdbms.lbac, 12.2.0.1.0...

Patching component oracle.ctx.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.rsf, 12.2.0.1.0...

Patching component oracle.ons, 12.2.0.1.0...

Patching component oracle.nlsrtl.rsf, 12.2.0.1.0...

Patching component oracle.ldap.client, 12.2.0.1.0...

Patching component oracle.tfa, 12.2.0.1.0...

Patching component oracle.network.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.oci, 12.2.0.1.0...

Patching component oracle.rdbms.deconfig, 12.2.0.1.0...

Patching component oracle.has.deconfig, 12.2.0.1.0...

Patching component oracle.assistants.deconfig, 12.2.0.1.0...

Patching component oracle.assistants.acf, 12.2.0.1.0...

Patching component oracle.rdbms.rman, 12.2.0.1.0...

Patching component oracle.rdbms.crs, 12.2.0.1.0...

Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...

Patching component oracle.assistants.server, 12.2.0.1.0...

Patching component oracle.ldap.rsf, 12.2.0.1.0...

Patching component oracle.rdbms, 12.2.0.1.0...

Patching component oracle.sdo, 12.2.0.1.0...
Patch 29314339 successfully applied.
Sub-set patch [28822515] has become inactive due to the application of a super-set patch [29314339].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/12.2.0.1/db_1/cfgtoollogs/opatch/opatch2020-12-19_03-58-11AM_1.log

OPatch succeeded.
[oracle@node-2 29314339]$

Step:-10 Startup the Database and Listener

[oracle@node-2 ~]$
[oracle@node-2 ~]$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-DEC-2020 04:03:29

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/node-2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node-2.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node-2.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                19-DEC-2020 04:03:29
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/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/node-2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node-2.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@node-2 ~]$
[oracle@node-2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 19 04:03:39 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             729810232 bytes
Database Buffers          331350016 bytes
Redo Buffers                3952640 bytes
Database mounted.
Database opened.
SQL>

Step:-11 Execute post patch steps and run datapatch command

[oracle@node-2 ~]$
[oracle@node-2 ~]$ cd $ORACLE_HOME/OPatch
[oracle@node-2 OPatch]$
[oracle@node-2 OPatch]$ pwd
/u01/app/oracle/product/12.2.0.1/db_1/OPatch
[oracle@node-2 OPatch]$
[oracle@node-2 OPatch]$ ./datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Sat Dec 19 04:05:04 2020
Copyright (c) 2012, 2019, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_22018_2020_12_19_04_05_05/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  ID 190416 in the binary registry and ID 190115 in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    29314339 (DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416)

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 29314339 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29314339/22821655/29314339_apply_PROD_2020Dec19_04_05_47.log (no errors)
SQL Patching tool complete on Sat Dec 19 04:07:22 2020
[oracle@node-2 OPatch]$

Step:-12 After applying RU patch, Check the DBA_REGISTRY_SQLPATCH

[oracle@node-2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Dec 19 04:07:58 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         12.2.0.1.0  VALID
CATPROC    Oracle Database Packages and Types    12.2.0.1.0  VALID
JAVAVM     JServer JAVA Virtual Machine          12.2.0.1.0  VALID
XML        Oracle XDK                            12.2.0.1.0  VALID
CATJAVA    Oracle Database Java Packages         12.2.0.1.0  VALID
APS        OLAP Analytic Workspace               12.2.0.1.0  VALID
RAC        Oracle Real Application Clusters      12.2.0.1.0  OPTION OFF
XDB        Oracle XML Database                   12.2.0.1.0  VALID
OWM        Oracle Workspace Manager              12.2.0.1.0  VALID
CONTEXT    Oracle Text                           12.2.0.1.0  VALID
ORDIM      Oracle Multimedia                     12.2.0.1.0  VALID
SDO        Spatial                               12.2.0.1.0  VALID
XOQ        Oracle OLAP API                       12.2.0.1.0  VALID
OLS        Oracle Label Security                 12.2.0.1.0  VALID
DV         Oracle Database Vault                 12.2.0.1.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 A60
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-18   APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.170814                        26609817
2020-12-18   APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.170718                        26123830
2020-12-19   APPLY      SUCCESS    DATABASE APR 2019 RELEASE UPDATE 12.2.0.1.190416               29314339
2020-12-19   APPLY      SUCCESS    DATABASE JUL 2018 RELEASE UPDATE 12.2.0.1.180717               28163133
2020-12-19   APPLY      SUCCESS    DATABASE OCT 2018 RELEASE UPDATE 12.2.0.1.181016               28662603
2020-12-19   APPLY      SUCCESS    DATABASE JAN 2019 RELEASE UPDATE 12.2.0.1.190115               28822515
2020-12-19   APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.180116                        27105253
2020-12-19   APPLY      SUCCESS    DATABASE RELEASE UPDATE 12.2.0.1.171017                        26710464
2020-12-19   APPLY      SUCCESS    DATABASE APR 2018 RELEASE UPDATE 12.2.0.1.180417               27674384

9 rows selected.

SQL>

Step:-13 Check opatch lsinventory and list of patches applied in ORACLE_HOME

[oracle@node-2 ~]$
[oracle@node-2 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
[oracle@node-2 ~]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/Opatch:$PATH
[oracle@node-2 ~]$ opatch lspatches
29314339;Database Apr 2019 Release Update : 12.2.0.1.190416 (29314339)

OPatch succeeded.
[oracle@node-2 ~]$

Step:-14 Check invalid objects after the execution of datapatch :

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;

OWNER                          OBJECT_TYPE             OBJECT_NAME                    STATUS
------------------------------ ----------------------- ------------------------------ -------
PUBLIC                         SYNONYM                 ALL_GOLDENGATE_RULES           INVALID
PUBLIC                         SYNONYM                 ALL_PROPAGATION                INVALID
PUBLIC                         SYNONYM                 ALL_STREAMS_GLOBAL_RULES       INVALID
PUBLIC                         SYNONYM                 ALL_STREAMS_MESSAGE_RULES      INVALID
PUBLIC                         SYNONYM                 ALL_STREAMS_RULES              INVALID
PUBLIC                         SYNONYM                 ALL_STREAMS_SCHEMA_RULES       INVALID
PUBLIC                         SYNONYM                 ALL_STREAMS_TABLE_RULES        INVALID
PUBLIC                         SYNONYM                 ALL_SYNC_CAPTURE_TABLES        INVALID
PUBLIC                         SYNONYM                 ALL_XSTREAM_RULES              INVALID
PUBLIC                         SYNONYM                 CDB_PROPAGATION                INVALID
PUBLIC                         SYNONYM                 DBA_PROPAGATION                INVALID
SYS                            PACKAGE BODY            DBMS_CAPTURE_SWITCH_ADM        INVALID
SYS                            PACKAGE BODY            DBMS_CAPTURE_SWITCH_INTERNAL   INVALID
SYS                            PACKAGE BODY            DBMS_LOGREP_EXP                INVALID
SYS                            PACKAGE BODY            DBMS_PROPAGATION_INTERNAL      INVALID
SYS                            PACKAGE BODY            DBMS_STREAMS_ADM_IVK           INVALID
SYS                            PACKAGE BODY            DBMS_STREAMS_ADM_UTL           INVALID
SYS                            PACKAGE BODY            DBMS_STREAMS_AUTO_INT          INVALID
SYS                            PACKAGE BODY            DBMS_STREAMS_SM                INVALID
SYS                            PROCEDURE               DBMS_FEATURE_STREAMS           INVALID
SYS                            PROCEDURE               DBMS_FEATURE_XSTREAM_STREAMS   INVALID
SYS                            VIEW                    ALL_GOLDENGATE_RULES           INVALID
SYS                            VIEW                    ALL_PROPAGATION                INVALID
SYS                            VIEW                    ALL_STREAMS_GLOBAL_RULES       INVALID
SYS                            VIEW                    ALL_STREAMS_MESSAGE_RULES      INVALID
SYS                            VIEW                    ALL_STREAMS_RULES              INVALID
SYS                            VIEW                    ALL_STREAMS_SCHEMA_RULES       INVALID
SYS                            VIEW                    ALL_STREAMS_TABLE_RULES        INVALID
SYS                            VIEW                    ALL_SYNC_CAPTURE_TABLES        INVALID
SYS                            VIEW                    ALL_XSTREAM_RULES              INVALID
SYS                            VIEW                    CDB_PROPAGATION                INVALID
SYS                            VIEW                    DBA_PROPAGATION                INVALID
SYS                            VIEW                    _ALL_STREAMS_PROCESSES         INVALID

33 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

TIMESTAMP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN              2020-12-19 04:10:46

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-19 04:11:24

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 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.

Leave a Comment