This discussion is archived
3 Replies Latest reply: Jul 10, 2013 10:48 PM by 978225 RSS

Job is RUNNING, but all chain steps are still NOT_STARTED

978225 Newbie
Currently Being Moderated

Hello,

 

I keep running into this problem again and again - I create a new chain and execute it.

The job's state in ALL_SCHEDULER_JOBS is "RUNNING", but all chain steps in ALL_SCHEDULER_RUNNING_CHAINS show NOT_STARTED.

I do have a chain rule that just has TRUE as condition, so the first step should definitely be running...

 

There are no other jobs running, so this should not be a resource issue...

 

The code is:

DBMS_SCHEDULER.CREATE_PROGRAM('XT_RMP_CSC_MESSAGE_RECEIVED','STORED_PROCEDURE','DN_DWH.SX_RMP_CSC_MESSAGE_RECEIVED',0, TRUE,NULL);

DBMS_SCHEDULER.ENABLE('XT_RMP_CSC_MESSAGE_RECEIVED');

 

DBMS_SCHEDULER.CREATE_PROGRAM('XT_RMP_EVALUATION_CSC_MESSAGE','STORED_PROCEDURE','DN_DWH.SX_RMP_EVALUATION_CSC_MESSAGE',0, TRUE,NULL);

DBMS_SCHEDULER.ENABLE('XT_RMP_EVALUATION_CSC_MESSAGE');

 

DBMS_SCHEDULER.CREATE_PROGRAM('XT_RMP_VEHICLE_STATE','STORED_PROCEDURE','DN_DWH.SX_RMP_VEHICLE_STATE',0, TRUE,NULL);

DBMS_SCHEDULER.ENABLE('XT_RMP_VEHICLE_STATE');

 

DBMS_SCHEDULER.CREATE_CHAIN('YYY', EVALUATION_INTERVAL=>NULL, COMMENTS=>NULL, RULE_SET_NAME=>NULL);

DBMS_SCHEDULER.DEFINE_CHAIN_STEP('YYY', STEP_NAME=>'XT_RMP_EVAL_CSC_MSG', PROGRAM_NAME=>'XT_RMP_EVALUATION_CSC_MESSAGE');

DBMS_SCHEDULER.DEFINE_CHAIN_STEP('YYY', STEP_NAME=>'XT_RMP_VEHICLE_STATE', PROGRAM_NAME=>'XT_RMP_VEHICLE_STATE');

DBMS_SCHEDULER.DEFINE_CHAIN_STEP('YYY', STEP_NAME=>'XT_RMP_CSC_MSG_RECEIVED', PROGRAM_NAME=>'XT_RMP_CSC_MESSAGE_RECEIVED');

 

DBMS_SCHEDULER.DEFINE_CHAIN_RULE('YYY', 'TRUE', 'START "XT_RMP_CSC_MSG_RECEIVED"', NULL, NULL);

DBMS_SCHEDULER.DEFINE_CHAIN_RULE('YYY', '"XT_RMP_CSC_MSG_RECEIVED" COMPLETED', 'START "XT_RMP_VEHICLE_STATE"', NULL, NULL);

DBMS_SCHEDULER.DEFINE_CHAIN_RULE('YYY', '"XT_RMP_VEHICLE_STATE" COMPLETED', 'START "XT_RMP_EVAL_CSC_MSG"', NULL, NULL);

DBMS_SCHEDULER.DEFINE_CHAIN_RULE('YYY', '"XT_RMP_EVAL_CSC_MSG" COMPLETED', 'END ', NULL, NULL);

 

DBMS_SCHEDULER.ENABLE('YYY');

 

What could be wrong here?

 

I had this problem in the past when I had created the chain using SqlDeveloper, and after creating the same chain again two or three times it worked all of a sudden. But I was never able to figure out the difference between the working chain and the ones that did not work...

 

 

Thank you

  • 1. Re: Job is RUNNING, but all chain steps are still NOT_STARTED
    978225 Newbie
    Currently Being Moderated

    I just created another chain manually in SqlDeveloper with just one step and started it.

    The same thing is happening.

    I logged all the SQL statements generated by SqlDeveloper:

     

    BEGIN

        sys.dbms_scheduler.create_chain(

            comments => 'Extract RMP sources',

            chain_name => 'DN_DWH.SX_RMP'

        );

          sys.dbms_scheduler.enable(name=>'DN_DWH.SX_RMP');

    END;

     

     

    BEGIN

        SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP(

            CHAIN_NAME  => '"DN_DWH"."SX_RMP"',

            STEP_NAME  => '"XT_RMP_VEHICLE_STATE"',

            PROGRAM_NAME => '"DN_DWH"."XT_RMP_VEHICLE_STATE"' ); 

            SYS.DBMS_SCHEDULER.ALTER_CHAIN(

                CHAIN_NAME  => '"DN_DWH"."SX_RMP"',

                STEP_NAME => '"XT_RMP_VEHICLE_STATE"',

                ATTRIBUTE => 'PAUSE',

                VALUE => false);

            SYS.DBMS_SCHEDULER.ALTER_CHAIN(

                CHAIN_NAME  => '"DN_DWH"."SX_RMP"',

                STEP_NAME => '"XT_RMP_VEHICLE_STATE"',

                ATTRIBUTE => 'SKIP',

                VALUE => false);

            SYS.DBMS_SCHEDULER.ALTER_CHAIN(

                CHAIN_NAME  => '"DN_DWH"."SX_RMP"',

                STEP_NAME => '"XT_RMP_VEHICLE_STATE"',

                ATTRIBUTE => 'RESTART_ON_FAILURE',

                VALUE => false);

            SYS.DBMS_SCHEDULER.ALTER_CHAIN(

                CHAIN_NAME  => '"DN_DWH"."SX_RMP"',

                STEP_NAME => '"XT_RMP_VEHICLE_STATE"',

                ATTRIBUTE => 'RESTART_ON_RECOVERY',

                VALUE => false);

    END;

     

     

    BEGIN

        sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (

            CHAIN_NAME  => '"DN_DWH"."SX_RMP"',

            condition => 'TRUE',

            action => 'START "XT_RMP_VEHICLE_STATE"'

            ); 

    END;

     

     

    BEGIN

        sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (

            CHAIN_NAME  => '"DN_DWH"."SX_RMP"',

            condition => '"XT_RMP_VEHICLE_STATE" COMPLETED',

            action => 'END'

            ); 

    END;

     

     

    BEGIN

        SYS.DBMS_SCHEDULER.RUN_CHAIN(chain_name => '"DN_DWH"."SX_RMP"',

                                     start_steps => NULL);

    END;

  • 2. Re: Job is RUNNING, but all chain steps are still NOT_STARTED
    978225 Newbie
    Currently Being Moderated

    I now called

    execute SYS.DBMS_SCHEDULER.RUN_CHAIN(chain_name => '"DN_DWH"."SX_RMP"', start_steps => 'XT_RMP_VEHICLE_STATE');

     

    This got my one step chain started, but the weird thing is that the chain does not complete. The step's state is SUCCEEDED, but the chain is still RUNNING, although there is no other step - execpt for the END step...

     

    It looks like somehow the scheduler is not processing the rules properly, although they could hardly be any simpler than they already are...

  • 3. Re: Job is RUNNING, but all chain steps are still NOT_STARTED
    978225 Newbie
    Currently Being Moderated

    The problem is definitely related to the chain object - I can take a chain that works, gutt it entirely of all steps and rules using SqlDeveloper and run all the step and rule creation statements for the broken chain, just with the chain names replaced - the old good chain will now run the logic of the other "broken" chain without problem.

     

    Another workaround that fixes my "broken" chains: log on to the Oracle Enterprise Manager webinterface management console, "CREATE LIKE" a new chain as copy of the old "broken" chain - just specify a new name and the new supposedly identical copy (except for the name of course) will run with no problem. The old chain still keeps having the same problem...

Legend

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