Oracle® Warehouse Builder

4.1 Upgrading from Oracle Warehouse Builder 11g Release 2 (11.2.0.1 or 11.2.0.2) to Release 11.2.0.4: Oracle Warehouse Builder Server-Side Patch

On all Oracle Database 11g platforms, Oracle Warehouse Builder 11g server-side functionality is an integral part of Oracle Database 11g database server. Before any user upgrade the Database installation in-place that is, reusing existing OWB_HOME for new Oracle Database installation, users must back up the OWB_HOME/owb/bin/admin directory. Consult the Oracle Database 11g Release 2 (11.2.0.4) documentation to upgrade an existing Oracle Database 11.2.0.1 or 11.2.0.2 to Release 11.2.0.4.

After you perform the steps required to upgrade to Oracle Database 11.2.0.4, perform the postinstallation steps required for Oracle Warehouse Builder 11g Release 2 (11.2.0.4) as described in "Postinstallation Steps for Oracle Warehouse Builder 11g Release 2 (11.2.0.4)".

4.2 Upgrading from Oracle Warehouse Builder Release 11.2.0.3 to Release 11.2.0.4: Oracle Warehouse Builder Server-Side Patch

Go to "Upgrading from Oracle Warehouse Builder 11g Release 2 (11.2.0.1 or 11.2.0.2) to Release 11.2.0.4: Oracle Warehouse Builder Server-Side Patch" and follow the steps. Skip step 5 mentioned in the "Inplace Upgrade (Reuse Existing OWB_HOME)" and "Out of Place Upgrade of New OWB_HOME", the remaining steps are the same.

4.3 Upgrading from Oracle Warehouse Builder 11g Release 1 (11.2.0.1, 11.2.02, or 11.2.0.3) to Release 11.2.0.4: Oracle Warehouse Builder Client-Side Patch

Oracle Warehouse Builder 11g Release 2 (11.2.0.1, 11.2.0.2, and 11.2.0.3) client-side functionality is available only on two platforms, Windows 32-bit and Linux x86.

To patch Oracle Warehouse Builder 11.2.0.1, 11.2.0.2, and 11.2.0.3 client-side to Release 11.2.0.4, you must apply the Oracle Database 11.2.0.4 patch set to Oracle Warehouse Builder 11.2.0.1, 11.2.0.2, and 11.2.0.3 Oracle home.

6.1 In-place Migration Steps on the Server-Side

If the Oracle Warehouse Builder release 11.2.0.4 runtime was initially installed on Oracle Database release 10.2 or 11.1 instance, and that database instance is migrated to Oracle Database release 12.1, the following steps must be followed. If the migrated database was 11.2, skip to step 4.

1. Run the following SQL statement to identify the ORACLE_HOME for the current installation. The rtrepos.properties file is located in the path indicated by SERVER_SIDE_HOME.

   sqlplus OWBSYS/OWBSYS_PASSWORD
       SELECT host_name, server_side_home from wb_rt_service_nodes s, v$instance v where s.instance_number=v.instance_number;
   HOST_NAME SERVER_SIDE_HOME yourhost_machine 
   ORACLE_HOME
   

2. Remove the ORACLE_HOME/owb/bin/admin/rtrepos.properties file.

   rm ORACLE_HOME/owb/bin/admin/rtrepos.properties
   

3. Run this step as the OWBSYS user to reset the repository password. Enter the password when prompted for a value.

   sqlplus OWBSYS/OWBSYS_PASSWORD
                 @ORACLE_HOME/owb/rtp/sql/reset_repository.sql OWBSYS_PASSWORD
                 SELECT host_name, server_side_home from wb_rt_service_nodes s, v$instance v where s.instance_number=v.instance_number;
   

   For Oracle RAC systems, after the reset_repository script displays the host and home where the ORACLE_HOME/owb/bin/admin/rtrepos.properties file is referenced and updated, copy it to all other nodes in the Oracle RAC cluster.

   The reset_repository forces the user to register all locations again using the Control Center Manager.

4. Modify all the design-time and runtime locations that are converted to Oracle Database release 12.1 by following these steps:

   1. Log into SQL*Plus as OWBSYS user.

   2. Set the Oracle Warehouse Builder workspace.

   3. Fix the release version on every location.

   Example 1 Modify Design-Time Location

   sqlplus OWBSYS/OWBSYS_PASSWORD
                set serveroutput on
                set serveroutput on size 100000
                exec wb_workspace_management.set_workspace('OWB_WKSP','OWB_REPO');  
   

   Where OWB_WKSP is the installed workspace, and OWB_REPO is the installed 11.2.0.4 repository.

   Now, for every design location that is converted to an Oracle Database release 12.1, copy the following block into the SQL*Plus session substituting the design time location name:

   declare
                  v_result boolean := FALSE;
                Begin
                  v_result := wb_rt_reset_location.fixDTLocation('ORACLE_DESIGN_TIME_LOCATION','','','','12','','');
                END;
                /
   

   Example 2 Modify Runtime Location

   sqlplus OWBSYS/OWBSYS_PASSWORD
                set serveroutput on
                set serveroutput on size 100000
                exec wb_workspace_management.set_workspace('OWB_WKSP','OWB_REPO');  
   

   Where OWB_WKSP is the installed workspace, and OWB_REPO is the installed 11.2.0.4 repository.

   Now, for every runtime location that is converted to an Oracle Database release 12.1, copy the following block into the SQL*Plus session substituting the design time location name:

   declare
                  v_result boolean := FALSE;
                Begin
                  v_result := wb_rt_reset_location.fixDTLocation('ORACLE_RUN_TIME_LOCATION','','','','12','','');
                END;
                /
   

   Refer to My Oracle Support Note 1526360.1 to upgrade and verify status of all locations in the Warehouse Builder Repository Browser.

6.2 Oracle Warehouse Builder on a Pluggable Database

Oracle Warehouse Builder on a pluggable database operates the same as a multi-instance or an OWB_HOME configuration. ODI-EE license is required to use this functionality.

Initial Installation

The following are the key points:

• For a given multitenant container database (CDB), there can be 1..n pluggable databases (PDB). Each of these PDBs exist on their own and have their own service name.

• Every PDB runs its own runtime service, and requires its own properties files and log directories.

• Connection to the PDB instance is in host:port:pdb_name (not the multitenant container database)

• Each PDB must be configured before installing the next database.

  Refer to My Oracle Support Note 1332043.1 to install the Oracle Warehouse Repository within a PDB instance.

  To configure the multi-instance, perform the following steps:

  1. Stop the Oracle Warehouse Builder runtime server.

     sqlplus OWBSYS/OWBSYS_PASSWORD@pdb_name
                 @?/owb/rtp/sql/stop_service.sql
                 @?/owb/rtp/sql/setup_rt_for_multi_instances.sql pdb_name
     

  2. Start the Oracle Warehouse Builder runtime server.

     sqlplus OWBSYS/OWBSYS_PASSWORD@pdb_name
                 @?/owb/rtp/sql/start_service.sql
     

  3. Install workflow in the PDB instance (if required)

• Before installing workflow on Oracle Database release 12.1, make the following change:

  Modify $ORACLE_HOME/network/admin/sqlnet.ora
                  # Add the following line
                  SQLNET.ALLOWED_LOGON_VERSION = 8
  

  Remove this value from the sqlnet.ora file when the workflow schema installation completes.

Detaching PDB from a Multitenant Container Database

The following are the key points:

• In order to detach a PDB from a multitenant container database, firstly, shut down all the Oracle Warehouse Builder processes using that PDB instance.

• Close any running Oracle Warehouse Builder products.

• Stop the Runtime Service using the SQL*PLUS script, stop_service.sql:

  sqlplus OWBSYS/OWBSYS_PASSWORD@pdb_name
              @?/owb/rtp/sql/stop_service.sql
  

• Use Oracle Database Configuration Assistant to manage pluggable databases. unplugg PDB from the PDB archive, and create pdb_file:

  pdb_name.tar.gz 
  

• Back up the $ORACLE_HOME/owb/bin/admin/pdb_name directory. It contains the following files:

  rtrepos.properties
  Runtime.properties
  

Connecting PDB to a CDB

This process is similar to the process defined in My Oracle Support Note 434272.1 to update Oracle Warehouse Builder after a database cloning. The steps depend upon the following two cases:

• Case1: PDB name and connection details remain the same during the import into a CDB

  1. Using Oracle Database Configuration Assistant, manage pluggable databases, create a pluggable database from PDB archive, follow the steps to import the file created by unplugging the PDB instance from the CDB.

     pdb_name.tar.gz 
     

  2. Use the same name as the original PDB for the Pluggable Database Name.

  3. When the import completes, create the $ORACLE_HOME/owb/bin/admin/pdb_name directory and move the following files into the directory:

     rtrepos.properties
     Runtime.properties
     

  4. Start the runtime service using start_service.sql:

     sqlplus OWBSYS/OWBSYS_PASSWORD@pdb_name
                 @?/owb/rtp/sql/start_service.sql
     

• Case 2: PDB name or host name changes during the import into a CDB

  1. Using Oracle Database Configuration Assistant, manage pluggable databases, create a pluggable database from PDB archive, follow the steps to import the file created by unplugging the PDB instance from the CDB.

     old_pdb_name.tar.gz 
     

  2. Create a new name for the Pluggable Database Name - new_pdb_name.

  3. When the import completes, create the $ORACLE_HOME/owb/bin/admin/old_pdb_name directory and move the following files into the directory:

     rtrepos.properties
     Runtime.properties
     

  4. The process to reset the rtrepos.properties file is as follows

     sqlplus OWBSYS/OWBSYS_PASSWORD@new_pdb_name
     

     - Load the wb_rt_reset_location/wb_location_helper packages:

     @?/owb/rtasst/wb_rt_reset_location.pls
     @?/owb/rtasst/wb_rt_reset_location.plb
     @?/owb/rtasst/wb_location_helper.pls
     @?/owb/rtasst/wb_location_helper.plb
     

     - Reset the multi-instance:

     @?/owb/rtp/sql/rollback_setup_rt_for_multi_instances.sql
     

     - If the runtime is contained in this new PDB instance, do the following:

     select node_id, instance_number, connect_spec, enabled, runtime_version, server_side_home from wb_rt_service_nodes;
     

     Update the appropriate row using the node_id:

     declare
               v_result number := 0;
               Begin
               v_result := wb_location_helper.fixRTServiceNode(NODE_ID,'HOST','PORT','SERVICE_NAME','SERVER_SIDE_HOME');
               END;
               /
     

     - Create a new rtrepos.properties file for this PDB instance:

     @?/owb/rtp/sql/reset_repository.sql OWBSYS_Password
     

     - Configure Oracle Warehouse Builder for multi-instance:

     @?/owb/rtp/sql/setup_rt_for_multi_instances.sql new_pdb_name
       
               commit;
               exit
     

  5. Follow the steps to modify the Design time location and Runtime location as described in "Modify the design time locations:" and "Modify the runtime locations:".

  6. Follow steps 5 and 6.

10.1 Control Center

9777682: CONTROL CENTER SERVICE MAY FAIL TO START WITH ORA-29532 ERROR ON AIX ON POWER (64-BIT)

On IBM AIX on POWER Systems (64-bit), Oracle Warehouse Builder Control Center Service may fail to start with the error ORA-29532 when started from within the Oracle Database (for example, using start_service.sql or the automatic startup from check_service job).

Workaround: Edit the run_service.sh file. Before the line starting java, add the following lines of code:

MPROTECT_TXT=OFF
export MPROTECT_TXT

Save the run_service.sh file. Restart the Control Center service.

For more information about restarting the Control Center service, see the "Managing the Control Center and Repository" chapter in Oracle Warehouse Builder Installation and Administration Guide.

This is not an issue in Oracle Warehouse Builder release 11.2.0.4.

10.2 Design Center Client

8596439: Oracle Warehouse Builder ONLINE HELP LINKS FROM DESIGN CENTER START PAGE NOT WORKING

In Oracle Warehouse Builder integrated installation that is included with Oracle Database 11.2, links from the Start Pages in the Design Center to Oracle Warehouse Builder online help does not function properly. This problem does not affect Oracle Warehouse Builder standalone installation.

Workaround: To open the online help from the Start Page, temporarily rename or delete the file OWB_HOME/jlib/oracle_ice.jar.

This is not an issue in Oracle Warehouse Builder release 11.2.0.1 or later.

8661211: OWB DESIGN CENTER WARNING: UNKNOWN DIRECTIVE: SETSKIPJ2SDKCHECK

When you start Oracle Warehouse Builder Design Center on Windows, you may notice the following warning in the command window that is displayed while starting the Design Center:

WARNING: Unknown directive: SetSkipJ2SDKCheck

Workaround: You can ignore this warning and proceed with logging in to the Design Center.

This is not an issue in Oracle Warehouse Builder release 11.2.0.1 or later.

10.3 Languages and Translations

5031270: NAMES OF SEEDED OBJECTS IN THE REPOSITORY ARE NOT TRANSLATED

Seeded objects in the repository are not translated. These include, for example, the names of predefined transformations as displayed under the Public Transformations node of the Globals Navigator. This also includes the default project named MY_PROJECT, the default Control Center named DEFAULT_CONTROL_CENTER, the folder called DERIVED_DATA_RULES that contains derived data rules, and the default configuration called DEFAULT_CONFIGURATION.

3066632: ORACLE WAREHOUSE BUILDER FLAT FILE SAMPLE WIZARD COUNTS A MULTIBYTE CHARACTER LENGTH AS 1

When translated into Japanese, Oracle Warehouse Builder Flat File Sample Wizard does not handle multibyte characters in delimited files correctly. The wizard handles a multibyte character in delimited files as 1 length.

Workaround: Manually edit the length using the Flat File Sample Wizard.

10.4 Mapping Designs and Configurations

This section describes mapping designs, configurations, and limitations of database links.

Limitations on Using Database Links Between Oracle Database Versions

When Oracle Warehouse Builder mapping that uses database links for data movement between Oracle Databases is executed on a given target database, connectivity between database versions are limited by compatibility supported for database links between the source and target database. For example, Oracle Warehouse Builder mapping running on Oracle Database 11.2 cannot access data on Oracle Database 10.1 over a database link. For a compatibility matrix that details support for database links between database versions, see note 207303.1 on My Oracle Support.

6050580: MAPPINGS WITH TIMESTAMP DATA MAY BEHAVE INCORRECTLY

Under some conditions, you may encounter unexpected behavior when running a mapping that loads and transforms timestamp data. For example, if you deploy and run a mapping that transforms a column from TIMESTAMP type to VARCHAR2, the resulting data in the target column may be incorrect.

Workaround: This behavior does not arise when using the Mapping Debugger. Run the mapping and load the data using the Mapping Debugger.

9882655: DEFAULT EXECUTION UNIT FOR CODE TEMPLATE (CT) MAPPINGS MAY NOT ALWAYS BE VALID

Oracle Warehouse Builder assigns default execution units to a Code Template (CT) mapping in the following circumstances:

• When the user selects Default Execution Units in the execution view of the Mapping Editor

• When validating or generating code for a CT mapping with no assigned execution units

• When importing a CT mapping with no assigned execution units from MDL

You should always review automatically generated default execution units for CT mappings, to ensure that processing and data are distributed optimally among locations. For some complex mappings, Oracle Warehouse Builder may generate default execution units that fail to validate. In such cases, use the Execution View to assign operators to execution units manually.

9830333: ERROR PLS-00305 WHEN DEPLOYING MAPPINGS WITH A REPOSITORY HOSTED ON ORACLE DATABASE 10.2

When hosting Oracle Warehouse Builder repository in Oracle Database 10.2, and using Table operators with long physical names in a mapping, Oracle Warehouse Builder may generate PL/SQL code containing types with non-unique names. In such cases, deployment fails with error PLS-00305.

Workaround: Assign shorter names to the Table operators.

This is not an issue in Oracle Warehouse Builder release 11.2.0.3 or later.

10.5 Non-Oracle Database Connectivity

To connect to non-Oracle databases, you need the JDBC driver corresponding to that database. Non-Oracle JDBC drivers are not shipped as part of Oracle Warehouse Builder software. You can obtain the JDBC drivers from the vendor and copy them to the OWB_HOME/owb/lib/ext folder.

Table 1 lists the drivers that are required to connect to DB2, SQL Server, and Teradata. For other databases, consult your vendor.

10.6 OMB*Plus Scripts

7135670: OMBPLUS CANNOT LOAD SCRIPTS CONTAINING NON-ASCII CHARACTERS

If you use OMB*Plus to run a Tcl script containing non-ASCII characters, the characters may be corrupted in OMB*Plus. Scripts with non-ASCII NLS characters may not execute successfully. There is no workaround.

10.7 Oracle Warehouse Builder Connector for SAP

5215322: ORACLE WAREHOUSE BUILDER CONNECTOR TO SAP R/3 REQUIRES SAP LIBRARY FILE

Workaround: To access SAP R/3 in the Design Center, first obtain the SAP RFC library from SAP and copy it to the OWB_CLIENT_HOME/owb/bin/admin directory.

To access SAP R/3 in the Control Center, first obtain the SAP RFC library from SAP and copy it to the OWB_SERVER_HOME/owb/bin/admin directory. OWB_SERVER_HOME is the directory where Oracle Warehouse Builder Control Center Service is installed.

10.8 Oracle Warehouse Builder on Exadata

Using Oracle Warehouse Builder 11g Release 2 in an Exadata environment requires additional setup steps. Contact Oracle Support for details.

10.9 Repository Browser

5129228: MULTIPLE REPOSITORY BROWSER SESSIONS FROM A SINGLE CLIENT MACHINE NOT SUPPORTED

If you start multiple Repository Browser sessions from a single Internet browser on a single client computer, you may encounter unexpected results. This scenario is not supported. As a consequence, you cannot browse multiple repositories with a single Internet browser on a single computer.

Workaround: Each Repository Browser session requires its own Internet cookies storage. If feasible, you can start multiple, different Internet browsers to maintain separate Repository Browser sessions on the same computer.

10.10 Repository Upgrades and Migrations from Oracle Warehouse Builder 11.1 or Earlier

9835013: ARRAYINDEXOUTOFBOUNDSEXCEPTION DURING MDL IMPORT

When importing MDL files, you may encounter the following errors:

• Trying to access invalid Object

• MDL1261: Error importing OWBAssoc_CopyOfMDW

Workaround: You can use the following workarounds:

• Increase the heap size when running the Repository Assistant

  On Windows, edit the OWB_HOME\owb\bin\win32\reposinst.bat file. On UNIX, edit OWB_HOME/owb/bin/unix/reposinst.sh. Increase the value set for the -Xmx flag.

• Increase the heap size when running the Design Center client

  Edit the OWB_HOME/bin/owb.conf file and increase the value set for the -Xmx flag in the AddVMOption.

Oracle Warehouse Builder 11.2 Repository Must Contain a Workspace Before Upgrade of Oracle Warehouse Builder 10.2 or 11.1 Repository MDL Export

If you are upgrading Oracle Warehouse Builder 10.2 or 11.1 repository to Oracle Warehouse Builder 11.2 by exporting the entire repository to MDL with the Repository Assistant, then importing it into Oracle Warehouse Builder 11.2, then your destination Oracle Warehouse Builder 11.2 repository must have at least one Oracle Warehouse Builder 11.2 workspace in it before you can import the MDL.

If your Oracle Warehouse Builder 11.2 repository does not contain a workspace before import, then you must create a dummy workspace, with a name that does not conflict with the names of any workspaces to be imported.

Follow the instructions in Oracle Warehouse Builder Installation and Administration Guide to create a workspace. At step 6 in the Repository Assistant, assign a workspace name and workspace owner that do not conflict with workspaces and workspace owners you actually plan to use. If importing an Oracle Warehouse Builder 10.2 repository, the workspace name should be the name of the source schema in which the repository was stored in Oracle Warehouse Builder 10.2.

You can then run Repository Assistant again to import your Oracle Warehouse Builder 10.2 or 11.1 repository export MDL file to your Oracle Warehouse Builder 11.2 repository.

Patch Addressing Multiple Repository Upgrade Issues

Before upgrading or migrating repository contents from a previous release, review Note 1074813.1 on My Oracle Support, and also the white paper "Master Note for Oracle Warehouse Builder Upgrade To 11.2" for details of migration options and processes, and known issues that may affect the migration process.

Customers using Oracle Warehouse Builder 11.2.0.1 should consider applying patch 9802120, which resolves all known upgrade and migration-related issues as of the date of its release. See My Oracle Support for more details.

https://support.oracle.com

10.11 Software and Repository Upgrade Issues

9970120: UPGRADE TABLE DEPLOYMENTS MAY FAIL WHEN USING CONTROL CENTER SERVICE ON DOUBLE-BYTE CHARACTER SET PLATFORMS

When running the Control Center Service on a platform with double byte character sets, an Upgrade deployment of a table may fail. In this situation, Oracle Warehouse Builder generates an invalid upgrade script.

Workaround: Force the Control Center Service to run in English by adding a JVM option to run_service.sh (on Linux) or run_service.bat (on Windows) and then restarting the Control Center Service.

On UNIX, edit the OWB_HOME/owb/bin/unix/run_service.sh file and replace the entry set JVM_OPTIONS=-Xmx768M with the following:

set JVM_OPTIONS="-Xmx768M -Duser.language=en"

On Windows, edit the OWB_HOME\owb\bin\win32\run_service.bat file and replace the entry set JVM_OPTIONS=-Xmx768M with the following:

set JVM_OPTIONS=-Xmx768M -Duser.language=en

Updating Client-Side Preference.Properties Before Upgrading Repository to Oracle Warehouse Builder 11.2.0.2

Using Oracle Warehouse Builder Design Center with an Oracle Warehouse Builder repository of a different patch level can cause repository corruption. The Design Center can check for inconsistent repository versions at connect time. To enable this check, edit OWB_CLIENT_HOME/owb/bin/admin/Preference.properties on each client installation, and update the OverrideRuntimeVersionCheck and OverrideRepositoryVersionCheck preferences as follows:

OverrideRuntimeVersionCheck=false

OverrideRepositoryVersionCheck=false

This change must be applied manually to all Oracle Warehouse Builder 11.2.0.1 Design Center installations.

Applying Oracle Warehouse Builder 11.2.0.2 patch to the Design Center host also updates these preferences.

Large Oracle Warehouse Builder Repository Upgrades May Appear to Hang at 96%

When upgrading or importing a very large older repository into Oracle Warehouse Builder 11.2, the import process may appear to hang around 96% complete. The import process can take several hours or more on large files, and shows a 96% complete status for much of this period before completing successfully. Do not interrupt the import process if it appears to still be running.

This is not an issue in Oracle Warehouse Builder release 11.2.0.3 or later.

10.12 Locale Builder Issue

12699232: Locale Builder in Standalone Version of Oracle Warehouse Builder Does Not Work

After the installation of the standalone version of Oracle Warehouse Builder, the Locale Builder does not work. The following error is displayed when you run OWB_HOME\bin\lbuilder.bat.

Exception in thread "main" java.lang.NoClassDefFoundError:
ice/pilots/html4/DDocument 
at java.lang.Class.forName0(Native Method) 
at java.lang.Class.forName(Class.java:164) 
at oracle.help.Help.<init>(Unknown Source)
at oracle.help.Help.<init>(Unknown Source)
at DataDef.<init>(DataDef.java:218)
at DataDef.main(DataDef.java:3238)

Workaround: On Windows, update the OWB_HOME\bin\lbuilder.bat file to use OWB_HOME/jlib/oracle_ice5.jar instead of the original file.

On UNIX, update the OWB_HOME/bin/lbuilder file to use OWB_HOME/jlib/oracle_ice5.jar instead of the original file.

10.13 Oracle Workflow Supports Latest JDBC Driver from Release 10.2

10325293: The installation of Oracle Workflow with SQLNET.Allowed_Logon_Version=11 in SQLNET.ORA file, displays an error.

Workaround: Oracle Workflow does not support the use of the new JDBC driver ojdbc5.jar or later. The driver ojdbc14.jar should be downloaded and replaced within OWB_HOME/owb/wf/lib directory.

10.14 Existence of an Object in the Schema With the Same Name as the Target Schema in the Database

12776878: If an object exists in the schema with the same name as the target schema in the database, then an error is displayed.

For example:

Schema named A has an object called Q. Schema named Q exists and has an object called Z.

When you call SchemaQ.ObjectZ from Schema A, it looks within Schema A for Q.Z. Z is not found and displays the following error:

ORA-06550: ..: PLS-00302: component 'XYZ' must be declared

Workaround: Rename one of the Q objects to R or some letter other than Q.