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
Even 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
- Remove and Clean Up Components from Oracle 11.2 – 12.2 – General Guidelines and Precautions
- APEX – Oracle Application Express Clean Up
- OWM – Oracle Workspace Manager Clean Up
- DV – Oracle Database Vault Clean Up
- OLS – Oracle Label Security Clean Up
- SDO – Oracle Spatial Data Option Clean Up
- CONTEXT – Oracle Text Clean Up
- ORDIM – Oracle Multi Media Clean Up
- XOQ – Oracle OLAP API Clean Up
- APS – Oracle OLAP Analytical Workspace Clean Up
- AMD – Oracle OLAP Catalog Clean Up
- OWB – Oracle Warehouse Builder Clean Up
- EXF/RUL – Oracle Expression Filters and Rules Manager Clean Up
- EM – Enterprise Manager Database Control Clean Up
- JAVAVM/XML – Oracle Java Virtual Machine and XDK Clean Up
- XDB – Oracle XML Database Clean Up
Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/
$ 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
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..
Did you file an SR for it you can share with me?
Thanks,
Mike
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.
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
Hi,
Can we disable olap with “chopt disable olap” if there are other databases running off the same oracle database home?
Regards,
Jafar
No – you will link it out of the kernel and it won’t be available to anybody running with this home.
Cheers,
Mike
Thanks Mike.
Can we still remove it from the database using “catnoaps.sql” without running “chopt disable olap”.
Thanks,
Jafar
Jafar,
I think so – it just would mean that the kernel module is present. But that should have no negative impact as far as I can see.
Cheers,
Mike
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
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
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
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?
Hi Gaurav,
it is not an ordinary component in the database. You can only unlink it:
https://mikedietrichde.com/2015/05/27/removing-options-from-the-oracle-database-kernel-in-12c/
Cheers,
Mike
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.
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
He Mike!
All the scripts at mikedietrichde.com/scripts/ are now 403 forbidden š
Yes, a colleague told me this last week – sorry for the inconvenience. I will try to change this later this week.
Cheers,
Mike
Thanks – I will fix this asap.
Cheers,
Mike
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?
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
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 .
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