Catalog and catproc are invalid in Oracle

Catalog and catproc are invalid in Oracle

Problem
Following component in Oracle is showing invaid.

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 the exiting databases.

Solution
To make them valid again. you can try following things step by step and check it is valid or not.

1. Execute the utlrp sql for validate all the invalid components and objects in Oracle

@ORACLE_HOME\rdbms\admin\utlrp.sql;

2. If not valid, then reexecute the catalog and catproc scripts for rebuild.

$ sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql

@$ORACLE_HOME/rdbms/admin/utlrp.sql

Note: you can try utlrp.sql more than once for make them valid.

3. If not valid, then check the each objects and make them valid with following script:

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

4. If not valid, then try to Execute the following command to make it valid.

exec dbms_registry_sys.validate_catproc;

Advertisements

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 )

Google photo

You are commenting using your Google 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.