1 Reply Latest reply on Dec 21, 2011 2:09 PM by Anderson Perim

    BDSSA - ORA-20200: ETL failed for Site - 1

      I´m trying to execute run_etl.nsh for the first time and generates the erro below in 0_BSARA_ETL section.

       

      20200 : 72000 : java.sql.SQLException: ORA-20200: ETL failed for Site - 1

      ORA-06512: at line 1

       

      java.sql.SQLException: ORA-20200: ETL failed for Site - 1

      ORA-06512: at line 1

       

         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)

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

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

         at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)

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

         at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)

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

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

         at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3057)

         at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)

         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)

         at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)

         at com.sunopsis.dwg.dbobj.SnpSessTaskSqlS.treatTaskTrt(SnpSessTaskSqlS.java)

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

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

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

         at com.sunopsis.dwg.cmd.DwgCommandScenario.treatCommand(DwgCommandScenario.java)

         at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)

         at com.sunopsis.dwg.cmd.e.i(e.java)

         at com.sunopsis.dwg.cmd.e.y(e.java)

         at com.sunopsis.dwg.DwgJv.treatCmd(DwgJv.java)

         at com.sunopsis.dwg.DwgJv.main(DwgJv.java)

         at oracle.odi.Agent.main(Agent.java)

       

      Can someone help me?

        • 1. BDSSA - ORA-20200: ETL failed for Site - 1

          Log for details.

           

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          create unique index     BSARA_DW.I1$_F_JOB_RUN_idx on        BSARA_DW.I1$_F_JOB_RUN (JOB_RUN_ID, BL_SITE_ID) NOLOGGING

           

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          update    BSARA_DW.I1$_F_JOB_RUN set    IND_UPDATE = 'U' where    (JOB_RUN_ID, BL_SITE_ID)     in    (         select    JOB_RUN_ID,             BL_SITE_ID         from    BSARA_DW.F_JOB_RUN         )

           

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          /*     21-Oct-07 Added UD1 flag, this is done so that we don't take UD1 field for Update Added UD4 to ignore in Update */ update    BSARA_DW.F_JOB_RUN T  set     (     T.JOB_ID,     T.APPLICATION_SERVER_ID,     T.ROLE_HIST_ID,     T.USER_HIST_ID,     T.OBJECT_TYPE_ID,     T.PROGRESS_STATUS_ID,     T.START_DATE_ID,     T.START_TIME_ID,     T.END_DATE_ID,     T.END_TIME_ID,     T.OVERALL_EXIT,     T.IS_ERRORS,     T.IS_WARNINGS,     T.IS_CANCELLED,     T.IS_REBOOT,     T.IS_RESET,     T.IN_PROGRESS_WORK_ITEMS,     T.FINISHED_WORK_ITEMS,     T.TOTAL_WORK_ITEMS,     T.DATE_MODIFIED,     T.IS_DELETED,     T.IS_RUN_BY_BATCH_JOB,     T.RUN_START_TIME,     T.RUN_END_TIME,     T.EXECUTION_TIME,     T.ETL_SEQ_ID,     T.JOB_VERSION_ID     ,T.IS_LATEST_RUN     ) =          (         select    S.JOB_ID,             S.APPLICATION_SERVER_ID,             S.ROLE_HIST_ID,             S.USER_HIST_ID,             S.OBJECT_TYPE_ID,             S.PROGRESS_STATUS_ID,             S.START_DATE_ID,             S.START_TIME_ID,             S.END_DATE_ID,             S.END_TIME_ID,             S.OVERALL_EXIT,             S.IS_ERRORS,             S.IS_WARNINGS,             S.IS_CANCELLED,             S.IS_REBOOT,             S.IS_RESET,             S.IN_PROGRESS_WORK_ITEMS,             S.FINISHED_WORK_ITEMS,             S.TOTAL_WORK_ITEMS,             S.DATE_MODIFIED,             S.IS_DELETED,             S.IS_RUN_BY_BATCH_JOB,             S.RUN_START_TIME,             S.RUN_END_TIME,             S.EXECUTION_TIME,             S.ETL_SEQ_ID,             S.JOB_VERSION_ID             ,0         from    BSARA_DW.I1$_F_JOB_RUN S         where    T.JOB_RUN_ID    =S.JOB_RUN_ID         and    T.BL_SITE_ID    =S.BL_SITE_ID              ) where    (JOB_RUN_ID, BL_SITE_ID)     in    (         select    JOB_RUN_ID,             BL_SITE_ID         from    BSARA_DW.I1$_F_JOB_RUN         where    IND_UPDATE = 'U'         )

           

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          insert into     BSARA_DW.F_JOB_RUN     (     JOB_RUN_ID,     JOB_HIST_ID,     JOB_ID,     APPLICATION_SERVER_ID,     ROLE_HIST_ID,     USER_HIST_ID,     OBJECT_TYPE_ID,     PROGRESS_STATUS_ID,     START_DATE_ID,     START_TIME_ID,     END_DATE_ID,     END_TIME_ID,     OVERALL_EXIT,     IS_ERRORS,     IS_WARNINGS,     IS_CANCELLED,     IS_REBOOT,     IS_RESET,     IN_PROGRESS_WORK_ITEMS,     FINISHED_WORK_ITEMS,     TOTAL_WORK_ITEMS,     DATE_MODIFIED,     IS_DELETED,     IS_RUN_BY_BATCH_JOB,     RUN_START_TIME,     RUN_END_TIME,     EXECUTION_TIME,     ETL_SEQ_ID,     JOB_VERSION_ID     ,IS_LATEST_RUN,     JOB_RUN_HIST_ID,     BL_SITE_ID     ) select     JOB_RUN_ID,     JOB_HIST_ID,     JOB_ID,     APPLICATION_SERVER_ID,     ROLE_HIST_ID,     USER_HIST_ID,     OBJECT_TYPE_ID,     PROGRESS_STATUS_ID,     START_DATE_ID,     START_TIME_ID,     END_DATE_ID,     END_TIME_ID,     OVERALL_EXIT,     IS_ERRORS,     IS_WARNINGS,     IS_CANCELLED,     IS_REBOOT,     IS_RESET,     IN_PROGRESS_WORK_ITEMS,     FINISHED_WORK_ITEMS,     TOTAL_WORK_ITEMS,     DATE_MODIFIED,     IS_DELETED,     IS_RUN_BY_BATCH_JOB,     RUN_START_TIME,     RUN_END_TIME,     EXECUTION_TIME,     ETL_SEQ_ID,     JOB_VERSION_ID     ,0,     SEQ_F_JOB_RUN.NEXTVAL,     1.0 from    BSARA_DW.I1$_F_JOB_RUN where    IND_UPDATE = 'I'

           

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          BEGIN  UPDATE ETL_LAST_RUN SET     END_TIME = sysdate,     STATUS = 'ERROR' where    ETL_LAST_RUN_ID IN      (select ETL_LAST_RUN_ID from ETL_LAST_RUN     where    BL_SITE_ID =  1.0     and    SRC_SCHEMA_NAME =   'bladelogic'     and    TABLE_NAME IN ('F_JOB_RUN', 'FOUNDATION')      and    STATUS = 'STARTED'     and     END_TIME IS NULL);  COMMIT; END;

           

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          /*commit*/

           

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          DECLARE raise_err EXCEPTION; BEGIN   RAISE raise_err;  EXCEPTION WHEN raise_err THEN   RAISE_APPLICATION_ERROR( -20101, 'Scenario failed for Site - ' || 1.0 ); END;

           

          12/21/2011 05:31:58 PM(main): Session : 34001 finished with return code : 20101

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          BEGIN  UPDATE ETL_LAST_RUN SET     END_TIME = sysdate,     STATUS = 'ERROR' where    ETL_LAST_RUN_ID IN      (select ETL_LAST_RUN_ID from ETL_LAST_RUN     where    BL_SITE_ID =  1.0     and    SRC_SCHEMA_NAME =   'bladelogic'     and    TABLE_NAME IN ('FACT', 'FOUNDATION')      and    STATUS = 'STARTED'     and     END_TIME IS NULL);  COMMIT; END;

           

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          /*commit*/

           

          12/21/2011 05:31:58 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          DECLARE raise_err EXCEPTION; BEGIN   RAISE raise_err;  EXCEPTION WHEN raise_err THEN   RAISE_APPLICATION_ERROR( -20101, 'Scenario failed for Site - ' || 1.0 ); END;

           

          12/21/2011 05:31:58 PM(main): Session : 30001 finished with return code : 20101

          12/21/2011 05:31:59 PM(main): SnpSessTaskSql.execStdOrders : snps std order :

          DECLARE raise_err EXCEPTION; BEGIN   RAISE raise_err;  EXCEPTION WHEN raise_err THEN   RAISE_APPLICATION_ERROR( -20200, 'ETL failed for Site - ' || 1.0 ); END;

           

          12/21/2011 05:31:59 PM(main): Session : 28001 finished with return code : 20200

          DwgJv.main: Exit. Return code:20200