Friday, June 24, 2011

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.

5 comments:

  1. Thanks man. I've migrate platform and this help me a lot!

    ReplyDelete
  2. Hi
    Thanks for this document it help me to fix this issue

    ReplyDelete
  3. Really good doc ,we can save our time before raising oracle SR

    Regards,
    Maddy

    ReplyDelete
  4. I tried this many times but no luck ):
    any other solution to get the rid of this error

    ReplyDelete