Thursday, March 08, 2012

DB TimeZone-itis - what it is, and what to do about it ...

It's that time of the year again: snow mold allergies, hay fever, and DB TimeZone-itis are about to hit us ...

This weekend, a number of countries and regions will perform a semi-annual ritual of changing their clock either ahead or back one hour.  Aside from irritating the cows and shifting costs of lighting from business to consumer >D , this also has an impact on unpatched Oracle9i and Oracle 10g database applications. This is called TimeZone-itis, and occurs twice a year.

Before 2007, many places shifted on first Sunday in April and last Sunday in October.  (I use 'shifted', because south of the equator, where fall is approaching, the shift is opposite to the that in the north..)  In 2005, President Bush enacted a law that moved this shift date 3 weeks earlier in March and 1 week later into November: this law came into effect in 2007 and many other jurisdictions around the world have since followed suit.
The symptoms: unexpected crashes, errors that did not occur before the time shift, applications suddenly stop working, applications (such as dbconsole) not starting.

The root cause: Oracle databases up to, and including, were built using the 'old' time zone shift.  Publicly available downloads of Oracle Database 9i,10gR1 and 10gR2 were released before 2007 and most have not been updated.

Who can this affect?: any application that stores data in "Timestamp with TimeZone" data type in an unpatched Oracle9i or Oracle10g (incl 10gR2) database, that compares the timestamp information against the operating system, or that uses the OJVM, might experience some symptoms. 

One particularly nasty one is the Oracle Enterprise Manager - database console and grid control are affected.

Why am I raising the alarm?: I teach a lot of Oracle University courses, and therefore see a lot of students who have downloaded a copy of the Oracle software.  Many students and developers will, by default, reach for the Oracle 10g database, and may experience trouble using the Enterprise Manager console.  This is often getting close to 'spring break' and 'year end' when students and developers want to cram.

Solutions:  Four practical solutions at the top of my personal list:
  1. Use Oracle Database 11gR2, as it is the one currently supported and is pretty much backward compatible to 10g;
  2. Get the appropriate patches from Oracle Support;
  3. Switch operating system anmd DB to GMT/UTC;
  4. Take 2 Aspirin, go to bed, and try again in 3 weeks.
For more details how this affects the database, look at

"Impact of changes to daylight saving time (DST) rules on the Oracle database [ID 357056.1]"
"USA 2007 DST Changes: Frequently Asked Questions for Oracle Database Patches [ID 402742.1]"
and a 'master note' covering a lot of product is

"Updated DST transitions and new Time Zones in Oracle Time Zone File patches [ID 412160.1]"


For Application Servers, WebLogic Server, and other Java environments, search out TZUpdater tool at







No comments: