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

Posted on August 2, 2017 by Removal 30

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:

Tags:

You may also like...

30 Responses

  1. arun says:
    November 30, 2018 at 04:55

    $ 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.??

    Reply
    • Mike.Dietrich says:
      December 12, 2018 at 13:19

      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

      Reply
      • Arun says:
        February 14, 2019 at 03:17

        Thanks Mike. I was able to reproduce. chopt has direct impact on all instances running on that home. Impact is ORA600 and connection failure and instance restart is must..

        Reply
        • Mike.Dietrich says:
          February 14, 2019 at 11:11

          Did you file an SR for it you can share with me?

          Thanks,
          Mike

          Reply
        • Marcin says:
          February 5, 2021 at 15:43

          Hi Arun,
          Maybe I’m wrong, but from the context of your post I conclude that you did “chopt disable olap” over ORACLE_HOME having still running instances from it. The chopt in fact relinks oracle binaries, so i’ts very importatnt to shut down all insances based on given ORACLE_HOME before this operation.

          Reply
  2. Sven W says:
    December 5, 2018 at 20:28

    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?

    Reply
    • Mike.Dietrich says:
      December 12, 2018 at 13:21

      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

      Reply
  3. Jafar says:
    January 16, 2019 at 10:21

    Hi,

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

    Regards,

    Jafar

    Reply
  4. Alain says:
    February 13, 2019 at 15:53

    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?

    Reply
    • Mike.Dietrich says:
      February 14, 2019 at 00:28

      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

      Reply
  5. Pradip says:
    April 12, 2019 at 16:21

    Hello Mike,

    Do we have similar command for window?

    $ $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

    Reply
    • Mike.Dietrich says:
      April 12, 2019 at 16:49

      Hi Pradip,

      the same should work on Windows – catcon.pl works the same as far as I know. But you need to exchange “$ORACLE_HOME” with “%ORACLE_HOME%”, and the slashes with backslashes.

      Cheers,
      Mike

      Reply
  6. Gaurav Kumar says:
    March 17, 2020 at 12:26

    Hi Mike. I need to remove ODM component from my non-CDB DB. Since it is creating issues and listed in PDB violations. I am planning to upgrade my DB from 12cR1 to 19c. Can you please provide me steps to remove ODM(oracle data mining) component?

    Reply
  7. Victor Grinevich says:
    March 24, 2020 at 15:35

    Hi, Mike!
    Just information for anybody, if got error after removing OLAP form DB.
    If you try EXP or EXPDB and got error
    ORA-04063: Package Body ā€œSYS.DBMS_CUBE_EXPā€
    information about fix is on MOS (Doc ID 1998072.1)
    SOLUTION
    Remove the entry as below:
    SQL> conn / as sysdba
    connected
    SQL> select * from sys.exppkgact$ where package=’DBMS_CUBE_EXP’ order by 1,2;
    PACKAGE SCHEMA CLASS LEVEL#
    —————————— —————————— ———— ————
    DBMS_CUBE_EXP SYS 2 1050
    DBMS_CUBE_EXP SYS 4 1050
    DBMS_CUBE_EXP SYS 6 1050
    — create a backup of the table SYS.EXPPKGACT$
    create table sys.exppkgact$_bkup as select * from sys.exppkgact$;
    — remove the references to DBMS_CUBE_EXP
    delete from sys.exppkgact$ where package=’DBMS_CUBE_EXP’ and schema=’SYS’;
    commit;
    Now run the expdp ,it will run successfully.

    Reply
    • Mike.Dietrich says:
      April 2, 2020 at 19:12

      Thanks Victor,

      I think I will need to revisit my tests and add an “expdp” run after removing a component.

      Thanks a lot for sharing your findings with us!!!

      Cheers,
      Mike

      Reply
  8. Evgeniy Morozov says:
    February 11, 2021 at 20:33

    He Mike!

    All the scripts at mikedietrichde.com/scripts/ are now 403 forbidden šŸ™

    Reply
  9. John says:
    June 2, 2021 at 05:29

    olapidrp.plb dont need to run because it include in catnoxoq.sql in 12C.
    “removal from CDB is NOT supported at this time” in mos 1940098.1.could you help to confirm it in 19C?

    Reply
    • Mike.Dietrich says:
      June 6, 2021 at 21:16

      I don’t own the MOS note. Hence, I can’t comment on its content.

      As you can link it off with “chopt” I don’t see any justification to say “it is not supported to remove it from a CDB$ROOT”.
      But you need to clarify with Oracle Support, at best via a bug filed against the removal script as I suppose the engineer will tell you what’s in the note.

      If you follow my example, you can remove it – as far as I know, it works fine and flawless.

      Cheers,
      Mike

      Reply
  10. ABhishek says:
    October 27, 2021 at 15:07

    Hi Mike,

    After applying the patch 2021 oct cpu patch,
    COMP_NAME VERSION STATUS
    ———————————– ———- ———-
    Oracle XML Database 12.2.0.1.0 INVALID
    Oracle OLAP API 12.2.0.1.0 INVALID
    these componet are invalid, can you suggest any for this .

    Reply
    • Mike.Dietrich says:
      November 26, 2021 at 20:02

      Hi ABhishek,

      please recompile with utlrp.sql – this has component validation checks.
      If that doesn’t validate, then please check for invalid objects and try to compile manually and see what errors you will get.
      You may need to open an SR then.

      Cheers,
      Mike

      Reply
  1. Oracle Warehouse Builder (OWB) Clean Up in Oracle Database 11.2-12.2
    August 3, 2017

    […] XOQ – Oracle OLAP API Clean Up […]

  2. Enterprise Manager (EM) Clean Up in Oracle Database 11.2-12.2
    August 5, 2017

    […] XOQ – Oracle OLAP API Clean Up […]

  3. JAVAVM and XML Clean Up in Oracle Database 11.2-12.2
    August 7, 2017

    […] XOQ – Oracle OLAP API Clean Up […]

  4. Oracle APEX (Application Express) Clean Up in Oracle Database 11.2-12.2
    August 7, 2017

    […] XOQ – Oracle OLAP API Clean Up […]

Leave a Reply

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

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

Mike Dietrich
Oracle Senior Director of Product Manager for Database Upgrade, Migrations and Patching writing just another blog.

Opinions certainly are my own as is this page.

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 7,331 other subscribers

Follow me on Twitter