Oracle Warehouse Builder (OWB) Clean Up in Oracle Database 11.2-12.2

Oracle Warehouse Builder (OWB) Clean Up in Oracle Database 11.2-12.2Oracle Warehouse Builder (OWB) clean up in Oracle Database 11.2-12.2 is very simple and straight forward. There’s only one script to execute. And it completes flawless and does not require any workarounds.

Oracle Warehouse Builder (OWB) Clean Up in Oracle Database 11.2-12.2

The Oracle Warehouse Builder actually does come automatically installed until Oracle Database 11.2. OWB is a data warehousing-centered data integration solution. It got replaced by ODI in later released.For more information on OWB click here. You may have to license certain parts of OWB.

Oracle Warehouse Builder (OWB) Clean Up in Oracle Database 11.2-12.2According to Oracle Data Integrator and Oracle Warehouse Builder Statement of Direction (First Published January 2010, Updated May 2011):

No major enhancements are planned for Oracle Warehouse Builder beyond the OWB 11.2 release. OWB 11.2 continues to be available and supported by Oracle, and patches and bug fixes will continue to be offered at regular intervals. Oracle will continue to support OWB 11.2 for the full lifetime of Database 11g and the full lifetime of the next major database release (“Database 12”) in accordance with Oracle’s Lifetime Support Policies for Database releases. Future database releases beyond Database 12 would not be certified with OWB 11.2.

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.

Oracle Database 11.2.0.4

The removal of OWB is very straight forward. The following MOS Note:1074425.1 –  How to remove OWB Objects in 11.1 and higher gives useful advice.

$ sqlplus / as sysdba

@?/owb/UnifiedRepos/clean_owbsys.sql

Not done! Thanks to the feedback by Erik (see below in the comments section). The procedure removes OWB from DBA_REGISTRY but still leaves the package DBMS_OWB in the database.

select object_name, object_type from dba_objects where object_name='DBMS_OWB';

OBJECT_NAME	     OBJECT_TYPE
-------------------- ------------
DBMS_OWB	     PACKAGE
DBMS_OWB	     PACKAGE BODY

Hence, this extra step is necessary to complete the cleanup:

drop package SYS.DBMS_OWB;

Oracle Database 12.1.0.2

In Oracle Database 12.1.0.2 OWB won’t be installed.

But: When you upgrade to Oracle Database 12.1.0.2,  OWB stays VALID but does not get removed as part of the upgrade to 12.1.0.2. It has still VERSION 11.2.0.4 in DBA_REGISTRY. You should remove it from the database.

select status, comp_id, version from dba_registry order by 2

STATUS	     COMP_ID	  VERSION
------------ ------------ ------------
OPTION OFF   AMD	  11.2.0.4.0
VALID	     APEX	  4.2.5.00.08
VALID	     APS	  12.1.0.2.0
VALID	     CATALOG	  12.1.0.2.0
VALID	     CATJAVA	  12.1.0.2.0
VALID	     CATPROC	  12.1.0.2.0
VALID	     CONTEXT	  12.1.0.2.0
VALID	     JAVAVM	  12.1.0.2.0
VALID	     ORDIM	  12.1.0.2.0
VALID	     OWB	  11.2.0.4.0
VALID	     OWM	  12.1.0.2.0
VALID	     SDO	  12.1.0.2.0
VALID	     XDB	  12.1.0.2.0
VALID	     XML	  12.1.0.2.0
VALID	     XOQ	  12.1.0.2.0

Oracle Database 12.2.0.1

In Oracle Database 12.2.0.1 OWB won’t be installed. Once you upgrade to Oracle Database 12.2.0.1 we set OWB to OPTION OFF.

Component Clean Up Series

Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/

 

9 thoughts on “Oracle Warehouse Builder (OWB) Clean Up in Oracle Database 11.2-12.2

  1. Pingback: JAVAVM and XML Clean Up in Oracle Database 11.2-12.2

  2. Pingback: Expression Filter and Rules Manager (EXF, RUL) Clean Up in Oracle 11.2-12.2

  3. Pingback: Oracle Text (CONTEXT) Clean Up in Oracle Database 11.2-12.2

  4. Pingback: Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2

  5. Pingback: Oracle Workspace Manager (OWM) Clean Up in Oracle Database 11.2-12.2

  6. Hey,

    Don’t we need a solution to remove it from the registry? The preupgrade tool is going to complain about it until we do. Also, I think we need to drop dbms_owb to clean up.

    I imagine something like:

    drop package sys.dbms_owb;
    execute sys.dbms_registry.removed(‘OWB’);
    delete from registry$ where cid=’OWB’ and status=’99’;
    commit;

    • Erik,

      in my case it does remove the install from the registry as well.

      But … and you are right, the package isn’t gone. Hence, at least a cleanup of this package is necessary.
      I wonder why nobody has detected this before. It is in the documentation as well as in the MOS note without any extra treatments.

      Hm … does DBA_REGISTRY not get cleaned up in your case?
      For me it did.

      Before:

      SQL> select comp_id from dba_registry;

      COMP_ID
      ——————————
      OWB
      OLS
      XDB
      OWM
      CATALOG
      CATPROC

      Then:
      SQL> @?/owb/UnifiedRepos/clean_owbsys.sql

      After:
      SQL> select comp_id from dba_registry;

      COMP_ID
      ——————————
      OLS
      XDB
      OWM
      CATALOG
      CATPROC

      Cheers,
      Mike

      • Mike,

        Thanks very much for the response. It does indeed get removed from the registry. I should have tested before leaving the comment. The registry is a bit of a mystery to me, I thought that I had to find a call to dbms_registry in order to have it be updated, but I guess just dropping the users and roles does it. My mistake, I was fearful of trying it out.

        The dbms_owb package is the only real loose end, yes. But, that is quite small and shouldn’t be a problem.

        Just as an fyi, before uninstalling my OWB component is still marked as VALID in my 12.1.0.2 databases which were upgraded from 11.2.0.4. They were not marked as OPTION OFF, but do keep the 11.2.0.4 version.

        • Erik,

          you are (unfortunately) right – and thanks for pointing me to this.
          In fact, OWB stays VALID as an option but didn’t get upgraded. I’m not sure if it still would work (I doubt it).

          I will document it.

          1* select status, comp_id, version from dba_registry order by 2

          STATUS COMP_ID VERSION
          ———— ———— ————
          OPTION OFF AMD 11.2.0.4.0
          VALID APEX 4.2.5.00.08
          VALID APS 12.1.0.2.0
          VALID CATALOG 12.1.0.2.0
          VALID CATJAVA 12.1.0.2.0
          VALID CATPROC 12.1.0.2.0
          VALID CONTEXT 12.1.0.2.0
          VALID JAVAVM 12.1.0.2.0
          VALID ORDIM 12.1.0.2.0
          VALID OWB 11.2.0.4.0
          VALID OWM 12.1.0.2.0
          VALID SDO 12.1.0.2.0
          VALID XDB 12.1.0.2.0
          VALID XML 12.1.0.2.0
          VALID XOQ 12.1.0.2.0

          Cheers,
          Mike

Leave a Reply

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

* Checkbox to comply with GDPR is required

*

I agree

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