3 Upgrading Your Oracle Data Integrator Environment

3.1.1 Starting Points When Upgrading the Middle Tier Instance

ODI Middle Tier Upgrade migrates an ODI 10g Run-time Agent configuration (drivers and odiparams file parameters) to an ODI 11g Standalone Agent.

The starting point for the Middle Tier Upgrade is to have both an ODI 10g Run-Time Agent and an ODI 11g Standalone Agent installed on this machine.

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.

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

• Oracle RDBMS

• 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 11gR1 Upgrade Assistant includes the drivers for all the supported databases except PostgreSQL. To use the Upgrade Assistant with PostgreSQL, you must download the latest JDBC driver from http://jdbc.postgresql.org/download.html and copy it to the ODI_HOME/upgrade/jlib directory where ODI_HOME is the installation directory of Oracle Data Integrator 11g.

3.4.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 schema 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
   

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 Master and Work schema 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
   

3.4.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_cp.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 ua_master_c with default_schema = odi_10g_cp; 
   go 
   grant create table, create view, create procedure,create function to 
   odi_10g_cp; 
   go
   

3.4.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.4.4 Schema Cloning Process for IBM DB2 Universal Database

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

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

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

3.4.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.4.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.4.7 Schema Cloning Process for Hypersonic SQL 1.7.3.3

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

1. Update server.properties

   server.database.1=file:ODI/odi_10g_cp
   server.dbname.1=odi_10g_cp
   

2. Update sqltool.rc with new connection information:

   urlid odicp_sa
   url jdbc:hsqldb:hsql://localhost/odi_10g_cp
   username SA
   password
   

3. Using SQL Tool, stop the server.

   java -jar ./lib/hsqldb.jar odi_sa
   checkpoint;
   shutdown;
   

4. 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 odi_10g_cp.properties
   copy odi_10g.script odi_10g_cp.script
   

5. Restart the server.

6. Using SQL Tool, create new logins for the cloned Oracle Data Integrator 10g database.

   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.7.1 Task 7a: Run 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.7.2 Task 7b: Upgrade the Middle Tier Instance

The Middle Tier upgrade copies the ODI 10g Agent configuration information into the ODI 11g Standalone Agent. Java EE components and ODI Studio are not affected by the middle tier upgrade.

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

3.7.3 Task 7c: Upgrade 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 Task 4: Copy (Clone) Existing 10g Master and Work Repositories.

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

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