Validate the CATALOG and CATPROC invalid components in the Registry of the Oracle
Problem: The following component in Oracle is showing invalid.
COMP_ID VERSION STATUS ------------------------------------- ---------- ------- Oracle Database Catalog Views CATALOG 220.127.116.11.0 INVALID Oracle Database Packages and T CATPROC 18.104.22.168.0 INVALID
It occurred when we are migrating or upgrading or patching the existing databases.
To make them valid again. you can try following things step by step and check whether it is valid or not.
1. Execute the UTLRP.SQL script for validating all the invalid components and objects in Oracle:
2. Check the list of objects invalid in the Oracle schemas.
select owner,object_name,object_type,status from dba_objects where status = 'INVALID' group by owner,object_name,object_type, status;
3. Compile the objects manually if utlrp.sql is not working for them:
--For package specification: alter package owner.object_name compile; --For package body: alter package owner.object_name compile body; --For Procedure: alter procedure owner.object_name compile; --For Function: alter function owner.object_name compile;
4. Execute the following command to make the CATALOG or CATPROC components valid.
-- For validate the CATPROC: execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC; -- For validate the CATALOG: execute DBMS_REGISTRY_SYS.VALIDATE_CATALOG;
5. Verify the registry component. If no row return means all registry components are valid.
select comp_name, version,status from dba_registry where STATUS <> 'VALID';
Note: You can also use the following script to find invalid objects in step 2
-- Execute this one by one and make objects valid until it return "CATPROC can be validated". $ sqlplus / as sysdba set serveroutput on; declare start_time date; end_time date; object_name varchar(100); object_id char(10); begin select date_loading, date_loaded into start_time, end_time from registry$ where cid = 'CATPROC'; select obj#,name into object_id,object_name from obj$ where status > 1 and (ctime between start_time and end_time or mtime between start_time and end_time or stime between start_time and end_time) and rownum <=1; dbms_output.put_line('Please compile the invalid object '||object_name||' object_id '||object_id ); exception when no_data_found then dbms_output.put_line('CATPROC can be validated now'); end; /
- If the registry component still invalid state, then we can use the following script to recreate the objects:
- Before running the following script you have the database backup or take a fresh backup or cold backup:
I run these 2 scripts to valid them: @$ORACLE_HOME/rdbms/admin/catalog.sql @$ORACLE_HOME/rdbms/admin/catproc.sql