MySQL Technical Track

Have Your Scheduler Jobs Changed Run Times Since DST ??

2 min read

Nov 10, 2010 12:00:00 AM

Have your scheduler jobs changed run times since the daylight savings time change? The issue hit me this week. On one client the scheduler jobs had changed run time. The strange thing is the scheduled time was 00:05 [code] SQL> select repeat_interval, next_run_date from dba_scheduler_jobs where job_name = 'myjob'; REPEAT_INTERVAL NEXTY_RUN_DATE ---------------------------------- --------------------------------- FREQ=DAILY; BYHOUR=00; BYMINUTE=05 10-11-10 00:05:00.900000000 -04:00 [/code] But it has started running at 23:05: [code] SQL> select log_id, log_date from DBA_SCHEDULER_JOB_LOG where job_name = 'DWMAINLOAD' order by log_date desc ; LOG_ID LOG_DATE ----- ---------------------------------- 13363 10-11-08 23:53:18.346555000 -05:00 13289 10-11-07 23:47:49.976860000 -05:00 13164 10-11-07 00:52:40.331728000 -04:00 13042 10-11-06 00:54:01.231236000 -04:00 12968 10-11-05 00:52:45.072446000 -04:00 12889 10-11-04 00:52:17.909098000 -04:00 12809 10-11-03 00:52:18.978296000 -04:00 12729 10-11-02 00:52:48.055781000 -04:00 [/code] As this hit just after the DST, at first I thought that I might be hitting a DST bug. I checked and both the OS time and oracle sysdate were correct and consistent with each other : [code] SQL> select to_char(sysdate, 'yyyy/mm/dd hh24:mi') from dual; TO_CHAR(SYSDATE, ---------------- 2010/11/09 08:09 SQL> !date Tue Nov 9 08:10:02 EST 2010 [/code] A co-worker suggested I check the timezone settings for the database. It was close to the problem and got me part-way to the solution, but there was no timezone setting at the database level. [code] SQL> show parameter tz NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ nls_time_tz_format string nls_timestamp_tz_format string SQL> show parameter time NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 28 cursor_space_for_time boolean FALSE ddl_lock_timeout integer 0 distributed_lock_timeout integer 60 log_checkpoint_timeout integer 1800 nls_time_format string nls_time_tz_format string nls_timestamp_format string nls_timestamp_tz_format string resumable_timeout integer 0 timed_os_statistics integer 0 timed_statistics boolean TRUE [/code] I checked and found the DBMS_SCHEDULER had NLS settings for each job. [code] NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM'<bold><font color=red> NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR'</bold></font> NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' [/code] However, these are just formatting settings. I was getting warmer but still not there. Finally, I found it! If the global attribute default_timezone is not set, the scheduler assumes timezone based on the start_date of the job. That was exactly my problem. So now I want to set the timezone using: [code] BEGIN DBMS_SCHEDULER.set_scheduler_attribute ( attribute => 'default_timezone', value => 'US/Eastern'); END; / [/code] But where do I get the value for the timezone? I found it is in the V$TIMEZONE_NAMES view. BE CAREFUL ! I found that some values existed in multiple timezones, so I made sure I found one that ONLY existed in the timezone I wanted. [code] SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES where tzname = 'America/Chicago'; America/Chicago LMT America/Chicago CST America/Chicago CWT America/Chicago CDT America/Chicago EST [/code]

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Have Your Scheduler Jobs Changed Run Times Since DST ??

Share this

Share this

More resources

AQ Notifications in Oracle 11gR1

Oracle Database: script to purge aud$ table using dbms_audit_mgmt package

Oracle Scheduler Job is Not Running as Expected After Bouncing Databases

Ready to unlock value from your data?