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.
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
For anyone who is having the following 19c upgrade issues, you can fix them by following the advice above and first removing oracle multimedia.
ORA-04067: not executed, package body “MDSYS.MDPRVT_IDX” does not exist ORA-
06512: at line 206
ORA-06508: PL/SQL: could not find program unit being called:
“MDSYS.MDPRVT_IDX”
ORA-06512: at “MDSYS.SDO_INDEX_METADATA_UPDATE”, line 4
ORA-0
4088: error during execution of trigger ‘MDSYS.SDO_INDEX_METADATA_UPDATE’
ORA-06
512: at line 199
As usual oracle support was useless and I had to work this out myself. If your getting the above error, drop oracle multimedia as explained in this article and your upgrade will work. If you have apex installed don’t worry it will continue working after the upgrade.
Hope this helps someone out we wasted a lot of time with oracle support trying to fix this.
Aleks,
would you mind to share the SR number with me – either here on the blog or via email to mike.dietrich //—– at —–// oracle.com.
I’m curious …
Thanks,
Mike
I don’t mind at all:
3-22446068331
This is pretty much my standard experience when dealing with oracle. I can’t remember the last time they fixed something for me. Anything that’s slightly complicated I end up fixing myself or we end up with no solution whatsoever. I’ve even had some SR’s that are years old! If it wasn’t for the availability of patching I’m pretty sure I could get my company to stop paying for support as we really don’t get much value from it apart from the PSU’s.
Regards,
Aleks
Hi Aleks,
I will check it once I’m back from vacation – I’m really curious.
Cheers,
Mike
Wow! Thanks Aleks. i just tried upgrading a sandbox database (12.2.0.1 to 19c) using AutoUpgrade version 19.9.1 and I got the same error you mentioned. Although it was a sandbox environment, I wanted to test it with some data and imported the HERE Sample Data for Oracle Analytics (https://www.oracle.com/database/technologies/spatial-graph-here-data-downloads.html). The AutoUpgrade log file shows ORDIM as being the culprit:
autoupgrade_20200721_user.log contains the following:
2020-07-21 22:18:35.561 ERROR
DATABASE NAME: DB12201
CAUSE: ERROR at Line 871197 in [/dump/autoupgrade_logs/DB12201/DB12201/101/dbupgrade/catupgrd20200721213306db122010.log]
REASON: ORA-04088: error during execution of trigger ‘MDSYS.SDO_INDEX_METADATA_UPDATE’
ACTION: [MANUAL]
DETAILS: 04088, 00000, “error during execution of trigger ‘%s.%s'”
// *Cause: A runtime error occurred during execution of a trigger.
// *Action: Check the triggers which were involved in the operation.
and dbupgrade/catupgrd20200721213306db122010.log contains the following:
Identifier ORDIM 20-07-21 10:10:52
SCRIPT = [/u01/app/oracle/product/19.0.0/db_db12201/md/admin/prvtimd.plb]
ERROR = [ORA-06512: at “MDSYS.SDO_INDEX_METADATA_UPDATE”, line 4 ORA-04088: error during execution of trigger ‘MDSYS.SDO_INDEX_METADATA_UPDATE’
ORA-06512: at line 199
]
STATEMENT = [as above]
@Mike, since Multimedia and Streams are deprecated in 19c, does AutoUpgrade check for them during -analyze operation? When I ran autoupgrade with -analyze it didn’t give me any errors or warning about this possible issue. I can package the autoupgrade error log folder as well as DB’s alert log and send it to you if that helps.
Hi Parsa,
it will not check for Streams – but you will receive a message regarding ORDIM. But it will only check whether the component is VALID – nothing beyond that.
Cheers,
Mike
Hi Mike,
about “the dependency between ORDIM and SDO (Spatial Data Option) has been removed with Oracle 19c”.
The oracle Support tells me other things.
See SR 3-24741925001 : Getting – ORA-38824 after apply patch 30403177 (RU 19.6)
Best regards
Hans-Peter
Hi Hans-Peter,
not sure if I can follow.
Bug 30403177 is a special fix to enable the creation of partitioned tables where no partitioning is enabled/used.
I suspect that this is more a packaging problem of the patch itself.
At least, the “dependency is gone” has been told by the development owners.
Cheers, and sorry for all the inconvenience 🙁
Mike
Mike, Thanks for your inputs. We have an automated provisioning of 19c DB and doing ORADIM cleanup in the case of a completely new server and CDB including PDB. But when we do in the case of adding a new CDB in the same home which already has a CDB, existing CDB prompts with errors as binary mismatch.
Any suggestions?
No – you please need to open an SR and check with Oracle Support.
Cheers,
Mike
Going through the procedure to remove ORDIM from a 19c database, really curious, what is the -n option in catcon.pl? I can’t seem to find it in the docs nor does it show in the usage if you run the -help option of catcon.pl.
Hi,
this is currently undocumented for no reason – it simply describes the “number of workers”. When you use “-n 1”, then catcon.pl will use only one worker. This makes sense when you start utlrp.sql which parallelizes already by itself due to the script’s architecture. Hence, asking catcon.pl to use more workers makes no sense. And this is when I use “-n 1”.
I filed a doc bug a few weeks ago, and the -n option may appear soon in the docs, too.
Sorry for the inconvenience – cheers,
Mike
Is there a reason why for 12.1.0.2 non-CDB you follow a procedure that’s different form the one in this oracle note
“Removing Oracle Multimedia from 12c and higher installations (Doc ID 2544494.1)”
that advices to use
$ORACLE_HOME/ord/im/admin/imremdo.sql
instead of the same 11.2 command you report in here for 12.1 too?
Thanks
Hi,
please see what I wrote in the blog post:
“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.”
Cheers,
Mike
Hi Mike
During the windows to Linux Oracle 12cr1 migration, We saw a issue on one of the table, post migration few columns got dropped
All those columns are associated to Multimedia type
Missing columns
=====================================
IMAGE ORDSYS.ORDIMAGE
ARCHIVE ORDSYS.ORDSOURCE
VIDEO ORDSYS.ORDVIDEO
AUDIO ORDSYS.ORDAUDIO
UNKNOWN ORDSYS.ORDSOURCE
After creating an SR we came to know that columns was dropped because
we unistalled and installed the Multimedia components when we saw multimedia components was in invalid state.
My question is
Components getting invalid is normal scenario during any migration or upgrades.
Do we have any workarounds for making the multimedia components valid without uninstalling and installing it?
and how do we get rid of such issues without impacting those columns
Hi Praveen,
it is not normal that a components becomes INVALID during an upgrade or migration.
And the normal way to fix this is to run utlrp.sql as it has a component validation routine.
If a component then stays invalid, it has to be investigated. But I wouldn’t drop or remove it right away unless I’m fully certain that nobody is using the component.
Cheers,
Mike
Thanks you so much for your prompt & quick response.
Welcome 🙂
Hello Mike,
We removed Multimedia prior to upgrade from 12.2 to 19.3. ORD_ADMIN and ORDIMDPCALLOUTS showing as invalid after upgrade. Your note above says it is not needed to drop ORD_ADMIN in 19c. Since it is showing as invalid, any concerns if we drop it or any other recommendations. This is non-CDB install.
I’d assume that there shouldn’t be any issue.
Cheers,
Mike
Hi Mike,
Just a quick (and yet another) thanks for these blog posts.
After a 11.2=>19c FTEX upgrade, found that ORDIMDPCALLOUTS and ORD_ADMIN were invalid, got scared, Googled, found your post very quickly, tidied up.
Thanks again for saving us all time trawling through endless MOS notes.
Regards,
Jes
Thanks Jes!
Cheers
Mike