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

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-19c 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-19c

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 – 19c 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

I adjusted this procedure slightly to comply with Oracle 19c. For Oracle 12.2.0.1 and Oracle 18c you may need to close and restart the PDBs after the first removal step. This is not necessary anymore in Oracle 19c, at least not in my environment.

At first, run this 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

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.

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB1' -e -b catnoxoq_pdb1 -d $ORACLE_HOME/olap/admin/ catnoxoq.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB2' -e -b catnoxoq_pdb2 -d $ORACLE_HOME/olap/admin/ catnoxoq.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'PDB$SEED' -e -b catnoxoq_pdbseed -d $ORACLE_HOME/olap/admin/ catnoxoq.sql
$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

The recompilation at the end is required.

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 

@?/olap/admin/catnoaps.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, then at last in the CDB$ROOT as otherwise you’ll receive:

create or replace procedure aps_clean as
*
ERROR at line 1:
ORA-65023: active transaction exists in container PDB$SEED

This issue still exists in Oracle 19c as it did exist since 12.2.0.1. 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 -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

AutoUpgrade will give you a similar message such as:

==============
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/

 

 

Share this: