Chain can become stalled when no steps are running - Oracle DBA

Discussion

• Nirja Shah   -Posted on 30 Sep 15
  - At the completion of a step, the chain rules are always evaluated to determine the next steps to run.
  
  - If none of the rules cause another step to start, none cause the chain to end, and the evaluation_interval for the chain is NULL, the chain enters the stalled state.
  
  - When a chain is stalled, no steps are running, no steps are scheduled to run (after waiting a designated time interval), and no event steps are waiting for an event.
  
  - The chain can make no further progress unless you manually intervene.
  
  - In this case, the state of the job that is running the chain is set to CHAIN_STALLED. (However, the job is still listed in the *_SCHEDULER_RUNNING_JOBS views.)
  
  - You can troubleshoot a stalled chain with the views ALL_SCHEDULER_RUNNING_CHAINS, which shows the state of all steps in the chain (including any nested chains), and ALL_SCHEDULER_CHAIN_RULES, which contains all the chain rules.
  
  - You can enable the chain to continue by altering the state of one of its steps with the ALTER_RUNNING_CHAIN procedure.
  
  - For example, if step 11 is waiting for step 9 to succeed before it can start, and if it makes sense to do so, you can set the state of step 9 to 'SUCCEEDED'.
  
  - Alternatively, if one or more rules are incorrect, you can use the DEFINE_CHAIN_RULE procedure to replace them (using the same rule names), or to create new rules.
  
  - The new and updated rules apply to the running chain and all future chain runs.
  
  - After adding or updating rules, you must run EVALUATE_RUNNING_CHAIN on the stalled chain job to trigger any required actions.

➨ Post your comment / Share knowledge