dbms_scheduler: job doesn't finish

[SimonSellick]

I'm encountering strange, but reproducible behaviour with a job comprising a chain with a number of steps.

There are 5 steps in the chain, and they run sequentially. The last step is also intended to tidy up if there's a problem with an earlier step.

The rules look like this:

no    condition         action
 1    -                 start step 1
 2    step 1 succeeded  start step 2
 3    step 2 succeeded  start step 3
 4    step 3 succeeded  start step 4
 5    step 4 completed
   or step 1 failed
   or step 2 failed
   or step 3 failed     start step 5
 6    step 5 completed  end

When the steps all succeed, there's no problem - everything tidies up as expected.

When a step fails, the remaining intermediate steps don't start (as expected) and step 5 runs and ends the chain - but it doesn't finish the job.

The user_scheduler_running_chains shows the failed, succeeded and not_started steps as though the chain is still running, but if I try to change the status of the unstarted steps with the proc alter_running_chain, it complains that the job isn't running.

The log view user_scheduler_job_run_details shows the failed and succeeded steps, but doesn't show the job that called the chain, so this appears to be where the hold-up is.

If I then try to run the job again, the run_job procedure returns normally, but the job doesn't run; however, the mess left behind by the previous failed run gets tidied up, and a subsequent run will now work (unless it fails again of course).

If anyone has run into this or has advice about how better to build the chain, I'd be grateful.

TIA
Simon.

 

[SimonSellick]

I've simplified it now to a single step in the chain and established that the problem appears only when the job has scheduled repeats.

If I create the job as a one-off (repeat_interval => null) and start it, it runs the chain and completes. I can start it again and it runs correctly and completes.

If I create it with a schedule and then start it, it runs the chain but doesn't complete, as shown by a row in user_scheduler_logs for the job starting but none for it finishing. If I now start it again, it tidies up the previous run but doesn't actually run again.

This is running on a unix (AIX) host; I'd be interested to know whether anyone else can reproduce it. Code to test follows:

drop table t;                                    -- tidy up

drop procedure t1;

begin
  dbms_scheduler.drop_chain(
    chain_name =>     't_chain',
    force =>           true
  );
end;
/

begin
  dbms_scheduler.drop_program(
    program_name =>   't_program_1',
    force =>           true
  );
end;
/

begin
  dbms_scheduler.drop_job(
    job_name =>       't_job',
    force =>           true
  );
end;
/

create table t(m varchar2(500));

create or replace procedure t1 as
begin
  insert into t values ('t1 run');
  commit;
end;
/

begin
  dbms_scheduler.create_program(                -- programs
    program_name =>       't_program_1',
    program_type =>       'STORED_PROCEDURE',
    program_action =>     't1',
    number_of_arguments => 0
  );
  dbms_scheduler.create_chain(                  -- chain
    chain_name =>         't_chain'
  );
  dbms_scheduler.define_chain_step(             -- chain steps
    step_name =>          't_step_1',
    chain_name =>         't_chain',
    program_name =>       't_program_1'
  );
  dbms_scheduler.define_chain_rule(             -- chain rules
    rule_name =>          't_rule_1',
    chain_name =>         't_chain',
    condition =>          'TRUE',
    action =>             'START t_step_1'
  );
  dbms_scheduler.define_chain_rule(
    rule_name =>          't_rule_2',
    chain_name =>         't_chain',
    condition =>          't_step_1 SUCCEEDED',
    action =>             'END'
  );
  dbms_scheduler.create_job(                    -- create the scheduled job
    job_name =>           't_job',
    job_type =>           'CHAIN',
    job_action =>         't_chain',
    start_date =>          round(sysdate),
    repeat_interval =>     'FREQ = WEEKLY; ' ||
                          'BYDAY = MON, TUE, WED, THU, FRI; ' ||
                          'BYHOUR = 06',
    enabled =>             true,
    comments =>           'Schedules and executes t_chain'
  );
end;
/

begin
  dbms_scheduler.enable(                        -- enable it all
    name => 't_program_1, ' ||
            't_chain'
  );
end;
/

This starts the job:

begin
  dbms_scheduler.run_job(
    job_name => 't_job', 
    use_current_session => false
  );
end;
/

And this checks the latest log entries:

select   log_id, 
         to_char(log_date) event_date, 
         job_name, 
         job_subname, 
         status,
         additional_info 
from     user_scheduler_job_log 
where    job_name like 'T_%'
and      log_id >= (
           select max(log_id)
           from   user_scheduler_job_log
           where  job_name like 'T_%'
           and    job_subname is null
           and    additional_info like 'REASON%'
         )
order by log_date desc;

Sorry for the length of the post.