Tuesday, July 25, 2017

Pre Upgrade Error : Oracle Packages and Types [upgrade] INVALID

Problem
Oracle Packages and Types Invalid from pre-upgrade tool for intend to upgrade oracle 11.2.0.1 to 11.2.0.4.
SQL> @<ORACLE_HOME>/rdbms/admin/utlu112i.sql
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  INVALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
--> Oracle Application Express   [upgrade]  VALID

Solution

1. Check the status of Registry components
SQL> SELECT comp_name,version,status FROM dba_registry;
Oracle Database Catalog Views     11.2.0.1.0 VALID 
Oracle Database Packages and Types    11.2.0.1.0 INVALID 
Oracle Workspace Manager     11.2.0.1.0 VALID 
JServer JAVA Virtual Machine     11.2.0.1.0 VALID 
Oracle XDK     11.2.0.1.0 VALID 
Oracle Database Java Packages     11.2.0.1.0 VALID 
Oracle Expression Filter     11.2.0.1.0 VALID 
Oracle Text     11.2.0.1.0 VALID 
Oracle XML Database     11.2.0.1.0 VALID 
Oracle Rules Manager     11.2.0.1.0 VALID 
Oracle Multimedia     11.2.0.1.0 VALID 
Oracle Application Express     3.2.1.00.10 VALID 
Oracle Enterprise Manager     11.2.0.1.0 VALID
2. log as SYSDBA and restart database in upgrade mode
$ sqlplus  / as sysdba
SQL> shutdown immediate;
SQL> startup upgrade;
3. Execute Following scripts
SQL> @<ORACLE_HOME>/rdbms/admin/catalog.sql      --Recreate Oracle database Catalog Views components
SQL> @<ORACLE_HOME>/rdbms/admin/catproc.sql      --Recreate Oracle database Packages and Types component
4. Recompile all invalid objects
SQL> @<ORACLE_HOME>/rdbms/admin/utlrp.sql
5. Shutdown database and startup in normal mode
SQL> shutdown immediate;
SQL> startup;
6. Check the status of Registry components Again
SQL> SELECT comp_name,version,status FROM dba_registry;
Oracle Database Catalog Views 11.2.0.1.0 VALID 
Oracle Database Packages and Types 11.2.0.1.0 VALID 
Oracle Workspace Manager 11.2.0.1.0 VALID 
JServer JAVA Virtual Machine 11.2.0.1.0 VALID 
Oracle XDK 11.2.0.1.0 VALID 
Oracle Database Java Packages 11.2.0.1.0 VALID 
Oracle Expression Filter 11.2.0.1.0 VALID 
Oracle Text 11.2.0.1.0 VALID 
Oracle XML Database 11.2.0.1.0 VALID 
Oracle Rules Manager 11.2.0.1.0 VALID 
Oracle Multimedia 11.2.0.1.0 VALID 
Oracle Application Express 3.2.1.00.10 VALID 
Oracle Enterprise Manager 11.2.0.1.0 VALID