Dealing with INVALID status of Oracle database components after upgrade to 12c (12.1.0.2)

This article deals with INVALID, OPTION OFF status of Oracle database components AMD, ORDIM, JAVAVM, CATJAVA and XOQ after upgrade to 12c using a full RMAN backup.

Table of contents:

1. Overview of Oracle components and their status.

2. Dealing with Oracle database components:

2.1. Oracle OLAP catalog (AMD) component:

2.2. Oracle OLAP API (XOQ) component

2.3. Oracle database Java & JServer Java VM components:

2.4. Oracle Multimedia (ORDIM) component

2.5. Oracle Enterprise Manager  (EM) component

1. Overview of Oracle components and their status.

After upgrade to 12.1.0.2, the Oracle database components Oracle Multimedia (ORDIM), JServer java VM (JAVAVM), Oracle database Java (CATJAV) and Oracle OLAP API (XOQ) have the status INVALID and also the component OLAP Catalog has the status OPTION OFF.

 

Note: Starting with Oracle Database 12c, Oracle XML DB is a mandatory component of Oracle Database. You cannot uninstall Oracle XML DB, and there is no option to exclude it when you create an Oracle database.

Note: DBA_REGISTRY displays information about all components in the database that are loaded into the component registry. The component registry tracks components that can be separately loaded into the Oracle Database.

2. Dealing with Oracle database components:

2.1. Oracle OLAP catalog (AMD) component:

This component is desupported with Oracle database 12c.  After upgrade from 11.2.0.4, this component is already exist with the status OPTION OFF. To remove it, then just execute the catnoamd.sql script before or after upgrade.

SQL> connect / as sysdba
SQL> @?/olap/admin/catnoamd.sql

 

2.2. Oracle OLAP API (XOQ) component:

Note: In my case, my Oracle database is an Enterprise Edition database that was created as a General Purpose, Transaction Processing, or Custom database without OLAP, then I am going to remove this component:

Step 1: Remove the XOQ component:

SQL> connect / as sysdba 
SQL> @?/olap/admin/olapidrp.plb 
SQL> @?/olap/admin/catnoxoq.sql

Step 2: Clean OLAP-Related Objects (OLAP AW component):

To ensure that Datapump (expdp) does not use the (possibly incorrect) export handlers for OLAP-related components:

SQL> select * from sys.exppkgact$ where package ='DBMS_AW_EXP' and schema= 'SYS';
SQL> delete from sys.exppkgact$ where package ='DBMS_AW_EXP' and schema= 'SYS';
SQL> commit;
SQL> shut immediate
SQL> startup

Note : This step has been updated by Maciej Tokar!!

To avoid the error: DataPump Export fails with PLS-201 identifier ‘SYS.DBMS_CUBE_EXP’

SQL> SELECT * FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP';
-- Backup the table SYS.EXPPKGACT$ before deleting the row 
SQL> CREATE TABLE SYS.EXPPKGACT$_BACKUP AS SELECT * FROM SYS.EXPPKGACT$; 
SQL> DELETE FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA= 'SYS';
SQL> COMMIT;
SQL> shutdown immediate; 
SQL> startup

Note: Restart the database to ensure the error is resolved.

Step 3: Resolve Invalid Objects

Once the database has been started, resolve any invalid objects by running the utlrp.sql script:

SQL> @?/rdbms/admin/utlrp.sql

 2.3. Oracle database Java & JServer Java VM components:

The Oracle JVM contains a number of components including the following:

• compiler: Standard Java compiler. When the CREATE JAVA SOURCE statement is run, it translates Java source files into architecture-neutral.
• interpreter: To run Java programs, Oracle JVM includes a standard Java2 bytecode interpreter. The interpreter and the associated Java run-time system run standard Java class files. The run-time system supports native methods and call-in and call-out from the host environment.
• library manager: Loads java source, class and resource files into the database
• …..

Note: I think these packages are useful after all, then I am going to remove them and to re-install them again.

Step 1: Remove the JVM

SQL> connect / as sysdba
SQL> @?/xdk/admin/rmxml.sql
SQL> @?/javavm/install/rmjvm.sql
SQL> @?/rdbms/admin/catnojav.sql

 

Note: If you want just to remove the JVM and you don’t want to install the JVM, then delete the JVM-Related objects to datapump (expdp).

SQL> delete from exppkgobj$ where package like '%JVM%'; 
SQL> commit;
SQL> shutdown immediate; 
SQL> startup

Note: Restart the database to ensure the error is resolved.

Step 2: Install the JVM

SQL> connect / as sysdba
SQL> @?/javavm/install/initjvm.sql
SQL> @?/xdk/admin/initxml.sql
SQL> @?/xdk/admin/xmlja.sql
SQL> @?/rdbms/admin/catjava.sql

Step 3: Resolve Invalid Objects

Once the database has been started, resolve any invalid objects by running the utlrp.sql script:

SQL> @?/rdbms/admin/utlrp.sql

2.4. Oracle Multimedia (ORDIM) component :

Oracle interMedia (“interMedia”) is a feature that enables Oracle Database to store, manage, and retrieve images, audio, video, … Oracle interMedia extends Oracle Database reliability, availability, and data management to multimedia content in traditional, Internet, electronic commerce, and media-rich applications.

Note: I guess this component is useful, so I am going to re-enable it.

Run the script as SYS, located in the $ORACLE_HOME/ord/im/admin/initimj.sql, that will reload those classes.

SQL> connect / as sysdba
SQL> @?/ord/im/admin/initimj.sql
SQL> connect / as sysdba
SQL> execute validate_ordim;

 

2.5. Oracle Enterprise Manager  (EM) component:

Starting with Oracle Database 12c, Oracle Enterprise Manager Database Control is desupported and is no longer available. Oracle introduces Oracle Enterprise Manager Database Express (Oracle EM Express) as a replacement. Oracle EM Express is installed when you upgrade to Oracle Database 12c.

Note: For these reasons, I will remove this component by using the emremove.sql script.

SQL> connect / as sysdba
SQL> @?/rdbms/admin/emremove.sql

 

Conclusion:

This article describes the steps to change the Oracle database components AMD, ORDIM, JAVAVM, CATJAVA, XOQ and EM status.