Archive for 2015

Auto-restart Load Plans / Sessions after failure

Some time we want to restart the load for a couple of time to accommodate for occasional network or system glitches to complete the load successfully.
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

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
Monday, November 30, 2015
Posted by Arjun Lagisetty

Loading same table asynchronously

This problem is on the older version of ODI (10g) . This issue is mitigated by default in the newer version of ODI 12c and some versions of 11g.


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. 
Wednesday, September 16, 2015
Posted by Arjun Lagisetty

Correlating between Oracle Database SQL ID and Oracle Data Integrator Session ID

The Problem:

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          = '') 
Monday, August 17, 2015
Posted by Arjun Lagisetty

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.

image

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

Monday, June 22, 2015
Posted by Arjun Lagisetty

Query to find out difference between two security profiles

Let's say we have a security profile which is able to do certain tasks but when you create a new one and edit it. We are unable to do certain things which you were able to do with the previous security profile.

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';
Thursday, March 19, 2015
Posted by Arjun Lagisetty

ODI Log files (Server and Studio)

ODI is a very developer friendly tool when it comes to development because of its graphical nature. &nbsp;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/

2015-01-06 17_19_00-_u01_app_test_fmw

 

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.

2015-01-06 17_29_37-_u01_app_test_fmw

 

 

Studio Log file location:

Studio log files are written in the directory with the following name

%ODI_HOME%\oracledi\client\log\studio*log

2015-01-06 17_43_15-C__WINDOWS_system32_cmd.exe

Tuesday, January 6, 2015
Posted by Arjun Lagisetty

Popular Post

Labels

Blog Archive

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