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

Oracle OLAP is disabled

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:

Verify that 'JServer JAVA Virtual Machine' is present and VALID in the database instance


–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:

check components for the XML Database

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:

Verify that Oracle Expression Filter is valid

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>>
Runing DBCA

–Choose “Configure Database Options” and click Next>>
DBCA(Configure Database Options)

–Choose Database SID and click Next>>
DBCA(select database to configure)


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

DBCA(Uncheck Configure Enterprise Manager)


–If you see this(OLAP is disabled),
DBCA(Oracle OLAP is disabled)


–Then press the button “Standard Database Components…”
Standard Database Components(Oracle JVM option)


–Check “Oracle JVM” and click OK

Standard Database Components(Checked Oracle JVM option)

–Now you should see this
DBCA(Oracle OLAP is enabled)


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: