Skip Headers
Oracle® Warehouse Builder Installation and Administration Guide
11g Release 2 (11.2) for Windows and Linux

Part Number E17130-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 Moving to Oracle Warehouse Builder 11.2 from Earlier Releases

Oracle Warehouse Builder enables you to upgrade repositories from Oracle Warehouse Builder 10g Release 2 and Oracle Warehouse Builder 11g Release 1 to Oracle Warehouse Builder 11g Release 2.

This section describes the different methods of moving to OWB release 11.2 from earlier releases, the upgrade and migration paths and upgrade process, and changes to upgraded ETL design objects to make them compatible with OWB release 11.2.

This section includes the following topics:

Understanding MDL Migration and Repository Upgrades

The two ways to move projects from earlier releases into OWB release 11.2 are migrating objects and upgrading repositories.

This discussion uses the following definitions:

Choosing Between MDL Migration and Repository Upgrade

The Repository Assistant in OWB release 11.2 provides ways to upgrade OWB release 10.2 and release 11.1 repositories directly. The advantages of upgrading an entire repository are:

  • You can move up to OWB release 11.2 without regenerating code or redeploying your mappings, for maximum stability, then decide on objects to regenerate or redeploy based on whether you need specific release 11.2 enhancements.

  • Upgrading can bring forward all design-time and runtime audit metadata from the previous repository, such as the history of deployments and executions of your ETL mappings, process flows, and other objects.

  • All projects and global objects in a repository are processed at once. (For OWB 11gR1 repositories, all projects in all workspaces are processed at once.)

The advantages of migrating individual projects or other objects include:

  • Speed, if you have large volumes of runtime metadata that you do not need to preserve.

  • Simplicity, in cases where the upgrade path for the complete repository is more complex than the migration path. In particular, OWB release 11.2 can directly import MDL for projects from OWB 9.2 and 10.1, but cannot upgrade the entire repository from these earlier releases. For example, upgrading an entire repository from OWB 10.1 or older may require creating one or more new OWB environments from intermediate releases, to be used for these intermediate upgrades.

MDL Migration Paths

OWB release 11.2 can directly import MDL for projects or other objects from OWB 9.2.0.n or higher. Importing metadata from MDL files from earlier releases is performed in the OWB release 11.2 Design Center. The process is described in Chapter 12, "Using the Metadata Loader." The process is like importing any other MDL file. However, you will be prompted for a filename for an intermediate MDL file (which is your file upgraded to the new format for OWB release 11.2). This file is generated, saved at a location you specify, and then immediately imported.

For releases older than 9.2.0.n, the supported migration path requires an intermediate step:

  1. Import the MDL to an intermediate 10.2.0.5 or 11.1.0.7 database. These versions should be able to read any older MDL.

    The MDL file upgrade process is performed during the import process. OWB generates an upgraded MDL file; you will be prompted for a filename.

    The upgraded MDL file is written to the filename you specify, and is then imported into your intermediate database. You can ignore this and simply copy the intermediate file to a new location.

  2. Import that upgraded MDL into OWB 11.2 using Design Center, as described in Chapter 12, "Using the Metadata Loader."

Note:

The latest 10.1, 10.2 or 11.1 patch release is strongly recommended for cases where an intermediate release is used in the upgrade path. In some cases, earlier patch releases may not import or upgrade metadata from all earlier releases of the product.

Repository Upgrade Paths from OWB 10.1 or 9.n to release 11.2

OWB release 11.2 can directly upgrade repositories from OWB 10.2.0.n or OWB 11.1.0.n. Repositories from older releases cannot be upgraded directly. OWB 10.2 becomes an intermediate step in the upgrade path.

OWB 10gR2 combined the separate design-time and runtime repositories from earlier releases into a single unified repository, which is used in all subsequent releases. The OWB 10gR2 Repository Assistant contains the functionality required to merge previously separate design-time and runtime repository contents from OWB 10.1.0.4 into the new combined repository model. If your source is 9.2.0.n or earlier, then you must first upgrade to OWB 10.1.0.4.

Therefore, if upgrading both runtime and design-time metadata from a 10.1 or earlier release, the stages required are:

  • Prepare an OWB 10.2 environment

  • Upgrade the separate design-time and run-time repositories from the earlier releases into the combined OWB 10.2 repository in that environment

  • Upgrade the resulting 10.2 repository to OWB 11.2.

The following table summarizes the repository upgrade paths for releases prior to OWB 10.2:

Table 6-1 Repository Upgrade Paths for OWB 10.1 and Earlier

Current Release Repository Upgrade Path

10.1.0.n

To 10.2.0.n, then to 11.2

9.2.0.n or Older

To 10.1.0.4, then to 10.2.0.n, then to 11.2


To perform these repository upgrades, you will require working environments for the intermediate versions on the upgrade path.

For example, if you are upgrading a 9.2 repository, you must set up an OWB 10.1.0.4 environment and an OWB release 10.2.0.5 environment for use during the upgrade steps, then upgrade from release 9.2 to release 10.1.0.4 and upgrade the result to OWB release 10.2.0.5.

See the documentation for those older releases for details on the intermediate upgrades.

If you are moving to OWB 11.2 from OWB 10.1 or earlier, then it is usually simpler is to migrate objects on a project-by-project basis into a clean OWB release 11.2 workspace. Export the projects to MDL in Design Center using the process for those releases, and import the MDL objects into OWB release 11.2, using the steps in Chapter 12, "Using the Metadata Loader."

Choosing a Repository Upgrade Scenario

The Repository Assistant for OWB 11.2 supports two major upgrade scenarios:

Note the following:

In-Place Upgrade of OWB 10gR2 or 11gR1 Repository to 11gR2

An in-place upgrade of an OWB repository is the simplest method of a repository upgrade where the target release 11.2 repository is stored in the same database instance that contained the source repository. You can perform an in-place upgrade of either a 10g R2 or 11g R1 repository that exists on the same database instance as your Oracle Database 11g R2 installation.

For OWB 10gR2, Repository Assistant exports the specified repository to an MDL file, then imports that MDL file into a new release 11.2 workspace in the OWBSYS schema in the same database. The workspace name is the same as the schema that contained the 10gR2 repository.

For OWB 11gR1, Repository Assistant exports the whole repository from the OWBSYS schema to an MDL file, drops all contents of the OWB 11gR1 repository (as if you had run clean_owbsys.sql), creates a new OWB release 11.2 repository in OWBSYS, and re-imports the repository from MDL.

Caution:

If your target database already has an OWB 11gR1 OWBSYS schema, then back it up before starting the actual migration using the 11.2 Repository Assistant. This provides you a way to recover from any possible failures during the upgrade process. The process described in "Preparing for In-Place Repository Upgrade" overwrites the OWBSYS schema (if it exists) with the OWB 11.2 schema.

Preparing for In-Place Repository Upgrade

To perform the tasks in this section, you must have SYSADMIN privileges, which allows you to run prerequisite scripts.

Note:

Check the latest OWB release notes on Oracle Technology Network (OTN) before starting the upgrade process.

The steps for preparing a repository for in-place repository upgrade are slightly different for Windows and Linux.

To prepare for an in-place repository upgrade on Windows 

  1. Ensure that you make a back-up copy of the OWBSYS schema, using the backup strategy employed in your organization.

  2. On the Microsoft Windows desktop, click Start and select Run.

  3. In the Run window, for Open, enter cmd.

    Click OK.

  4. At the command prompt, start SQL*Plus:

    C:\>sqlplus
    
  5. Connect as a user with administrative privileges:

    SQL> connect sys as sysdba
    Enter password: password
    
  6. You have two options for the OWBSYS schema:

    • If the OWBSYS schema does not exist on the Oracle Database instance, then run the cat_owb.sql script to create it:

      SQL> @C:\OWB112\owb\UnifiedRepos\cat_owb.sql
      
    • If the OWBSYS schema exists on the Oracle Database instance, and you are upgrading from OWB 11g Release 1, then run the cat_owb_reposupgrade.sql script to upgrade it:

      SQL> @C:\OWB112\owb\UnifiedRepos\cat_owb_reposupgrade.sql
      
  7. Run the reset_owbcc_home.sql script to reset the OWB_HOME directory:

    SQL> @C:/OWB112/owb/UnifiedRepos/reset_owbcc_home.sql
    

    Note that this entry is case-sensitive, does not accept a trailing slash, and requires forward slashes only, regardless of the operating system.

To prepare for an in-place repository upgrade on Linux 

  1. Ensure that you make a back-up copy of the OWBSYS schema, using the backup strategy employed in your organization.

  2. At the command prompt, start SQL*Plus:

    % sqlplus
    SQL*Plus: Release 11.2 - Production on Mon July 27 16:37:21 2009
    
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
  3. Connect as a user with administrative privileges:

    SQL> connect sys as sysdba
    Enter password: password
    
  4. You have two options:

    • If the OWBSYS schema does not exist on the Oracle Database instance, run the cat_owb.sql script:

      SQL> @OWB112/owb/UnifiedRepos/cat_owb.sql
      

      Enter the tablespace name when you are prompted for it.

    • If the OWBSYS schema exists on the Oracle Database instance, and you are upgrading from OWB 11g Release 1, then run the cat_owb_reposupgrade.sql script:

      SQL> @OWB112/owb/UnifiedRepos/cat_owb_reposupgrade.sql
      

      You are prompted for the tablespace name; enter it.

  5. Run the reset_owbcc_home.sql script to reset the OWB_HOME directory:

    SQL> @OWB112/owb/UnifiedRepos/reset_owbcc_home.sql
    

    Note that this entry is case-sensitive, does not accept a trailing slash, and requires forward slashes only, regardless of the operating system.

  6. Unlock the OWBSYS and OWBSYS_AUDIT accounts, as described in the following sections:

Performing the In-Place Repository Upgrade

When upgrading from an OWB 11g R1 repository, you are upgrading an existing OWBSYS schema on an existing repository. When upgrading from OWB 10.2, your upgrade process will create a new workspace in the OWB 11.2 repository, with the same name as the schema containing the OWB 10.2 repository. In either case, the process is similar.

To perform an in-place repository upgrade 

  1. Start the Repository Assistant.

    In Windows, from the Start menu, select Program Files, then Oracle - OWB112, then Warehouse Builder, then Administration, then Repository Assistant.

    In Linux, from the command line, run OWB_HOME/owb/bin/unix/reposinst.sh.

  2. On the Welcome page of the Repository Assistant, click Next.

  3. On the Database Information page, enter the host name, port number, and service name where OWB is installed.

    Description of upgrade_00.gif follows
    Description of the illustration upgrade_00.gif

    Click Next.

  4. On the Choose Operation page, select Upgrade repository to current release of Oracle Warehouse Builder.

    Description of upgrade_01.gif follows
    Description of the illustration upgrade_01.gif

    Click Next.

  5. On the Choose Upgrade Operations page, select Upgrade repository on the same database instance.

    Description of upgrade_02.gif follows
    Description of the illustration upgrade_02.gif

    Click Next.

  6. On the OWBSYS Information page, enter the password for the OWBSYS user.

    Description of upgrade_03.gif follows
    Description of the illustration upgrade_03.gif

    Click Next.

  7. The File Information page appears. The in-place repository upgrade process creates a repository export MDL file. The default file names and location for this exported MDL file and export log file are displayed here. You can change the location of these files.

    Description of upgrade_04.gif follows
    Description of the illustration upgrade_04.gif

    Click Next to accept the file information.

  8. On the Summary page, click Finish to initiate the upgrade process.

    Description of upgrade_05.gif follows
    Description of the illustration upgrade_05.gif

  9. The Installation Progress popup window indicates the progress of the upgrade operation. When complete, click OK.

You may wish to review the log for errors, and save the exported MDL file as a backup of your pre-upgrade repository.

Upgrading a Repository to release 11.2 through MDL Files

You can upgrade an OWB release 10.2 or 11.1 repository by exporting its content to a file, and then importing the file into an OWB release 11.2 repository, using the Repository Assistant.

Exporting an OWB Repository to a File

You export a repository with the Repository Assistant as follows.

To export a repository to a file 

  1. Start the Repository Assistant.

    In Windows, from the Start menu, select Program Files, then Oracle - OWB112, then Warehouse Builder, then Administration, then Repository Assistant.

    In Linux, from the command line, run OWB_HOME/owb/bin/unix/reposinst.sh.

  2. On the Welcome page of the Repository Assistant, click Next.

  3. On the Database Information page, enter the host name, port number, and the service name where the OWB is installed.

    Click Next.

  4. On the Choose Operation page, select Upgrade repository to current release of Oracle Warehouse Builder.

    Description of upgrade_01.gif follows
    Description of the illustration upgrade_01.gif

    Click Next.

  5. On the Choose Upgrade Operation page, select Export entire repository to a file.

    Description of export_01.gif follows
    Description of the illustration export_01.gif

    Click Next.

  6. On the User Information page, for an OWB release 10.2 source, enter the user name and password of the OWB release 10.2 repository owner. For an OWB release 11.1 source, enter the OWBSYS user name and password.

    Description of export_02.gif follows
    Description of the illustration export_02.gif

    Click Next.

  7. On the File Information page, enter the following information:

    • File Name: the name of the MDL file for storing the repository.

    • Log File: the name of the export operation log file that stores status and statistical information.

    Description of upgrade_04.gif follows
    Description of the illustration upgrade_04.gif

    Click Next.

  8. On the Summary page, click Finish.

    An Export Repository Progress window appears showing progress.

    At the end of the export process, a status message appears. The export operation creates the file.

Importing a Repository Export MDL File to OWB Release 11.2

To import a repository export MDL file 

  1. Start the Repository Assistant.

    In Windows, from the Start menu, select Program Files, then Oracle - OWB112, then Warehouse Builder, then Administration, then Repository Assistant.

    In Linux, from the command line, run OWB_HOME/owb/bin/unix/reposinst.sh.

  2. On the Welcome page of the Repository Assistant, click Next.

  3. On the Database Information page, enter the host name, port number, and the service name where the OWB repository is installed. Click Next.

  4. On the Choose Upgrade Operation page, select Import entire repository from file.

    Description of import_02.gif follows
    Description of the illustration import_02.gif

    Click Next.

  5. On the OWBSYS Information page, enter the OWBSYS password.

    Description of import_01.gif follows
    Description of the illustration import_01.gif

    Click Next.

  6. On the File Information page, enter or browse to find the name of the files used when exporting the repository.

    Description of upgrade_04.gif follows
    Description of the illustration upgrade_04.gif

    Click Next.

  7. On the Summary page, click Finish.

    Note:

    When importing an OWB release 10.2 MDL repository export, the import process creates a workspace that has the same name as the OWB release 10.2 repository schema. When importing an OWB release 11.1 repository export, the import process creates one workspace for each workspace being imported from the OWB release 11.1 repository.

    When importing an OWB release 10.2 MDL repository export, the import process creates a workspace that has the same name as the OWB release 10.2 repository schema. When importing an OWB release 11.1 repository export, the import process creates one workspace for each workspace being imported from the OWB release 11.1 repository.

Migrating the Repository to a Different Oracle Database Instance

The process of migrating the repository to a different Oracle Database instance involves the export of the existing repository to a file, and subsequent import of information from that file into a new repository on the other Oracle Database instance.

This process is virtually identical to upgrading from an OWB release 10.2 repository to a current version of the repository. The main differences are:

Note:

Check the latest OWB release notes on Oracle Technology Network (OTN) before starting the migration process.

Exporting a Repository to MDL File

The export part of the scenario uses the Repository Assistant to create an MDL zip archive. This MDL zip archive includes information about all the workspaces in the repository, with each workspace stored as a separate file.

See Also:

"Exporting an OWB Repository to a File" for a description of the export operation

Creating OWB Users in the Target Instance

Before importing the repository, in the target Oracle Database instance that contains the migrated repository, create the same users who had access to the original repository. You must do this to ensure that the security information is correctly imported. However, you do not need to register these users as OWB users; the upgrade process handles this automatically.

Importing a Repository from MDL File

The import operation is described in section "Importing a Repository Export MDL File to OWB Release 11.2". Note that in step 6, you are entering the credential of the source (remote) database, which contains the repository MDL and log files.

Upgrading Locations to OWB Release 11.2

The locations in your OWB repository must also be upgraded for compatibility with OWB 11.2 after the upgrade process. There are two ways to perform this upgrade:

Alternatively, you can also un-register and re-register all locations. However, in this case you must also redeploy all your mappings to affected locations; therefore, this procedure is not recommended.

Upgrading Locations to OWB Release 11.2 Using the Repository Browser

You can also use Locations Reports from the Repository Browser to perform the locations upgrade process as follows.

To upgrade locations using the Repository Browser 

  1. In Repository Browser, select Control Center. Click Reports.

  2. In the Reports window, under Deployment, click Locations Reports.

    Description of locations_01.gif follows
    Description of the illustration locations_01.gif

  3. On the Locations Report, under Logical Details, in the Validation column of the table, click Unknown.

    Description of locations_02.gif follows
    Description of the illustration locations_02.gif

  4. Under Connection details, under Location Status, because the status is Unknown, enter the password and click Get Status.

    Description of locations_03.gif follows
    Description of the illustration locations_03.gif

    Note that the Current Status is Invalid.

  5. Under Location Status, click Upgrade.

    Description of locations_04.gif follows
    Description of the illustration locations_04.gif

    Note that the Current Status is now Valid.

  6. Repeat steps 3 through 5 for all installations.

  7. Once you are finished, notice that the Validation column shows Tested locations

    Description of locations_05.gif follows
    Description of the illustration locations_05.gif

Changes to Repository Objects When Moving to OWB Release 11gR2

Design-time objects in your projects are automatically modified during the upgrade process to be compatible with OWB release 11.2. This section lists the changes that are made.

Changes to OWB Release 10.2 and Release 11.1 Objects

When you move a design from OWB release 10.2 or OWB release 11.1 to OWB release 11.2, the following objects are modified during the migration or upgrade:

Changes to OWB Release 9.2 and Release 10.1 Objects

When you move a design from OWB release 9.2 or OWB release 10.1 to OWB release 11.2, the following objects are modified during the migration or upgrade: