This discussion is archived
8 Replies Latest reply: Oct 22, 2012 3:22 AM by Saro RSS

Execution task Error

Saro Explorer
Currently Being Moderated
Hi friends,

Currently im performing HRMS ETL load using DAC.

While doing so, i faced one failed task "Load Row into Run Table" with the below error
2012-10-21 15:51:15.647 CREATE INDEX:W_ETL_RUN_S:W_ETL_RUN_S_U2 has started.
ANOMALY INFO::: Error while executing : CREATE INDEX:W_ETL_RUN_S:W_ETL_RUN_S_U2
MESSAGE:::java.lang.Exception: Error while execution : CREATE UNIQUE INDEX 
     W_ETL_RUN_S_U2 
ON 
     W_ETL_RUN_S
(
     NUMERIC_ROW_ID ASC
) 
NOLOGGING TABLESPACE OBIEE_INDEX
 with error DataWarehouse:CREATE UNIQUE INDEX 
     W_ETL_RUN_S_U2 
ON 
     W_ETL_RUN_S
(
     NUMERIC_ROW_ID ASC
) 
NOLOGGING TABLESPACE OBIEE_INDEX
EXCEPTION CLASS::: java.lang.Exception

com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:449)
com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:306)
com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:213)
com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:585)
com.siebel.analytics.etl.taskmanager.XCallable.call(XCallable.java:63)
java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
java.util.concurrent.FutureTask.run(FutureTask.java:138)
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
java.util.concurrent.FutureTask.run(FutureTask.java:138)
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
java.lang.Thread.run(Thread.java:619)

::: CAUSE :::
MESSAGE:::Error while execution : CREATE UNIQUE INDEX 
     W_ETL_RUN_S_U2 
ON 
     W_ETL_RUN_S
(
     NUMERIC_ROW_ID ASC
) 
NOLOGGING TABLESPACE OBIEE_INDEX
 with error DataWarehouse:CREATE UNIQUE INDEX 
     W_ETL_RUN_S_U2 
ON 
     W_ETL_RUN_S
(
     NUMERIC_ROW_ID ASC
) 
NOLOGGING TABLESPACE OBIEE_INDEX
EXCEPTION CLASS::: java.lang.Exception

com.siebel.analytics.etl.etltask.SQLTask.doExecute(SQLTask.java:128)
com.siebel.analytics.etl.etltask.CreateIndexTask.doExecute(CreateIndexTask.java:90)
com.siebel.analytics.etl.etltask.CreateIndexTaskWrapper.doExecute(CreateIndexTaskWrapper.java:46)
com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:410)
com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:306)
com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:213)
com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:585)
com.siebel.analytics.etl.taskmanager.XCallable.call(XCallable.java:63)
java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
java.util.concurrent.FutureTask.run(FutureTask.java:138)
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
java.util.concurrent.FutureTask.run(FutureTask.java:138)
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
java.lang.Thread.run(Thread.java:619)

::: CAUSE :::
MESSAGE:::DataWarehouse:CREATE UNIQUE INDEX 
     W_ETL_RUN_S_U2 
ON 
     W_ETL_RUN_S
(
     NUMERIC_ROW_ID ASC
) 
NOLOGGING TABLESPACE OBIEE_INDEX
EXCEPTION CLASS::: com.siebel.etl.database.IllegalSQLQueryException

com.siebel.etl.database.DBUtils.executeUpdate(DBUtils.java:288)
com.siebel.etl.database.WeakDBUtils.executeUpdate(WeakDBUtils.java:357)
com.siebel.analytics.etl.etltask.SQLTask.doExecute(SQLTask.java:116)
com.siebel.analytics.etl.etltask.CreateIndexTask.doExecute(CreateIndexTask.java:90)
com.siebel.analytics.etl.etltask.CreateIndexTaskWrapper.doExecute(CreateIndexTaskWrapper.java:46)
com.siebel.analytics.etl.etltask.GenericTaskImpl.doExecuteWithRetries(GenericTaskImpl.java:410)
com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:306)
com.siebel.analytics.etl.etltask.GenericTaskImpl.execute(GenericTaskImpl.java:213)
com.siebel.analytics.etl.etltask.GenericTaskImpl.run(GenericTaskImpl.java:585)
com.siebel.analytics.e
source database of this failed task is ORA_R1213_FLATFILE
Target database: Datawarehouse
Folder: SILOS
From the above error, it seemed like there might be a problem with my tablespace "OBIEE_INDEX". But i checked with the tablespace but it seemed to be fine as it has enough space in it. Im not sure what is the reason for the problem.

Kindly help me with this.

Regards,
Saro
  • 1. Re: Execution task Error
    Ahsan Shah Expert
    Currently Being Moderated
    Can you mark that execution plan complete (make sure all tasks stop) and then restart the load cleanly? You may also want to bounce services for INFA and DAC before you restart.

    If helpful, pls mark correct.
  • 2. Re: Execution task Error
    Saro Explorer
    Currently Being Moderated
    Hi Shah,

    Thanks for your reply.

    Like you said if i mark that task as completed means, it is like skipping that failed task. Due to that skipping whether it will cause any data loss after the ETL.

    I couldnt guess what is the exact reason for that Failed task. What may be the reason for the failure shah.

    Whether it is possible to troubleshoot the issue behind the failed task, instead of skipping it.

    Thanks for your help.

    Regards,
    Saro
  • 3. Re: Execution task Error
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Can you try to unit test any of SIL or SDE task and let us know the updates.
    You can do this from EP.

    Also make sure you have set right values at Parameter tab of EP

    Edited by: veeravalli on Oct 21, 2012 11:40 AM
  • 4. Re: Execution task Error
    Ahsan Shah Expert
    Currently Being Moderated
    I did not mean mark the TASK complete..I mean the whole Execution Plan..basically stop the Execution Plan, shut down services..and then restart. If you are doing a FULL LOAD, this will be ok since everything will be reloaded. This is usually part of the first few tasks before the actual load and so it usually means something did not stop properly from a previous run.

    Try to mark the EP complete, shutdown and restart DAC and INFA server, and then run a FULL load and see if the issue persists. If hepful, pls mark.
  • 5. Re: Execution task Error
    Saro Explorer
    Currently Being Moderated
    Hi Shah,

    Thanks for your reply.

    Like you said, i marked my Execution plan and restarted those services(Informatica + DAC). Again created a new execution plan(with SA and Parameters)

    But when i tried to submit a full load, soon after 3 seconds DAC returned a error like
    FAILED TO CREATE CONNECTION POOL ORA_R1213 Process failed during creation of connection pool.
    
    ORA-12537: TNS:connection closed
    Im not sure the reason behind the problem. Since i have checked with source and target connection as it is working fine.
    Also checked with ODBC connectivity with the drivers and that is also successfully.

    Even i can login into toad for both source and target as a system user. But then too, the DAC is returning error related to DB like Connection closed.

    Not sure why the DAC is returning the db related issue even though the DB is up and running. I need to analyze with the root cause and need to check with my DBA it seems.

    Thanks

    Regards,
    Saro
  • 6. Re: Execution task Error
    Saro Explorer
    Currently Being Moderated
    Dear Svee,

    Will try with your steps and update u once my DAC DB connectivity problem is resolved.

    Thanks for your help.

    Regards,
    Saro
  • 7. Re: Execution task Error
    Ahsan Shah Expert
    Currently Being Moderated
    It is a connection issue..has nothing to do with any specific task. Does the UNIT TEST work in DAC to the source system (ORA_R1213)? Also have you logged into the DAC server and tried a TNSPING to the DB? If the DB is up and the DAC is able to connect to to DB, you should not get a TNS error.

    If helpful pls mark correct.
  • 8. Re: Execution task Error
    Saro Explorer
    Currently Being Moderated
    Hi Shah,

    Thanks once after i started a new EP, the things worked fine as the reported issue in the thread is completed successfully.

    Regards,
    Saro

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points