How to determine what is keeping Catalog and Catproc invalid in the Registry
1 min read
SQL> select comp_id, status, version from dba_registry;
COMP_ID STATUS VERSION
------------ ----------- --------------
CATALOG INVALID 11.2.0.3.0
CATPROC INVALID 11.2.0.3.0
SQL> select object_name, object_type from dba_objects where owner = 'SYS' and status = 'INVALID';
no rows selected
1. Run this SQL script until it returns 'CATPROC can be validated now'. For any objects that are reported by this script, they must be addressed in some manner and made valid to continue. To check CATALOG, simply change the two entries in the script. (i.e. replace CATPROC with CATALOG before running)
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 Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/
2. Then run the utlrp.sql script.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
3. Now check the Registry to ensure a valid status for Catalog and Catproc.
select comp_id, status, version from dba_registry;
