DBMS_SCHEDULER JOB randomly skips run

0
votes

I have to create a scheduler job, which execute chain everyday at 00:01.

In case that job execution is longer than 24H, next run should start immediately after current completion.

I tried to set:

  • freq=Daily interval=1
  • freq=Daily interval=1 BYHOUR=0 BYMINUTE=1
  • freq=Weekly BYDAY=" mon, tue, etc... " BYHOUR=0 BYMINUTE=1

All above failed.

Actually there were executions longer than 24H and next job run was immediate, but also happened that job skipped a day and scheduled next run for next day.

Edit: I noticed that run was skipped when(examples): *With such interval: FREQ=WEEKLY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=0; BYMINUTE=1;BYSECOND=0

  1. During Monday execution, some chain steps were skipped(in Monday), job completed execution in tuesday and set next run on wednsday.

  2. Chain step(only one, there are more) was paused. During Monday execution I unpaused the step, job completed execution in tuesday and set next run on wednsday.

2
oraclejobschaindbms-scheduler
Did you set attribute schedule_limit? Check also view ALL_SCHEDULER_JOB_LOG – Wernfried Domscheit
I did not set limit. I checked logs, there was nothing interesting - logging level full – MPAW

2 Answers

1
votes

Taking an example

  • Monday start 00:01, next scheduled for Tue 00:01. Finishes 23:07
  • Tuesday starts 00:01, next scheduled for Wed 00:01, Finish Wed 00:05
  • Wednesday starts at 00:05 (delayed, because Tuesday was still running)

It is the Wednesday start you need to pay attention to, as that is when it will determine the next start time. If it is set to 'daily interval=1' it won't run for at least another day. Go with an hourly interval but with a byhour/byminute filter so it doesn't run every hour.

0
votes

Have you tried enabling the job?

BEGIN
  DBMS_SCHEDULER.enable(name=>'"schema"."job_name"');
END;