Tuesday, November 17, 2009

Oracle Database Packages and Types INVALID status in DBA_REGISTRY

Description:
I noticed Oracle Database Packages and Types status is INVALID in DBA_REGISTRY . I followed the below steps to solve this issue. This issue occurred on 10g (10.2.0.2.0) database.
SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
COMP_NAME
--------------------------------------------------------------------------------
Oracle Database Packages and Types

Solution I tried:
Step 1: Login as SYSDBA using the below command
$ sqlplus “/as sysdba”
Step 2: Shutdown the database if it is running using the below command
SQL> shutdown immediate
Step 3: SQL> startup ugrade;
Step 4: I ran the following sql files,
SQL>@$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
catalog.sql is to recreate Oracle database Catalog
catproc.sql is to recreate Oracle database Packages and Types
utlrp.sql is to recompile all the invalid objects
Step 5: Shutdown immediate;
Step 6: startup
STEP 7: Re run the below query
SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
no rows selected

No comments: