Friday, March 4, 2011

ORA-04063: view "SYS.DBA_REGISTRY" has errors

One day I was building 11.1.0.7 standalone database. I messed up the catalog and catproc views and both of the packages became Invalid and I was unable to query DBA_REGISTRY. raised an SR with Oracle and the solution is as follows:

select * from dba_registry;

ORA-04063: view "SYS.DBA_REGISTRY" has errors

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
Normally this will be resolved by re- running catalog.sql and then catproc.sql as sysdba as per below metalink note
Note: 457861.1 CATPROC - Packages and Types Invalid in Dba_registry

This issue can happen if you run catproc.sql first and then catalog.sql. It should be run as following. This note is applicable to 11g database as well.

Solution:
$sqlplus "/as sysdba"
SQL> startup upgrade
-- Use 'startup migrate' if database version is lower than 9.2.
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql

4 comments:

  1. I have a environment that DBA_REGISTRY and others objects are invalids.

    Can I rerun catalog and catproc scripts? If Yes the order would be as below:

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

    ReplyDelete