Tuesday, November 17, 2009

“DBA_REGISTRY” status “INVALID” after an upgrade

Description:
I noticed the below objects in DBA_REGISTRY status as INVALID after an upgrade. I followed the below steps to solve this issue. Below steps I have tried based on Metalink ID: 753041.1
SQL> SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
COMP_NAME
--------------------------------------------------------------------------------
Oracle Text
Spatial
Oracle interMedia
Oracle Data Mining
Oracle Workspace Manager

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/ctx/admin/ctxpatch.sql --Oracle Text
SQL>@$ORACLE_HOME/md/admin/sdopatch.sql --Spatial
SQL>@$ORACLE_HOME/ord/im/admin/impatch.sql -- Oracle Intermedia
SQL>@$ORACLE_HOME/rdbms/admin/odmpatch.sql --Oracle Data Mining
SQL>@$ORACLE_HOME/rdbms/admin/owmpatch.sql --Oracle Workspace Manager
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql - To recompile all the Invalid Objects
Step 5: SQL>Shutdown immediate;
Step 6: SQL>startup;
STEP 7: Re run the below query
SELECT COMP_NAME FROM DBA_REGISTRY WHERE STATUS='INVALID';
no rows selected

No comments: