CATPROC - Packages and Types Invalid in Dba_registry (Doc ID 457861.1)
In this Document
Goal
Solution
References
APPLIES TO:
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.
GOAL
Dba_registry shows 'Packages and Types' as invalid. How it can be validated.
SQL> select comp_name, version, status from dba_registry;
COMP_NAME VERSION STATUS
---------------------------------------- ---------- ----------
Oracle XML Database 10.2.0.1.0 VALID
Oracle Database Catalog Views 10.2.0.1.0 VALID
Oracle Database Packages and Types 10.2.0.1.0 INVALID
JServer JAVA Virtual Machine 10.2.0.1.0 VALID
Oracle XDK 10.2.0.1.0 VALID
Oracle Database Java Packages 10.2.0.1.0 VALID
SOLUTION
'Packages and Types' can usually be validated by running catalog and catproc scripts (but not adviced, if XDB is installed and version is lower then 10.1.0.2, see next section):
$sqlplus "/as sysdba"
SQL> spool fix_cat_logfile.txt
SQL> startup upgrade
-- Use 'startup migrate' if database version is lower than 10g.
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> spool off
Restart the database after executing the scripts.
sqlplus / as sysdba
shutdown immediate
startup
If selecting dba_registry only shows errors like "ORA-04063: View "SYS.DBA_REGISTRY" has errors" then refer Note 358015.1 and then run above steps
REFERENCES
NOTE:759635.1 - CATPROC Component Is Invalid and Will Not Validate
NOTE:300056.1 - Debug and Validate Invalid Objects
NOTE:358015.1 - ORA-04063 and ORA-00904 on "SYS.DBA_REGISTRY" ORA-00904
BUG:3355444 - CATALOG AND CATPROC CORRUPT XDB REPOSITORY LEADING TO ORA-600 [KQLUDP2]
NOTE:1506140.1 - Primary Note: Troubleshooting Oracle Data Dictionary
NOTE:753041.1 - How to Diagnose Components with NON VALID Status in DBA_REGISTRY after an Upgrade