ODI Repository Renumber Precaution

ODI Repository Renumber Precaution: (Oracle Data Integrator 11g)

Think Twice Before Taking Actions!

Yes, “think twice before taking action”; if you take action regarding something important without at least giving it a minute’s thought, you are likely to face quite a few issues.


Today I am going to narrate a real incident that recently occurred with our ODI DEV and DEV-QA environments. I hope reading this article will help you save your precious time when faced with such problems in the future, and will force you to be more cautious while renumbering any ODI repository. Reading about this should also give you a decent understanding about ODI deployments across environments and the role of internal id in them.


For instance, lets say we have two environments DEV2 and DEV3 having repository internal id 2 and 3 respectively. You can consider these environments are for release 2 and release 3. Now what you have to do is to deploy all the DEV2 projects into the DEV3 environment so that people can start working on release 3. Also, say that, over time, developers have started working on a couple of new projects in DEV3.


After few days you start getting queries from some developers regarding internal id clashes, possibly because they were importing some common projects. As you can imagine, people would now start pointing the finger at you, saying that this could be because you might not have followed the repository management document.


So now what would be the default action plan to fix it? Immediately, one would think of renumbering the repository. You right click, click on renumber (refer this link to know how to renumber), and soon the new internal id has been set as 4 for DEV3. A simple fix, and the issue is resolved, right? Well, not really, as we shall soon see.


Two days after the earlier fix, you get a mail from a developer complaining about a few common artifacts which are missing in the new environment. So now, you have to do the redeployment again from DEV2 to DEV3, and you start following the usual procedure for redeploying the DEV2 projects into DEV 3. Suddenly, everyone gets a mail saying that the deployment has failed. Welcome to the R&D team!


While investigating, you come across the following error:

OracleDI: Starting Command : OdiImportObject -FILE_NAME=/scratch/app/product/zenkin/apc/staging_area/2014-11-14/odi/topology/servers/AGENT_OracleDIAgent.xml -IMPORT_MODE=SYNONYM_INSERT_UPDATE …

java.lang.RuntimeException: oracle.odi.oditools.OdiToolException: Error while running ODI Tool OdiImportObject

at oracle.odi.oditools.OdiAbstractToolRunner$4.doAction(OdiAbstractToolRunner.java:243)


Caused by: com.sunopsis.core.SnpsDuplicateAKException: ODI-10035: SNP_AGENT : ‘OracleDIAgent’ violates alternate key constraint AK_SNP_AGENT (AGENT_NAME) for values OracleDIAgent


That’s strange! So again, what is the quick fix? Hmm, maybe we can try deleting or renaming the agent, and then redeploying. Great, so we update the agent, and restart the deployment. However during the deployment, again all the groups get notified with a failure message! You check the logs, and see almost the exact same error message as before, except with another object:

java.lang.RuntimeException: oracle.odi.oditools.OdiToolException: Error while running ODI Tool OdiImportObject

at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

at oracle.odi.oditools.OdiAbstractToolRunner$3.doAction(OdiAbstractToolRunner.java:218)

… 9 more

Caused by: com.sunopsis.core.SnpsDuplicateAKException: ODI-10035: SNP_SCEN : ‘APC_SALES_VALIDATE_XML 001’ violates alternate key constraint AK_SNP_SCEN (SCEN_NAME,SCEN_VERSION) for values (APC_SALES_VALIDATE_XML,001)


Well thats really bad  🙁 .
This time you should find the root cause and apply a permanent fix. Otherwise you will keep getting error message for subsequent ODI objects.  So what could be the reason??


If you remember, few minutes back we had talked about renumbering the repository. Right?? Now you can say what is the problem with that ? We know Oracle always recommends  to maintain unique id across the repositories. And we did that.

That’s correct my friend, but Oracle has not recommended to reimport the same objects after a renumber to target repository. If you will browse your repository metadata, you will get to see the SNP_AGENT table. It has got a index named as AK_AGENT on AGENT_NAME. There is one more index on I_AGENT which stores the internal id of agent.



Same in case of scenario also. If  you see SNP_SCEN, We have got unique constraint on SCEN_NAME and SCEN_VERSION. Here SCEN_NO stores the internal id.


Now when you import the same agent/scenario to target repository, ODI believes that it is coming from a different repository not from the DEV2 repository (as imported during 1st deployment. after that the internal id of target repository has been renumbered and hence the related objects resides in the repository will also get changed). So ODI will  try to insert instead updating the existing record. And for that your RDBMS will shout about the constraint violation error.


Now you should understand why the quick fix worked at that time.  Since the agent was deleted manually from target repository via console/odistudio, ODI successfully inserted the record without complaining about constraint violation.


Same thing happened with the second error. This time you cannot just delete and proceed with deployment as you will  definitely face the same issue for next object. So what is the solution?

1. Create another temporary repository and dump DEV3 repository (both master and work) into it. This will be used as backup purposes. For instance if a developer has already created a new project then he can again export import back to DEV3 at later point of time.

2. Drop and recreate the DEV3 repository. Make sure you have unique IDs. This time we gave 5.

3. Redeploy DEV2 into DEV3.

4. Now if you want to import some of newly developed project present in temp repository back to DEV3 then you can.


Bottom line : Never import existing objects from one repository to another soon after a renumbering of the source or target repositories.

Thats all folks. Hope you have enjoyed it. Feel free to comment regarding your doubts/concerns. See you soon.


About Bhabani 86 Articles
Bhabani has 12 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.


  1. Hi Bhabani, This is a good post. Thank you for that. As per oracle recommendation, we should have unique ID for all environments. But the example you have said that DEV2 having internal id as 2 and Dev3 having internal ID 3. I think this should be same as per best practice (for example : Dev2 internal id:001 and Dev3 internal ID:001) is my understanding correct.

  2. HI
    I think you can help me regarding one problem I am facing during import of ODI objects into target repository.
    I have one environment 1 where master rep ID is 0 and work rep ID is 1. I have another environment where Master rep id is 100 and work rep id is 200. When I am importing model from environment 1 to environment 2 some additional columns added automatically in data store attached to model.

    My First environment is ODI – on MySQL and second environment is ODI on Oracle.

    Please help

  3. Hi Bhabhani,
    I am trying to import model, datastore and project respectively using ODIImportObject. It is importing the model but throwing following error while importing datastore:
    “Caused by: com.sunopsis.dwg.SQLMasterReposException: ORA-00942: table or view does not exist”
    I am running following command :

    ODI version is 11g.

    I have checked ODI_HOME and odiparams.sh file and all connection details along with workrep name is correct. There is only one work repository in under the master repository.

Comments are closed.