Forum Stats

  • 3,839,782 Users
  • 2,262,536 Discussions


Generating Trace file for ODI Sessions

Uma Shankar-Oracle
Uma Shankar-Oracle Member Posts: 13
edited Jan 27, 2012 11:22AM in Data Integrator
ODI Gurus

am new to ODI, for one of our integration project am using ODI for transforming data from Source to Target Instance.
Oracle Database is installed on both Source and Target instances. Is there a way to generate Database Trace files for ODI sessions triggered.
If yes can you provide the steps to enable/disable database trace for ODI sessions.

Uma Shankar


  • Ankit J
    Ankit J Member Posts: 750 Bronze Trophy
    edited Jan 27, 2012 11:22AM
    Hi Uma Shankar,

    My assumptions:
    1.) You want to selectively enable Oracle tracing when only the ODI scenarios are executed and not otherwise.
    If that is the case then you will have to add some external steps comprising of OS commands and variables that will control if those steps are to be executed or not. These steps will invoke the OS commands to start Oracle tracing.
    2.) You know the steps to create a package and know how to use Interfaces and other ODI utilities in the flow of the package.
    3.) You know how to create and execute scenarios

    Steps to Selectively enable tracing.
    1.) Add a variable named V_START_TRACE to the package. This variable will be passed a value from scenario which will determine if you want to enable to trace or not. This variable type is EVALUATE and can have 2 values - TRUE or FALSE. You will have to check for the condition and depending on the condition you will take a path forward.
    2.) Add 2 OS commands to the package - OS_START_TRACE and OS_END_TRACE
    3.) Create 2 shell scripts (or bat files) - and with the following commands: :-
    sqlplus session - ALTER SYSTEM SET trace_enabled = TRUE; :-
    sqlplus session - ALTER SYSTEM SET trace_enabled = FALSE;
    You need to invoke SQLPLUS sessions from the above shell scripts to enable/disable the oracle level tracing.

    4.) Add the above shell scripts to the OS commands created in Step 2.
    5.) Join the steps in the following order in the package :
    V_START_TRACE (OK) ->OS_START_TRACE (OK) -> <<+Your Actual Integration Code+>>(OK) -> OS_END_TRACE
    V_START_TRACE (KO) -> <<+Your Actual Integration Code+>>(OK)

    The above KO variable statement doesnt start the trace if the evaluated value doesnt = TRUE. So this will let you do a selective trace.

    6.) Create the scenario from the package and execute the scenario by passing it a value "TRUE" for variable V_START_TRACE.
    Check the syntax, something like:

    The above is a general solution for enabling trace for any RDBMS

    If you need any more information, feel free to ping back.
This discussion has been closed.