This is my first post, and I hope to have many more to come. My name is Brian Sauer, and I am fairly new here at Rittman Mead and proud to be here. I have close to 5 years of experience with Oracle Data Integrator, both 10g and 11c. I have been involved in ETL activities for close to 8 years. Prior to using Oracle Data Integrator, most of my activities in ETL utilized Perl. Ever since being exposed to Oracle and ODI, I have found a home that has been both comforting and challenging. It is a passion of mine to be able to work with any type of database and to get data from point A to point B, making the necessary changes to get user’s hands on it in a meaningful and useful way. Thus, I’ve put together this blog post to assist with the upgrade of ODI 11g to ODI 12c. I hope it’s useful to you.

After going through the process of upgrading from ODI 11g to ODI 12c, I found the documentation to be scattered around a bit and began putting together a roadmap of the information that was vital to the upgrade itself. This post is essentially a brief explanation of some added enhancements to ODI in 12.1.3.0 and a straightforward guide to the upgrade process. I’m not addressing 12.2.1.0 in this post, as many of our clients have been moving to 12.1.3.0 due to stability and testing issues with 12.2.1.0. I will take a look 12.2.1.0 in my next post in this series.

ODI 12.1.3.0 was released in November 2014, and along with it came a number of enhancements and significant changes from ODI 11.1.1.7.0. I am going to focus on the upgrade process with this post and some of the more significant changes.

According to the Oracle documentation, the only supported starting points for upgrading to ODI 12c (12.1.3) are the following:

• Oracle Data Integrator 11g Release 1 (11.1.1.6.0)
• Oracle Data Integrator 11g Release 1 (11.1.1.7.0)

There are some significant changes to ODI from 11g. First and foremost is the way that the agents are handled in 12c. Standalone Agents are now managed by the WebLogic Management Framework, and they are installed in their own directories. Also, interfaces have given way to mappings and reusable mappings. Lastly, the repository upgrade to 12.1.3 validates name uniqueness for objects such as interfaces, folders, procedures, knowledge modules, packages, and profiles. If duplicates exist, the upgrade process will check for it, and you can print a report that will list the duplicates in the upgrade log. You’ll then need to reopen ODI 11g and manually fix the duplicates and restart the upgrade.

Below is a comparison of the ODI JEE Agent Upgrade topology. Notice that in 12c only an LDAP-based store can be used for OPSS as file-based stores are no longer allowed.

You’ll also notice the topology for Standalone Agents not registered with a WebLogic Domain have changed as mentioned earlier; they are now part of the WebLogic Management Framework. The agent is configured in a standalone domain unlike its predecessor in 11g.

For Standalone Agents in 11g that were registered with a WebLogic Domain nothing has really changed from a topology standpoint as shown below:

Some considerations to take into account before diving into the upgrade are the following:

• Verify that the database which will host the schemas required by Fusion Middleware is supported. You can verify at: http://docs.oracle.com/middleware/1213/core/ODIUG/tasklist.htm#BABGAIFA
• ODI 12.1.3 requires a 64-bit operating system. Verify your machine is 64-bit, and, if not, then upgrade.
• Verify you are running a supported version (11.1.1.6, 11.1.1.7).
• Develop a backup and recovery strategy.
• Plan for system downtime during the upgrade process.

Upgrade Process

BACKUPS

The first and probably most important step before you even begin the upgrade is to make sure you have a solid backup of your 11g environment. There are many ways to do this, but you will want to make sure that you have a backup of the following so you can restore your prior environment in the event that a failure occurs:

1. ODI Home Directory
2. ODI Work Repository(s)
3. ODI Master Repository

To preserve the ODI 11g Home Directory, I simply make a copy of the ODI_HOME directory, zip it up, and save in another location. To preserve the ODI Master and Work Repositories, simply use the export utility within ODI.

This will provide a saved copy of each. In addition to the backup, record the Repository IDs in the event you need to restore. Lastly, I create a database backup of the ODI schemas that will be upgraded. These are the schemas where the Master and Work Repositories reside.

Zero or Limited Downtime Approaches

An important question is, How can I upgrade my system with limited or even zero downtime?

Oracle recommends cloning the work and master repositories on your database in another database instance and then upgrading the cloned repositories. This will keep your current 11g repositories active and untouched during the upgrade process, and once the upgrade is complete and you’ve been able to test it out, you can switch to the new 12c repositories as your active ODI implementation. Oracle has provided sample scripts for different database environments at the following location:

https://docs.oracle.com/middleware/1212/odi/ODIUG/tasklist.htm#ODIUG117

As a part of your testing you will want to verify, after you have set up your cloned repositories, that the cloned master repository points to the correct work repository(s). Also, as a part of cloning the repositories, you’ll need to copy the row about the master repository in SYSTEM.SCHEMA_VERSION_REGISTRY$ from the old to the new instance.  This is not a supported change by Oracle, however it is required for the upgrade to be successful.

Another upgrade option other than cloning is to create a brand new ODI 12c repository on your database and then export your objects from 11g and import them individually to your new 12c repository using ODI Studio. To perform this task you’ll need to use the upgrade key which is new to 12c and which I’ve explained later in this post for the imports. This is not my recommended approach as it takes longer and is more tedious which can lead to missed objects during the export/import process.

However, if you choose to go this route, the export/import order I have used in the past is the following:

1. Topology
2. Models
3. Global Objects
4. Projects

Installing Oracle Data Integrator 12c Products

Now that backups have been made and we are confident that in the event of an unsuccessful upgrade we can recover and we have decided on a transition strategy, it is time to begin installing our 12c products.

We have a couple questions to answer first. Are we upgrading with a Standalone Agent or a Java EE Agent? If we are installing an environment with a Java EE Agent then we will need to download two separate files from Oracle:

1. Oracle Data Integrator 12cR1 (12.1.3.0.0) for all platforms install file.
2. Oracle Fusion Middleware Infrastructure (for All Platforms).

If we are installing for a standalone environment which we are in this post, then the second file is not required or needed. You will need approximately 1.5GB of space for the home directory where you install ODI 12c. I would recommend an additional 1GB of space to account for domains for agents. The following link will give you the ODI 12c Studio installation instructions in detail:

https://docs.oracle.com/middleware/1212/odi/ODING/install.htm#ODING286

Creating/Upgrading the Master and Work Repositories

Now that ODI 12c Studio is installed, we’ll need to create the Master and Work repositories in the database so that they can effectively communicate with ODI 12c Studio. To do this you’ll need to navigate to the <ODI_HOME>/oracle_common/bin directory and run either ./rcu.sh or rcu.bat, depending on your environment.

Before beginning the creation of the 12c repositories, make sure you have a solid backup of the 11g repositories.                

You’ll want to make sure that you have DBA privileges for this step as you’ll be creating new schemas and tables in the database that will require these permissions. The wizard does give you the option of creating the scripts for the DBA to use later if necessary, although it may be best to work with the DBA directly as you create the repositories.

First, you will want to choose an existing prefix since this is how you identify the repository to be upgraded in the DB.

Secondly, you’ll need to check off the following components to identify them in your 12c upgrade:

1. Oracle AS Repository Components/AS Common Schemas/Common Infrastructure Services which creates the new *_STB schema for 12c.
2. Oracle Data Integrator/Master and Work Repository

As you enter the schema passwords and mapping information, the utility will ultimately reach the summary screen where it will show you the new schemas it will create. If any schemas have already been created, be aware that they will not be listed here since they do not need to be created again. At this screen, you click create, and the creation of the new schemas/components will commence.

Once you have created the new schemas necessary for 12c, we will start the Upgrade Assistant so the previous version of ODI can be upgraded to 12.1.3.0.0. You will find the upgrade assistant at:

<ODI_HOME>/oracle_common/upgrade/bin

You’ll execute the ua.bat or ua file depending on your environment.

When asked what type of upgrade you wish to perform, you will want to select “Schemas” as this will allow you to identify the schemas where the upgrade will take place.

The upgrade assistant will then list the schemas available to upgrade. When asked what components to upgrade, you will select “Oracle Data Integrator”.

The only other item that I’d draw your attention to is the screen providing the “Upgrade Key”. This key is used to convert 11g object IDs into unique GUIDs. You will want to save this key as you will need this for additional 11g objects that may need to be imported at a later time. This key allows ODI to consistently calculate the same GUID for an 11g object when importing to 12c. The key identifies uniquely the set of repositories that were working together before an upgrade. Since the 12c repository has switched to using GUIDs to better guarantee unique ID’s, the 11g ID will not work without providing the upgrade key.

As you upgrade to 12c, the question will likely arises as to why would you need this upgrade key. The situation may arise where you would need to restore an object, import a scenario, or revert back to a prior topology. This key will allow you to do so with your 11g exports.

Once completion of the Upgrade Assistant has occurred, you will want to verify that the version of ODI has been updated in the schema. You can run the following command to verify:

SELECT OWNER, VERSION, STATUS FROM SCHEMA_VERSION_REGISTRY WHERE OWNER = ‘<prefix>_ODI_REPO’;

You should see the version for your schema as: 12.1.3.0.0 with a status of ‘VALID’

Creating the Standalone Agent

Our final step is creating the Standalone Agent. As I wrote earlier, this has changed with 12c since the WebLogic framework is being used now for all agents. Due to this, you cannot upgrade 11g standalone agents to 12c, and you will need to create new ones.

We will place our new Standalone Agent in a domain folder which will sit outside of the ODI_HOME.

To create the Standalone Agent, navigate to ODI_HOME/oracle_common/common/bin and execute either config.sh or config.exe based on your operating system.

The first screen that you will come across will ask you to either create a new domain or update an existing domain.  In our case here, we did not have an existing domain so we will create a new Standalone Agent Domain. The wizard will default to a location within your ODI_HOME. You will want to change this location so that it resides OUTSIDE the ODI_HOME as I wrote earlier. The reason for this is that in the future it could create problems for upgrading your ODI software if the domain is created in the ODI_HOME.

The rest of the steps are straightforward. You’ll choose to create the “Oracle Data Integrator – Standalone Agent – 12.1.3.0[odi]”, enter database configuration information, and choose a user name and password for the Node Configuration. Then, you will be prompted to create your agent.

Once you have completed these steps to start the agent, go to DOMAIN_HOME/bin and select either startNodeManger.cmd or startNodeManager.sh depending on your operating system. You’ll be prompted to enter the credentials you chose when configuring the node above.

Once you’ve completed these steps, you’ll go through the process of creating your agent within ODI Studio both within the Physical and Logical Architecture.

Then run the following command depending on your operating system:

./agent.sh –NAME=<AGENT_NAME>

agent.cmd –NAME=<AGENT_NAME>

Once these commands have executed successfully you’ll want to confirm the agent is running without issue by clicking on the “Test” button by opening the agent you created in the Physical Architecture in ODI Studio.

I would shut down and remove the agent that existed in your 11g environment as you remove the 11g ODI home directory. Remember in our backup that we zipped a copy of the 11g home directory and stored it in another location. Once you have confirmed that ODI 12c is operating as expected, you may remove this directory if it’s on the same server.

Testing, Upgrade Issues, and Clean Up

The approach that I have used when testing has been to test each individual interface and load plan against the results in its 11g counterpart. I verify the counts initially and then have the users/owners verify the data. I then allow the load plans to run a few days to a week, and once the users/owners are comfortable with the results, migrate to the new implementation.

That being said, the following issues and clean up items may need to be addressed, which I and some of my colleagues at Rittman Mead have encountered while upgrading to 12c:

• Variables in mappings/KMs are not recognized by the parser when creating scenarios, so they are not recognized as input parameters. Consequently, you’ll want to wrap each mapping in a package or load plan with the variables declared in it.
• The variables are case-sensitive in 12c, so if you called a variable with a different case in a mapping, you will need to change it to the matching case.
• Make sure to prefix all variables with the project code or global.
• Do not start any data store alias with a number as this does not work well in 12c.
• Temporary interfaces from 11g are triplicated in 12c during the upgrade and require cleanup in projects as well as the models. The upgrade creates these as a regular mapping and creates a target table in the models which can be removed. It also creates two copies of each temporary interface in Reusable Mappings with suffixes of RMS (Re-usable Mapping Source) and RMT (Re-usable Mapping Target). For the most part the RMT reusable mappings can be removed, however verify the mapping(s) using the temporary interface are suffixed by RMS before deleting.
• Upgrade the mappings to flow-based using “Convert to Flow” on the dataset. (Optional)
• You can check out Michael Rainey’s post on this at: http://www.rittmanmead.com/2015/04/di-tips-odi-convert-to-flow/

In my next post, I will go over the process that involves upgrading the Java EE Agent to 12c from 11g and highlight where the upgrade to 12.2.1.0 differs from the upgrade to 12.1.3.0. Stay tuned!

The post Upgrade to ODI 12c: Repository and Standalone Agent appeared first on Rittman Mead Consulting.