Archive for 2015
Auto-restart Load Plans / Sessions after failure
When we schedule a load plan/ scenario from the operator we can ask the agent to automatically restart the load plan for any number of times after fails.
1. Add Schedule
![2015-06-22 18_53_33-[DEV-DCPEDB1-CPE] Oracle Data Integrator 11g _ GLOBAL _ null](http://lh3.googleusercontent.com/-_jp7fYB6IR0/VYihIA1YpeI/AAAAAAAAFAw/kBdH7LV-AAE/s1600-h/2015-06-22%25252018_53_33-%25255BDEV-DCPEDB1-CPE%25255D%252520Oracle%252520Data%252520Integrator%25252011g%252520_%252520GLOBAL%252520_%252520null%25255B2%25255D.jpg "2015-06-22 18_53_33-[DEV-DCPEDB1-CPE] Oracle Data Integrator 11g _ GLOBAL _ null")
2. Choose “Execution Cycle” in the scheduling options.
3. Change "Number of Attempts on Failure" to “1” or any other number
![2015-06-22 18_54_20-[DEV-DCPEDB1-CPE] Oracle Data Integrator 11g _ GLOBAL _ null](http://lh3.googleusercontent.com/-OKDIxT3EAeI/VYihJM-tWpI/AAAAAAAAFBA/XoQXMYKtdAk/s1600-h/2015-06-22%25252018_54_20-%25255BDEV-DCPEDB1-CPE%25255D%252520Oracle%252520Data%252520Integrator%25252011g%252520_%252520GLOBAL%252520_%252520null%25255B3%25255D.jpg "2015-06-22 18_54_20-[DEV-DCPEDB1-CPE] Oracle Data Integrator 11g _ GLOBAL _ null")
Loading same table asynchronously
The Problem:
When you load a the same target table across two interfaces which run in parallel. We have an issue in with the load.We will get a conflict in the I$tables, as the I$ tables are created with based on the target table name. Since the target table is same across both the interfaces. We will have clash of the table names.
The Solution:
So should append session numbers to the I$ in topology..Topology > Physical Architecture > Technology (Oracle) > Advanced > Default Integration Table Prefix
Change value to I$_<%=odiRef.getSession("SESS_NO")%>
Pitfall:
Cannot run the same interface in parallel in the same session.Correlating between Oracle Database SQL ID and Oracle Data Integrator Session ID
Let's say if you visited the DBA and he told you that
"Hey, The SQL with ID ase234x is killing my CPU, do something"
What do you do? You go back to operator and boom you see that there are 100 session running how would you narrow down which session spawned that sql with that ID.
The Solution:
Use the below SQL which takes in either SQL_ID, username of the user who is running that sql or os username who is spawned that sql to get the Session Id, step number and the task number, using this you can quickly go to the particular session in the operator and work your magic.
SELECT SUBSTR(A.ACTION, 1, INSTR(A.ACTION, '/') -1) AS SESSION_ID,
SUBSTR(A.ACTION, INSTR(A.ACTION, '/',1,1) +1, INSTR(A.ACTION, '/',1,2)-INSTR(A.ACTION, '/',1,1)-1) AS STEP_NB,
SUBSTR(A.ACTION, INSTR(A.ACTION, '/',1,2) +1, INSTR(A.ACTION, '/',1,3)-INSTR(A.ACTION, '/',1,2)-1) AS STEP_RUN_NUMBER,
SUBSTR(A.ACTION, INSTR(A.ACTION, '/',1,3) +1) AS TASK_ORDER_NUMBER,
B.SQL_TEXT AS SQL_TEXT
FROM V$SESSION A, V$SQLAREA B
WHERE A.SQL_ADDRESS=B.ADDRESS
AND A.ACTION IS NOT NULL
AND (A.USERNAME =''
OR A.SQL_ID =''
OR A.OSUSER = '')
Understanding the logging format for Oracle Applications
Oracle Data Integrator logs are logged in the ODL logging format. So Its import to understand the ODL logging format.
Below given blogs give a good overview of the Oracle Logging formats.
http://technology.amis.nl/2011/01/12/oracle-diagnostics-logging-odl-for-application-development/
http://docs.oracle.com/cd/E14571_01/core.1111/e10105/logs.htm#ASADM217
https://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/PatternLayout.html
Query to find out difference between two security profiles
We had a similar situation in the past. Where I had to create a new security profile called LIMITED_OPERATOR , who is allowed to execute, stop, restart the load plans and scenarios but should not be able to delete or import them. We wanted to identify what exactly the difference in this two profiles was (I was getting an error while executing with limited_operator profile) I wrote the following query to help me debug the issue.
SELECT -- SNP_PROFILE.PROF_NAME,
SNP_METHOD.SHORT_NAME METH_SHORT_NAME,
SNP_METHOD.INT_JAVA_NAME AS METHOD_NAME,
SNP_PROF_METH.GENERIC_PRIV,
SNP_METHOD.I_OBJECTS,
SNP_OBJECT.INT_JAVA_NAME AS OBJECT_NAME
FROM SNP_PROF_METH
INNER JOIN SNP_PROFILE
ON SNP_PROFILE.I_PROF = SNP_PROF_METH.I_PROF
INNER JOIN SNP_METHOD
ON SNP_PROF_METH.I_METH = SNP_METHOD.I_METH
INNER JOIN SNP_OBJECT
ON SNP_METHOD.I_OBJECTS = SNP_OBJECT.I_OBJECTS
WHERE SNP_PROFILE.PROF_NAME = 'OPERATOR'
MINUS
SELECT -- SNP_PROFILE.PROF_NAME,
SNP_METHOD.SHORT_NAME METH_SHORT_NAME,
SNP_METHOD.INT_JAVA_NAME AS METHOD_NAME,
SNP_PROF_METH.GENERIC_PRIV,
SNP_METHOD.I_OBJECTS,
SNP_OBJECT.INT_JAVA_NAME AS OBJECT_NAME
FROM SNP_PROF_METH
INNER JOIN SNP_PROFILE
ON SNP_PROFILE.I_PROF = SNP_PROF_METH.I_PROF
INNER JOIN SNP_METHOD
ON SNP_PROF_METH.I_METH = SNP_METHOD.I_METH
INNER JOIN SNP_OBJECT
ON SNP_METHOD.I_OBJECTS = SNP_OBJECT.I_OBJECTS
WHERE SNP_PROFILE.PROF_NAME = 'LIMITED_OPERATOR';
ODI Log files (Server and Studio)
ODI is a very developer friendly tool when it comes to development because of its graphical nature. But when something goes awry, Operator is not useful beyond certain level of debugging. To get debug at the application level an ODI personnel should understand structure of the ODI server side logs to be able to debug infrastructure issues.
Standalone Agent:
For standalone agent logging is pretty straightforward,
All the Log files for the Stand alone agent are present in directory
$ORACLE_HOME/oracledi/log
Log file names and comments
| Log file name | Generated from shell file | Comments |
| odiagent.log | All the agent logs are written to this file. Connectivity error, startup, shutdown messages etc.. | |
| stop_session.log | stopsession.sh | |
| stop_lpi.log | stoploadplan.sh | |
| startscen.log | startscen.sh | |
| start_lp.log | startloadplan.sh | |
| startcmd.log | startcmd.sh | |
| restartsession.log | restartsession.sh | |
| restart_lpi.log | restartloadplan.sh |
Configuration for Standalone Agent logging is described in this blog post (Agent Logging)
Web Logic (J2EE) Agent:
ODI Component Log file location:
In J2EE agent is a part of odi managed server, where ODI agent is just a deployment
odiagent.log files are located in the following three locations (under each managed server and admin server – it comes along with the template installation in the domain) Even though the files are present in all the managed server. Its populated actively in the managed server folder where the deployment is active.
$FUSION_MIDDLEWARE_HOME/user_projects/domains/bifoundation_domain/servers/<managed_server_where_odi_is_deployed>/logs/oracledi/
Managed Server Log file location:
All the managed servers write the output for all the deployments to managed server log files. Checking these files is a good start for ODI infrastructure debugging issue.
Studio Log file location:
Studio log files are written in the directory with the following name
%ODI_HOME%\oracledi\client\log\studio*log
