Oracle

Oracle Data Integrator (ODI) Work Repository Internal ID

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.

Screen Shot 2014-09-01 at 6.46.50 PM

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.

Screen Shot 2014-09-01 at 6.47.15 PM

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.

Screen Shot 2014-09-01 at 6.47.39 PM

Now that you have looked into your DEV and TEST lets take a look at PROD.

Screen Shot 2014-09-01 at 6.48.03 PM

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.

 

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

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.

Screen Shot 2014-09-01 at 6.49.15 PM3.  The following two pop ups will appear, click Yes and Ok.

Screen Shot 2014-09-01 at 6.49.46 PM

4.  Next you will be asked to provide a new id.  Enter in a number not being currently used.

Screen Shot 2014-09-01 at 6.50.15 PM

5.  A pop up will appear asking you if you want to continue.  Click Yes.

Screen Shot 2014-09-01 at 6.50.49 PM

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

About the Author

Chris Evans has extensive management, technical, consulting and customer service skills. He provides hands-on expertise in project leadership and management, assessments, methodologies, data modeling, database design, meta data, systems analysis, and development. He has worked with multiple platforms, and his experience spans a wide range of operational and data warehouse environments. In addition to his BI background, Chris Evans has a experience in marketing strategy for major consumer brands.

More from this Author

Thoughts on “Oracle Data Integrator (ODI) Work Repository Internal ID”

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up
Categories