Have Your Scheduler Jobs Changed Run Times Since DST ??

Nov 10, 2010 / By Babette Turner-Underwood

Tags: , ,

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

   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

But it has started running at 23:05:

   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

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 :

   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

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.

     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

I checked and found the DBMS_SCHEDULER had NLS settings for each job.

      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'

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:

     BEGIN
       DBMS_SCHEDULER.set_scheduler_attribute (
         attribute => 'default_timezone',
         value     => 'US/Eastern');
     END;
     /

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.

   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

One Response to “Have Your Scheduler Jobs Changed Run Times Since DST ??”

  • Babette Turner-Underwood says:

    The dangers of using OLD queries!!!

    I was using Benthic Golden for a long time and the client I had was not able to handle timezone datatype. So I had created a script that would query the DBA_SCHEDULER_JOBS table using “to_char” on the start_date. BUT, I did not use the TZ in the date format.

    When doing my analysis of the problem, I was using my old scripts to query the DBA_SCHEDULER_JOBS table. I completely missed the timezone component in the start date field.

    So the timezone component from the start date was being applied and making changes to the global parameter will not help in this instance.

    The actually changes required ( when timezone stored in the start date) is :

    exec DBMS_SCHEDULER.SET_ATTRIBUTE (‘MyJob’,
    ‘start_date’, TO_TIMESTAMP_TZ(‘2010-11-10 00:00:00 -05:00′,
    ‘YYYY-MM-DD HH24:MI:SS TZH:TZM’));

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>