On : 19.18.0.0.0 version, Patching
After applying a RU Patch , CATALOG / CATPROC Component goes Invalid with lot of invalid objects
SQL> select COMP_NAME,VERSION_FULL,STATUS from dba_registry;
COMP_NAME VERSION_FULL STATUS
------------------ ----------------------- ------------------
Oracle Database Catalog Views 19.14.0.0.0 VALID
Oracle Database Packages and Types 19.18.0.0.0 VALID
Oracle Real Application Clusters 19.18.0.0.0 OPTION OFF
JServer JAVA Virtual Machine 19.18.0.0.0 VALID
Oracle XDK 19.18.0.0.0 VALID
Oracle Database Java Packages 19.18.0.0.0 VALID
Oracle XML Database 19.14.0.0.0 VALID
Oracle Application Express 21.1.3 VALID
Oracle Text 19.18.0.0.0 VALID
Oracle Workspace Manager 19.14.0.0.0 VALID
Running datapatch also fails because invalid objects :
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 34765931 apply: WITH ERRORS
logfile: /opt/oracle/cfgtoollogs/sqlpatch/34765931/25098466/34765931_apply_RCOAPXD_2023Apr13_12_21_16.log (errors)
-> Error at line 10202: script rdbms/admin/dbmsstr.sql
- Warning: Package created with compilation errors.
-> Error at line 10208: script rdbms/admin/dbmsstr.sql
- 3/8 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
-> Error at line 10209: script rdbms/admin/dbmsstr.sql
- 121/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
-> Error at line 10232: script rdbms/admin/dbmsstr.sql
- Warning: Package created with compilation errors.
-> Error at line 10253: script rdbms/admin/dbmsstr.sql
- Warning: Package created with compilation errors.
-> Error at line 10268: script rdbms/admin/dbmsstr.sql
- Warning: Package created with compilation errors.
-> Error at line 10329: script rdbms/admin/dbmsstr.sql
- Warning: Package created with compilation errors.
-> Error at line 10335: script rdbms/admin/dbmsstr.sql
- 2/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
-> Error at line 10336: script rdbms/admin/dbmsstr.sql
- 645/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
-> Error at line 10425: script rdbms/admin/dbmsstr.sql
- Warning: Package created with compilation errors.
-> Error at line 10437: script rdbms/admin/dbmsstr.sql
- Warning: Package created with compilation errors.
-> Error at line 10449: script rdbms/admin/dbmsstr.sql
- Warning: Package created with compilation errors.
-> Error at line 10461: script rdbms/admin/dbmsstr.sql
- Warning: Package created with compilation errors.
-> Error at line 10467: script rdbms/admin/dbmsstr.sql
- 0/0 PL/SQL: Compilation unit analysis terminated
-> Error at line 10468: script rdbms/admin/dbmsstr.sql
- 2/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
-> Error at line 10469: script rdbms/admin/dbmsstr.sql
- 551/35 PLS-00905: object SYS.DBMS_STREAMS_TABLESPACE_ADM is invalid
There are a lot of invalid objects which will not validate by running catalog.sql or catproc.sql or utlrp.sql
SQL> select count (*) , object_type from dba_objects where status='INVALID' and owner='SYS' group by object_type;
COUNT(*) OBJECT_TYPE
---------- -----------------------
4 SYNONYM
165 PACKAGE BODY
5 TYPE BODY
37 PACKAGE
13 PROCEDURE
2 FUNCTION
187 VIEW
Compiling individual objects will fail with:
SQL> alter package DBMS_STREAMS_ADM_IVK compile;
Warning: Package altered with compilation errors.
SQL> show errors
Errors for PACKAGE DBMS_STREAMS_ADM_IVK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
107/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
118/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
129/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
142/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
562/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
618/10 PLS-00710: Pragma SUPPLEMENTAL_LOG_DATA cannot be specified here
687/37 PLS-00905: object SYS.DBMS_STREAMS_TABLESPACE_ADM is invalid
Issue was resolved after performing various things :
1) Validated certain logminer objects by using :
How To Rebuild LogMiner Metadata Tables (Doc ID 550197.1)
2) Performed relink on binaries :
