DBMS_SCHEDULER – Did the time change mess with your database jobs?

It is that time of year again, when the time changes, and you end up having an extra hour for one day, as we move the clock back one hour. In preparation for the time change, I have recreated all the database jobs in the database, with a named timezone, i.e. AMERICA/DENVER, instead of an absolute offset, such as -7:00. I was sure that none of the jobs will be affected by the time change. And I was almost right…none of the jobs were affected, except one. The job that runs daily, every six hours. I kept looking at the settings of my jobs, the timezone was the same, everything was the same except how often the jobs were running.

I decided that an SR with Oracle would clarify things. And it did.
Actually, it turns out, the database was behaving as expected. The job was scheduled to run every 6 hours repeat_interval = ‘FREQ=HOURLY; INTERVAL=6 ‘.
The last run of the job, before DST end, was 21:30:00 -6:00, which is the same as 3:30 UTC.
The next run, after DST, was 02:30:00 -7:00, which is the same as 09:30 UTC

So it is still a 6 hour interval before and after the DST, based on UTC time.

The trick to have the job running at the same time, independent of DST, is to specify the hour explicitly in the repeat_interval, such as FREQ=DAILY;BYHOUR=3,9,15,21;BYMINUTE=30

See example below when creating the job:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘GATHER_STATS’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN DBMS_STATS.GATHER_TABLE_STATS(”oe”,”sales”); END;’,
start_date => ’04-NOV-14 3.00.00AM AMERICA/DENVER’,
repeat_interval => ‘FREQ=DAILY;BYHOUR=3,9,15,21;BYMINUTE=30’,
enabled => TRUE,
comments => ‘Gather table statistics OE schema’);
END;
/

All the other jobs in the database were setup with an explicit hour in the repeat_interval, thus they were not affected by the time change. Go have a look at the jobs you have running in DBA_SCHEDULER_JOBS, and make sure they run at the time they are supposed to, not an hour earlier.