DBMS_SCHEDULER and DST - jobs running one hour off expected time

There's an awful lot of stuff written out there about this topic, so this will be relatively brief. However, none of the posts I'll be linking to answered my question or solved my problem, that's why I'm putting this here.

DST ended in the UK on Sunday 26th October with the clocks going back one hour.  From that point on, my audit log purge job, which runs daily through DBMS_SCHEDULER has been running an hour early!  I wouldn't normally have noticed as it runs in a quiet time but I happened to be looking at some other logs and spotted it wasn't right.

The Oracle DBMS_SCHEDULER docs seem to have the information to sort this out:
Repeating jobs with frequencies smaller than daily follow their frequencies exactly across daylight savings adjustments. For example, suppose that a job is scheduled to repeat every 3 hours, the clock is moved forward from 1:00 a.m. to 2:00 a.m., and the last time the job ran was midnight. Its next scheduled time will be 4:00 a.m. Thus, the 3 hour period between subsequent job runs is retained. The same applies when the clock is moved back. This behavior is not the case for repeating jobs that have frequencies of daily or larger. For example, if a repeating job is supposed to be executed on a daily basis at midnight, it will continue to run at midnight if the clock is moved forward or backward. When the execution time of such a daily (or larger frequency) job happens to fall inside a window where the clock is moved forward, the job executes at the end of the window.
AND:
The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.
A quick reading suggests that a job that is defined as less than daily will follow it's interval, rather than the time of day, and if the job start time was defined without a named offset, it will not work as expected.

Great!  2 simple things to check.

This is the relevant section from the job definition:
sys.dbms_scheduler.set_attribute( name => '"SYS"."DB_DAILY_AUDIT_PURGE"', attribute => 'repeat_interval', value => 'FREQ=HOURLY;INTERVAL=24'); sys.dbms_scheduler.set_attribute( name => '"SYS"."DB_DAILY_AUDIT_PURGE"', attribute => 'start_date', value => to_timestamp_tz('2014-06-13 00:00:00 Europe/London', 'YYYY-MM-DD HH24:MI:SS TZR'));
Ah. Neither of those two things appear to be true.  The job interval is configured to run daily (24hrs), and the start date has a named timezone.  Both of these are as the Oracle docs suggest should work, right?

Every link I read suggested that it should be this simple!  Check the job start time has a named timezone and the interval is not less than daily.  The three below are a fairly random sample:
http://deryaoktay.wordpress.com/2010/01/21/oracle-job-scheduler-runs-my-jobs-one-hour-earlier/
http://dbaforums.org/oracle/index.php?showtopic=21713
http://www.dbspecialists.com/blog/uncategorized/more-on-dst-and-oracle-scheduled-jobs/
Then, overnight, it hit me.

The frequency is hourly.

Look at that first excerpt from the Oracle docs.  If the frequency is less than daily, the time won't be used to schedule the job, the interval will.  As I chose a frequency of hours (not days, even though the interval was equal to one day), the job runs 24hrs after the last one.  The last job ran at 1730 (+ 60)/1630 (+0), so the next job will run 24 chronological hours later, at 1630(+0).

Blindingly obvious really.  But when you're used to using '24hrs' and 'daily' as equivalent it's not an easy spot.

Comments

Popular posts from this blog

Data Guard with Transparent Application Failover (TAF)

RMAN-05531 During RMAN Duplicate from Active Data Guard Standby

Data pump - "ORA-39786: Number of columns does not match between export and import databases"