Unable to open session logs from Load plan executed in ODI 11g

 Hello there,

I have faced this strange issue which is related to ODI 11g navigation and would like to discuss about the fix that we used in our Linux environment.

We have been using ODI 11g since 5 years and as the days pass by the no of jobs and no of loadplans got increased dramatically and hence the session logs sequence number is very high.

Basically the difficulty here will be from the operations team where they have to filter the logs and check the errors (Time consuming task) rather than directly opening the errored session log from the load plan.

Issue and cause:- ODI 11g has a bug where if the sequence generator of the session logs reach to certain threshold then the link between session logs and executed loadploan will break and you wont be able to open the session logs from the load plans.

Fix:- The sequence generators of various elements will be stored in SNP_ID table of the work repository so we need to rest the SNP_SESSION element in the table.

1) Take backup of your work repository.
2) Shut down all ODI services and Agents (Standalone if any).
3) Make sure there are no running sessions in the repository.
4) Purge all logs from the work repository with scenario reports. make sure there are no abandoned sessions left.
5) Connect to work repository and execute the below queries just to make sure all session logs are purged.

##########################################################################


update snp_id set id_next = 1 where id_tbl = 'SNP_SESSION';


/* >>> Delete all the Session-related records, using a combination of DELETE and TRUNCATE SQL commands. <<< */

/* Naming conventions used in this script:
 *
 *   PRD_BIA_ODIREPO is the name of the Work Repository Schema.
 *
 * Replace in this document all the occurrences of PRD_BIA_ODIREPO with the name of schema containing the Work Repository tables.
 */

 /*
 * 0. First switch off the AutoCommit mode
 */
    set autocommit off ;

/*
 * 1. Delete Session parameters and messages
 */

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT where I_TXT in
    (select I_TXT_TASK_MESS from PRD_BIA_ODIREPO.SNP_SESS_TASK_LOG) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT where I_TXT in
    (select I_TXT_STEP_MESS from PRD_BIA_ODIREPO.SNP_STEP_LOG) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT where I_TXT in
    (select I_TXT_VAR from PRD_BIA_ODIREPO.SNP_VAR_SESS) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT where I_TXT in
    (select I_TXT_DEF_T from PRD_BIA_ODIREPO.SNP_VAR_SESS) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT where I_TXT in
    (select I_TXT_SESS_PARAMS from PRD_BIA_ODIREPO.SNP_SESSION) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT where I_TXT in
    (select I_TXT_SESS_MESS from PRD_BIA_ODIREPO.SNP_SESSION) ;
     
/*
 * 2. Delete Session Header parameters and messages
 */
    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT_HEADER where I_TXT in
    (select I_TXT_TASK_MESS from PRD_BIA_ODIREPO.SNP_SESS_TASK_LOG) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT_HEADER where I_TXT in
    (select I_TXT_STEP_MESS from PRD_BIA_ODIREPO.SNP_STEP_LOG) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT_HEADER where I_TXT in
    (select I_TXT_VAR from PRD_BIA_ODIREPO.SNP_VAR_SESS) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT_HEADER where I_TXT in
    (select I_TXT_DEF_T from PRD_BIA_ODIREPO.SNP_VAR_SESS) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT_HEADER where I_TXT in
    (select I_TXT_SESS_PARAMS from PRD_BIA_ODIREPO.SNP_SESSION) ;

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT_HEADER where I_TXT in
    (select I_TXT_SESS_MESS from PRD_BIA_ODIREPO.SNP_SESSION) ;
     
/*
 * 3. Delete Session execution reports
 */

    /* (a). Deactivate the constraints */
    alter table PRD_BIA_ODIREPO.SNP_SESS_TXT_LOG disable constraint FK_SESS_TXT_LOG ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_TASK_LS disable constraint FK_SESS_TASK_LS ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_TASK_LS disable constraint FK_SESS_LS_SEQ ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_TASK_LOG disable constraint FK_SESS_TASK_LOG ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_STEP_LV disable constraint FK_SESS_STEP_LV ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_STEP_LV disable constraint FK_SESS_LV_VAR ;
    alter table PRD_BIA_ODIREPO.SNP_STEP_LOG disable constraint FK_STEP_LOG ;
    alter table PRD_BIA_ODIREPO.SNP_TASK_TXT disable constraint FK_TASK_TXT ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_TASK disable constraint FK_SESS_TASK ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_STEP disable constraint FK_SESS_STEP ;
    alter table PRD_BIA_ODIREPO.SNP_SEQ_SESS disable constraint FK_SEQ_SESS ;
    alter table PRD_BIA_ODIREPO.SNP_VAR_SESS disable constraint FK_VAR_SESS ;
    alter table PRD_BIA_ODIREPO.SNP_PARAM_SESS disable constraint FK_PARAM_SESS ;

    /* (b). Delete */
    truncate table PRD_BIA_ODIREPO.SNP_SESS_TXT_LOG ;
    truncate table PRD_BIA_ODIREPO.SNP_SESS_TASK_LS ;
    truncate table PRD_BIA_ODIREPO.SNP_SESS_TASK_LOG ;
    truncate table PRD_BIA_ODIREPO.SNP_SESS_STEP_LV ;
    truncate table PRD_BIA_ODIREPO.SNP_STEP_LOG ;
    truncate table PRD_BIA_ODIREPO.SNP_TASK_TXT ;
    truncate table PRD_BIA_ODIREPO.SNP_SESS_TASK ;
    truncate table PRD_BIA_ODIREPO.SNP_SESS_STEP ;
    truncate table PRD_BIA_ODIREPO.SNP_SEQ_SESS ;
    truncate table PRD_BIA_ODIREPO.SNP_VAR_SESS ;
    truncate table PRD_BIA_ODIREPO.SNP_PARAM_SESS ;   
    truncate table PRD_BIA_ODIREPO.SNP_SESSION ;
   
    /* (c). Reactivate the constraints */
    alter table PRD_BIA_ODIREPO.SNP_SESS_TXT_LOG enable constraint FK_SESS_TXT_LOG ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_TASK_LS enable constraint FK_SESS_TASK_LS ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_TASK_LS enable constraint FK_SESS_LS_SEQ ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_TASK_LOG enable constraint FK_SESS_TASK_LOG ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_STEP_LV enable constraint FK_SESS_STEP_LV ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_STEP_LV enable constraint FK_SESS_LV_VAR ;
    alter table PRD_BIA_ODIREPO.SNP_STEP_LOG enable constraint FK_STEP_LOG ;
    alter table PRD_BIA_ODIREPO.SNP_TASK_TXT enable constraint FK_TASK_TXT ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_TASK enable constraint FK_SESS_TASK ;
    alter table PRD_BIA_ODIREPO.SNP_SESS_STEP enable constraint FK_SESS_STEP ;
    alter table PRD_BIA_ODIREPO.SNP_SEQ_SESS enable constraint FK_SEQ_SESS ;
    alter table PRD_BIA_ODIREPO.SNP_VAR_SESS enable constraint FK_VAR_SESS ;   
    alter table PRD_BIA_ODIREPO.SNP_PARAM_SESS enable constraint FK_PARAM_SESS ;
     
/*
 * 4. Delete Scenario associated messages
 */
    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT where I_TXT in
    (select I_TXT_STEP_MESS from PRD_BIA_ODIREPO.SNP_STEP_REPORT) ;   

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT where I_TXT in
    (select I_TXT_SESS_MESS from PRD_BIA_ODIREPO.SNP_SCEN_REPORT) ;   
     
 /*
 * 5. Delete Scenario Header associated messages
 */
    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT_HEADER where I_TXT in
    (select I_TXT_STEP_MESS from PRD_BIA_ODIREPO.SNP_STEP_REPORT) ;   

    delete from PRD_BIA_ODIREPO.SNP_EXP_TXT_HEADER where I_TXT in
    (select I_TXT_SESS_MESS from PRD_BIA_ODIREPO.SNP_SCEN_REPORT) ;   
     
 /*
 * 6. Delete Scenario associated reports
 */
    /* (a). Deactivate the constraints */
    alter table PRD_BIA_ODIREPO.SNP_STEP_REPORT disable constraint FK_STEP_REPORT ;
   
    /* (b). Delete */
    truncate table PRD_BIA_ODIREPO.SNP_STEP_REPORT ;
    truncate table PRD_BIA_ODIREPO.SNP_SCEN_REPORT ;

    /* (c). Reactivate the constraints */
    alter table PRD_BIA_ODIREPO.SNP_STEP_REPORT enable constraint FK_STEP_REPORT ;
   
/*
 * 7. Commit the changes
 */
    commit ;


#######################################################################
 
Clear  tmp and Cache folders in ODI server paths, after restart the ODI server will create new TMP folders  automatically.

Now you can start back your ODI services and Agents and you can test by triggering a new load plan.
You willsee the sessions will start with 2.... and now you will be able to open the session logs directly from the executed load plans.

Oracle reference:-
Unable to Open The Scenario Object Using The Link Of The Execution Session ID From Load Plan Session Log In ODI Studio Operator (Doc ID 2222817.1 )

       How To Manually Delete ODI 10g And 11g Sessions And Scenario-Related Reports (Doc ID 424740.1)

 

K@run@

Interface mapping Query ODI 11G

Below query will give you complete details of an ODI PROJECT like

PROJECT_NAME,FOLDER_NAME,INTERFACE_NAME,INTERFACE_TYPE,JOIN_CONDITION_OR_FILTER_TEXT, KM_USED,SOURCE_TABLES,COLUMNS and TARGET_TABLES,COLUMNS.

(Without connecting to ODI STUDIO to know the column mappings)

SELECT

SNP_PROJECT.PROJECT_NAME    AS PROJECT_NAME,

SNP_FOLDER.FOLDER_NAME      AS FOLDER_NAME,

SNP_POP.POP_NAME            AS INTERFACE_NAME,

CASE

WHEN SNP_POP.WSTAGE='E' THEN 'PRIMARY_INTEFACE'

ELSE 'TEMP_INTERFACE' END   AS INTERFACE_TYPE,

SNP_TXT_HEADER.FULL_TEXT JOIN_CONDITION_OR_FILTER_TEXT,

DECODE (SNP_POP_CLAUSE.JOIN_TYPE ,'J', 'JOIN','FILTER') EXPRESSION_TYPE,

NVL(STL.TRT_NAME,'NONE') AS LKM_USED ,

NVL(STI.TRT_NAME,'NONE') AS IKM_USED ,

NVL(STC.TRT_NAME,'NONE') AS CKM_USED,

SNP_SOURCE_TAB.SRC_TAB_ALIAS SOURCE_TABLE,

SNP_COL.COL_NAME  AS SOURCE_COLUMN,

SNP_POP.LSCHEMA_NAME        AS TARGET_SCHEMA,

SNP_POP.TABLE_NAME          AS TARGET_TABLE,

SNP_POP_COL.COL_NAME        AS TARGET_COLUMN

FROM SNP_FOLDER

LEFT OUTER JOIN SNP_PROJECT ON SNP_PROJECT.I_PROJECT=SNP_FOLDER.I_PROJECT

LEFT OUTER JOIN SNP_POP ON SNP_FOLDER.I_FOLDER = SNP_POP.I_FOLDER

LEFT OUTER JOIN SNP_DATA_SET ON SNP_DATA_SET.I_POP = SNP_POP.I_POP

LEFT OUTER JOIN SNP_POP_CLAUSE ON SNP_POP_CLAUSE.I_POP_CLAUSE = SNP_DATA_SET.I_POP

LEFT OUTER JOIN SNP_TXT_HEADER  ON SNP_POP_CLAUSE.I_TXT_SQL  = SNP_TXT_HEADER.I_TXT

LEFT OUTER JOIN SNP_SRC_SET ON SNP_POP.I_POP = SNP_SRC_SET.I_POP

LEFT OUTER JOIN SNP_TRT STL ON SNP_SRC_SET.I_TRT_KLM = STL.I_TRT

LEFT OUTER JOIN SNP_TRT STI ON STI.I_TRT = SNP_POP.I_TRT_KIM

LEFT OUTER JOIN SNP_TRT STC ON STC.I_TRT = SNP_POP.I_TRT_KCM

LEFT OUTER JOIN SNP_SOURCE_TAB ON SNP_SOURCE_TAB.I_DATA_SET=SNP_DATA_SET.I_DATA_SET

LEFT OUTER JOIN SNP_COL ON SNP_SOURCE_TAB.I_TABLE=SNP_COL.I_TABLE

LEFT OUTER JOIN SNP_POP_COL ON SNP_POP_COL.I_POP=SNP_POP.I_POP

WHERE

SNP_PROJECT.PROJECT_NAME='DEVELOPMENT'

--and SNP_TXT_HEADER.FULL_TEXT is not null

--AND SNP_SOURCE_TAB.I_TABLE=SNP_COL.I_TABLE

ORDER BY SNP_POP.POP_NAME;

K@run@