CATPROC and CATALOG are INVALID in the Registry

We are working on pre-upgrade steps and noticed that “Oracle Database Packages and Types” and “Oracle Database Catalog Views “ components are  showing as INVALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS

---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID

XDB        Oracle XML Database                                       11.2.0.4.0                     VALID

EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID

RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID

OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID

CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     INVALID

CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     INVALID

JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID

XML        Oracle XDK                                                          11.2.0.4.0                     VALID

CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

I see that CATPROC, CATALOG  is INVALID in the registry and followed below steps to validate.

Step1: Validate ‘packages and types’ by running catalog and catproc scripts

$sqlplus "/as sysdba"
SQL> spool cata_logfile.txt
SQL> startup upgrade
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> spool off

Note that you need to use Use 'startup migrate' instead of ‘startup upgrade’ if database version is lower than 10g.

This should resolve the issue. If CATPROC, CATALOG is still invalid in the registry then follow next step

Step2:- Run below script as SYSDBA until it returns 'CATPROC can be validated now'. This script recompiles all dependent objects. 

 Note that you need to replace CATPROC with CATALOG in below script and execute to recompile the objects related to CATALOG.

REM ***************
REM CHECKVALID.SQL
REM ***************
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;
/

Validate CATPROC. CATALOG by executing following command
SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATALOG;

Now I can see that all Database components are objects are VALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS

---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID

XDB        Oracle XML Database                                       11.2.0.4.0                     VALID

EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID

RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID

OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID

CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     VALID

CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     VALID

JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID

XML        Oracle XDK                                                          11.2.0.4.0                     VALID

CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com