Expdp/Impdp fails,version incompatibility issue(Installing OLAP)

Real World Scenario

Exchanging data between Oracle 11.1.0 and Oracle 11.2.0 using export/import data pump caused the following error.

PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_BEG' must be declared
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_LOOP' must be declared
PLS-00201: identifier 'DBMS_CUBE_EXP.SCHEMA_INFO_IMP_END' must be declared

Sometimes this happens when you are exchanging data not just between these specific versions of Oracle but between other versions too, especially between higher and lower versions.

One solution for this is to use exp instead of expdp. It really works, but if you still want to use expdp then you should manually install OLAP on that oracle version, which doesn’t have it.

To determine if OLAP is installed or not just run DBCA(located in $ORACLE_HOME/bin). Choose “Configure Database Options”,press Next>> 3 times and if you see this, which means OLAP is not installed

Do the following steps:

Step 1:

–Connect database as sysdba

export ORACLE_SID=db_sid
Sqlplus sys/sys_pass as sysdba

–Run the following query

SELECT value
FROM v$option
WHERE parameter = 'OLAP';

if the value is TRUE, OLAP is turned on. Go to Step 2.
If the value is FALSE, then shutdown DB and turn it on:

For Linux:

make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk olap_on
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk ioracle

For Windows:

1. All oracle processes must be stopped. This includes the database (if any), listener, http (apache) server, intelligent agent, etc..
2. Change to the %ORACLE_HOME%\bin directory
3. Rename oraolapop10.dll.OFF to oraolapop10.dll
4. Restart the database

Step 2:

Verify that ‘JServer JAVA Virtual Machine’ is present and VALID in the database instance:

–Run the following query

SELECT comp_id, comp_name, status, substr(version,1,10) as version
FROM dba_registry
WHERE comp_id like 'JAVAVM';

–If the output is NOT like this:

–Then run the following commands when connected as SYSDBA:

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

Note: ? is $ORACLE_HOME

Step 3:

Now we need to check or possibly  install the components for the XML Database

–Run the following query

select comp_name, version,status
from dba_registry
where comp_id in ('XDB','XML');

–If the output is NOT like this:

Then you should run:

SQL> conn / as SYSDBA
SQL> @?/rdbms/admin/catqm.sql change_on_install XMLDB TEMP;

Step 4:

Check other  requirements:

–Run the following query

select comp_name, version,status
 from dba_registry
 where comp_name='Oracle Expression Filter'

–If the output is NOT like this:

Then run the following as SYS:

–This file is located in $ORACLE_HOME/rdbms/admin/

@catexf.sql

Step 5:

Finally to install  OLAP.

–Run the following script as sysdba

SQL>$ORACLE_HOME/olap/admin/olap.sql

Another Method to install OLAP is with DBCA

–Run DBCA and Click Next>>

–Choose “Configure Database Options” and click Next>>

–Choose Database SID and click Next>>

— Choose Enterprise Manager should be installed or not.Depends on your needs(I have unchecked it)

–If you see this(OLAP is disabled),

–Then press the button “Standard Database Components…”

–Check “Oracle JVM” and click OK

–Now you should see this

–Check the option “Oracle OLAP” and click Next>>