How To Remove The OLAP Option In 10g And 11g

How To Remove The OLAP Option In 10g And 11g (Doc ID 332351.1)

To Bottom

In this Document

APPLIES TO:

Oracle OLAP - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]
Information in this document applies to any platform.
<internal_only* ***< internal_only="">

GOAL

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

Purpose

The purpose of this article is to provide information on how to remove  the OLAP option from an Enterprise Edition (EE) database or  an ORACLE_HOME of an EE database .

Scope and Application

This article is intended for OLAP users who wish to remove Oracle OLAP from their EE database or from its ORACLE_HOME.

SOLUTION

The first section deals with removing OLAP from a particular database. You will still be able to use OLAP in any other database sharing the same ORACLE_HOME. The second section deals with removing OLAP from the ORACLE_HOME.

A) Remove  OLAP objects from the database.
This removes Oracle OLAP specific database objects.

B) Remove  OLAP from the Oracle Home.
This removes  Oracle OLAP specific files from the Oracle Home and also unlinks  the Oracle executable with OLAP.

Case A) Remove OLAP objects from the database

In order to remove OLAP from database you need to open a terminal session on the database server machine, set ORACLE_HOME, ORACLE_SID environment variables, and execute the following scripts
as user SYS AS SYSDBA:

ORACLE_HOME=your_oracle_home
ORACLE_SID=your_db_sid
PATH=$ORACLE_HOME/bin:$PATH
sqlplus /nolog
SQL> conn / as sysdba
SQL> spool remove_olap.log
----> Remove OLAP Catalog
SQL> @?/olap/admin/catnoamd.sql
----> Remove OLAP API
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
----> Deinstall APS - OLAP AW component
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/olap/admin/cwm2drop.sql
----> Recompile invalids
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

catnoamd.sql used above will drop the OLAPSYS schema (which is completely OLAP specific).
cwm2drop.sql needs to be run only in 11g. In 10g, catnoamd.sql already calls it.

But in 11.2.0.4, 'CATNOAMD.SQL' will NOT drop 'OLAPSYS' user.
As per this internal Bug 16362503, dropping OLAPSYS user makes the upgrade to 12.1 more difficult.
Solution: dropping manually OLAPSYS user.

If you connect to the database in SQLPLUS, the banner still shows the OLAP option.
This happens because the Oracle executable is still aware of OLAP, it is linked with OLAP, and also the OLAP files are still present in the ORACLE_HOME.

Note that catnoadm.sql could fail from 10.1.0.5 to 11.1.0.7 release due to the fact that it refers to three scripts which only exist in 11.2 release.

The script will also error on 7 non-existing synonyms to drop.

For database versions prior to 11.2, execute these three drop synonym statements:

SQL> drop public synonym OlapFactView;
SQL> drop public synonym OlapDimView;
SQL> drop public synonym DBMS_ODM;

After running the deinstall scripts, the utlrp.sql could report invalid objects with SYS and PUBLIC owners. If so, the following SQL can be used to report more details:

SQL> select owner, object_name, object_type, status from dba_objects where status='INVALID';

If there are further invalid OLAP specific objects after the above removal steps, please review the following documents:

Removing Oracle OLAP from the Database does not Remove All OLAP Objects Note 1060023.1

Remove Invalid OLAP Objects From SYS And OLAPSYS Schema Note 565773.1

Finally, modify the EXPDP datapump handler table to remove handlers used to export OLAP data:

 IF YOU INTEND TO ADD OLAP BACK, DO NOT PERFORM THIS STEP. 

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;

Case B) Remove Oracle OLAP from the ORACLE_HOME.

Ensure that all processes in the ORACLE_HOME are shut down, and then use the Oracle Universal Installer (OUI) to remove the OLAP option from the Oracle Home.

Please note that, you should perform a full backup of the ORACLE_HOME and oraInventory before you do the above, in case software recovery is required.

Note: With Oracle Database 11g Release 2 (11.2.0.x), it is not possible to deinstall/remove a specific licensable option from the Oracle home using Oracle Universal Installer.

If you removed OLAP from the database and plan to add it again, do NOT remove it from the ORACLE_HOME and do not set relink the Oracle executable with olap_off.

For 10G and 11.1:

1. Launch Oracle Universal Installer
2. On welcome screen, you will find the button "Installed Products". Click on installed products which will show a list of installed products/components
3. Choose The Oracle Home
4. Choose and expand Oracle Database 10/11g
5. Choose and expand Enterprise Edition Options
6. Choose Oracle Olap
7. Click the Remove button to remove OLAP binaries.
8. Check the status in the dba_Registry.

On Unix or Linux, you can relink the Oracle executable with olap_off (instead of doing the OUI part above):

IF YOU INTEND TO ADD OLAP BACK OR HAVE OLAP IN OTHER INSTANCES, DO NOT PERFORM THIS STEP.

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

This unregisters OLAP with the Oracle executable, and OLAP will no longer appear in the banner of the database, also the v$option will report False for OLAP.

For Windows Platform needs to turn OLAP option library OFF:
a) Ensure that all processes in the ORACLE_HOME are stopped. This includes the database (if any), listener, http (apache) server, intelligent agent, etc..
b) cd %ORACLE_HOME%\bin
c) Rename oraolapop10.dll oraolapop10.dll.OFF

When connecting to the database the banner should no longer show the OLAP option.

Still have questions

Use MOS Data Warehousing community to search for similar discussions or start a new discussion on this subject.

REFERENCES

</internal_only* ***<>