CATPROC - Packages and Types Invalid in Dba_registry

When i looked in to the DBA_REGISTRY, I found that the Oracle Database Packages and Types Column in the Table is in INVALID  state.

I had this problem after my database has been upgraded from Oracle 10gR2 to 11gR2.

SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)

      comp_id,substr(version,1,12) version,status from dba_registry;

  2

COMP_NAME                      COMP_ID    VERSION      STATUS

------------------------------ ---------- ------------ -----------

Oracle XML Database            XDB        11.2.0.2.0   VALID

Oracle Expression Filter       EXF        11.2.0.2.0   VALID

Oracle Rule Manager            RUL        11.2.0.2.0   VALID

Oracle Workspace Manager       OWM        11.2.0.2.0   VALID

Oracle Database Catalog Views  CATALOG    11.2.0.2.0   VALID

Oracle Database Packages and T CATPROC    11.2.0.2.0   INVALID

JServer JAVA Virtual Machine   JAVAVM     11.2.0.2.0   VALID

Oracle XDK                     XML        11.2.0.2.0   VALID

Oracle Database Java Packages  CATJAVA    11.2.0.2.0   VALID

9 rows selected.

Causes:
The probable causes for dba_registry components became invalid could be any of the following.

·                     Applied a patch and after the patch application because of some dependent object status change registry can become invalid

·                     Installed a new component and the new component installation got failed then registry components could become invalid

·                     catalog.sql or catproc.sql was not successfully ran after database creation. Any of them would have failed somewhere or any of the dependent object got invalid afterward

You can try the below steps to validate if you don’t have any invalid object in the database

SQL> select count(*) from dba_objects where status=’INVALID’;

COUNT(*)
———-
0

SQL> exec DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

If you have any invalid objects in the database then follow the below steps

SQL> SQL> select count(*) from dba_objects where status='INVALID';

  COUNT(*)

----------

       449

1 row selected.

'Packages and Types' can usually be validated by running catalog and catproc scripts:

$sqlplus "/as sysdba"
SQL> startup restrict
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql

SQL> select substr(comp_name,1,30) comp_name, substr(comp_id,1,10)

      comp_id,substr(version,1,12) version,status from dba_registry;

  2

COMP_NAME                                COMP_ID    VERSION      STATUS

---------------------------------------- ---------- ------------ -----------

Oracle XML Database                      XDB        11.2.0.2.0   VALID

Oracle Expression Filter                 EXF        11.2.0.2.0   VALID

Oracle Rule Manager                      RUL        11.2.0.2.0   VALID

Oracle Workspace Manager                 OWM        11.2.0.2.0   VALID

Oracle Database Catalog Views            CATALOG    11.2.0.2.0   VALID

Oracle Database Packages and T           CATPROC    11.2.0.2.0   VALID

JServer JAVA Virtual Machine             JAVAVM     11.2.0.2.0   VALID

Oracle XDK                               XML        11.2.0.2.0   VALID

Oracle Database Java Packages            CATJAVA    11.2.0.2.0   VALID

9 rows selected.

Shutdown your database in the normal mode and startup with the normal mode.

Hope the above document help you in solving the issue.