Skip Headers
Oracle® Fusion Middleware Upgrade Guide for Oracle Data Integrator
11g Release 1 (11.1.1)

Part Number E12642-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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.

This chapter contains the following sections:

3.1 Understanding 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.6.0, you will use the Upgrade Assistant for ODI as described in the following sections. If you are upgrading ODI 11.1.1.5.0 to 11.1.1.6.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:

Note:

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. For more information, see Oracle Fusion Middleware Upgrade Guide for Oracle Data Profiling and Oracle Data Quality for Oracle Data Integrator.

3.1.1 Starting Points When Upgrading the Middle Tier Instance

The ODI Middle Tier Upgrade copies the 10g Agent libraries, drivers and scripting engines into the 11g Standalone Agent installation folder.

To run the Middle Tier Upgrade for Oracle Data Integrator, you must have both the ODI 10g (10.1.3) Run-Time Agent and ODI 11g Standalone Agent installed on the same system.

3.1.2 Starting Points When Upgrading the Oracle Data Integrator Repositories

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:

You can export objects from 10g and import into 11.1.1.4.0 or higher using the ODI Smart Export and Import feature.

For more information on upgrading your ODI projects through export/import, see "Exporting/Importing" in Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

If your current database is not listed below or to validate that your database version is compatible with ODI 11g, see Upgrading the Database that Contains the ODI Repository.

  • Oracle RDBMS

    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.

    For details on increasing the page size, refer to http://www.sybase.com/content/1021203/sybmigrate_wp.pdf

  • Hypersonic SQL

  • IBM DB2

  • IBM DB2/400

  • PostgreSQL

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

http://www.oracle.com/technetwork/middleware/ias/downloads/fusion-certification-100350.html

For instructions on verifying that your database meets the requirements of Oracle Fusion Middleware 11g, see "Upgrading and Preparing Your Databases" in the Oracle Fusion Middleware Upgrade Planning Guide. Oracle recommends that you refer to your database-specific upgrade documentation for additional information.

Note:

If your RDBMS was supported in Oracle Data Integrator 10g, but is no longer supported in Oracle Data Integrator 11g, use the ODI 10g version to do the following before you upgrade:
  1. Export the ODI 10g repositories from the unsupported database systems/versions.

  2. Import Master and Work Repositories into new repositories created with the 10g version into supported database systems/versions.

For additional information, see "Repository-Level Export/Import" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

3.4 Verifying that Work Repositories are Attached to the Correct Schemas

As of version 11.1.1.6.0, the Upgrade Assistant upgrades all work repositories attached to master repository. In order to have a successful upgrade of the work repositories, you must ensure that the repositories are attached to the correct schema and host before you upgrade:

  1. Connect to the ODI master repository using your existing ODI client (pre-upgraded version).

    For information on connecting to the ODI master repository, see "Connecting to the Master Repository" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

  2. Validate that the work repositories are attached to the correct work repository schema and host.

    For more information, see: "Connecting to a Work Repository" and "Attaching and Deleting a Work Repository" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

Note:

If your schema is hosted on an Oracle database, you must also issue the following grant to all work repository schemas before upgrading:

GRANT EXECUTE on dbms_lock to <WORKSCHEAMA>;

3.5 Cloning Existing Master and Work Repositories

Oracle recommends that you clone (copy) 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.

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.

Note:

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.

3.5.1 Schema Cloning Process for Oracle Database

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

  1. Export ODI 10g Master and Work schemas using Oracle Export Utility

    Example:

    exp userid=odi_master_10g/odi_master_10g file=/tmp/odi_master_10g.dmp
    exp userid=odi_work_10g/odi_work_10g file=/tmp/odi_work_10g.dmp
    exp userid=odi_work1_10g/odi_work1_10g file=/tmp/odi_work1_10g.dmp
    

    Export ODI 11g Master and Work schemas using Datapump Utilities

    Example:

    expdp odi_tmp/odi_tmppwd schemas=odiw10135 dumpfile=odiw10135.dmp
    
  2. Create Master and Work clone schemas

    Using SQL*Plus, create Master and Work clone schemas and grant connect/resource privileges.

    Example:

    create user  odi_master_10g_cp identified by odi_master_10g_cp;
    create user  odi_work_10g_cp identified by odi_work_10g_cp;
    create user  odi_work1_10g_cp identified by odi_work1_10g_cp;
    grant connect,resource to odi_master_10g_cp, odi_work_10g_cp,odi_work1_10g_cp;
    
  3. Import ODI 10g Master and Work schemas into cloned schemas using Oracle Import Utility.

    Using Oracle Import, import the ODI 10g Master and Work schema dump into the cloned Master and Work schemas.

    Example:

    imp userid='system/manager' touser=odi_master_10g_cp fromuser=odi_master_10g file=/tmp/odi_master_10g.dmp
    imp userid='system/manager' touser=odi_work_10g_cp fromuser=odi_work_10g file=/tmp/odi_work_10g.dmp
    imp userid='system/manager' touser=odi_work1_10g_cp fromuser=odi_work1_10g file=/tmp/odi_work1_10g.dmp
    

    Import ODI 11g Master and Work schemas using the Datapump Utilities

    Example:

    impdp ODI_TMP/ODI_TMPPWD dumpfile=odim10135 remap_tablespace=repo10135:odi11gremap_schema=odim10135:odim1113
    

    Note that with impdp it is also possible to modify the schema name and tablespace for data storage. The remap_xx parameters are optional.

3.5.2 Schema Cloning Process for Microsoft SQL Server Database

The following steps can be used to clone Microsoft SQL 2005/2008 database schemas:

  1. Export the ODI 10g Master and Work schemas using SQL Management Studio.

    Example:

    BACKUP DATABASE [odi_10g] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\odi_10g.bak' WITH INIT, NOSKIP;
    
  2. Restore Master and Work schemas into the new database using SQL Management Studio.

    Using SQL Management Studio Express perform the following:

    1. Restore the Master and Work schemas.

    2. Print logical names of files used to store the database.

    3. Move the files used to store database.

    Example:

    RESTORE DATABASE [odi_10g_cp] FROM DISK = N'C:\Program Files\Microsoft SQL 
    Server\MSSQL.1\MSSQL\Backup\odi_10g.bak' 
    WITH FILE = 1, MOVE N'odi_10g' TO N'C:\Program Files\Microsoft SQL 
    Server\MSSQL.1\MSSQL\DATA\odi_10g_cp.mdf', 
    MOVE N'odi_10g_log' TO N'C:\Program Files\Microsoft SQL 
    Server\MSSQL.1\MSSQL\DATA\odi_10g_cp_log.ldf', NOUNLOAD; 
    go 
    
  3. 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.

    Example:

    create login odi_10g_cp with password=N'odi_10g_cp', 
    default_database=odi_10g_cp,  check_expiration = off, check_policy = off; 
    go 
    USE odi_10g_cp
    go 
    create user odi_10g_cp  for login odi_10g_cp; 
    go 
    USE odi_10g_cp 
    go 
    
  4. To move the old schema to the new schema location, run the following SQL script:

    NOTE: In the example below, the old schema name is odi_10g and the new schema name is odi_10g_cp.

    CREATE SCHEMA [odi_10g_cp] AUTHORIZATION odi_10g_cp
    go
    .
    DECLARE @OldSchema AS varchar(255)
    DECLARE @NewSchema AS varchar(255)
    . 
    SET @OldSchema = 'odi_10g'
    SET @NewSchema = 'odi_10g_cp'
    .
    DECLARE @sql AS varchar(MAX)
    SET @sql = CHAR(13) + CHAR(10)
    .
    SELECT @sql = @sql + 'ALTER SCHEMA [' + @NewSchema + '] TRANSFER [' + 
    TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + CHAR(13) + CHAR(10) 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = @OldSchema
    .
    EXEC (@sql)
    go 
    
  5. To finalize the schema move, run the following SQL query:

    DROP SCHEMA [odi_10g] 
    go 
    drop user odi_10g; 
    go 
    alter user odi_10g_cp with default_schema = odi_10g_cp; 
    go 
    grant create table, create view, create procedure,create function to 
    odi_10g_cp; 
    go
    

3.5.3 Schema Cloning Process for PostgreSQL Database

Use the following steps to clone PostgreSQL database schemas:

  1. Export ODI 10g Master and Work schemas using PostgreSQL Backup Utility.

    Example:

    pg_dump -U odi_master_10g --schema=odi_master_10g -Ft -f "c:/odi_master_10g.dmp.tar" odi_10g
    pg_dump -U odi_work_10g --schema=odi_work_10g -Ft -f "c:/odi_work_10g.dmp.tar" odi_10g
    pg_dump -U odi_work1_10g --schema=odi_work1_10g -Ft -f "c:/odi_work1_10g.dmp.tar" odi_10g
    
  2. Create a clone of the database and the Master and Work users.

    Using PostgreSQL Interactive Terminal, create a clone database and users for Master and Work clone schemas with SUPERUSER option.

    Example:

    psql -U postres
    CREATE ROLE odi_master_10g_cp WITH SUPERUSER LOGIN PASSWORD 'odi_master_10g_cp';
    CREATE ROLE odi_work_10g_cp WITH SUPERUSER LOGIN PASSWORD 'odi_work_10g_cp';
    CREATE ROLE odi_work1_10g_cp WITH SUPERUSER LOGIN PASSWORD 'odi_work1_10g_cp';
    CREATE DATABASE odi_10g_cp with owner=odi_master_10g_cp;
    
  3. Restore Master and Work schemas into clone database using PostgreSQL.

    Using PostgreSQL Restore Utility, restore Master and Work schemas into cloned database.

    Example:

    pg_restore -O -U odi_master_10g_cp -d odi_10g_cp -C "c:/odi_master_10g.dmp.tar"
    pg_restore -O -U odi_work_10g_cp -d odi_10g_cp "c:/odi_work_10g.dmp.tar"
    pg_restore -O -U odi_work1_10g_cp -d odi_10g_cp "c:/odi_work1_10g.dmp.tar"
    
  4. Configure Master and Work clone schemas.

    Using PostgreSQL Interactive Terminal, set NOSUPERUSER for Master and Work clone schema users.

    Example:

    psql -U postres -d odi_10g_cp
     
    ALTER USER  odi_master_10g_cp WITH NOSUPERUSER;
    ALTER USER  odi_work_10g_cp WITH NOSUPERUSER;
    ALTER USER  odi_work1_10g_cp WITH NOSUPERUSER;
    

    Using PostgreSQL Interactive Terminal, rename Master and Work clone schemas.

    Example:

    ALTER SCHEMA odi_master_10g RENAME TO odi_master_10g_cp;
    ALTER SCHEMA odi_work_10g RENAME TO odi_work_10g_cp;
    ALTER SCHEMA odi_work1_10g RENAME TO odi_work1_10g_cp;
    

3.5.4 Schema Cloning Process for IBM DB2 Universal Database

Chose one of the following procedures to clone IBM's DB2 Universal Database schemas:

Note:

The Page size for database has to be 32768 (32k) and operating system users ODI_MASTER_10G_CP and ODI_WORK_10G_CP have to be created manually.

3.5.4.1 Same Host Cloning Process for ODI 10g Master and Work Schemas

Use the following steps to clone IBM DB2 schemas on the same host or platform:

  1. Create DB2 Database using Command Line Processor.

    Example:

    db2 CREATE DATABASE ODI11 AUTOMATIC STORAGE YES  ON 'C:\' DBPATH ON 'C:\' USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 32768
    
  2. Copy ODI 10g Master and Work schemas using DB2 Database Movement Tool to new schema.

    Master Schema Example:

    db2move ODI10G COPY -sn odi_master_10g -co TARGET_DB ODI10GCP USER db2admin USING welcome SCHEMA_MAP ((odi_master_10g,odi_master_10g_cp)) TABLESPACE_MAP ((USERSPACE1,USERSPACE1),SYS_ANY) owner odi_master_10g_cp
    

    Work Schema Example:

    db2move ODI10G COPY -sn odi_work_10g -co TARGET_DB ODI10GCP USER db2admin USING welcome SCHEMA_MAP ((odi_work_10g,odi_work_10g_cp)) TABLESPACE_MAP ((USERSPACE1,USERSPACE1),SYS_ANY) owner odi_work_10g_cp
    

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

  1. 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.

    Example:

    db2move ODI10G export -sn odi_master_10g,odi_work_10g
    db2look -d ODI10G -z odi_master_10g -e -o c:/db2master.sql
    db2look -d ODI10G -z odi_work_10g -e -o c:/db2work.sql
    
  2. Transfer exported files to new location.

    1. Ensure that the PC/IXF files were transferred in binary mode, and that the db2move.lst file and the db2master.sql and db2work.sql files were transferred in ASCII mode.

    2. Place the PC/IXF files where the DB2 Database Movement Tools is located.

  3. Create DB2 database using Command Line Processor.

    Example:

    db2 CREATE DATABASE ODI10G AUTOMATIC STORAGE YES  ON 'C:\' DBPATH ON 'C:\' USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 32768
    
  4. Import the exported DDL to the new database using the Command Line Processor.

    Example:

    db2 -tvf c:/db2backup/db2master.sql
    db2 -tvf c:/db2backup/db2work.sql
    
  5. Import exported data to new database using DB2 Database Movement Tool.

    Example:

    db2move ODI10G load
    
  6. Verify that cloned schemas are intact; some tables may be in "check pending" state (because of check constraint).

    Use command set integrity to move to the normal state.

    Example:

    db2 set integrity for <table name> immediate checked
    

3.5.5 Schema Cloning Process for IBM DB2 AS/400 Database

Use the following steps to clone IBM DB2 AS/400 database schemas:

  1. Save ODI 10g Master and Work schemas.

    Using Telnet Client, save the ODI 10g Master and Work schemas.

    Example:

    CRTLIB LIB(ODI10GDMP) TYPE(*TEST)
    CRTSAVF FILE(ODI10GDMP/ODIM10GDMP)
    CRTSAVF FILE(ODI10GDMP/ODIW10GDMP)
    SAVLIB ODIM10G DEV(*SAVF) SAVF(ODI10GDMP/ODIM10GDMP) ACCPTH(*YES) CLEAR(*ALL)
    SAVLIB ODIW10G DEV(*SAVF) SAVF(ODI10GDMP/ODIW10GDMP) ACCPTH(*YES) CLEAR(*ALL)
    
  2. Create Master and Work clone schemas.

    Using Telnet, create user profiles for the cloned schemas.

    Example:

    CRTUSRPRF USRPRF(ODIMCP) PASSWORD(ODIMCP) PWDEXP(*NO)
    CRTUSRPRF USRPRF(ODIWCP) PASSWORD(ODIWCP) PWDEXP(*NO)
    
  3. Create ODI 10g Master and Work clone schemas.

    Using iSeries Navigator or Interactive SQL (STRSQL), create ODI 10g Master and Work clone schemas.

    Example:

    CREATE SCHEMA AUTHORIZATION ODIMCP
    CREATE SCHEMA AUTHORIZATION ODIWCP
    
  4. Restore ODI 10g Master and Work schemas into clone schemas.

    Using Telnet, restore ODI 10g Master and Work schema into clone schemas.

    Example:

    RSTLIB SAVLIB(ODIM10G) DEV(*SAVF) SAVF(ODI10GDMP/ODIM10GDMP) OPTION(*NEW) RSTLIB(ODIMCP)
    RSTLIB SAVLIB(ODIW10G) DEV(*SAVF) SAVF(ODI10GDMP/ODIW10GDMP) OPTION(*NEW) RSTLIB(ODIWCP)
    CHGOWN OBJ('QSYS.LIB/ODIMCP.LIB/*.*')  NEWOWN(ODIMCP)
    CHGOWN OBJ('QSYS.LIB/ODIWCP.LIB/*.*')  NEWOWN(ODIWCP)
    
  5. Using iSeries Navigator, select tables in schemas ODIM10G and ODIW10G to stop journalizing.

    1. Select Journal from schema ODIM10G: Databases>Denmlas3>Schemas>ODIM10G>Journals

    2. Select file QSQJRN.

    3. Select Start or End Table Journaling from journal's context menu.

    4. Select tables for schema ODIM10G in 'Tables already journalized' view.

    5. Click Remove to remove them.

    6. Repeat steps a-e for Schema ODIW10G (Use ODIW10G instead of ODIM10G).

  6. Using iSeries Navigator, select tables in schemas ODIMCP and ODIWCP to start journalizing.

    Example:

    1. Select Journal from schema ODIMCP: Databases->Denmlas3->Schemas->ODIMCP->Journals.

    2. Select file QSQJRN.

    3. Select Start or End Table Journaling from journal's context menu.

    4. Select schema ODIMCP in tables' view.

    5. Add all tables from schema ODIMCP to 'Table to journal' view.

    6. Check 'Journal both images' and 'Omit open/close entries' for them. Click OK.

    7. Repeat steps a-f for Schema ODIWCP (Use ODIWCP instead of ODIMCP).

3.5.6 Schema Cloning Process for Sybase AS Enterprise 15+

Use the following steps to clone Sybase AS Enterprise database schemas:

  1. Export ODI 10g Master and Work schema using ISQL Utility.

    Example:

    isql -D odi -S STANE08 -U sa -P welcome
     
    dump database odi10g to "c:/odi10g.dmp" with init
    go
    
  2. Restore Master and Work schemas into new database using ISQL Utility.

    Using ISQL Utility, restore Master and Work schemas.

    Example:

    isql -S STANE08 -U sa -P welcome
     
    create database odi10gcp on master = 860
    go
    load database odi10gcp from "c:/odi10g.dmp" with override
    go
    ONLINE DATABASE odi10gcp
    go
    
  3. Create logins for cloned Master and Work schemas using ISQL Utility.

    Using ISQL Utility, create logins to access the cloned Master and Work schemas.

    Example:

    sp_addlogin ODIMCP, ODIMCP, odi10gcp       
    go
    sp_addlogin ODIWCP, ODIWCP, odi10gcp
    go
    
  4. Rename original Master and Work schemas using ISQL Utility. Note that you cannot change the owner of a table or rename user. You must rename users directly.

    Example:

    sql -D odi10gcp -S STANE08 -U sa -P welcome
     
    sp_configure 'allow updates', 1
    go
    update dbo.sysusers set name='ODIMCP' where name='ODI_MASTER_10G'
    go
    update dbo.sysusers set name='ODIWCP' where name='ODI_WORK_10G'
    go
    sp_configure 'allow updates', 0
    go
    
  5. 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.5.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.

For more information on upgrading to HSQL database version 2.0, see http://www.hsqldb.org/doc/2.0/guide/deployment-chapt.html#deployment_upgrade-sect.

Use the following steps to clone Hypersonic SQL 1.7.3.3 database schemas while upgrading to Hypersonic SQL 2.0.0:

  1. Update server.properties

    server.database.0=file:ODI/odi_10g
    server.dbname.0=odi_10g
    
  2. Update sqltool.rc with new connection information:

    urlid odi_sa
    url jdbc:hsqldb:hsql://localhost:9001/odi_10g
    username SA
    password
    
  3. Start Server

    /etc/init.d/hsqldb start
    
  4. Using SQL Tool, stop the server.

    java -jar ./lib/hsqldb.jar odi_sa
    checkpoint;
    shutdown;
    
  5. Clone ODI 10g Database copying *.properties, *.script and *.backup to location of new database.

    1. Copy declaration for your database from HSQL 1.7.3.3 to HSQL 1.8.x server.properties file.

    2. Copy your database files from HSQL 1.7.3.3 to HSQL 1.8.x

    3. Open script file for your database from HSQL 1.8.x and replace the following words to include quotation marks:

      POSITION in DDL statements to "POSITION"

      SCHEMA_VERSION_REGISTRY$ to "SCHEMA_VERSION_REGISTRY$"

    4. Start HSQL 1.8.x and database instance automatically upgrade your database.

    5. Using SQL TOOL, stop HSQL 1.8.x

      java -jar ./lib/hsqldb.jar odi_sa
      checkpoint;
      shutdown;
      
    6. Copy declaration for your database from HSQL 1.8.x to HSQL 2.0.0 server.properties file.

    7. Copy your database files from HSQL 1.8.x to HSQL 2.0.0

    8. Update server.properties for HSQL 2.0.0 to add declaration for clone database.

      server.database.1=file:ODI/odi_10g_cpserver.dbname.1=odi_10g_cp
      
    9. Update sqltool.rc with new connection information for clone database

      urlid odicp_sa
      url jdbc:hsqldb:hsql://localhost/odi_10g_cp
      username SA
      password
      
    10. Copy database's file to the location of new database. ODI Uses MEMORY table therefore copy only properties and script files.

      copy odi_10g.properties <HSQL1.8>/ODI/odi_10g.properties
      copy odi_10g.script <HSQL1.8>/ODI/odi_10g.script
      start HSQL 2.0.0
      
  6. Create new logins for cloned 10g Database Using SQL Tool

    Example of SQL Queries:

    create user odim10g_cp password odim10g_cp ADMIN
    create user odiw10g_cp password odiw10g_cp ADMIN
    drop user odim10g
    drop user odiw10g
    

3.6 Creating a Backup of the ODI Repositories to be Upgraded

Oracle recommends that you create a backup for each ODI Master and Work repository. The backup enables you to restore to a pre-upgrade state if necessary. For more information, see "Backup Strategies for Upgrade," in Oracle Fusion Middleware Upgrade Planning Guide.

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.7 Installing and Configuring 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 ODI installation and configuration procedures are documented in the Oracle Fusion Middleware Installation Guide for Oracle Data Integrator.

3.8 Running Upgrade Assistant to Upgrade ODI Middle Tier and Repositories

The Oracle Fusion Middleware Upgrade Assistant automates the upgrade of many aspects of your Oracle Data Integrator 10g environment. Some post-upgrade configuration procedures may be required, however.

The Upgrade Assistant performs the following tasks and provides the progress on each task:

The Upgrade Assistant is installed automatically into the /bin directory of your Oracle Data Integrator home (ODI_HOME).

3.8.1 Running the Upgrade Assistant for Oracle Data Integrator

To start the Upgrade Assistant using the graphical user interface:

  1. Change directory to the ODI_HOME/bin where ODI_HOME is the installation folder of Oracle Data Integrator directory of the Oracle Fusion Middleware installation.

  2. Enter the following command to start the Upgrade Assistant.

    On UNIX system:

    ./ua
    

    On Windows systems:

    ua.bat
    
  3. Provide the required information in each of the Upgrade Assistant screens.

3.8.2 Upgrading the Middle Tier Instance

The Middle Tier upgrade copies the ODI 10g Agent libraries, drivers and scripting engines into the 11g Standalone Agent installation folder.

Note:

You can perform the ODI upgrade operations in any order. If you want to upgrade the ODI repositories first, see Section 3.8.3.

To upgrade the Middle Tier Instance, start the Upgrade Assistant as described in Running the Upgrade Assistant for Oracle Data Integrator and provide the required information in the following screens:

Table 3-1 Installation Screens for Upgrading the Middle Tier Instance

Screen When Screen Appears Description

Welcome

Always

No action required.

Specify Operation

Always

Select the Upgrade Middle Tier Instance option.

Specify Source Home

Only if you selected Upgrade Middle Tier Instance on the Specify Operation screen.

Specify the location of the 10g Oracle home you want to upgrade.

Examine

Always

The Upgrade Assistant performs a series of validations before upgrading the selected components. Ensure that all validations have succeeded.

Upgrade Summary

Always

If the summary information looks correct, click Upgrade.

Upgrade Progress

Always

The Upgrading Components screen shows the progress of the upgrade process. Once complete, click Next.

End of Upgrade

Always

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.3 Upgrading the Oracle Data Integrator Repositories

The Oracle Data Integrator Repository upgrade option upgrades the Oracle Data Integrator 10g repositories to ODI 11g versions. The ODI 10g repositories should always be cloned (copied) before launching the Upgrade Assistant. See Cloning Existing Master and Work Repositories.

Note:

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

SELECT COMP_ID,COMP_NAME,MRC_NAME,OWNER,VERSION,STATUS,UPGRADED FROM schema_version_registry;

(On DB2/400 operating systems, the Admin user is QSECOFR, and the schema_version_registry table is located in the schema 'NULLID'.)

Rows with the component "ODI" are used to track ODI repositories.

To upgrade ODI Repositories, start the Upgrade Assistant as described in Running the Upgrade Assistant for Oracle Data Integrator and provide the required information in the following screens:

Caution:

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.8.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.

Table 3-2 Installation Screens for Upgrading the ODI Repositories

Screen Name When Screen Appears Description

Welcome

Always

No action required.

Specify Operation

Always

Select Upgrade Oracle Data Integrator Repositories and choose which additional repository upgrade options you want to perform:

  • Update Topology and Security Metadata

  • Replace KMs with Mandatory Updates

  • Check that Upgrade Occurs Only on Cloned Repositories

For more information on these options, see the Specify Operation screen description in Appendix A.

Prerequisites

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

The Upgrade Assistant requires that you indicate the following prerequisites have been met in order to continue the upgrade:

  • Database schema backup completed

    Oracle recommends that you backup your ODI repositories on the database before upgrading. The Upgrade Assistant does not verify that the repositories have been backed up, so this option serves as a reminder. See Creating a Backup of the ODI Repositories to be Upgraded for more information.

  • Database version is certified by Oracle for Fusion Middleware upgrade

    The Upgrade Assistant requires that the Oracle Data Integrator repositories reside on a supported database. See Upgrading the Database that Contains the ODI Repository for a link to the latest list of Oracle Data Integrator supported databases.

Target Database

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.

NOTE: The DBA user must have SYSDBA privileges.

Examine

Always

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.)

Upgrade Summary

Always

If the summary information looks correct, click Upgrade.

Upgrade Progress

Always

The Upgrading Components screen shows the progress of the upgrade process. Once complete, click Next.

End of Upgrade

Always

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.9 Performing Post-Upgrade Steps

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

Refer to Chapter 4, "Reconfiguring ODI After an Upgrade" for a description of the post-upgrade tasks you might need to perform for each of the Oracle Data Integrator components.