3
Upgrading to Release 9.2.0.3 and Migrating Data

This chapter provides instructions for upgrading an existing system that was built using a prior version of Warehouse Builder to Oracle9i Warehouse Builder Release 9.2.0.3. Upgrading to the current version consists of three main tasks: upgrading your software, upgrading your metadata, and migrating your runtime system. Test the new installation before you deinstall the previous Warehouse Builder version and its repositories.

This chapter includes the following topics:

• Overview

• Installing Warehouse Builder in a Separate ORACLE_HOME

• Exporting Metadata from Your Existing Warehouse Builder Repository

• Importing Design Metadata

• Upgrading Target Schema and Audit Data from Release 9.0.4

• Migrating Data to Your New Target Schemas

• Deploying Your Mappings to the New Target Schemas

• Updating Mapping Configuration to Benefit from New Features

• Optional: Deinstalling the Old Version of Warehouse Builder

3.1 Overview

To upgrade your current repository, follow these general steps:

1. Without deinstalling any existing software, install the new version of Warehouse Builder into a separate ORACLE_HOME after reading the instructions in Section 3.2, "Installing Warehouse Builder in a Separate ORACLE_HOME".

   For detailed installation instructions, refer to Chapter 2, "Installing Oracle9i Warehouse Builder Components" of this manual. These instructions also guide you through the creation of a new Design Repository, Runtime Repository, and one or more Target Schemas. You will need these new repositories and schema(s) for the migration, unless you are upgrading from Warehouse Builder Release 9.0.4, in which case you can choose not to create a new Design Repository (refer to Section 3.4, "Importing Design Metadata" for more information on this choice).

2. Export all your existing Warehouse Builder projects to Metadata Loader (MDL) files by following the instructions in Section 3.3, "Exporting Metadata from Your Existing Warehouse Builder Repository".

3. Import your design metadata following the instructions in Section 3.4, "Importing Design Metadata". Import the MDL files into Warehouse Builder Release 9.2.0.3. They will be upgraded automatically as part of the import.

4. If you are upgrading from Warehouse Builder Release 9.0.4, upgrade your Runtime Repository audit data to the current version, following the instructions in Section 3.5, "Upgrading Target Schema and Audit Data from Release 9.0.4".

5. If you are upgrading from Warehouse Builder Release 9.0.3 or earlier, migrate data from your previous runtime environment to the new Target Schema you created. You can choose from three migration methodologies. Follow the instructions in Section 3.6, "Migrating Data to Your New Target Schemas".

6. In the new version of Warehouse Builder, redeploy all your mappings and test them, following the instructions in Section 3.7, "Deploying Your Mappings to the New Target Schemas". If you are upgrading from Warehouse Builder Release 9.0.4, you can skip this step.

7. In the new version of Warehouse Builder, update the configuration for each mapping to take advantage of the new features in this release. Follow the instructions in Section 3.8, "Updating Mapping Configuration to Benefit from New Features". If you are upgrading from Warehouse Builder Release 9.0.4, read the instructions carefully to determine whether you can skip this section.

8. Optional: Gradually phase out your old system using the deinstallation instructions for that version of Warehouse Builder. You can perform this step in parallel to Step 7. Follow the instructions in Section 3.9, "Optional: Deinstalling the Old Version of Warehouse Builder".

3.2 Installing Warehouse Builder in a Separate ORACLE_HOME

To install a new Warehouse Builder system in parallel to the existing one:

1. Follow the installation instructions in Section 2.2, "Step 1. Perform the OWB Design Time Install using Oracle Universal Installer".

   When you mount the installation CD, specify the source file locations and your ORACLE_HOME destination. Install the new version of the Warehouse Builder software in a separate ORACLE_HOME. For more information on ORACLE_HOMEs, refer to Section 1.7, "Coexistence: ORACLE_HOME Considerations".

2. Follow the installation instructions in Section 2.3, "Step 2. Install the Design Repository using the Repository Assistant" to create a new Warehouse Builder Design Repository.

3. Follow the installation instructions in Section 2.4, "Step 3. Enable Warehouse Builder Design Browser Client".

4. Follow the installation instructions in Section 2.5, "Step 4. Perform the OWB Server Side Install using Oracle Universal Installer".

5. Follow the instructions in Section 2.6, "Step 5. Install Runtime Objects Using the Runtime Assistant" to create a new Warehouse Builder Runtime Repository and as many Target Schemas as you currently have in your existing system. You will need these new repositories and schema(s) for the migration.

   If you are using your Warehouse Builder 9.0.4 Runtime Access User as your Warehouse Builder 9.2 Runtime Access User, leave the Runtime Access User Name blank in the Runtime Assistant.

   If you plan to use a new Runtime Access User as your Warehouse Builder 9.2 Runtime Access User, enter the Runtime Access User Name in the Runtime Assistant.

6. Follow the installation instructions in Section 2.7, "Step 6. Configure the Web Browser for Design and Runtime Audit Browsers".

3.3 Exporting Metadata from Your Existing Warehouse Builder Repository

Keep your previous repository until the entire upgrade process has been performed and tested. Export each project or collection you want to migrate to Metadata Loader (MDL) files. For more information on exporting metadata, see the Oracle9i Warehouse Builder User's Guide.

Note: To upgrade, you must export and import your metadata using the Metadata Loader (MDL). Warehouse Builder upgrade does not support files that were exported or imported using back end database commands.

To export existing metadata into an MDL file:

1. Use the prior version of the Warehouse Builder client to select the Project you want to export.

2. From the Project menu, choose Export Metadata.

   The Metadata Loader assigns a path and file name to the exported MDL file. Make a note of the path and filename for all data you export. For more information on exporting metadata, refer to the Oracle9i Warehouse Builder User's Guide.

3.4 Importing Design Metadata

After having installed the new software, you must import and upgrade your design metadata into the new version of Warehouse Builder. For more information on importing metadata, see the Oracle9i Warehouse Builder User's Guide.

To import and upgrade metadata into the new Warehouse Builder repository:

1. From the new Warehouse Builder console, select the Project menu, Metadata Import, and File.

2. Locate the path and file name to the exported data from your former repository. Select Import.

   If the file is from the current version of Warehouse Builder, the Metadata Import Progress panel displays. This dialog displays the object types and the number of each type that were imported or skipped. For a detailed view of the import process, click View Log File.

   If Warehouse Builder detects that the file is from a prior version of Warehouse Builder, the Metadata Upgrade Confirmation dialog displays. In this case, Warehouse Builder automatically upgrades the MDL file to the current version and then imports it.

3. Click OK to automatically upgrade the file to the current version and import it into Warehouse Builder. The Metadata Loader Upgrade Utility dialog displays for you to enter a file and a log name.

   Migrated File Name: Specify the path and name of the new, upgraded MDL file. Use the Browse button to specify the destination of the file.

   Log File: Specify the path and name of the log that MDL creates during the upgrade process. Use the Browse button to specify the destination of the file.

   Click OK to continue the upgrade and import.

   Click Cancel to stop the upgrade and import of the file. You can upgrade the file manually by using the MDL File Upgrade utility. For more information, refer to Appendix C, "Upgrading Metadata with the MDL File Upgrade Utility".

3.5 Upgrading Target Schema and Audit Data from Release 9.0.4

This step applies only if you are upgrading from Warehouse Builder Release 9.0.4 to the current release. If you are upgrading from an earlier release, skip this section and continue to Section 3.6, "Migrating Data to Your New Target Schemas".

Warehouse Builder includes a script that upgrades your Target Schema and migrates the audit data from your Release 9.0.4 Runtime Repository to a Release 9.2.0.3 Runtime Repository. This script migrates all the audit data, including the deployment audit data. This means that the Deployment Manager in your new Runtime Repository will show the correct status for objects you deployed using the previous version of Warehouse Builder. Additionally, you will have access to audit data from previous runs that you performed in Oracle9i Warehouse Builder Release 9.0.4.

For this step, you can either use a new Runtime Access User or specify an existing Release 9.0.4 Runtime Access User. The upgrade script converts all users associated with the Release 9.0.4 Runtime Repository to be associated with the Release 9.2.0.3 Runtime Repository.

To migrate audit data from Release 9.0.4 to a Release 9.2.0.3 Runtime Repository:

1. In SQL*Plus, connect to the Runtime Repository with a SYS account as SYSDBA. For example:

   connect sys/password@db_connect_string as sysdba;
   
   

2. Run the following script:

   For Windows: ORACLE_HOME\owb\rtp\sql\upgrade_9_0_4_runtime_repos_to_9_2.sql 904_repository_schema 92_repository_schema

   For UNIX: ORACLE_HOME/owb/rtp/sql/upgrade_9_0_4_runtime_repos_to_9_2.sql 904_repository_schema 92_repository_schema

   In this statement, ORACLE_HOME is the path of the ORACLE_HOME in which you installed Warehouse Builder Release 9.2.0.3.

   For example, if your Release 9.0.4 Runtime Repository schema was RTR904 and your new schema is RTR92, then run the following script:

   ORACLE_HOME\owb\rtp\sql\upgrade_9_0_4_runtime_repos_to_9_2.sql RTR904 
   RTR92
   
   

   For UNIX, replace each backward slash (\) with a forward slash (/).

Once you have migrated the data, the Release 9.0.4 Target Schema and (if you selected) the Runtime Access User are associated with the Release 9.2.0.3 Runtime Repository. All deployment and execution data is migrated to this new repository, and you can browse it using the Release 9.2.0.3 Runtime Audit Browser.

3.6 Migrating Data to Your New Target Schemas

The migration instructions depend on the version of Warehouse Builder from which you are upgrading:

• If You Are Upgrading From Warehouse Builder 9.0.4, skip this section and proceed to Section 3.9, "Optional: Deinstalling the Old Version of Warehouse Builder".

• If You Are Upgrading From Warehouse Builder 9.0.3 or earlier, choose from three methods to migrate data from your old runtime environment to the new Target Schema(s) you created when you installed the new software:

  Option 1. Regenerate Database Objects Using the Warehouse Builder Interface

  Option 2. Export and Import Database Objects in the Oracle Database Server

  Option 3. Create Synonyms for Original Objects in the New Target Schema

3.6.1 Option 1. Regenerate Database Objects Using the Warehouse Builder Interface

In this method, consider using the Warehouse Builder interface for a one-time migration. This method is recommended for beginning and intermediate users of Warehouse Builder, provided that your old Warehouse Builder environment does not stray from the recommended setup.

Table 3-1 describes the advantages and drawbacks of this method.

Table 3-1 Advantages and Drawbacks of Option 1  

Outcome	Description
Advantages	Complete: This method results in a complete migration. You can fully deinstall your old system. Straightforward: This is the simplest method for a complete migration. Fully up-to-date deployment audit.
Drawbacks	Time-consuming: This method is more labor-intensive. Requires a copy environment: You must duplicate data until you decommission your old system.

To migrate data using the Warehouse Builder interface:

1. In the Deployment Manager of your upgraded Warehouse Builder client, register the new locations for your database objects.

2. One at a time, select each Target module and click Default Action. Because the objects are new to this version, the default action is creation.

3. Click Generate/Deploy. You are ready to migrate your data following Steps 4 through 7.

4. Create a Source module pointing to your old Target Schema.

5. Create simple mappings that extract from your old Target Schema as a source and load to your new Target Schema objects.

6. Deploy these migration mappings and run them only once. Test to ensure that a 1-to-1 migration occurred.

7. When you are satisfied with the results, delete the one-time migration mappings and the Source modules you created that point to the old Target Schema.

For more instructions on registering locations, creating source and target modules, and creating and executing mappings, refer to the Oracle9i Warehouse Builder User's Guide.

3.6.2 Option 2. Export and Import Database Objects in the Oracle Database Server

This method is recommended for advanced users of Warehouse Builder. You perform the data migration in the back end by exporting and importing the appropriate Warehouse Builder database objects.

Table 3-2 describes the advantages and drawbacks of this method.

Table 3-2 Advantages and Drawbacks of Option 2  

Outcome	Description
Advantages	Complete: This method results in a complete migration. You can fully deinstall your old system. Fast: This is the fastest route to a complete migration.
Drawbacks	Complex: This method requires the most detailed knowledge of Warehouse Builder database objects. Inaccurate Deployment Audit Results: Your deployment audit data will not be accurate. It will show existing database objects to be new. This drawback only applies if you are upgrading from Warehouse Builder Release 9.0.3 or earlier. If you are upgrading from Warehouse Builder Release 9.0.4, Upgrading Target Schema and Audit Data from Release 9.0.4, ensures accurate audit data even with this option.

To migrate by exporting and importing database objects in the back end:

Follow instructions in the Oracle Database Server documentation to export and import database objects.

Make sure that you are only working with database objects that you created yourself, and not with Warehouse Builder objects generated by Warehouse Builder. To this end, consider simply migrating the data and then regenerating the constraints, indexes, dimensions, and other objects you can generate in the database.

Examples of objects you should not export or import follow:

• Do not export or import runtime audit tables or packages. If your old Warehouse Builder environment does not stray from the standard, then the tables and packages related to audit data are in your Runtime Schema.

• Do not export or import objects whose names begin with WB. These objects are generated by Warehouse Builder.

After the data migration, regenerate all mappings.

3.6.3 Option 3. Create Synonyms for Original Objects in the New Target Schema

This method is a shortcut in which you create synonyms pointing to your original database objects in your new Target Schema. Instead of actual data, your new Target Schema will contain only the regenerated mappings and the pointers to the database objects containing the data.

Table 3-3 describes the advantages and drawbacks of this method.

Table 3-3 Advantages and Drawbacks of Option 3  

Outcome	Description
Advantages	Fast: This is a shortcut if you do not require a complete migration.
Drawbacks	Incomplete: This method does not constitute a complete migration; you will not be able to completely phase out your old system. Inaccurate Deployment Audit Results: Your deployment audit data will not be accurate. It will show existing database objects to be new. This drawback only applies if you are upgrading from Warehouse Builder Release 9.0.3 or earlier. If you are upgrading from Warehouse Builder Release 9.0.4, Upgrading Target Schema and Audit Data from Release 9.0.4, ensures accurate audit data even with this option.

To migrate by creating synonyms to existing objects:

Follow instructions in the Oracle Database Server documentation to create synonyms in your new Target Schema that point to existing database objects in your old environment.

Make sure that you are only working with database objects that you created yourself, and not with Warehouse Builder objects generated by Warehouse Builder. To this end, simply create synonyms for the data objects and then regenerate the mappings.

For example, do not create synonyms for audit tables or packages. If your old Warehouse Builder environment does not stray from the standard, then the tables and packages related to audit data are in your Runtime Schema.

3.7 Deploying Your Mappings to the New Target Schemas

If you are upgrading from Oracle9i Warehouse Builder Release 9.0.4 and followed the instructions in Section 3.5, "Upgrading Target Schema and Audit Data from Release 9.0.4", you can choose to skip this step.

If you are upgrading from Oracle9i Warehouse Builder Release 9.0.3 or earlier, you must redeploy your mappings. With the upgraded version of Warehouse Builder, the runtime architecture has changed. To propagate these changes into your generated code, redeploy your mappings. After you have redeployed your mappings, do not use the old mappings again.

For example, redeployment ensures that your mappings refer to the correct auditing packages. Additionally, redeploying allows you to take advantage of new code generation features available in the new version of Warehouse Builder.

For instructions on deploying mappings, refer to the Oracle9i Warehouse Builder User's Guide. After redeploying, test your mappings to ensure that they still work.

3.8 Updating Mapping Configuration to Benefit from New Features

After redeploying your mappings, update the configuration of each mapping to benefit from the new features available in the upgraded version of Warehouse Builder.

Use the following criteria to determine whether you can skip this section:

• If you are upgrading from Warehouse Builder Release 9.0.3 or earlier, you must follow the instructions in this section.

• If you are upgrading from Release 9.0.4, you only need to follow these instructions if you migrated from an earlier release to Release 9.0.4 without updating the mapping configuration at that time.

• You can skip this section if Release 9.0.4 was your first installation of Warehouse Builder, or if you already performed these steps when you migrated to Release 9.0.4.

You can update the mapping configuration either by using the Warehouse Builder interface, or by running a script to update all the mappings in the Oracle Metabase (OMB) Plus scripting utility.

To update the configuration of each mapping using the Warehouse Builder interface:

1. In the upgraded Warehouse Builder client, right-click on each mapping and select Configure.

2. In the Configuration Properties box, expand the Sources and Targets node.

3. In the Sources and Targets node:

   Delete the contents of the Schema configuration parameters field.

   Delete the contents of the DB Links configuration parameters field.

4. Redeploy each mapping for which you changed the configuration properties in order to generate new code. For instructions on deploying mappings, refer to the Oracle9i Warehouse Builder User's Guide.

Alternatively, you can update all your mappings by running the script provided for this purpose on the Oracle Technology Network. The update script is called UpdMapConfig.tcl.

To update the configuration of each mapping using a script:

1. Download the UpdMapConfig.tcl file from http://otn.oracle.com/sample_code/products/warehouse/content.html.

2. Start Oracle Metabase (OMB) Plus.

3. Connect to your new Design Repository.

4. Change the context in OMB Plus to the correct project and module. You must run this script module by module.

5. Run the script in OMB Plus.

   At the OMB Plus prompt, type source, followed by the location of the script enclosed in double quotes. For every backward slash, enter a second backward slash to escape the first. For example, if your script is located in c:\temp, type:
   source "c:\\temp\\UpdMapConfig.tcl"

6. Run the following commands in OMB Plus to update the mappings and commit the changes to the Design Repository:

   owb_reset_mapping_conns

   OMBCOMMIT

   Repeat this step by navigating to other target modules using the OWBCC command and running the owb_reset_mapping_conns command again.

7. Redeploy each mapping for which you changed the configuration properties in order to generate new code. For instructions on deploying mappings, refer to the Oracle9i Warehouse Builder User's Guide.

3.9 Optional: Deinstalling the Old Version of Warehouse Builder

You can gradually phase out your old system once your upgrade has been tested and is working. You can perform this step in parallel with Section 3.8, "Updating Mapping Configuration to Benefit from New Features".

Note: Do not indiscriminately remove your old system if you followed "Option 3. Create Synonyms for Original Objects in the New Target Schema" in the "Migrating Data to Your New Target Schemas" step. Do not remove the database objects to which the synonyms your created are pointing.

To phase out your old system:

1. Remove the database objects you created in the back end. For more information, refer to the Oracle Database Server documentation.

2. Remove the old version of the Warehouse Builder software. Use the deinstallation instructions provided with the version of Warehouse Builder you are removing.