X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

How to handle BIAPPS ETL failures due to duplicate data issues in Oracle DW

Guest Author

How to handle BIAPPS ETL failures due to duplicate data issues in Oracle DW

Author: Amit Kothari

Sometimes there are duplicate data rows in the source tables, due to this while running a ODI Load Plan (LP) in BI Applications 11.1.1.7.1 the ODI sessions may error out while creating the unique indexes. It is always recommended that the source data issues be fixed first but that may not be feasible sometimes or you may just want to fix the data in the warehouse and continue with the LP run. This blog shows you one way to do that – first we find the table and the indexed columns for the failed index and then we substitute it in the delete sql to delete the duplicate rows.

Sample Error:

ODI-1217: Session EXEC_TABLE_MAINT_PROC (12383500) fails with return code 20000.
ODI-1226: Step TABLE_MAINT_PROC fails after 1 attempt(s).
ODI-1232: Procedure TABLE_MAINT_PROC execution fails.
ODI-1228: Task TABLE_MAINT_PROC (Procedure) fails on the target ORACLE connection BIAPPS_DW.
Caused By: java.sql.SQLException: ORA-20000: Error creating Index/Constraint : W_EMPLOYEE_D_U1=> ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
ORA-06512: at line 152


Solution:

From sql developer run this sql in your ODI repository schema based on the failed indexed name which we got from the error above, it will give you the indexed
columns for the failed index, for eg -


SELECT st.table_name,sc.col_name
FROM SNP_TABLE st, SNP_KEY sk, snp_key_col skc, snp_col sc
WHERE sk.key_name ='W_EMPLOYEE_D_U1'
  AND sk.I_table = st.I_table   AND skc.I_key = sk.I_key   AND
sc.I_col= skc.i_col   AND st.I_table = sc.i_table


When we run the above sql we get the table name as W_EMPLOYEE_D and columns as – EFFECTIVE_FROM_DT , DATASOURCE_NUM_ID,INTEGRATION_ID

These are the columns which has the duplicate rows resulting in the failure of the unique index creation. Now we can proceed to delete the duplicate rows via a delete sql, make sure you backup the table data before issuing the delete statement.

From sql developer run this sql in your Oracle target schema and then simply restart the ODI load plan - a sample delete sql based on the above index is as follows. Just substitute the columns and the table name for your use case.

DELETE FROM W_EMPLOYEE_D A
WHERE ROWID > (SELECT MIN(ROWID) FROM W_EMPLOYEE_D B WHERE A.INTEGRATION_ID
= B.INTEGRATION_ID AND A.DATASOURCE_NUM_ID = B.DATASOURCE_NUM_ID AND
A.EFFECTIVE_FROM_DT = B.EFFECTIVE_FROM_DT) ;

commit;

 

Join the discussion

Comments ( 11 )
  • John Thursday, November 14, 2013

    Hello Amit,

    Thank you for providing a quick fix for this problem I've been having. I don't know how it is with other people having this problem, but in our case I think it's not a problem in the source system. We are attached to a PeopleSoft 9.1 HCM environment and this problem occurs each time a person has more than one effective dated row. Then they have identical rows besides the row_wid and the effective_from_dt fields. Yet the index is based on the integration_id and datasource_num_id.

    EXECUTE IMMEDIATE 'CREATE INDEX RABO_DW.W_HR_PERSON_D_U1 ON RABO_DW.W_HR_PERSON_D (DATASOURCE_NUM_ID,INTEGRATION_ID) NOLOGGING ';

    This can never work, can it?


  • guest Friday, November 15, 2013

    If you are getting errors on W_HR_PERSON_D, there might be a data or code issue that we might need to investigate. Please send us the SR that is already logged with Oracle.


  • guest Tuesday, February 11, 2014

    There is a more elegant way of handling such issues.

    In ODI Studio, open the interface causing the issue, go to the flow tab, select the target datastore, and in the KM options that will apppear in the property inspector set FLOW_CONTROL to yes. Save en generate the scenario. Next run, any records that violate constraints will be set aside in an E$_<tablename> error table, and an overview of the errors can be found in SNP_CHECK_TABLE.


  • Amit Wednesday, February 12, 2014

    Yes, that is another way to handle errors but turning on FLOW_CONTROL has a negative performance impact and that is why out of the box it is turned off for BIAPPS interfaces. The steps given above is just a quick solution for unblocking ETL run issues during a POC or testing. It is always better to fix the source data. Thanks for your comments.


  • John Thursday, February 13, 2014

    It's not a data issue, it looks like a flaw in the design.

    Whenever someone has a job entry and a termination in the same year, their person data gets added to the data warehouse twice instead of once. Rather than loading the person data first, it's derived from the job records with not enough fields to make it unique.

    There are more data loading issues popping up and SR's are being issued. We already had a couple of fixes on delivered software.


  • guest Saturday, July 5, 2014

    Dear Amit,

    A load plan for payrol ends up with the following two excetions, If you could help me fix this.

    Start Date

    Saturday, July 5, 2014 4:04:01 AM GMT+03:00

    End Date

    Saturday, July 5, 2014 10:02:09 AM GMT+03:00

    Duration (s)

    2

    Return Code

    20000

    Message

    ODI-1217: Session EXEC_TABLE_MAINT_PROC (1790500) fails with return code 20000. ODI-1226: Step TABLE_MAINT_PROC fails after 1 attempt(s). ODI-1232: Procedure TABLE_MAINT_PROC execution fails. ODI-1228: Task TABLE_MAINT_PROC (Procedure) fails on the target ORACLE connection BIAPPS_DW. Caused By: java.sql.SQLException: ORA-20000: Error creating Index/Constraint :W_POSITION_T1_D_PK => ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at line 627

    Start Date

    Saturday, July 5, 2014 4:04:12 AM GMT+03:00

    End Date

    Saturday, July 5, 2014 10:02:10 AM GMT+03:00

    Duration (s)

    3

    Return Code

    20000

    Message

    ODI-1217: Session EXEC_TABLE_MAINT_PROC (1800500) fails with return code 20000. ODI-1226: Step TABLE_MAINT_PROC fails after 1 attempt(s). ODI-1232: Procedure TABLE_MAINT_PROC execution fails. ODI-1228: Task TABLE_MAINT_PROC (Procedure) fails on the target ORACLE connection BIAPPS_DW. Caused By: java.sql.SQLException: ORA-20000: Error creating Index/Constraint :W_POSITION_DH_U2 => ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at line 1023

    Thanks in advance.


  • guest Monday, July 21, 2014

    Is there any way so that

    1.we can insert duplicate records deletion code in EXEC_TABLE_MAINT_PROC 2. Can we execute it as pre session


  • Guna Tuesday, July 22, 2014

    When you load a Fact Group or Dimension Group, there could be a series of steps. EXEC_TABLE_MAINT_PROC is called before the series of steps or after the series of steps. So having delete duplicate records code, in the EXEC_TABLE_MAINT_PROC, is not always effective as the duplicates may have been caused by the first step in that series of steps. Furthermore since this is BIAPPS standard procedure and a widely used one, you could risk destabilizing the entire load plan if you did incorrect changes to it. If you want to automate the step to delete the duplicates and have the SQL to do it (refer to Amit's Post above), then create a new ODI procedure and include that step in your generated load plan precisely at the point where you want it to run.

    High level steps for the ODI procedure:

    1) Create a new ODI procedure

    2) in the first step, Command on Target - Choose Oracle as technology and DW_BIAPPS11G as the schema. Paste the SQL you want to run in there. Name the Procedure step and Procedure itself appropriately.

    3) Create a scenario for that procedure

    4) Drag the scenario to the appropriate place in the generated load plan.

    Note1: Everytime you generate a new load plan, you will need to do step 4 again.

    Note2: If you change the load plan, you cannot restart it. It has to be a new load plan run. So you cant add this step and expect that a restart to the failed load plan will now pick up this new step.

    Note3: If this is your production instance, then you should be investigating the reason for duplicates rather than just deleting them.

    Note4: Save the procedures and any such customizations in your own custom folders. Otherwise when your BIAPPS repository is patched, it will overwrite any changes you did. Refer to the customization guide in BIAPPS documentation for more details.


  • Amit Kothari Monday, August 4, 2014

    For the issue with W_POSITION_T1_D_PK this maybe due to source data issues. We will need an SR/etc to handle the specifics.


  • guest Saturday, July 16, 2016

    I am getting below error while running Domain in biapps.

    Return Code

    ODI-1519

    Error Message

    ODI-1519: Serial step "Start Load Plan (InternalID:13412500)" failed because child step "Global Variable Refresh (InternalID:13413500)" is in error.

    ODI-1519: Serial step "Global Variable Refresh (InternalID:13413500)" failed because child step "1 Domain (InternalID:13420500)" is in error.

    ODI-1519: Serial step "1 Domain (InternalID:13420500)" failed because child step "2 Domain SDE (InternalID:13497500)" is in error.

    ODI-1519: Serial step "2 Domain SDE (InternalID:13497500)" failed because child step "Serial (InternalID:13498500)" is in error.

    ODI-1519: Serial step "Serial (InternalID:13498500)" failed because child step "3 SDE General Domain (InternalID:13502500)" is in error.

    ODI-1519: Serial step "3 SDE General Domain (InternalID:13502500)" failed because child step "Load Target Table (InternalID:13503500)" is in error.

    ODI-1519: Serial step "Load Target Table (InternalID:13503500)" failed because child step "EBS_12_2 - DSN 1 (InternalID:13504500)" is in error.

    ODI-1519: Serial step "EBS_12_2 - DSN 1 (InternalID:13504500)" failed because child step "DOMAIN (InternalID:13505500)" is in error.

    ODI-1519: Serial step "DOMAIN (InternalID:13505500)" failed because child step "Parallel (InternalID:13506500)" is in error.

    ODI-1518: Parallel step "Parallel (InternalID:13506500)" failed; 3 child step(s) in error, which is more than the maximum number of allowed errors (0) defined for the parallel step. Failed child steps: COMMON (InternalID:13507500), FINANCIALS (InternalID:13524500), HUMAN_RES (InternalID:13521500)

    ODI-1518: Parallel step "COMMON (InternalID:13507500)" failed; 8 child step(s) in error, which is more than the maximum number of allowed errors (0) defined for the parallel step. Failed child steps: SDE_ORA_DomainGeneral_FND_LOOKUPS_MONTH_OF_YEAR (InternalID:13514500), SDE_ORA_DOMAINGENERAL_FND_LOOKUPS VENDOR TYPE (InternalID:13517500), SDE_ORA_DOMAINGENERAL_STATE (InternalID:13508500), SDE_ORA_DomainGeneral_FND_LOOKUPS_DAY_OF_WEEK (InternalID:13513500), SDE_ORA_DOMAINGENERAL_FND_LOOKUPS ORGANIZATION_TYPE (InternalID:13518500), SDE_ORA_DOMAINGENERAL_COUNTRY (InternalID:13509500), SDE_ORA_DOMAINGENERAL_CURRENCY_RATE_TYPE (InternalID:13510500), SDE_ORA_DOMAINGENERAL_CURRENCY (InternalID:13511500)

    ODI-1217: Session SDE_ORAR122_ADAPTOR_SDE_ORA_DOMAINGENERAL_FND_LOOKUPS (506500) fails with return code 20942.

    ODI-1226: Step Diagnostics Raise Exception fails after 1 attempt(s).

    ODI-1232: Procedure Diagnostics Raise Exception execution fails.

    ODI-1228: Task Diagnostics Raise Exception (Procedure) fails on the target ORACLE connection BIAPPS_DW.

    Caused By: java.sql.SQLException: ORA-20942: This task failed and could not be autocorrected. Please see complete ODI operator log to get the actual error message => ORA-20942:

    ORA-06512: at line 49

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)

    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)

    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)

    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)

    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)

    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)

    at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)

    at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)

    at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)

    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)

    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2925)

    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2637)

    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)

    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)

    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)

    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1898)

    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:583)

    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:514)

    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1074)

    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:133)

    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:84)

    at java.lang.Thread.run(Thread.java:745)


  • Muhammad Saad Saturday, July 16, 2016

    Return Code

    ODI-1519

    Error Message

    ODI-1519: Serial step "Start Load Plan (InternalID:13412500)" failed because child step "Global Variable Refresh (InternalID:13413500)" is in error.

    ODI-1519: Serial step "Global Variable Refresh (InternalID:13413500)" failed because child step "1 Domain (InternalID:13420500)" is in error.

    ODI-1519: Serial step "1 Domain (InternalID:13420500)" failed because child step "2 Domain SDE (InternalID:13497500)" is in error.

    ODI-1519: Serial step "2 Domain SDE (InternalID:13497500)" failed because child step "Serial (InternalID:13498500)" is in error.

    ODI-1519: Serial step "Serial (InternalID:13498500)" failed because child step "3 SDE General Domain (InternalID:13502500)" is in error.

    ODI-1519: Serial step "3 SDE General Domain (InternalID:13502500)" failed because child step "Load Target Table (InternalID:13503500)" is in error.

    ODI-1519: Serial step "Load Target Table (InternalID:13503500)" failed because child step "EBS_12_2 - DSN 1 (InternalID:13504500)" is in error.

    ODI-1519: Serial step "EBS_12_2 - DSN 1 (InternalID:13504500)" failed because child step "DOMAIN (InternalID:13505500)" is in error.

    ODI-1519: Serial step "DOMAIN (InternalID:13505500)" failed because child step "Parallel (InternalID:13506500)" is in error.

    ODI-1518: Parallel step "Parallel (InternalID:13506500)" failed; 3 child step(s) in error, which is more than the maximum number of allowed errors (0) defined for the parallel step. Failed child steps: COMMON (InternalID:13507500), FINANCIALS (InternalID:13524500), HUMAN_RES (InternalID:13521500)

    ODI-1518: Parallel step "COMMON (InternalID:13507500)" failed; 8 child step(s) in error, which is more than the maximum number of allowed errors (0) defined for the parallel step. Failed child steps: SDE_ORA_DomainGeneral_FND_LOOKUPS_MONTH_OF_YEAR (InternalID:13514500), SDE_ORA_DOMAINGENERAL_FND_LOOKUPS VENDOR TYPE (InternalID:13517500), SDE_ORA_DOMAINGENERAL_STATE (InternalID:13508500), SDE_ORA_DomainGeneral_FND_LOOKUPS_DAY_OF_WEEK (InternalID:13513500), SDE_ORA_DOMAINGENERAL_FND_LOOKUPS ORGANIZATION_TYPE (InternalID:13518500), SDE_ORA_DOMAINGENERAL_COUNTRY (InternalID:13509500), SDE_ORA_DOMAINGENERAL_CURRENCY_RATE_TYPE (InternalID:13510500), SDE_ORA_DOMAINGENERAL_CURRENCY (InternalID:13511500)

    ODI-1217: Session SDE_ORAR122_ADAPTOR_SDE_ORA_DOMAINGENERAL_FND_LOOKUPS (506500) fails with return code 20942.

    ODI-1226: Step Diagnostics Raise Exception fails after 1 attempt(s).

    ODI-1232: Procedure Diagnostics Raise Exception execution fails.

    ODI-1228: Task Diagnostics Raise Exception (Procedure) fails on the target ORACLE connection BIAPPS_DW.

    Caused By: java.sql.SQLException: ORA-20942: This task failed and could not be autocorrected. Please see complete ODI operator log to get the actual error message => ORA-20942:

    ORA-06512: at line 49

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)

    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)

    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)

    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)

    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)

    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)

    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1115)

    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1488)

    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)

    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3954)

    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1539)

    at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)

    at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)

    at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)

    at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)

    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2925)

    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2637)

    at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)

    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)

    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)

    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1898)

    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$2.doAction(StartScenRequestProcessor.java:583)

    at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)

    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor.doProcessStartScenTask(StartScenRequestProcessor.java:514)

    at oracle.odi.runtime.agent.processor.impl.StartScenRequestProcessor$StartScenTask.doExecute(StartScenRequestProcessor.java:1074)

    at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:133)

    at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:84)

    at java.lang.Thread.run(Thread.java:745)


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.