Archive for 2014

How to capture Error in Load plan Exception step?

The title is pretty self explanatory. In the good old days of 10G when there was no load plans and when  we were dealing with just packages it was quiet easy to get the previous step log using odiRef.getPrevStepLog() function or if you wish to be little fancy you can get use this( blog post from  ODI Experts


Anyway with load plans we are starting to write the failure notifications in the exception step section. Now the challenge is that there is no substitution api functions or any tools for getting the error message from the failed load plan.

After analyzing the load plan tables in ODI repository I wrote the following query which has to be run in “Exception step” to get the log of the step which raised triggered the exception step.

Lets a Load plan step “Load Geo Dimension” step failed and raised an error and ran the exception step “Send load failed message” to get the error message in “Load Geo Dimension” we have to use the following query in “Send load failed message”

FROM snp_lpi_exc_log
WHERE I_LP_INST = <%=odiRef.getLoadPlanInstance("BATCH_ID")%>
AND NB_RUN      = <%=odiRef.getLoadPlanInstance("RESTART_ATTEMPTS")%>
AND SESS_NO     = <%=odiRef.getSession( "SESS_NO" )%>


Since error message is a CLOB, my recommendation is to use OdiSqlUnload tool to dump the error message in a file and attach the file to the email notification (If you are using an email).



Note: Very Less has been written about the structure of the load plan tables as compared to session tables (SNP_SESS). Future blog posts will try to detail the structure of the load plan tables.

Monday, August 18, 2014
Posted by Arjun Lagisetty

BIAPPS External authentication for ODI support for IPLANET (ODISEE)

Switching to external authentication is documented in the Oracle note

Doc: Steps to Configure BI Applications With External Authentication (Doc ID 1606637.1)

However, By default it version only works if the external authenticator is OID. For non OID identity store to be supported we need to change the file

Please contact Oracle support with the bug reference Bug 18400279 

Thursday, July 17, 2014
Posted by Arjun Lagisetty

BiApps Data load performance tuning variable hints

BI Apps out of the box code takes bulk insert only for full loads to use bulk insert mode for incremental loads we need to change the following variables. Following list of variables is not exhaustive but the variables are per fact. This list mostly contains facts for Financial analytic.
Please note that parallelism has to be enabled at table level as well.

                a. BIAPPS_ETL_BULK_MODE
                       Default value : F
                       Change to: Y
                b. ETL_HINT_INSERT_VALUES
                       Default value : ‘No value’
                      Changed to: /*+ APPEND */
                TARGET: W_GL_OTHER_FS
                SOURCE: GL_JE_LINES

2. SDE_ORA_PersistedStage_GLLinkageInformation_GLExtract
                a. BIAPPS_ETL_BULK_MODE
                      Default value : F
                     Change to: Y
                b. ETL_HINT_INSERT_VALUES
                      Default value : ‘No value’
                      Changed to: /*+ APPEND */

3. SDE_ORA_GLBalanceFact
               a. BIAPPS_ETL_BULK_MODE
                      Default value : F
                      Change to: Y
                b. ETL_HINT_INSERT_VALUES
                      Default value : ‘No value’
                     Changed to: /*+ APPEND */
                TARGET: W_GL_BALANCE_FS

               a. BIAPPS_ETL_BULK_MODE
                        Default value: F
                        Change to: Y
               b. ETL_HINT_UPDATE
                       Default value: ‘No value’
                       Changed to: /*+ APPEND */
               TARGET: W_GL_OTHER_F
               SOURCES: LKP+FS
5. SIL_GLBalanceFact:
              a. BIAPPS_ETL_BULK_MODE
                      Default value: F
                      Change to: Y
              b. ETL_HINT_UPDATE
                      Default value: ‘No value’
                     Changed to: /*+ APPEND */

6. SIL_GLLinkageInformationGeneral
               a. BIAPPS_ETL_BULK_MODE
                     Default value: F
                     Change to: Y
                b. ETL_HINT_UPDATE
                       Default value: ‘No value’
                       Changed to: /*+ APPEND */

7. PLP_GLOtherGroupAccount_A_Stage
              a. BIAPPS_ETL_BULK_MODE
                      Default value : F
                      Change to: Y

Posted by Arjun Lagisetty

Tips from the ODI Certified!

Here is a good authoritative link, which we used to successfully prepare for the exam. Being a fan of no repetition I am going to point to that website.

Also we followed instructions on

Here are some of the topics specifically which were asked:
  1. web_cat folder file in OBIEEE Location
  2. Questions about Oracle EDQ date parser
  3. Quite a few questions on EDQ parsing
  4. EDQ AV
  5. EDQ Data profiling
  6. Perform a matching process, identify duplicated records, balance performance and duplicate detection accuracy
  7. Configure EDQ to work with Data Stores, Snapshots, Staged Data, Exports
  8. Describe profiling results and the use of Resultbooks and Publish features
  9. Explain address verification and Address Verification statistics (this is also related to EDQ.
  10. Advanced package editing: In package step -->advanced tab -->log steps in journal.
  11. Monitor the execution of interfaces
  12. Explain the parsing process and referencing review results.
  13. Questions on how to deal with partitioning in ODI.

  14. The only way to pass the exam is to answer a few questions correctly on EDQ. At the least study the basics of EDQ on how profiling, matching process is done. If you don't study EDQ, you have to answers all the other questions related to ODI and golden gate correctly. I was not that confident that I would pass the exam as they were a lot of questions on EDQ (atleast 10--15)
    FYI: This really works. 
Good luck... See this on your resume soon.

Posted by Arjun Lagisetty

BIACM Import Error

Migration domain member configuration and the system configuration from one environment to another is a very tedious task if done manually. For this BIACM offers export import utility. This utility is very bug ridden, we got several issues during migration, below is one of the error and how we fixed it.

General rule of thumb is that when we do migrations and it fails, we ought to just redo it multiple times and it just works. Lets say if the import fails in the middle we try importing one by one starting with System setup, Data Load Parameters, Reporting Parameters and Domain member mappings.

Error 1:

oracle.jbo.DMLConstraintException: JBO-26048: Constraint "CDM31_PK" is violated during post operation "Insert" using SQL statement "INSERT INTO C_DOMAIN_MEMBER(DOMAIN_MEMBER_CODE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,DOMAIN_KEY) VALUES (:1,:2,:3,:4,:5,:6,:7)".
Fetching Data...


This was simply fixed by re-importing domain member mapping

Error 2:

 java.lang.Exception: oracle.jbo.TooManyObjectsException: JBO-25013: Too many objects match the primary key oracle.jbo.Key[US ].

Open languages.xml from the zip file and delete all the XML nodes.

Repackage the xml in the zip file with the same name and reimport the zip file

Wednesday, July 16, 2014
Posted by shankar

ODI 12c Upgrade Process

ODI Studio Version 12c Install

Step 1 – ODI Studio Install
Step 2 – Installing 12c Patches
2) Install JDK/Weblogic/DB and ODI 12c in Machine2
3) Configure the ODI12c configuration in Machine2
4) once the above steps completion, please stop all services like managed servers/nodemanager and Adminserver in Machine 2
5) export the ODI 11g repository into machine2 (.dmp file) 
6) run the below select query and check whether you able to see the ODI 11g schema in the list
8) now you apply the patch from command prompt
9) once you patch applied successfully, then you ran same select query (from point 6) and see the output
if the version has changed from to, then you ran the ua.bat file
in the middle of the upgradation, when it is asking to select the ODi schema that time you have to choose ODI 11g repository schema
and proceed with further steps to complete the upgradation.
10) once it is done, goto ODI_HOME\odi\studio location and run odi.exe to open the ODI 12c studio
11) it is open you just configure the ODI 11g repository schema where it is upgrated to and conne to the repository and verify the code in ODI 12c
1) You can upgrade ODI 11g version ( and to ODI 12c and you can't upgrade from ODI 11g to ODi 12c.
2) You can easily upgrade code from ODI 11g to ODI and for this no need to apply any patch.
3) You can upgrade ODi 11g to higher version of  ODI11g without applying any patch.

File needed:

Information needed:     Location of the installed Java JDK.
                                                Oracle Home location if you have one.

Note: There should be Java JDK of version JDK 1.7 need to be present.
Copy file from the share drive to a local directory and unzip.  There will be a jar file called odi_121200.jar.  Open a Command window as administrator and navigate to the directory the jar file is in.  To start the installer, enter the following command in the command window and press enter:

java-jar odi_121200.jar
The window should display some preparatory information and then the installer screen should display.  Click <Next> if you’re at the welcome screen to go to the Install Location screen.  Here, enter or browse to your oracle home directory if you have one.  If you don’t have one, enter a new directory for home and click <Next>.
At the Installation Type screen check the Enterprise Installation radio button to Include ODI Studio in the install.  Click <Next>.
On the next screen click <Next>.
Click <Install>.
After the install is done, click <Next>.
Click <Finish>.

File needed:

Information needed:     Oracle Home location you used in the previous step. 

Open Windows Explorer and navigate to the oracle home directory. If there is an OPatchsubdirectory delete it( Instead Deleting take backup and rename and put new opatch in sub directory).  Extract the zip file to the Oracle Home directory.
Open a command window as administrator and navigate to the new OPatch directory you just extracted.  Once you are in that directory, run the following command to confirm the opatchsetup:
You should see output similar to the following:
Create a directory call PATCH_TOPoff the root directory for storing the unzipped patches. Unzip the file to that directory.  In the command window, cd to the PATCH_TOP directory and run the following command substituting your Oracle Home directory.  You could also optionally add the OPatch directory to the PATH variable:
     <Oracle Home>\opatchnapply odi_1212_opatch
Answer y to the prompt and press <Enter>.
Answer y to the next prompt and press <Enter>. The patch installs will commence.

The error indicated in the above is the only one you get then the patches were applied successfully.
Note ODI Patch install steps ( same as above process but little detail steps):
1) when you download odi we get file this is file where we need to apply patch.
   2) go to oracle home path C:oracle/middleware/oracle-home and take backup of Opatch and inventory folders
note: download latest opatch software from oracle support and take original opatch as backup and new one paste in oracle_home.
3) change environmental variables:
a) if there is no oracle_home path create it(C:oracle/middleware/oracle-home).
b) if there is no odi_home path create it(C:oracle/middleware/oracle-home).
c) put opatch path in existing path location in environmental variables(C:oracle/middleware/oracle-home/opatch).
4) create "Patch_top" folder in c drive
5) put "" file in "patch_top" folder and extract the file in patch_top location.
7) open cmd in admin mode
7a) Validate the OUI inventory with the following commands: ( go to C:oracle/middleware/oracle-home/opatch and type  "opatch lsinventory"
8) give (cd c:\patch_top and enter)
9) next give opatch napply odi_1212_opatch command and enter( you can see in readme file).
note: if there is any problem reboot your system. 
Before upgrade we need to do this vvvvvvvimp:
when we are trying to upgrade. before upgrade we need to create clone copy of original master and work repo and then:
1) we need to go to original master repo in odi studio and there edit user name and password by giving
cloned user name and password in master repo in topology and connect to cloned work and test by entering into repos.
2) just create new master and work repo( from master repo creation wizard) in odi studio with clone_master and clone_work names. in db create 2 empty schemas with clone_master and clone_work names and then export master and work repo from original master and work repo from odi studio and import into newly created clone_master and clone_work in odi studio and perform upgrade on this copies.
note: never just create clone copy and directly create connection by pressing + sign in master repo and work repo. We need to create master and work repo( from master repo creation wizard)  then connect to them and then upgrade.
Note: we can upgrade from to above to odi 12c but below we cannot upgrade to 12c we need to apply patch to upgrade to and then we can upgrade.(eg: we cannot upgrade directly to odi 12c we need to apply patch in order to upgrade to then we can upgrade to odi 12c).
After applying this patch just go to C:\oracle\middleware\oracle_home\oracle_common\upgrade\bin and open cmd in admin mode and navigate to above path and just type ua and press enter a screen appear then after everything straight forward.
Note: for details upgrade steps go to DWteam( link and just follow steps over there

1) Export ODI 11g repository schema from machine1 and place the .dmp file into machine2.
7) once you saw the ODI 11g schema in select query output (eg: in version column:
8) You should be apply the patch after you import the ODI 11g schema not before that.
Hope you followed the same steps to upgrade ODI code from ODI 11g to ODI 12c
Hope this information will helps you to fix your issue.

For Standalone Agent Installation:
First to open RCU we need to change environmental variable path of oracle_home to C:oracle/middleware/oracle-home/oracle_common then after
Type cd C:oracle/middleware/oracle-home/oracle_common/bin and press enter and then type RCU and press enter then RCU screen appears and then after everything is straight forward.

Tuesday, June 3, 2014
Posted by Arjun Lagisetty

ODI and Heartbleed

 Good news to you all ODI admins out there. Oracle Data Integrator is not really effected by
OpenSSL Security Bug - Heartbleed / CVE-2014-0160.  Please check out the link
However, if your ODI stack includes any web server or proxies or is installed on OEL6 you might want to get check it out right away.
Thursday, May 15, 2014
Posted by Arjun Lagisetty

OpenTool for Executing a command on Remote machine

As we discussed in the previous post, here , We had a gap in ODI openTool tool set in terms of calling a command from a remote machine via ssh.

I created an open tool which can remote log in into a machine via ssh and execute a command.

You can find the source code and the jar file of the open tool here. This tool is released under Free BSD license.

Instructions for the installation can be found here

Some limitations of the tools:

  1. Only password based authentication is possible.
  2. Synchronous parameter is not yet implemented.

Your comments and contributions are welcome. 

Friday, February 21, 2014
Posted by Arjun Lagisetty

Call OS command on a Remote machine

How do you use ODI to call OS command on Remote machine?

Use case:

Our requirement was to call a script (for namesake lets call it on a machine 2 from a ODI procedure which is executed by an agent installed on machine 1. So the challenge here, was that our agent did not have access to the script, its not just the matter of not having UNIX permission to execute the script. Agent and the script were present in different machines. 


If agent and farFarAwayScript were present in the same machine it would be a simple matter of using OdiOSCommand tool, but the tool does not execute command on remote machines. So, I decided to write a procedure to invoke the farFarAwayScript. Logic of the procedure would be same as a work flow of a human being executing the farFarAwayScript form his/her laptop. It (the human in question, I am trying to be gender neutral here) would login to the machine2 using ssh/telnet/X11 and execute the command. He/She would need the following details to login to the machine and execute the script.

  1. IP address/ Hostname of the remote machine
  2. Username and Password to log into the remote machine.
  3. Script name and the complete path.
These are the steps a human would follow:
  1. Open the SSH client like putty
  2. Enter the credentials and login
  3. Execute the script
  4. Close the client and the connection.
We have to execute the similar steps programatically via agent. Language of choice for us to do this is Java. We will use Jsch java library which enables us to ssh via Java code in to the remote machine. You can install Jsch by downloading the Jar from the website and copy it into $ODI_HOME\oracledi\agent\lib (Recommended for non driver jars - Just a matter of convention) or  $ODI_HOME\oracledi\agent\drivers folder.

Note: You can use variable or procedure options to pass USER_NAME, PASSWORD, HOST_NAME, SSH_PORT and COMMAND. Additionally error stream and the out stream can be printed to the local files also, this code snippet is meant to be guidance.

Now to the code..
import com.jcraft.jsch.*;
String USER_NAME = "user_name";
String HOST_NAME = "host_name";
int SSH_PORT = 22;
String PASSWORD = "password";
String COMMAND = "absolute_path_to_the_command";
JSch jsch = new JSch();
Session session = jsch.getSession(USER_NAME,HOST_NAME, SSH_PORT);
session.setConfig("PreferredAuthentications", "password");
session.setConfig("StrictHostKeyChecking", "no");
Channel channel = session.openChannel("exec");
((ChannelExec) channel).setCommand(COMMAND);
((ChannelExec) channel).setErrStream(System.err);
InputStream in = channel.getInputStream();
while (true) {
    if (channel.isClosed()) {
    try {
        } catch (Exception ee) {
        throw ee;
 Parameters to set:

Thursday, February 20, 2014
Posted by Arjun Lagisetty

ODI Long running sessions and the firewall. (Happy Valentines day)

Ah! the classic tale of romance between the network and the app..


Long running DB session tasks from ODI are ending with a timeout error.


When a long running task on the DB is kicked of from ODI. After a certain time ODI  session is ending with a timeout error. Irrespective of the status of the task kicked of on the db side. The culprit here was the firewall setting. Due to the corporate policies our firewall was configured to kill any "inactive" connections after an hour. Some of the DB tasks took more than hour, in this time there is no data movement between the agent and the database because these specific task are DML in nature and the processing is confined to the DB session itself. Since, there is no data movement firewall categorized this as an inactive connection and killed it.

Now comes the romance of the app and the network team. App blames network, network blames app and they live happily every after. Alas, only if it were true. Since, it was a corporate security policy, this rule could not be relaxed. It was the Apps responsibility to maintain an active connection. 


ODI inherently does not have any capability to ping the db at regular intervals once the session is kicked off. So, we had to shift this burden to our all time favorite hero the Oracle Database. Oracle SQL_NET profile can be configured with a parameter called SQLNET.EXPIRE_TIME, which is used to detect inactive connections by checking for an inactive client. By default this value is zero, if we assign a non-zero value (X) to this parameter Db server probes client every X minutes to ensure that the client is up. And the firewall thinks that the database is up because a signal is sent every X minute from the server to the client. And the network and the app live happily every after. :) 
I personally feel this solution is a hack and is very server specific (Oracle Db server). If you have a different database you should find out a similar parameters in your database. Though, it would be nice if oracle can build in this mechanism in the agent.

Wednesday, February 12, 2014
Posted by Arjun Lagisetty

IKM SQL Incremental Update (row by row) log file error [Goovy file path seperator exception in Windows]

This exception is specific to windows. It should file perfectly in a UNIX system (I did not test it) .
ODI introduced log for a row by row incremental update KM. This is very exciting as it would help keep track all the rows which miss during update.
To understand this blog post we have to understand the concept of path separators, typically we use backslash as a path separator in windows i.e. when we are referring to a file we use '\' to specify the path to the file from root directory.

IKM in question fetches the path and the separator from the Java properties the default value is 
 <default>:<?= java.lang.System.getProperty("user.home")?><?=java.lang.System.getProperty("file.separator") ?>ikm_rowbyrow.log
Please see the screenshot below.

This default value gets translated to the path  C:\Users\xxxxx\ikm_rowbyrow.log

This value causes the following error in interface execution:

org.apache.bsf.BSFException: exception from Groovy: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Insert_flow_into_I$_table: 6: unexpected char: '\' @ line 6, column 18.
   logFileName = 'C:\Users\xxxxx\ikm_rowbyrow.log'

1 error

    at org.codehaus.groovy.bsf.GroovyEngine.exec(
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.execInBSFEngine(
    at com.sunopsis.dwg.codeinterpretor.SnpScriptingInterpretor.exec(
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.scripting(
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(
    at oracle.odi.runtime.agent.execution.cmd.ScriptingExecutor.execute(
    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(
    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(
    at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(
    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(
Caused by: org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
Insert_flow_into_I$_table: 6: unexpected char: '\' @ line 6, column 18.
   logFileName = 'C:\Users\lagiseta\ikm_rowbyrow.log'

1 error

    at org.codehaus.groovy.control.ErrorCollector.failIfErrors(
    at org.codehaus.groovy.control.ErrorCollector.addFatalError(
    at org.codehaus.groovy.control.ErrorCollector.addError(
    at org.codehaus.groovy.control.ErrorCollector.addError(
    at org.codehaus.groovy.control.SourceUnit.addError(
    at org.codehaus.groovy.antlr.AntlrParserPlugin.transformCSTIntoAST(
    at org.codehaus.groovy.antlr.AntlrParserPlugin.parseCST(
    at org.codehaus.groovy.control.SourceUnit.parse(
    at org.codehaus.groovy.control.CompilationUnit$
    at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(
    at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(
    at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(
    at org.codehaus.groovy.control.CompilationUnit.compile(
    at groovy.lang.GroovyClassLoader.doParseClass(
    at groovy.lang.GroovyClassLoader.parseClass(
    at groovy.lang.GroovyShell.parseClass(
    at groovy.lang.GroovyShell.parse(
    at groovy.lang.GroovyShell.evaluate(
    at groovy.lang.GroovyShell.evaluate(
    at groovy.lang.GroovyShell.evaluate(
    at org.codehaus.groovy.bsf.GroovyEngine.exec(
    ... 19 more

It seems like there is a problem with Groovy and it does not seems to accept "\" as the file seperator.

Changing the file separator to "/" (forward slash seems to work)
Interface options have to changed as shown below: In the following example I specified absolute path in the interface options for convenience but its recommended to use a variable value to specify the log path.


Saturday, February 1, 2014
Posted by Arjun Lagisetty

Popular Post


Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty