Uninstalling OWM from a Multitenant container database

Caution

Let me put a warning first: The intention of this blog post is NOT to recommend to uninstall anything just “because”. It is meant only as a guidance and explanation how to uninstall a component in an Oracle Multitenant container database deployment correctly without braking anything. And as this is not documented yet clearly, I bring up this blog post.

Oracle Workspace Manager

Workspace Tree

Oracle Workspace Manager (OWM) is a pretty cool feature of the Oracle Database. It does not require an extra license.

OWM is meant to enable application developers and DBAs to manage current, proposed and historical versions of data in the same database. It has even its own manual with 350 pages. And I know at least two very large customers who are using the OWM on a regular basis to optimize track routing and such.

OWM Creation

That is the interesting part – OWM does not get created in the process of database creation where all the other options and components get created (such as JAVA, Spatial, APEX etc). It gets created in the phase of the “create catalog” part. When you ask the DBCA (Database Configuration Assistant) to generate scripts for database creation you’ll find the OWM being created in the CreateDBCatalog.sql, and not in the <SID>.sql where all the other components get created. A while back I tried to find out why this has been moved – and why it never got moved back. There may have been an issue in the past – but there was no evidence that OWM couldn’t be created in the <SID>.sql phase nowadays as well.

See the owminstl.plb script in the CreateDBCatalog.sql for a non-CDB below:

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /home/oracle/CreateDBCatalog.log append
@/u01/app/oracle/product/12.2.0.1/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/12.2.0.1/rdbms/admin/catproc.sql;
@/u01/app/oracle/product/12.2.0.1/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/12.2.0.1/rdbms/admin/owminst.plb;
connect "SYSTEM"/"&&systemPassword"
@/u01/app/oracle/product/12.2.0.1/sqlplus/admin/pupbld.sql;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /home/oracle/sqlPlusHelp.log append
@/u01/app/oracle/product/12.2.0.1/sqlplus/admin/help/hlpbld.sql helpus.sql;
spool off
spool off

And here, for comparison, just an excerpt from a CreateDBCatalog.sql for a CDB:

...
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/HUGO121/scripts -b catalog
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catalog.sql;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/HUGO121/scripts -b catproc
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catproc.sql;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/HUGO121/scripts -b catoctk
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catoctk.sql;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n
1 -l /u01/app/oracle/admin/HUGO121/scripts -b owminst
/u01/app/oracle/product/12.1.0.2/rdbms/admin/owminst.plb;
host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n 1
-l /u01/app/oracle/admin/HUGO121/scripts -b pupbld -u
...

The conceptual sequence of having OWM not created as part of the DB options leads to the fact that “unclicking” OWM in the DBCA is not possible as the option simply does not exist.

Not-Installing or Removing OWM

There are actually two options to have no OWM in your database:

1. Create the DB-create-scripts with DBCA and uncomment OWM
2. Remove OWM post database creation

What is the best option? I leave it up to you. Uninstalling OWM is no magic rocket science thing. It’s a simple script which does neither require a restart or a recompilation. And it works flawless with all versions of the Oracle database I tested it with – back since Oracle 10.1.0.5.

Option 1 – Uncomment OWM

This is simple – just add -- before the call in the scripts. For a non-CDB use:

-- @/u01/app/oracle/product/12.2.0.1/rdbms/admin/owminst.plb;

and for a CDB container database use:

-- host perl
/u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -n
1 -l /u01/app/oracle/admin/HUGO122/scripts -b owminst
/u01/app/oracle/product/12.1.0.2/rdbms/admin/owminst.plb;

to prevent OWM creation.

Option 2 – Remove OWM

This isn’t complicated either. Remove OWM from a non-CDB with:

-- Uninstalling OWM - Workspace Manager
-- from a non-CDB database

@?/rdbms/admin/owmuinst.plb

and from a CDB container database you’ll have to use catcon.pl to ensure it gets removed from the CDB$ROOT, the PDB$SEED and all PDBs.

-- Uninstalling OWM - Workspace Manager
-- from a CDB container database
--
-- Remove it from all PDBs first
-- Then remove it from CDB$ROOT as otherwise you'll receive:
--    ERROR at line 1:ORA-06598: insufficient INHERIT PRIVILEGES privilege
--    ORA-06512: at "WMSYS.LT", line 1
--    ORA-06512: at line 1
--    ORA-06512: at line 54ORA-06512: at line 54
--    ORA-06512: at line 91
--

alter pluggable database all open;
host $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/12.1.0.2/rdbms/admin/catcon.pl -r -n 1 -l /tmp -b uninstall_ovm /u01/app/oracle/product/12.1.0.2/rdbms/admin/owmuinst.plb;

That’s it.

More Information

• OTN Page: Oracle Workspace Manager
• Oracle Workspace Manager Developer’s Guide 12.2

–Mike