Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Blogs » Chris Foot Blog » Chris Foot's Oracle10g Blog » 2007 Daylight Saving Time Changes
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 1974
 

2007 Daylight Saving Time Changes 2007 Daylight Saving Time Changes

We are breaking in to our regularly scheduled blog with the following emergency message: “Are you ready for the impact that the 2007 Daylight Saving Time Changes will have on your Oracle Ecosystems?” I think that after reading this blog, you’ll agree that much work needs to be done to ensure that our systems are able to handle the new Daylight Saving Time dates for 2007.


The Daylight Saving Time Changes for 2007
In previous years, daylight time began in the US on the first Sunday in April and ended on the last Sunday in October. The clocks were set ahead one hour at 2:00 AM to begin local daylight time. On the last Sunday on October, the process was reversed. The clocks were set back one hour at 2:00 AM.

To improve energy conservation, the Energy Policy Act of 2005, Pub. L. no. 109-58, 119 Stat 594 (2005) changed the start and end dates of Daylight Time. Starting in 2007, Daylight Time begins on the second Sunday in March and ends on the first Sunday in November. That means that in 2007, Daylight Time begins on March 11 and ends on November 4. In 2008, Daylight Time begins on March 9 and ends on November 2.

Impact on the Oracle Database Ecosystem
Most computer systems have been configured to accept the Daylight Time changes that have been in effect since 1986. My personal desktop computer pops up a box in April and October to let me know that it needs to change the date to adhere to the Daylight Time policy.

If I don't apply the recommended Microsoft fixes, my PC will have the incorrect time for the duration of the new DST 2007 change periods (March 11, 2007 - April 1, 2007 and between October 28, 2007 and November 04, 2007). I'm assuming that if I manually change the time and don't apply the fix, I'll wake up one morning to find that my PC operating system made another change for me using the old Daylight Saving Time dates. One way or another, my PC's date will be off.

You understand how this could have a pretty dramatic effect on virtually every database ecosystem that we support.

Warnings and Recommendations
I am by no means a 2007 DST impact expert. Personally, I find a lot of the documentation provided by the vendors on this subject to be vague and confusing. I'm providing you with links to assist you in your own education.

We are taking the safe route. When in doubt, we are asking the vendor detailed questions to clarify their statements. Our feeling is that this is what we pay support for - to get answers to questions. Many of the vendors responses will tell you to "play it safe" and patch the systems.

Operating Systems and Java Engines
If your organization does not apply the operating system patches to correct the timezone change, the timestamps those systems use will probably be incorrect by one hour for the duration of the new DST 2007 change periods (March 11, 2007 - April 1, 2007 and between October 28, 2007 and November 04, 2007). That means any dates and times that the database pulls from the operating system for SYSDATE and DATE will be incorrect. In addition, the operating system's Java engines are also affected because they use their own embedded time zone information.

Recommendation - Visit your operating system provider's website to determine what patches are required for the operating system and the Java engine.

Oracle Database Impact
Oracle is providing two patches, one for the JVM and one for the time zone files. Each patch has a different set of requirements on when to implement it.

Oracle JVM
Like its operating system counterpart, Oracle's embedded Java engine uses embedded time zone information and is impacted by the new Daylight Time start and stop dates.

Here's a snippet from one of Oracle's white papers:

JVM patch - for all Oracle versions 8.1.7, 9.x, 10.x (database only):
If you have the Oracle JVM installed you are advised to apply the JVM patch. See section "JVM Fixes" below for more details.

The problem is that a lot of shops install the Oracle JVM when they install the database but don't run Java programs in Oracle. I'll provide you with a Metalink Note that will provide you with instructions on how to determine if the Oracle JVM is installed and what to do if it is.

Time Zone Files
Depending on the release, there are Oracle datatypes that will be affected by the Daylight Time changes. Oracle states that the TIMESTAMP and DATE datatypes do not have time zone knowledge. But the 10G TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) and TIMESTAMP WITH TIME ZONE (TSTZ) datatypes and the TZ_OFFSET function take their time zone information from Oracle's time zone files. These datatypes can be used as column types or as PL/SQL types.

The time zone files are installed during the database installation. Oracle states that the version 3 and higher time zone files include the 2007 Daylight Time changes. I'll provide you with a Metalink Note that will show you how to determine what version you have.

Here's the killer, Oracle is stating that if you use the affected datatypes, you will be required to patch the database and all clients that need to access those datatypes. Once again, read the notes I am providing you and make your own decision on what to do.

Oracle Grid Control and Agents
Oracle Grid Control agents must have their times synchronized with the operating system. If not, the agents will not start. Both the O/S and agents must be patched or not-patched in order for Grid Control Agents to work. Oracle has supplied a manual work-around in case of emergency. The repository databases must also be patched. We have asked Oracle to provide us with clarification on the 9i OEM agents. We are patching all 9i OEM and 10G Grid Control repositories.

Oracle E-Business Suite Applications and Application Server
I have included an Oracle Metalink noted that discuss the impact the Daylight Saving Time changes have on Oracle applications and the Oracle Application Server.

Recommendation for all Oracle products - Visit the Metalink website, use the Metalink notes I provide as a starting point.

Third-Party Applications
There are numerous applications that will require a patch in addition to the required operating system patches.

Recommendation- Your organization will need to contact all third-party applications to determine the impact that timezone changes have on your applications.

Oracle Metalink Notes
The following Metalink Notes will provide you with a good starting point to begin your 2007 Daylight Time changes education. Good luck!

Start your education by reading these notes:
Note 359145.1 - Impact and overview of 2007 USA daylight saving changes on the Oracle database.

Note 402742.1 - USA 2007 DST Changes: Frequently Asked Questions for Oracle RDBMS. Probably the most beneficial of all the overview documents.

Note 397281.1 - USA 2007 Daylight Saving Time (DST) Compliance for Oracle Server Technologies Products.

Generic time zone information:
Note 357056.1 - Impact of changes to daylight saving time (DST) rules on the Oracle database.

Note 340512.1 - Timestamps & time zones - Frequently Asked Questions.

Note 396906.1 - Patches to Update the Time Zone Transition Rules.

Note 412160.1 - Updated Time Zones in Oracle Time Zone File patches.

Installing and using the utltzuv2.sql script:
Note 406410.1 - Dealing with existing Database time zone use when updating Oracle TZ-files.

Note 396670.1 - Usage of utltzuv2.sql before updating time zone files in Oracle 9.

Note 396671.1 - Usage of utltzuv2.sql before updating time zone files in Oracle 10.

Note 397013.1 - Advanced use of utltzuv2.sql.

Note 402614.1 - Time Zone Data in the Data Dictionary During a Time Zone File Update.

Note 399832.1 - Workarounds when utltzuv2.sql patches are not available for your patchset.

Applying the time zone file patch:
Note 359145.1 - Impact of 2007 USA daylight saving changes on the Oracle database.

Note 396387.1 - Workarounds when Database time zone patches are not available for your patchset.

Note 396426.1 - Effects on client and middle-tier of applying time zone patches on the Oracle Database.

Note 402316.1 - Database Time Zone Patching Strategy in the Enterprise.

Platform specific information for those platforms with some special patching issues:
Note 406196.1 - VMS: DST 2007: Information for OpenVMS platforms.

JVM issues:
Note 359145.1 - Impact of 2007 USA daylight saving changes on the Oracle database.

Note 397770.1 - How to check if Oracle JVM is installed in the Database.

Note:276554.1 - How to Reload the JVM in 10.1.0.X and 10.2.0.X.

Note:209870.1 - How to Reload the JVM in 9.2.0.X.

Oracle Applications:
Note 403659.1 2007 - Daylight Saving Time Changes For Oracle Applications.

Note:403311.1 -Impact of U.S. 2007 Timezone Changes on Oracle E-Business Suite, Release 11i Environments.

Grid Control Issues:
Note:401834.1 - EM Agent will not start - due to a Daylight Savings change - (Workaround). Explains why it is impacted, how to fix it and the workaround.

Thanks for Reading,

Chris Foot
Oracle Ace



Monday, January 29, 2007  |  Permalink |  Comments (0)
trackback URL:   http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2007-01-27.8134820403/sbtrackback
Chris Foot
Senior Database Architect
Oracle Ace
Bio & Writings
Subscribe to my blog Subscribe to my blog
 
 

Powered by Plone