CATPROC and CATALOG are INVALID in the Registry

We are working on pre-upgrade steps and noticed that “Oracle Database Packages and Types” and “Oracle Database Catalog Views “ components are  showing as INVALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS

---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID

XDB        Oracle XML Database                                       11.2.0.4.0                     VALID

EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID

RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID

OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID

CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     INVALID

CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     INVALID

JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID

XML        Oracle XDK                                                          11.2.0.4.0                     VALID

CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

I see that CATPROC, CATALOG  is INVALID in the registry and followed below steps to validate.

Step1: Validate ‘packages and types’ by running catalog and catproc scripts

$sqlplus "/as sysdba"
SQL> spool cata_logfile.txt
SQL> startup upgrade
SQL > @?/rdbms/admin/catalog.sql
SQL > @?/rdbms/admin/catproc.sql
SQL > @?/rdbms/admin/utlrp.sql
SQL> spool off

Note that you need to use Use 'startup migrate' instead of ‘startup upgrade’ if database version is lower than 10g.

This should resolve the issue. If CATPROC, CATALOG is still invalid in the registry then follow next step

Step2:- Run below script as SYSDBA until it returns 'CATPROC can be validated now'. This script recompiles all dependent objects. 

 Note that you need to replace CATPROC with CATALOG in below script and execute to recompile the objects related to CATALOG.

REM ***************
REM CHECKVALID.SQL
REM ***************
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
cid = 'CATPROC';
SELECT obj#,name into object_id,object_name
FROM obj$
WHERE status > 1 AND
(ctime BETWEEN start_time AND end_time OR
mtime BETWEEN start_time AND end_time OR
stime BETWEEN start_time AND end_time) AND
ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||'
Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/

Validate CATPROC. CATALOG by executing following command
SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

SQL> execute DBMS_REGISTRY_SYS.VALIDATE_CATALOG;

Now I can see that all Database components are objects are VALID.

SQL> select comp_id, comp_name,version, statusfrom dba_registry;
COMP_ID    COMP_NAME                                VERSION                        STATUS

---------- ---------------------------------------- ------------------------------ --------------------------------------------
ORDIM      Oracle Multimedia                                        11.2.0.4.0                     VALID

XDB        Oracle XML Database                                       11.2.0.4.0                     VALID

EXF        Oracle Expression Filter                                    11.2.0.4.0                     VALID

RUL        Oracle Rules Manager                                       11.2.0.4.0                     VALID

OWM        Oracle Workspace Manager                         11.2.0.4.0                     VALID

CATALOG    Oracle Database Catalog Views                 11.2.0.4.0                     VALID

CATPROC    Oracle Database Packages and Types       11.2.0.4.0                     VALID

JAVAVM     JServer JAVA Virtual Machine                     11.2.0.4.0                     VALID

XML        Oracle XDK                                                          11.2.0.4.0                     VALID

CATJAVA    Oracle Database Java Packages                   11.2.0.4.0                     VALID

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

Upgrade Oracle 8i to 10g

Here are the simple steps to upgrade your oracle 8i database to 10g.

Please see the below guidelines:-

. Direct upgrade to 10g supported from 9.2.0,9.0.1,8.1.7,8.0.6.
. Make sure you take a cold backup of the database before upgradation.
. Make sure that required oracle version already installed on the system.
. Make sure you backup configuration files before you update for ex: - init.ora.
. Apply security patches after database upgraded to higher version, if necessary

Step:-1 Pre-upgradation check
Login to the Oracle 8i Database and run the utlu102i.sql from <10g_oracle_home>/rdbms/admin/utlu102i.sql.

Spool the contents of the file.
SQL> spool pre_check.log
SQL> @<10g_oracle_home>/rdbms/admin/utlu102i.sql
SQL> spool off

Review the spool file and make the necessary Database/parameter changes based on the recommendation.

Step:-2 Take cold backup of the database
Take backup using RMAN or command line file level backup

Step:-3 update oratab (/etc/oratab) and copy the init.ora from oracle 8i to 10g
Change the oracle home from 8i to 10g home to which it is being upgraded to.

For ex:-testdb:/oracle/v8174:N:UTF8 <-- 8i entry testdb:/oracle/v1020:N:UTF8 < --10g entry Copy init.ora from 8i to 10g oracle home. For ex:- $cp <8i_oracle_home>/dbs/init.ora <10g_oracle_home>/dbs/init.ora

If you are upgrading a RAC database, then set CLUSTER_DATABASE to “FALSE” during the upgrade.

Step:-4 Set the environment and Start the upgrade
Set the ORACLE SID and ORACLE_HOME

$ORACLE_SID=; export ORACLE_SID
$ORACLE_HOME=<10g_oracle_home>; export ORACLE_HOME

Run the upgrade script
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup upgrade

Create a SYSAUX tablespace.
SQL> CREATE TABLESPACE sysaux DATAFILE ‘’ SIZE 512M REUSEEXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

SQL> spool upgrade_10g.log
SQL> @<10g_oracle_home>/rdbms/admin/catupgrd.sql
SQL> spool off

Step:-5 Post upgradationCheck weather all components successfully upgraded or not.

SQL> @<10g_oracle_home>/rdbms/admin/utlu102s.sql
Run the script to recompile invalid objects.
SQL> @<10g_oracle_home>/rdbms/admin/utlrp.sql

Step:-6 make the database security compliant.
Check the current CPU Version.
SQL> select * from registry$history;

Please see the below link to check available Critical Patch Updates to your 10g Oracle version.http://www.oracle.com/technology/deploy/security/alerts.htm

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/