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.
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
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
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
Good one. This really helped me
ReplyDeleteThanks worked for me tooo!!
ReplyDeleteI have a environment that DBA_REGISTRY and others objects are invalids.
ReplyDeleteCan 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
Yes
Delete