Oracle OLAP (XOQ, APS, AMD) Clean Up in Oracle Database 11.2-12.2

Oracle OLAP (XOQ, APS, AMD) Clean Up in Oracle Database 11.2-12.2

Oracle OLAP (XOQ, APS, AMD) Clean Up in Oracle Database 11.2-12.2 requires recompilation and some workarounds in most releases. Nevertheless, I’m documenting the removal steps for all 3 OLAP parts:

  • XOQ – OLAP API
  • APS – OLAP Analytical Workspace
  • AMD – OLAP Catalog

Again, let me emphasize that none of these blog posts is meant to recommend the removal of any component. It is just meant to explain procedures and workarounds as I sometimes face the situation to remove a component from a database.

Oracle OLAP (XOQ, APS, AMD) Clean Up in Oracle Database 11.2-12.2

Oracle OLAP (XOQ, APS, AMD) Clean Up in Oracle Database 11.2-12.2Even though a lot of fantastic analytical functions have made it over time into the standard set of Oracle SQL commands, Oracle OLAP is still very useful and has good market traction. Oracle OLAP cubes deliver sophisticated calculations using simple SQL queries – producing results with speed of thought response times. This outstanding query performance may be leveraged transparently when deploying OLAP cubes as materialized views – enhancing the performance of summary queries against detail relational tables. Because Oracle OLAP is embedded in Oracle Database 12c, it allows centralized management of data and business rules in a secure, scalable and enterprise-ready platform.

Oracle OLAP is an extra cost option.

Before you start removing anything from your database please make sure you read my introductory blog post about how to Remove and Clean Up Components from Oracle 11.2 – 12.2 . You’ll find links to monitor proper component removal there as well as in the SQL Scripts Repository on this blog.

Please read MOS Note: 332351.1 – How to remove or reinstall the OLAP option to 10g/11g upfront. You can combine each of the following 3 steps into one script and recompile afterwards.

.

Oracle Database 11.2.0.4

XOQ – OLAP API

To remove XQO you will have to execute to scripts but add recompilation as well as approximately 20 synonyms will stay invalid:

$ sqlplus / as sysdba
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
SQL> @?/rdbms/admin/utlrp.sql

The recompilation will clean up the invalid objects. Furthermore the removal scripts have some failing “drop” statements. Please ignore them.

APS – OLAP Analytic Workspace

To clean up APS you must unlink OLAP first.

$ chopt disable olap

In the next step execute the removal script and recompile. The recompilation is necessary to clean up 3 invalid synonyms.

$ sqlplus / as sysdba
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/rdbms/admin/utlrp.sql
AMD – OLAP Catalog

AMD is the third OLAP component. You can remove it with one script anytime without recompilation, restart or relink. MOS Note: 332351.1 – How to remove or reinstall the OLAP option to 10g/11g offers a description. Furthermore this option does not exist anymore in Oracle Database 12.1 and therefore can be removed before an upgrade safely to safe upgrade time.

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

 

Oracle Database 12.1.0.2

XOQ – OLAP API

To remove XQO you will have to execute to scripts but add recompilation as well as approximately 38 synonyms will stay invalid:

$ sqlplus / as sysdba
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
SQL> @?/rdbms/admin/utlrp.sql

The recompilation will clean up the invalid objects. Furthermore the removal scripts have some failing “drop” statements. Please ignore them.

APS – OLAP Analytic Workspace

To clean up APS you must unlink OLAP first.

$ chopt disable olap

In the next step execute the removal script and recompile. The recompilation is necessary to clean up 3 invalid public synonyms.

$ sqlplus / as sysdba
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/rdbms/admin/utlrp.sql
AMD – OLAP Catalog

In Oracle Database 12c the OLAP Catalog (AMD) does not exist anymore. We will switch it to OPTION OFF during the upgrade but you can remove it upfront to save on upgrade time. Once the AMD component is detected by the preupgrd.sql you’ll receive these messages:

INFORMATION: --> OLAP Catalog(AMD) exists in database

     Starting with Oracle Database 12c, OLAP Catalog component is desupported.
     If you are not using the OLAP Catalog component and want
     to remove it, then execute the ORACLE_HOME/olap/admin/catnoamd.sql script before or
     after the upgrade.

And:

INFORMATION: --> There are existing Oracle components that will NOT be
     upgraded by the database upgrade script. Typically, such components
     have their own upgrade scripts, are deprecated, or obsolete.
     Those components are:  OLAP Catalog

Please go forward and remove it in your previous environment. You will find the steps also in the Oracle Database 11.0.2.4 section.

.

Oracle Database 12.2.0.1

XOQ – OLAP API
non-CDB

Remove XOQ by executing these scripts but add recompilation as well as approximately 20 synonyms will stay invalid:

$ sqlplus / as sysdba
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
SQL> @?/rdbms/admin/utlrp.sql

The recompilation will clean up the invalid objects. Furthermore the removal scripts have some failing “drop” statements. Please ignore them.

CDB

The first removal script can run in all PDBs:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b olapidrp_pdbs -d $ORACLE_HOME/olap/admin/ olapidrp.plb

But the following steps must be execute for PDB after PDB as otherwise you’ll receive an ORA-65023: active transaction exists in container PDB$SEED during drop package dbms_cube_advise.This happens for all PDBs.

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB1' -e -b catnoxoq_pdb1 -d $ORACLE_HOME/olap/admin/ catnoxoq.sql
$ sqlplus / as sysdba
SQL> alter pluggable database PDB1 close;
SQL> alter pluggable database PDB1 open;

Repeat this steps for all PDBs, one after another.

The PDB$SEED must be restarted “read only“:

SQL> alter pluggable database pdb$seed close;
SQL> alter pluggable database pdb$seed open read only;

Before you approach the removal from CDB$ROOT you must recompile:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

Afterwards you can approach the XOQ removal from CDB$ROOT:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b olapidrp_cdb -d $ORACLE_HOME/olap/admin/ olapidrp.plb
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnoxoq_cdb -d $ORACLE_HOME/olap/admin/ catnoxoq.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
APS – OLAP Analytic Workspace
non-CDB

At first unlink the option from the Oracle kernel:

chopt disable olap

In the next step execute the removal script and recompile. The recompilation is necessary to clean up invalid public synonyms.

$ sqlplus / as sysdba
SQL> @?/olap/admin/catnoaps.sql
SQL> @?/rdbms/admin/utlrp.sql
CDB

When you remove APS from a Multitenant container database you must unlink the option from the Oracle kernel first. The database has to be shutdown during the relink operation. Afterwards all pluggable databases need to be opened again.

chopt disable olap

If you want to remove APS you will have to do this PDB after PDB as during the removal of XOQ as otherwise you’ll receive an ORA-65023: active transaction exists in container PDBn. A recompilation afterwards is mandatory:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB2' -e -b catnoaps_pdb2 -d $ORACLE_HOME/olap/admin/ catnoaps.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB1' -e -b catnoaps_pdb1 -d $ORACLE_HOME/olap/admin/ catnoaps.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB$SEED' -e -b catnoaps_pdbseed -d $ORACLE_HOME/olap/admin/ catnoaps.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

Then you can approach the removal from CDB$ROOT, again followed by a recompilation:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnoaps_cdb -d $ORACLE_HOME/olap/admin/ catnoaps.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
AMD – OLAP Catalog

The preupgrade.jar tool will give you the following output:

==============
BEFORE UPGRADE
==============

  RECOMMENDED ACTIONS
  ===================

   + Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
     $ORACLE_HOME/olap/admin/catnoamd.sql script.

     The OLAP Catalog component, AMD, exists in the database.

     Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
     desupported and will be automatically marked as OPTION OFF during the
     database upgrade if present. Oracle recommends removing OLAP Catalog
     (OLAP AMD) before database upgrade.

And in the “AFTER UPGRADE” section:

=============
AFTER UPGRADE
=============

  INFORMATION ONLY
  ================
   + Check the Oracle documentation for the identified components for their
     specific upgrade procedure.

     The database upgrade script will not upgrade the following Oracle
     components:  OLAP Catalog,OWB

     The Oracle database upgrade script upgrades most, but not all Oracle
     Database components that may be installed.  Some components that are not
     upgraded may have their own upgrade scripts, or they may be deprecated
     or obsolete.

If you are not using OLAP AMD, safely remove it from your Oracle 11.2.0.4 database before attempting the upgrade. As you will remove it in the source environment beforehand you won’t get into a situation with OLAP AMD in a Multitenant database.

Component Clean Up Series

Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/

 

16 thoughts on “Oracle OLAP (XOQ, APS, AMD) Clean Up in Oracle Database 11.2-12.2

  1. Pingback: Oracle Warehouse Builder (OWB) Clean Up in Oracle Database 11.2-12.2

  2. Pingback: Enterprise Manager (EM) Clean Up in Oracle Database 11.2-12.2

  3. Pingback: JAVAVM and XML Clean Up in Oracle Database 11.2-12.2

  4. Pingback: Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2

  5. $ chopt disable olap causes ORA-600 on all db’s running on 12.2.0.1 home. This requires a db restart. Please clarify if this a bug specific to our env or a expected symptom.??

    • Hi Arun,

      first: it works in my 18.3.0 environment. I have no 12.2.0.1 environment to reproduce it at the moment.
      second: for such issues, please always file an SR
      third: _IF_ I should do any further digging, I would need the argument of the ORA-600 – and even more important, the exact steps you’d followed and the snippet of the alert.log showing when and where the ORA-600 happens (during chopt (unlikely!), during startup etc).

      Thanks,
      Mike

  6. I have an ODA with a freshly installed new SE2 12.2.0.1.180417 database.
    Checking pdb_plug_in_violations shows me that OLAP is installed in the PDB$SEED but not in the CDB.

    NAME CAUSE TYPE ERROR_NUMBER LINE MESSAGE
    PDB$SEED OPTION ERROR 0 1 Database option APS mismatch: PDB installed version 12.2.0.1.0. CDB installed version NULL.
    PDB$SEED OPTION ERROR 0 2 Database option SDO mismatch: PDB installed version 12.2.0.1.0. CDB installed version NULL.
    PDB$SEED OPTION ERROR 0 3 Database option XOQ mismatch: PDB installed version 12.2.0.1.0. CDB installed version NULL.

    I want to remove theses violation errors.
    Is that correct scenario, where it makes sense to have those components removed?
    Does the way that is described above also apply to ODA?

    • Hi Sven,

      ODA is special – and you may please need top open an SR for this (you can drop me the SR number via email or here on the blog).
      First of all, it does not make any sense to me to have OLAP in the PDB$SEED (and thus in all subsequent PDBs) but not in the CDB$ROOT. Only APEX to my knowledge allows to do this. Hence, please double check with support of this is a general bug.

      Thanks,
      Mike

  7. Hi,

    Can we disable olap with “chopt disable olap” if there are other databases running off the same oracle database home?

    Regards,

    Jafar

  8. Hi,
    I have an oracle 11.2.0.4 SE and I would like to drop OLAP before upgrade to reduce downtime.
    the scripts mentioned are NOT present in oracle 11.2 SE. It seems there is a “private” note on metalink (1362752.1) to do this but content is protected. Any hint?

    • Hi Alain,

      I think the scripts are not present because OLAP shouldn’t be present on SE 😉

      What you could do:
      Install an EE somewhere for test, grab the scripts, do the action and remove the EE installation again.

      And for MOS notes being private:
      Please open an SR for this – it could be that the MOS note is not public for a specific reason.
      You need to check with Oracle Support.

      Cheers,
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.