3 Upgrading Your Oracle Data Integrator Environment

This chapter describes how to upgrade your existing Oracle Data Integrator 10g environment to Oracle Data Integrator 11g. If you are upgrading an existing 11g environment to a newer 11g version of ODI, see the Oracle Fusion Middleware Patching Guide.

3.1 Task 1: Understand the Starting Points for Oracle Data Integrator

The supported starting points for an upgrade depend on the components you will be upgrading and the components you have installed.

It is also important to note that if you are upgrading ODI 10g to 11.1.1.5.0, you will use the Upgrade Assistant for ODI as described in the following sections. If you are upgrading ODI 11.1.1.3.0 to 11.1.1.5.0, you will use the Oracle Patch Set Assistant for ODI. For more information on using the Patch Set Assistant, see Oracle Fusion Middleware Patching Guide.

This section describes the starting points for the following upgrade operations:

The Oracle Data Integrator 10g Release 3 (10.1.3) Data Quality products are not part of the standard Oracle Data Integrator 11g installation. These components must be installed as a separate installation and upgraded in a separate upgrade process.

The database that contains the Oracle Data Integrator repository must be supported by Oracle Fusion Middleware 11g. The Upgrade Assistant supports Master and Work repository upgrades for databases listed below.

NOTE: The Oracle Database DBA must have SYSDBA privileges to perform the upgrade.

Microsoft SQL Server

Sybase Adaptive Server

NOTE: In order to upgrade repositories stored in Sybase, it is required that the repository is stored in a database with a page size of 4k or greater. If the repository is stored in a database with a smaller page size, the database should be migrated to a database of an appropriate page size before upgrading the repository.

NOTE: The ODI 11g Upgrade Assistant includes the drivers for all the supported databases except PostgreSQL. To use the Upgrade Assistant with PostgreSQL, you must have postgresql-8.4-701.jdbc4.jar installed in the following directory before using the Upgrade Assistant:

ODI_HOME/upgrade/jlib

Where ODI_HOME is the installation directory of Oracle Data Integrator 11g.

3.2 Task 2: Plan the Oracle Data Integrator Component Architecture

The Oracle Data Integrator 10g component architecture changed with ODI 11g. Although you can keep the same component organization as ODI 10g, Oracle recommends that you review the new components organization in "Introduction to Oracle Data Integrator" in Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator to identify any architecture changes.

3.3 Task 3: If Necessary, Upgrade the Database that Contains the ODI Repository

The database that contains the Oracle Data Integrator repositories must be supported by Oracle Fusion Middleware 11g. For the latest list of supported databases, see:

3.4 Task 4: Copy (Clone) Existing Master and Work Repositories

Oracle recommends that you clone each original ODI Master and Work repository before beginning the upgrade process. During the Master Repository upgrade process, the Upgrade Assistant will prompt you for the location and credentials of the cloned Master Repository and Work Repositories.

Caution:

The Upgrade Assistant does not support the upgrade of repositories distributed on different instances or technologies.

Both ODI 10g and ODI 11g support a repository configuration where the Master Repository can be on different database instances than the Work Repository. During the upgrade process, however, the Master Repository and all Work Repositories must reside on the same database instance. After the upgrade process they can be redistributed.

Once both the Master and Work repositories are upgraded and optionally redistributed, you must update the Work Repositories connections defined in the Topology to match their post-upgrade location. See Section 4.2, "Updating the Work Repository Connection Parameters" for more information on updating the connection parameters to the Work Repository as defined in the Topology.

The following sections provide basic schema cloning procedures for databases that are supported to host ODI repositories. For detailed information, refer to your database-specific documentation.

The goal of this section is to stress the importance of creating a clone (or copy) of each of the ODI 10g or 11g repositories before you begin the upgrade process. The cloning procedures documented in this section are sample procedures for each of the ODI 11g supported databases. You are not restricted to using these procedures. Always use the cloning procedures that suit your specific needs.

WARNING:

Once you have completed the cloning process, DO NOT use your Oracle Data Integrator 10.1.3.5 client to validate that the cloning process worked properly. The Upgrade Assistant will perform validation checks on the cloned repositories before the upgrade.

3.4.1 Schema Cloning Process for Oracle Database

The following steps can be used to clone Oracle Database schemas for ODI:

Create login and user for cloned Master and Work schemas using SQL Management Studio.

Using SQL Management Studio Express, create logins and users to access cloned Master and Work schemas. Be sure to select the correct database instance in SQL Management Studio Express, as these commands are applied to the selected database instance.

3.4.4.2 Different Host Cloning Process for ODI 10g Master and Work Schemas

Use the following steps to clone IBM DB2 schemas on different hosts or platforms:

Export DDL and Data from Master and Work schemas using DB2 Database Movement Tool and DDL Extracting Tool.

DB2 Database Movement Tool produces PC/IXF files with data and db2move.lst file with list of tables, Files are produced in the folder where the tool was called. The DDL Extracting Tool produces db2master.sql and db2work.sql with SQL queries to recreate database structure.

Assign created logins to renamed users using ISQL Utility. Note that you cannot assign a login to a user. You must assign logins to users directly.

Example:

select suid from master.dbo.syslogins where name='ODIMCP'
go
NOTE: Use the returned suid 'ODIMCP' in the first update query below.
select suid from master.dbo.syslogins where name='ODIWCP'
go
NOTE: Use returned suid 'ODIWCP' in the second update query below.
sp_configure 'allow updates', 1
go
update dbo.sysusers set suid=5 where name='ODIMCP'
go
update dbo.sysusers set suid=6 where name='ODIWCP'
go
sp_configure 'allow updates', 0
go

3.4.7 Schema Cloning Process for Hypersonic SQL 1.7.3.3

You must upgrade your HSQL database version 1.7.3 to version 2.0 before upgrading to ODI 11.1.1.5.0. The upgrade process is performed in two steps: from version 1.7.3.3 to 1.8.x and from 1.8.x to 2.0.0.

The Upgrade Assistant Prerequisite screen prompts you to indicate whether the backup of the ODI repositories has been completed. It is important to note, however, that the Upgrade Assistant will not validate that a backup has been created.

Caution:

This is a critical step of the upgrade process; especially if the repositories were not cloned. In the event that upgrade results are unsatisfactory, having a backup copy of the ODI repositories ensures that you will not lose important data. For more information on creating a backup, refer to your database backup and recovery documentation.

3.6 Task 6: Install and Configure Oracle Data Integrator 11g

Before running the Upgrade Assistant, use the Oracle Universal Installer to install and configure ODI 11g. Note that it is not necessary to run the Repository Configuration Utility (RCU), finalize the agent configuration, or configure Java EE components during the installation process.

The Upgrade Complete screen provides a link to the upgrade log file. Review the log file and test the upgraded applications to ensure that they are working as expected. Click Close to dismiss the Upgrade Assistant.

ODI UA uses actual data and structure of the ODI master repository to determine if a repository has already been upgraded. The Upgrade Assistant will return a message stating that the repository has already been upgraded if the following conditions exist:

a version registry has valid state and version for the repository

the repository is 11g

version of the repository is equal or greater than version of ODI SDK used by UA

To debug or view the repository catalog information, use the following query on Table schema_version_registry which is stored in the Admin user (not in the ODI schema/repository):

If the upgrade process fails, you must close the Upgrade Assistant, correct the issue, and then restart the Upgrade Assistant as described in Section 3.7.1.

If the upgrade process fails after the upgrade process has started, you must drop the cloned repository and start from a freshly cloned repository in addition to correcting the underlying issue. There is no way to restart the failed upgrade process.

Only if you selected Upgrade Oracle Data Integrator Repositories on the Specify Operation screen.

The Specify Target Database screen prompts you for the database type and DBA login credentials of the target database that hosts the Master and Work Repositories. You must also provide the database user name and password used to connect to the Master Repository and the ODI Supervisor name and password.

If status is succeeded for all components, click Next. If the examination fails for one or more of the components, click Back to review the information you entered. The log file will also provide important information. (The log file location is listed at the bottom of the screen.)

The Upgrade Complete screen provides a link to the upgrade log file. Review the log file and test the upgraded applications to ensure that they are working as expected. Click Close to dismiss the Upgrade Assistant.

3.8 Task 8: Perform Any Required Post-Upgrade Manual Steps

Depending on the options you selected during the upgrade process, you may have to perform manual procedures to finalize the upgrade process.