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/

 

4 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

Leave a Reply

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

* Checkbox to comply with GDPR is required

*

I agree