DST and my Oracle environment

How does the new Daylight Savings Time change effect my Oracle database systems? Not sure why I didn’t ask that question a little sooner, but fortunately I still time to research it and address it well before March 11. There’s an overwhelming amount of information on Metalink.

Start Here
402742.1– USA 2007 DST Changes: Frequently Asked Questions for Oracle RDBMS

Other Important Notes
359145.1 – Impact of 2007 USA daylight saving changes on the Oracle database
357056.1 – Impact of changes to daylight saving time (DST) rules on the Oracle database
396670.1– Usage of utltzuv2.sql before updating time zone files in Oracle 9
396671.1– Usage of utltzuv2.sql before updating time zone files in Oracle 10
396387.1– Workarounds when Database time zone patches are not available for your patchset

There are potentially three areas to be patched, the Oracle database, the Oracle JVM, and the Oracle Client.

Oracle Database
There is a required pre-install script called utltzuv2.sql that needs to be executed to determine what data is affected. The data needs to be saved off before the DST patch is applied. There is a utltzuv2.sql script for Oracle 9.2.0.x (Patch 5548107) and Oracle (Patch 5601428). See Metalink Note 359145.1 for more information.

Regardless whether any data is affected, it’s probably a good idea to apply the patch. Recent threads on the Oracle-L seem to say you can apply the patch (using opatch) while the database is running and there is no need to restart the database.  I have found this to be true (no database restart) on my Solaris and HP-UX servers, but that does not appear to be the case for my Windows servers.

For my databases on Solaris and HP-UX PA-RISC, I am applying the version 4 timezone files patch (Patch 5632264).

Oracle JVM
Metalink Note 397770.1explains how to determine if the Oracle JVM is installed in your database. If the Oracle JVM is not installed or you have the most current patchsets (e.g.,, installed, then no patching is necessary. This information is detailed in Metalink Note 359145.1.

If you have an database with the Oracle JVM installed, then Patch 5075503 can be applied.

Oracle Client
Metalink Note 396426.1 explains the effects on the client software of the timezone patches.

OTN Techblog
Chris Foot Blog – 2007 Daylight Saving Time Changes
Chris Foot Blog – 2007 Daylight Saving Time Changes Update
Oracle-L Archive – Oracle and DST changes
Daylight Saving Time 2007 Forum on Metalink Now Open
OTN – Oracle Database Daylight Saving Time Update Guide
Chris Foot Blog – DST Deadline – One Week and Counting

2007 Daylight Savings Time E-Business Customer Webcast


5 Responses to DST and my Oracle environment

  1. susan says:

    From what I understand, only datatype with TSTZ, TSLTZ and packages/functions calling TZ_OFFSET are affected. And whoever wrote their apps to store their permanent data with transitory variables must think twice before asking DBA to apply patches for them.

  2. Phil says:

    What do you think will happen during the new DST, if all my databases are in Europe but I have clients connecting to those database from America? The new DST doesn’t change a thing for the databases in Europe, and most of the applications that use the databases are on Application servers also located in Europe (people use Citrix to connect to the app or the use a Web Server).

    If the American clients’ time changes for DST, will that effect anything when they connect to the app server/web app. / or database via SQL*PLUS?

  3. mrothouse says:

    Presuming you have checked that your database does use TSTZ and TSLTZ data types, Metalink Note 412971.1 has a paragraph that states “Regardless of the results of this script, you are always advised to apply new time zone files if available, even if there is no strict need to do that at this moment. It is always better to be using the up-to-date time zone information, in case this functionality is used in the future.”

    The DST change may not impact you immediately, but perhaps it is safer to apply it now than to be sorry later.

  4. […] just tackling the problem now, you’re up the proverbial creek (Mike R. has a good list of Metalink resources to get you started). But for those who have already completed the task, how did it go?  Is it […]

  5. […] go wrong, and we’re none of us perfect, so we are lucky to have bloggers like Mike Rothouse sharing what he knows on the matter, on Mike R.’s […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: