Catalog and catproc are invalid in Oracle

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  12.2.0.1.0 INVALID 
Oracle Database Packages and T CATPROC 12.2.0.1.0 INVALID 

Cause
It occurred when we are migrating or upgrading or patching the existing databases.

Solution
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:

 @ORACLE_HOME\rdbms\admin\utlrp.sql;

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;
 /
 

Note:

  1. If the registry component still invalid state, then we can use the following script to recreate the objects:
  2. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.