Hopefully you all had a chance to read my previous post, “Types of Oracle Data Integrator (ODI) Repositories.” As this will give you a high level understand of the ODI master repository. I wanted to put together this short post, because due to my recent experience with ODI I was very surprised with how very senior level ODI architects setup their master ODI repository incorrectly. Though this is a very simple setup once the install has been complete and your master repository is set up, many people overlook the smallest things and forget that one incorrect setting in your work repository could affect your whole ODI environment, though it looks like it is working perfectly. The issue I want to address is the ODI work repository internal id in which is overlooked many times. So I hope this post helps you all avoid this type of pitfall.
Now if you have already setup your work repository within ODI logon and take a look at the settings. Log into your development ODI server. Once you are logged in you can locate your ODI work repositories settings by clicking on the “Topology” tab then click on the “Repositories” sub tab.
After you located your work repository, double click and open the settings. Now click on the definition tab. Take a look at the work repository id. Make you sure you remember this, as this is an id you will need to input during your setup or have ODI choose once for you.
Now that you have looked into your development ODI sever log into your test environment see what you get. Now the image below may look like the same as development but I want to assure you that it is not. The following picture below if from an ODI test server.
Now that you have looked into your DEV and TEST lets take a look at PROD.
Do you see all the ids being the same? Now you may think ok, so what’s the problem Chris? Well the issue here is that this is not setup correctly. Within ODI your work repository ids need to be different, especially if you plan to follow best practices and migrate code from DEV to Test and Test to Prod.
Now Oracle states, “Due to the structure of the object IDs, these guidelines should be followed:
Work repositories must always have different internal IDs. Work repositories with the same ID are considered to contain same objects.
If an export/import operation is performed between two Master/Work repositories possessing identical internal IDs, there is a risk of overwriting objects when importing. Objects from both repositories that have same IDs are considered the same.”[1]
Now talking to some of my Perficient Oracle colleagues this is a common issue in which is always overlooked. But luckily the fix is simple as Oracle allows you to renumber your work repository internal id in 4 easy steps.
1. Backup the current repository in which you want to renumber before attempting the below steps. Otherwise you may lose your valuable projects.
2. Right click on the work repository and click renumber.
3. The following two pop ups will appear, click Yes and Ok.
4. Next you will be asked to provide a new id. Enter in a number not being currently used.
5. A pop up will appear asking you if you want to continue. Click Yes.
6. This will complete the steps in renumbering your work repository internal id.
Well hopefully I helped shed some light on a simple over looked issue in ODI.
[1] http://docs.oracle.com/cd/E17904_01/integrate.1111/e12643/export_import.htm
My problem is not the renumbering which this post explains very nicely, it’s the loss of connectivity between master and workrep after a database upgrade (11x to 12.1). What could be wrong. This is 11.1.1.7.0_generic_130302.2156 (aka 11.1.1.8.1 odi with biapps)