3 Upgrading to Oracle Warehouse Builder 11g Release 1 (11.1)

Exporting Target Schemas from the Existing Runtime Environment

Pre-create the tablespaces in the new Database environment to exactly match the tablespaces in the existing version of Oracle Database.

1. Use Oracle Export in the existing version of the Oracle Database to export the existing target schemas into a DMP file with the following command for each schema:

   exp OldOWBTargetUserName/OldOWBTargetUserPassword@Old_DBTNSConnection Owner=OldOWBTargetUserName FILE=OldOWBTarget.dmp LOG=OldOWBTarget.log

   OldOWBTargetUser stands for the Warehouse Builder target schema user from the existing version of Warehouse Builder.

   For example, type:

   exp owb_target/owb_target owner=owb_target FILE=owb_target.dmp LOG=owb_target.log

2. Identify all the tablespaces for each of the existing Warehouse Builder target schema users.

   Connect to SQL*Plus in the existing version of the Oracle Database as the Warehouse Builder target schema user, and then enter the following command:

   select distinct TABLESPACE_NAME from user_segments;

   Enter the following to check the default and temporary tablespaces for existing Warehouse Builder target schema users:

   select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from user_users;

Creating the Target Schemas and Users in the New Database

The steps you take here depend on the version from which you are upgrading.

When migrating from Warehouse Builder 9.2 or 10.1, complete the following instructions:

1. In the new Database instance, create the tablespaces you listed from the existing instance in "Exporting Target Schemas from the Existing Runtime Environment".

2. In the new Database, connect as a SYS user to SQL*Plus to create each target schema and grant privileges to it.

   For each target schema you create, enter the following commands in SQL*PLus:

   connect SYS/SYS as sysdba;

   create user OldOWBTargetSchemaUser identified by OldOWBTargetSchemaPassword default tablespace users temporary tablespace temp;

   SET DEFINE %

   define user=OldOWBTargetSchemaUser

   @new owb home\owb\rtasst\upgrade\preowb10_2\warehouse_system_rights.sql

   @new owb home\owb\rtasst\upgrade\preowb10_2\xmltk_grant.sql

When migrating from Warehouse Builder 10.2, complete the following instructions:

1. In the new Database instance, create the tablespaces you listed from the existing instance in "Exporting Target Schemas from the Existing Runtime Environment".

2. In the new Database, connect as a SYS user to SQL*Plus to create each target schema and grant privileges to it.

   For each target schema you create, enter the following commands in SQL*PLus:

   connect SYS/SYS as sysdba;

   create user OldOWBTargetSchemaUser identified by OldOWBTargetSchemaPassword default tablespace users temporary tablespace temp;

   SET DEFINE %

   define user=OldOWBTargetSchemaUser

   @new owb home\owb\rtasst\upgrade\owb10_2\warehouse_system_rights.sql

Copying External Directory References to the New Database Instance

Complete this section if in the pre-existing Warehouse Builder environment includes external directories which are used by external tables and flat files.

External directories have two elements: the logical and the physical. The logical element is the reference residing in the database to a directory located outside the database. These instructions migrate the logical elements. You migrate the physical elements in a later step subsequent to upgrading the runtime environment.

To migrate the external directories for each target schema users:

1. Create a copy of the script gen_ext_dirs.sql.

   Locate owb home\owb\mig\gen_ext_dirs.sql on the new host, that is, the computer hosting the new Warehouse Builder installation.

   Copy the file to a temporary directory on the original host, that is, the computer hosting the pre-existing version of Warehouse Builder from which you are migrating.

2. In SQL*Plus, connect as a Warehouse Builder target schema user and run TEMP\gen_ext_dirs.sql on the original host computer.

3. Locate the ext_dirs.sql file generated in the SQL*Plus default directory.

   Typically, this default directory is owb home\bin.

4. Rename the generated script.

   As you complete these instructions, you generate a separate script for each Warehouse Builder target schema use. Consider renaming the file to indicate the target schema.

5. Transfer the generated and renamed ext_dirs_TargetUserName.sql file to a temporary location on the new host.

6. On the new host, use SQL*Plus to connect as the Warehouse Builder 11g Release 1 (11.1) target schema user and run ext_dirs.sql.

7. Repeat steps 2 through 6 for each target schema user you intend to migrate.

Importing Target Schemas to the New Database

Use Oracle Import to import the target schema files into the new user you created.

To import a target schema:

1. To import the target schema DMP file you created in "Exporting Target Schemas from the Existing Runtime Environment", enter the following command:

   imp OldOWBTargetUserName/OldOWBTargetUserPassword@New_DBTNSConnection FILE=OldOWBTarget.dmp LOG=NewOWBTarget.log

   OldOWBTargetUser stands for the Warehouse Builder target schema user from the existing version of Warehouse Builder.

   For example, enter:

   imp owb_target/owb_target@New10gConnection FILE=owb_target.dmp LOG=c:\temp\owb_target_import.log

2. Examine the import log file, whose name and location you specified in the import command.

   Proceed to the next step only if the last line of the log file indicates a successful completion.

   If the last line of the log file indicates an unsuccessful completion, you must fix all import errors before proceeding.

3. Repeat these instructions for each target schema you want to migrate.

Exporting All Design Metadata from Warehouse Builder 10g Release 2 (10.2)

In these instructions, you modify and run a tcl script using Warehouse Builder 10g Release 2 (10.2). The script exports the entire design metadata from a Warehouse Builder 10.2 repository.All the design metadata is exported as a single unit.

To export all design metadata:

1. Copy the tcl script from the home directory of the current release into a temporary directory.

   Locate ExportEntireRepos.tcl in owb 11.1 home/owb/bin/upgrade/.

2. Use a text editor to update the copy of ExportEntireRepos.tcl that you saved in a temporary directory.

   Specify the Warehouse Builder 10g Release 2 repository owner for the connection information. This includes the repository owner user name, password, host, port and service.

   Specify the export and import directory. These directories must specify the same directory location. The export and import directory will contain the MDL data files, log files and generated scripts.

3. Make sure no one is logged into the Warehouse Builder 10g Release 2 repository. The export script requires exclusive access to the repository for the upgrade process.

4. Run OMB Plus using the tcl script.

   For Unix, type the following command on the same line:

   owb 10.2 home/owb/bin/unix/OMBPlus.sh temp directory/ExportEntireRepos.tcl

   For Windows, type the following command on the same line:

   owb 10.2 home\owb\bin\win32\OMBPlus.bat temp directory\ExportEntireRepos.tcl

5. Verify that the export completed successfully by reviewing ExportEntireRepos.log located in the export directory.

Importing All Design Metadata into the Current Release

In these instructions, you modify and run a tcl script. The script imports the entire design metadata as a single unit from Warehouse Builder 10.2 into a Warehouse Builder 11.1 repository.

To import all design metadata:

1. If you have not already done so, create a workspace and a workspace owner in the new release.

   Refer to Chapter 2, "Managing Workspaces and Workspace Users" for additional information.

2. If the new repository resides on the same Database as the previous repository, skip to the next step.

   If the new repository resides on a different Database from the previous repository, then create the same Warehouse Builder users from the previous repository in the new database, e.g., use SQL*Plus.

   SQL> CREATE USER owb User Name IDENTIFIED BY password DEFAULT TABLESPACE debasing...

   Refer to Oracle SQL Language Reference documentation for details for CREATE USER command.

   Note: This step is important if you want to migrate security information into Warehouse Builder 11g Release 1. The upgrade process creates the Warehouse Builder users if the database users exist. So there is no need to create the Warehouse Builder users explicitly.

3. Use a text editor to update the script in owb 11.1 home/owb/bin/upgrade/ImportEntireRepos.tcl.

   Specify the Warehouse Builder 11g Release 1 workspace owner and workspace. For the connection information, include the workspace owner user name, password, host, port and service.

   Specify the export and import directory. The export and import directory must be the same directory specified in ExportEntireRepos.tcl. The import directory will contain the log files of the import.

4. Run OMB Plus using the tcl script. Enter the commands on the same line.

   For UNIX, type the following command on the same line:

   owb 11.1 home/owb/bin/unix/OMBPlus.sh owb 11.1 home/owb/bin/upgrade/ImportEntireRepos.tcl

   For Windows, type the following command on the same line:

   owb 11.1 home\owb\bin\win32\OMBPlus.bat owb 11.1 home\owb\bin\upgrade\ImportEntireRepos.tcl

5. Verify that the Import completed successfully by reviewing ImportEntireRepos.log located in the import directory.

Exporting Design Metadata from a Prior Release of Warehouse Builder

Export each project, collection, or public transformation you want to migrate to Metadata Loader (MDL) files using the Metadata Loader. If you have created any user-defined definitions, then export these objects too. For more information on exporting metadata, see Oracle Warehouse Builder User's Guide.

Note: You must export and import metadata using the Metadata Loader. 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, collection, or public transformation you want to export.

   For information about exporting user-defined definitions, refer to Oracle Warehouse Builder User's Guide.

2. From the Project menu, choose Export Metadata, then File.

   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 Oracle Warehouse Builder User's Guide.

Importing Design Metadata to Warehouse Builder

After having installed the new software, you must import and upgrade design metadata into the new version of Warehouse Builder. Ensure that you first import custom public transformations, if any. Use the Metadata Import utility to import design metadata. For more information on importing metadata, see Oracle Warehouse Builder User's Guide.

Note: Warehouse Builder upgrade does not support files that were exported or imported using back end database commands.

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

1. From the new Warehouse Builder Design Center, select the Design menu, Import, and Warehouse Builder Metadata.

   The Metadata Import dialog is displayed.

2. In the File Name field, specify the path and file name of the exported data from the former repository.

3. In the Log File field, specify the path and file name of the log file or click Browse to locate a directory and file name. Warehouse Builder records information about the import in this log file.

4. In the Import Option section, select the import option used while importing metadata. The options available are:

   • Create new metadata only: Adds new metadata to a repository.

   • Update metadata (replace existing objects and create new metadata): Adds new objects and replaces existing objects with those in the MDL file.

   • Merge metadata (merge existing objects and create new metadata): Adds new objects and overwrites existing objects only if they are different from those in the MDL file. Existing objects are not deleted.

   • Replace existing objects only: Replaces existing objects in the repository.

5. In the Match By section, select Universal Identifier.

6. (Optional) If the MDL file contains additional languages or user-defined definitions, click the Advanced button to select the options that include them in the import.

   Because the MDL file being imported was created using an earlier version of Warehouse Builder, the Metadata Upgrade dialog is displayed. Click Upgrade to upgrade the MDL file to the current version. Click Cancel if you do not want to upgrade the MDL file.

   If the MDL file is upgraded, then the Import Advanced Options dialog displays. Use this dialog to import the following:

   • Additional language metadata

   • User-defined definitions

   Click OK to save your selections and close the Import Advanced dialog. For more information about the advanced import options, refer Oracle Warehouse Builder User's Guide.

7. (Optional) To view a detailed summary of the contents of the export MDL file, click Show Summary.

   Because the MDL file being imported was created using an earlier version of Warehouse Builder, the Metadata Upgrade dialog is displayed. Click Upgrade to upgrade the export MDL file to the current version. Click Cancel if you do not want to upgrade the MDL file.

   If the MDL file is upgraded, the Show Summary dialog is displayed. This dialog provides a brief summary of the contents of the export MDL file.

8. Click Import to import the MDL file.

   If the MDL file was not previously upgraded in step 6 or step 7, the Metadata Upgrade dialog is displayed. Click Upgrade to upgrade the export MDL file to the current version. Click Cancel if you do not want to upgrade the MDL file.

   If you click Upgrade, then the Metadata Progress dialog displays the progress of the upgrade and import operation. After the upgrade completes, click Close to return to the Design Center.

In prior versions of Warehouse Builder, locations and runtime repository connections were owned by individual projects. Beginning in Oracle Warehouse Builder 10g Release 2, locations and connections are owned by a project called PUBLIC_PROJECT. If locations or runtime repository connections with the same names as the ones being upgraded exist in the repository, then Warehouse Builder generates unique names when they are imported for the upgrade. You may need to manually clean up location associations after the upgrade is complete.

For more information about the changes made to repository objects after an upgrade and import operation, see Oracle Warehouse Builder User's Guide.

Choose a Workspace

From the newly created repository, select the workspace you created during the installation process.

Move

When you select the Move operation, the assistant prompts you to connect to the original runtime repository or control center as a repository owner.

In this step, you upgrade runtime audit data so that the new control center displays the correct status, history, location details, and version numbers for objects you deployed and ran in the runtime repository or control center from the previous release.

This option is only available if the new control center does not contain any registered locations or audit data.

Upgrade

The assistant lists the locations registered in the new control center and displays whether they are valid or not. Figure 3-2 displays the Upgrade Registered Locations dialog.

Figure 3-2 Upgrade Registered Locations Dialog

Description of "Figure 3-2 Upgrade Registered Locations Dialog"

Upgrade Operation: Location Details

Use this button to check and fix locations for possible errors. Ensure that all locations are valid before proceeding. Figure 3-3 displays the Registered Locations Details dialog.

Figure 3-3 Registered Locations Details Dialog

Description of "Figure 3-3 Registered Locations Details Dialog"

This dialog box displays the registration details of the currently selected location. Select Get Status to evaluate its status and view the results of various tests.

For each location, the assistant checks that the password is valid and that it is the same as the registered password. The assistant verifies that the version registered for the location is the same as the actual version. If the assistant detects any errors, then you can select Upgrade to correct the errors.

Generate

Use this operation to generate a TCL script of OMB Plus commands that you can use to alter the location information stored against locations in the design repository. Such information was not stored against locations in Warehouse Builder 10.1.x. Also, depending on your migration path, it may have been altered as part of migration.

To generate a Tcl script that updates location information:

1. Ensure that you previously imported MDL from the previous design repository to the new Warehouse Builder 11.1 repository as described in "Upgrading a Design Repository".

2. Click Generate.

3. Edit the script to change the value of the variable CC_NAME.

   Either edit the script in the user interface and select save again. Or edit the script in Notepad.

   Set CC_NAME to the name of the control center object in the new design repository. For example, assume that you migrated a runtime repository called MY_DEV_RUNTIME from a previous of Warehouse Builder. Therefore, in the Tcl script, change the line

   CC_NAME cc_name

   to

   CC_NAME MY_DEV_RUNTIME

4. Save the edited script in a temporary directory.

5. Click OK in the Generate dialog and complete the Control Center Upgrade Assistant.

6. Proceed with "Upgrading Locations in the Design Repository".