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"

• "Choosing a Repository Upgrade Scenario"

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

• "Migrating the Repository to a Different Oracle Database Instance"

• "Upgrading Locations to OWB Release 11.2"

• "Changes to Repository Objects When Moving to OWB Release 11gR2"

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:

• 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 OWB release and moving it to OWB release 11.2. This process is performed from within Repository Assistant.

• Migrating objects, in the context of moving between OWB releases, is the process of exporting existing OWB design-time objects from an earlier OWB release, and importing the resulting MDL file into OWB release 11.2. This process is performed from OWB Design Center.

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:

• Repository upgrade through MDL files, where the complete repository from OWB 10.2 or OWB 11.1 is exported to an MDL file, and then that MDL file is later imported into a database with an OWB 11.2 repository schema

• An in-place repository upgrade, in which case the new OWB 11.2 repository is immediately created in the same database where the OWB 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 OWB 11.1 overwrites the OWB 11.1 repository with the OWB 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 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:

   • For Windows, "Unlocking the OWBSYS and OWBSYS_AUDIT Accounts"

   • For Linux, "Unlocking the OWBSYS and OWBSYS_AUDIT Accounts"

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 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 the illustration upgrade_01.gif
   
   

   Click Next.

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

   
   Description of the illustration upgrade_02.gif
   
   

   Click Next.

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

   
   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 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 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 the illustration upgrade_01.gif
   
   

   Click Next.

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

   
   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 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 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 the illustration import_02.gif
   
   

   Click Next.

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

   
   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 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:

• Before the import, you must create users on the target instance with the same names as the OWB 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 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:

• Use the script provided with OWB release 11.2.0.2 or higher. (This is the simpler procedure and is recommended.)

• 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 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 the illustration locations_01.gif
   
   

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

   
   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 the illustration locations_03.gif
   
   

   Note that the Current Status is Invalid.

5. Under Location Status, click Upgrade.

   
   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 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:

• 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 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:

• 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 originally 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.