Oracle Multimedia (ORDIM) Clean Up in Oracle Database 11.2-12.2

Oracle Multimedia (ORDIM) clean up in Oracle Database 11.2-12.2 is not complicated especially as the removal script checks for the usage of the option first.

Oracle Multimedia (ORDIM) Clean Up in Oracle Database 11.2-12.2

Please be aware that Oracle Spatial (SDO) depends on Oracle Multimedia. Therefore, when you remove Oracle Multimedia then Oracle Spatial will be removed as well.

Oracle Multimedia was called “Intermedia” in earlier releases. It integrates multimedia data with enterprise information stored in Oracle Database. Multimedia data can be stored, managed and retrieved with SQL, PL/SQL and Java in the same way as standard relational data. This enables rapid development and maintenance of multimedia applications using existing skills.

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.

There’s a “Master” MOS Note: 337415.1 – Where to Find the Information to Install, Upgrade, Downgrade and Deinstall interMedia/Oracle Multimedia? which is not really helpful as it points you only to an OTN page but not to the specific information. Google, Bing or Yandex can do the same job.

Oracle Database 11.2.0.4

You can remove Oracle Multimedia with a single script which checks for usage and expects an interactive Y/N response before it proceeds.

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catcmprm.sql ORDIM

   ==> You must answer with y/n to allow the script to either proceed or cancel

Oracle Database 12.1.0.2

To remove ORDIM from Oracle Database 12.1.0.2 use the same procedure as in Oracle Database 11.2.0.4:.

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catcmprm.sql ORDIM

   ==> You must answer with y/n to allow the script to either proceed or cancel

Oracle Database 12.2.0.1

non-CDB

Again, the same process as in previous releases applies. But it isn’t as flawless as in previous releases. Therefore some leftovers require a cleanup and a recompilation afterwards::.

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catcmprm.sql ORDIM

   ==> You must answer with y/n to allow the script to either proceed or cancel

The following objects must be cleaned up manually:

SYS          JAVA DATA    prv//////OBPJCDOAAAAAAAAA     INVALID
SYS          JAVA DATA    prv//////NGOANDOGAAAAAAAA     INVALID
SYS          PACKAGE BODY ORD_ADMIN                     INVALID
SYS          PACKAGE BODY ORDIMDPCALLOUTS               INVALID

The recompilation cleans out the invalid Java Data. In addition you must drop the invalid packages manually.

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlrp.sql
SQL> drop package SYS.ORD_ADMIN;  --not necessary in Oracle 19c
SQL> drop package SYS.ORDIMDPCALLOUTS;

CDB

To remove ORDIM from a Multitenant environment we have to use another script as the interactive checks in imremov.sql don’t allow usage with catcon.pl. You can use the script imremchk.sql to check usage of ORDIM upfront. Afterwards you will invoke imremdo.sql with catcon.pl to remove ORDIM.

At first you’ll run an optional check for ORDIM usage:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b imremchk -d $ORACLE_HOME/ord/im/admin imremchk.sql

Please check the log file imremchk0.log.

Afterwards you’ll remove ORDIM from all PDBs first followed by a mandatory recompilation:

$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b imremdo_pdbs -d $ORACLE_HOME/ord/im/admin imremdo.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

In the next phase you will remove ORDIM now from the CDB$ROOT and recompile again:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b imremdo_cdb -d $ORACLE_HOME/ord/im/admin imremdo.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

And finally you have to cleanup leftovers from all container:

$ vi dropim.sql
         drop package SYS.ORD_ADMIN;
         drop package SYS.ORDIMDPCALLOUTS;

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

You are done!

Oracle Database 18c

Please note that as of Oracle Database 18c Oracle Multimedia is deprecated and does not get developed further.

Oracle Database 19c

Please note that Oracle Multimedia is desupported and its API gets removed as you upgrade to Oracle Database 19c. But be aware that the component ORDIM stays in CDB_REGISTRY as VALID. In addition, the dependency between ORDIM and SDO (Spatial Data Option) has been removed with Oracle 19c.

• Oracle Multimedia is desupported in Oracle Database 19c

CDB

The approach to remove ORDIM from a Multitenant environment is the same as in Oracle 12.2.0.1 – but the leftovers are a bit different. But overall the process works quick and save. As usual, the order of removal is important. It has to be removed from the PDBs and PDB$SEED at first, then you can remove it from CDB$ROOT.

At first you’ll run an optional check for ORDIM usage:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b imremchk -d $ORACLE_HOME/ord/im/admin imremchk.sql

Please check the log file imremchk0.log.

Afterwards you’ll remove ORDIM from all PDBs first followed by a mandatory recompilation:

$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b imremdo_pdbs -d $ORACLE_HOME/ord/im/admin imremdo.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

In the next phase you will remove ORDIM now from the CDB$ROOT and recompile again:

$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b imremdo_cdb -d $ORACLE_HOME/ord/im/admin imremdo.sql
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

And finally you have to cleanup leftovers from all container:

$ vi dropim.sql
         drop package SYS.ORDIMDPCALLOUTS;

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

The result is:

SQL> select object_name, object_type, con_id, owner from cdb_objects where status='INVALID' order by con_id;
no rows selected

Done!

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/

–Mike