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.
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:
- Use Oracle Database 11gR2, as it is the one currently supported and is pretty much backward compatible to 10g;
- Get the appropriate patches from Oracle Support;
- Switch operating system anmd DB to GMT/UTC;
- Take 2 Aspirin, go to bed, and try again in 3 weeks.
"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]"
"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 http://www.oracle.com/technetwork/java/javase/tzupdater-readme-136440.html