Knowledge increases exponentially. Today, you probably own more books than great universities of times past—Cambridge University owned less than two hundred books in the fifteenth century. First came the invention of writing, then alphabets, then paper, then the printing press, then mechanization. Each step caused an exponential increase in the collective human knowledge. In our generation, Al Gore invented the internet and the last barriers to the spread of knowledge have been broken. Today, everybody has the ability to contribute, communicate, and collaborate. We are all caught up in a tsunami, an avalanche, a conflagration, a veritable explosion of knowledge for the betterment of humankind. This is the blog of the good folks at Database Specialists, a brave band of Oracle database administrators from the great state of California. We bid you greeting, traveler. We hope you find something of value on these pages and we wish you good fortune in your journey.

More on DST and Oracle Scheduled Jobs

Ian Jones wrote on March 11 about the often-overlooked issue of Daylight Saving Time (DST) and its impact on scheduled jobs.  We had another client with a related glitch but this time with the beloved DBMS_SCHEDULER facility.  Their jobs were suddenly running an hour behind schedule even though the server time was right and the sysdate value had the right time as well.  The time zone used by the scheduler appeared correct given its East Coast location:

 

SQL> alter session set nls_date_format=’dd-MON-yy hh24:mi:ss’;

 

Session altered.

 

SQL> select sysdate from dual;

 

SYSDATE

——————-

27-MAR-12 09:35:06

 

SQL> select dbms_scheduler.stime from dual;

 

STIME

—————————————————————————

27-MAR-12 08.35.10.083878000 AM US/EASTERN

 

But this query doesn’t quite give us the information we need because the DBMS_SCHEDULER employs a special data type, TIMESTAMP WITH TIME ZONE, which has embedded DST information in it.  So if we alter the display format for that data type, we get:

 

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT =’DD/MM/YYYY HH24:MI:SS TZR TZD’;

 

Session altered.

 

SQL> select dbms_scheduler.stime from dual;

 

STIME

—————————————————————————

27/03/2012 08:35:10 US/EASTERN EST

 

Now it is clear to see that the scheduler thinks we’re still in Eastern Standard Time, not Eastern Daylight Time.  But why?  The answer lies in the DST changes made in 2007 where the move to Daylight time in the spring took effect on the second Sunday in March.  In had previously taken effect on the first Sunday in April.  Given that this was March 27 it fell into that rule-change period.  This database was running 10.2.0.1 software which sports the Version 2 time zone files.  In 10g and above you can run a query to see the version of your time zone files.

 

SQL>  SELECT version FROM v$timezone_file;

 

VERSION

————————–

      2

 

The 2007 change wasn’t enforced at that point.  It wasn’t until Version 3 of the time zone files that we saw the 2007 changes reflected.  So the client was looking at the possibility of patching their software to get the right time zone files.  A time zone files update is relatively risk-free but for 10.2.0.1 it involves obtaining the 10.2.0.2 (or better) patch and extracting the necessary files - not a quick fix.  Fortunately there was a quick fix available.  By clearing the default time zone attribute assigned to the scheduler, the time zone enforced by the systimestamp value would be used which was correct in this case.  sysdate and systimestamp come from system calls to the OS and use the host server time.

 

SQL> exec dbms_scheduler.set_scheduler_attribute(’default_timezone’,NULL);

 

PL/SQL procedure successfully completed.

 

SQL> select dbms_scheduler.stime from dual;

 

STIME

—————————————————————————

27/03/2012 10:16:24 -04:00

 

SQL> select systimestamp from dual;

 

SYSTIMESTAMP

—————————————————————————

27/03/2012 10:16:36 -04:00

 

Bingo! The scheduler time is now correct. The best long-term solution would be to patch up the database to 10.2.0.5 or something but this would at least get them by for now.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>