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 (10.2) and Oracle Warehouse Builder 11g Release 1 (11.1) to Oracle Warehouse Builder 11g Release 2 (11.2).

This section describes the different methods of moving to Oracle Warehouse Builder 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 Oracle Warehouse Builder Release 11.2.

This section includes the following topics:

"Understanding MDL Migration and Repository Upgrades"
"Choosing a Repository Upgrade Scenario"
"In-Place Upgrade of Oracle Warehouse Builder 10g Release 2 or 11g Release 1 Repository to Oracle Warehouse Builder 11g Release 2"
"Migrating the Repository to a Different Oracle Database Instance"
"Upgrading Locations to Oracle Warehouse Builder Release 11.2"
"Changes to Repository Objects When Moving to Oracle Warehouse Builder 11g Release 2"

Understanding MDL Migration and Repository Upgrades

The two ways to move projects from earlier releases into Oracle Warehouse Builder Release 11.2 are migrating objects and upgrading repositories.

This discussion uses the following definitions:

Upgrading a repository is the process of taking an entire repository, including the control center audit data such as deployment and execution history, from an earlier Oracle Warehouse Builder release and moving it to Oracle Warehouse Builder 11.2. This process is performed from within Repository Assistant.
Migrating objects, in moving between Oracle Warehouse Builder Releases, is the process of exporting existing Oracle Warehouse Builder design-time objects from an earlier Oracle Warehouse Builder release, and importing the resulting MDL file into Oracle Warehouse Builder Release 11.2. This process is performed from Oracle Warehouse Builder Design Center.

Choosing Between MDL Migration and Repository Upgrade

The Repository Assistant in Oracle Warehouse Builder Release 11.2 provides ways to upgrade Oracle Warehouse Builder Release 10.2 and Release 11.1 repositories directly. The advantages of upgrading an entire repository are:

You can move up to Oracle Warehouse Builder 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 run-time 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.

The advantages of migrating individual projects or other objects include:

Speed, if you have large volumes of run-time metadata that you must preserve.
Simplicity, in cases where the upgrade path for the complete repository is more complex than the migration path. In particular, Oracle Warehouse Builder Release 11.2 can directly import MDL for projects from Oracle Warehouse Builder 9.2 and 10.1, but cannot upgrade the entire repository from these earlier releases. For example, upgrading an entire repository from Oracle Warehouse Builder 10.1 or older may require creating one or more new Oracle Warehouse Builder environments from intermediate releases, to be used for these intermediate upgrades.

MDL Migration Paths

Oracle Warehouse Builder Release 11.2 can directly import MDL for projects or other objects from Oracle Warehouse Builder 9.2.0.n or higher. Importing metadata from MDL files from earlier releases is performed in Oracle Warehouse Builder Release 11.2 Design Center. Chapter 12, "Importing, Exporting, and Upgrading Metadata" describes the process. The process is like importing any other MDL file. However, you are prompted for a file name for an intermediate MDL file (which is your file upgraded to the new format for Oracle Warehouse Builder 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:

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. Oracle Warehouse Builder generates an upgraded MDL file; you are prompted for a file name.

The file name writes the upgraded MDL file you specify, and is then imported into your intermediate database. You can ignore this and simply copy the intermediate file to a new location.
Import that upgraded MDL into Oracle Warehouse Builder 11.2 using Design Center, as described in Chapter 12, "Importing, Exporting, and Upgrading Metadata."

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 Oracle Warehouse Builder 10.1 or 9.n to Release 11.2

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

Oracle Warehouse Builder 10g Release 2 combined the separate design-time and run-time repositories from earlier releases into a single unified repository, which is used in all subsequent releases. Oracle Warehouse Builder 10g Release 2 Repository Assistant contains the functionality required to merge previously separate design-time and run-time repository contents from Oracle Warehouse Builder 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 Oracle Warehouse Builder 10.1.0.4.

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

Prepare an Oracle Warehouse Builder 10.2 environment
Upgrade the separate design-time and run-time repositories from the earlier releases into the combined Oracle Warehouse Builder 10.2 repository in that environment
Upgrade the resulting 10.2 repository to Oracle Warehouse Builder 11.2.

The following table summarizes the repository upgrade paths for releases before Oracle Warehouse Builder 10.2:

Table 6-1 Repository Upgrade Paths for Oracle Warehouse Builder 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 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 Oracle Warehouse Builder 10.1.0.4 environment and an Oracle Warehouse Builder 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 Oracle Warehouse Builder Release 10.2.0.5.

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

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

Choosing a Repository Upgrade Scenario

The Repository Assistant for Oracle Warehouse Builder 11.2 supports two major upgrade scenarios:

Repository upgrade through MDL files, where the complete repository from Oracle Warehouse Builder 10.2 or Oracle Warehouse Builder 11.1 is exported to an MDL file, and then that MDL file is later imported into a database with an Oracle Warehouse Builder 11.2 repository schema.
An in-place repository upgrade, in which case the new Oracle Warehouse Builder 11.2 repository is immediately created in the same database where Oracle Warehouse Builder 10.2 or 11.1 repository was stored.

Note the following:

Upgrading through MDL files is the only option when moving your entire repository to a new database. (Exporting the entire repository to MDL is also a good option for backing up the repository.)
An in-place repository upgrade of Oracle Warehouse Builder 11.1 overwrites the Oracle Warehouse Builder 11.1 repository with Oracle Warehouse Builder 11.2 repository, because both are stored in the same repository schema.
Internally, an in-place repository upgrade actually performs a repository export to MDL and repository import from MDL. The advantage of in-place repository upgrade is that the user performs fewer manual steps than in the upgrade through MDL files when targeting the same database.

In-Place Upgrade of Oracle Warehouse Builder 10g Release 2 or 11g Release 1 Repository to Oracle Warehouse Builder 11g Release 2

An in-place upgrade of an Oracle Warehouse Builder 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 Oracle Warehouse Builder 10g Release 2 or Oracle Warehouse Builder 11g Release 1 Repository that exists on the same database instance as your Oracle Database 11g Release 2 installation.

For Oracle Warehouse Builder 10g Release 2, 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 same as the schema that contained the 10g Release 2 repository.

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

Note:

If your target database has an Oracle Warehouse Builder 11g Release 1 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 Oracle Warehouse Builder 11.2 schema.

Preparing for In-Place Repository Upgrade

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

Note:

Check the latest Oracle Warehouse Builder Release Notes at http://www.oracle.com/technetwork/developer-tools/warehouse/overview/index.html 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

Ensure that you make a back-up copy of the OWBSYS schema, using the backup strategy employed in your organization.
On the Microsoft Windows desktop, click Start and select Run.
In the Run window, for Open, enter cmd.

Click OK.
At the command prompt, start SQL*Plus:
```
C:\> sqlplus
```

Connect as a user with administrative privileges:

SQL> connect sys as sysdba
Enter password: password

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 Oracle Warehouse Builder 11g Release 1, then run the cat_owb_reposupgrade.sql script to upgrade it:
```
SQL> @C:\OWB112\owb\UnifiedRepos\cat_owb_reposupgrade.sql
```
Run the reset_owbcc_home.sql script to reset the OWB_HOME directory:
```
SQL> @C:/OWB112/owb/UnifiedRepos/reset_owbcc_home.sql
```
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

Ensure that you make a back-up copy of the OWBSYS schema, using the backup strategy employed in your organization.
At the command prompt, start SQL*Plus:
```
C:\> sqlplus
```

Connect as a user with administrative privileges:

SQL> connect sys as sysdba
Enter password: password

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 Oracle Warehouse Builder 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.
Run the reset_owbcc_home.sql script to reset the OWB_HOME directory:
```
SQL> @OWB112/owb/UnifiedRepos/reset_owbcc_home.sql
```
This entry is case-sensitive, does not accept a trailing slash, and requires forward slashes only, regardless of the operating system.
Unlock the OWBSYS and OWBSYS_AUDIT accounts, as described in the following sections:
- On Windows, "Unlocking the OWBSYS and OWBSYS_AUDIT Accounts"
- On Linux, "Unlocking the OWBSYS and OWBSYS_AUDIT Accounts"

Performing the In-Place Repository Upgrade

When upgrading from an Oracle Warehouse Builder 11g Release 1 repository, you are upgrading an existing OWBSYS schema on an existing repository. When upgrading from Oracle Warehouse Builder 10.2, your upgrade process creates a new workspace in the Oracle Warehouse Builder 11.2 repository, with the same name as the schema containing the Oracle Warehouse Builder 10.2 repository. In either case, the process is similar.

To perform an in-place repository upgrade

Start the Repository Assistant.

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

On Linux, from the command line, run OWB_HOME/owb/bin/unix/reposinst.sh.
On the Welcome page of the Repository Assistant, click Next.
On the Database Information page, enter the host name, port number, and service name where Oracle Warehouse Builder is installed.

Description of the illustration upgrade_00.gif

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

Description of the illustration upgrade_01.gif

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

Description of the illustration upgrade_02.gif

Click Next.
On the OWBSYS Information page, enter the password for the OWBSYS user.

Description of the illustration upgrade_03.gif

Click Next.
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 the illustration upgrade_04.gif

Click Next to accept the file information.
On the Summary page, click Finish to initiate the upgrade process.

Description of the illustration upgrade_05.gif
The Installation Progress popup window indicates the progress of the upgrade operation. When complete, click OK.

You may want 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 Oracle Warehouse Builder Release 10.2 or 11.1 Repository by exporting its content to a file, and then importing the file into an Oracle Warehouse Builder Release 11.2 Repository, using the Repository Assistant.

Exporting an Oracle Warehouse Builder Repository to a File

You export a repository with the Repository Assistant as follows.

To export a repository to a file

Start the Repository Assistant.

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

On Linux, from the command line, run OWB_HOME/owb/bin/unix/reposinst.sh.
On the Welcome page of the Repository Assistant, click Next.
On the Database Information page, enter the host name, port number, and the service name where Oracle Warehouse Builder is installed.

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

Description of the illustration upgrade_01.gif

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

Description of the illustration export_01.gif

Click Next.
On the User Information page, for an Oracle Warehouse Builder Release 10.2 source, enter the user name and password of Oracle Warehouse Builder Release 10.2 repository owner. For an Oracle Warehouse Builder Release 11.1 source, enter the OWBSYS user name and password.

Description of the illustration export_02.gif

Click Next.
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 the illustration upgrade_04.gif

Click Next.
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 Oracle Warehouse Builder Release 11.2

To import a repository export MDL file

Start the Repository Assistant.

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

On Linux, from the command line, run OWB_HOME/owb/bin/unix/reposinst.sh.
On the Welcome page of the Repository Assistant, click Next.
On the Database Information page, enter the host name, port number, and the service name where Oracle Warehouse Builder Repository is installed. Click Next.
On the Choose Upgrade Operation page, select Import entire repository from file.

Description of the illustration import_02.gif

Click Next.
On the OWBSYS Information page, enter the OWBSYS password.

Description of the illustration import_01.gif

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

Description of the illustration upgrade_04.gif

Click Next.
On the Summary page, click Finish.

Note:
When importing an Oracle Warehouse Builder Release 10.2 MDL repository export, the import process creates a workspace that has the same name as Oracle Warehouse Builder Release 10.2 repository schema. When importing an Oracle Warehouse Builder Release 11.1 repository export, the import process creates one workspace for each workspace being imported from Oracle Warehouse Builder Release 11.1 repository.

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

Deploying Any Process Flow Package With Repository Upgrade to Release 11.2

Oracle Warehouse Builder provides the utility to upgrade the entire repository from Release 10.2 to Release 11.2 through Repository Assistant. After the upgrade, you should use the Repository browser to update all locations, and then run the deployed objects, and mappings.

For Repository upgrade from Release 10.2 to Release 11.2, some extra steps are required for Process Flow, besides updating the locations through the Repository browser.

A warning information message is displayed after the location is upgraded.

Warning:

This OWF location has not been seeded.

It can be seeded by using the Design Client to deploy at least one Process Flow Package into it.

This warning message is not displayed every time, such as if you have both 11.2 and 10.2 Repositories, when you upgrade your Repository 10.2 to 11.2, you cannot see the warning as the packages are present in the Repository collectively.

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 Oracle Warehouse Builder Release 10.2 repository to a current version of the repository. The main differences are:

Before the import, you must create users on the target instance with the same names as Oracle Warehouse Builder users on the original instance.
At the time of importing, you must provide the credentials of a remote Oracle Database instance where the export files reside.

Note:

Check the latest Oracle Warehouse Builder Release Notes on http://www.oracle.com/technetwork/developer-tools/warehouse/overview/index.html 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.

Upgrading Locations to Oracle Warehouse Builder Release 11.2

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

Use the script provided with Oracle Warehouse Builder Release 11.2.0.2 or higher. Oracle recommends using the provided scripts.

On Windows - run OWB_HOME\owb\bin\win32\upgradeloc.bat

On Linux - run OWB_HOME/owb/bin/unix/upgradeloc.sh
Use the Repository Browser Locations Report to perform the upgrades one at a time. (This procedure is as effective as using the script, but requires more effort.)

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 Oracle Warehouse Builder 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

In Repository Browser, select Control Center. Click Reports.
In the Reports window, under Deployment, click Locations Reports.

Description of the illustration locations_01.gif
On the Locations Report, under Logical Details, in the Validation column of the table, click Unknown.

Description of the illustration locations_02.gif
Under Connection details, under Location Status, because the status is Unknown, enter the password and click Get Status.

Description of the illustration locations_03.gif

The Current Status is Invalid.
Under Location Status, click Upgrade.

Description of the illustration locations_04.gif

The Current Status is now Valid.
Repeat steps 3 through 5 for all installations.
After you are finished, notice that the Validation column shows Tested locations

Description of the illustration locations_05.gif

Changes to Repository Objects When Moving to Oracle Warehouse Builder 11g Release 2

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

Changes to Oracle Warehouse Builder Release 10.2 and Release 11.1 Objects

When you move a design from Oracle Warehouse Builder Release 10.2 or Oracle Warehouse Builder Release 11.1 to Oracle Warehouse Builder Release 11.2, the following objects are modified during the migration or upgrade:

Mapping Table Function Operators: The mapping operators for table functions are upgraded to have only one input group and one output group. Old input groups that were configured as a REF cursor type are upgraded to a SYS_REFCURSOR type, and a new construct operator is added to the map; its input group values are the same as in the REF cursor type input group, and the output group attribute is set to SYS_RECURSOR type.
Mapping Key Lookup Operators: The mapping operators for key lookups are upgraded to the new lookup operator.
Business Presentation Modules: Business presentation modules and their contents are discarded.
BI Beans Locations: BI beans locations are discarded.
Data Profile Metadata: Data profile metadata is not upgraded.

Changes to Oracle Warehouse Builder Release 9.2 and Release 10.1 Objects

When you move a design from Oracle Warehouse Builder Release 9.2 or Oracle Warehouse Builder Release 10.1 to Oracle Warehouse Builder Release 11.2, the following objects are modified during the migration or upgrade:

Control Centers and Locations: After an upgrade, there is no association between the locations and the control centers that they reference. You must review the control center details using the Edit Control Center dialog box and select the locations associated with this control center.
Locations and Control Center Connections: Locations and Control Center connections are moved out of the projects that own them so that they can be shared across the entire workspace. Thus the statistics in the import log file displays an additional project for these objects.
Mapping Operator Names: The MDL Upgrade Utility ensures that the physical names and business names of all mapping operators are unique.
Dimensions: An associated dimension table is created with the same name as the dimension. The table contains the columns, constraints, and attribute sets defined in the Dimension Editor Table Properties of the dimension in the earlier release.
Cubes: An associated cube table is created with the same name as the cube. The cube table contains columns, constraints, and attribute sets defined in the Cube Editor Table Properties of the cube in the earlier release.
Dimension and Cube Mapping Operators: The mapping operators for dimensions and cubes are converted to table operators. These table operators use the physical tables created by the MDL Upgrade Utility for dimensions and cubes.
Mapping Display Sets for Dimension Hierarchies: Any mapping sets created based on the named attribute set for a dimension hierarchy are removed. This is because display sets for dimension hierarchies are no longer automatically created and maintained.
Dimension Attributes: For each level attribute upgraded, a dimension attribute with the same name is created, if it does not exist in the dimension.
Intelligence Objects and Reports: In the earlier release, intelligence objects and reports were available only using OMB Plus scripting. These objects are not upgraded.
Advanced Queues: An associated queue table is created based on the property AQ queue table name. The queue table created by the MDL File Upgrade Utility contains a column whose data type is the object type for that advanced queue.
Advanced Queue Operator in a Mapping: Mapping Advanced Queue operators are changed to contain only one attribute called PAYLOAD. For Mapping Advanced Queue operators that are used as a source, a new Expand operator is added after the Mapping Advanced Queue operator. For Mapping Advanced Queue operators that are used as a target, a new Construct operator is added before the Mapping Advanced Queue operator.