Impact of US Daylight saving changes on Oracle

Well, just for a briefing, since 1966, most of the United States has observed Daylight Saving Time from at 2:00 a.m. on the first Sunday of April to 2:00 a.m. on the last Sunday of October. But in 2007, most of the U.S. will begin Daylight Saving Time at 2:00 a.m. on the second Sunday in March and revert to standard time on the first Sunday in November.

So, Oracle has released patches to adapt these Daylight saving time changes. The databases that are using the following will be impacted…

1. Databases using TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types and TZ_OFFSET function as they take their time zone information from Oracle's time zone files.

2. The Oracle JVM as it uses time zone information from the JVM in the database itself.

So there are two kinds of patches i.e. Time Zone patch and JVM patch. Refer to note 359145.1 on metalink for details.

Time Zone patch: No patch for Oracle 8 is needed as it does not use Timestamp but Oracle 9i and 10g are likely to be impacted. Time Zone patch is available for both client and database side. 10g is most likely to be affected as it uses Timestamp data type in the data dictionary.

JVM patch: If one has the Oracle JVM installed one is advised to apply the JVM patch. The JVM patch needs to be applied only on the database side.

The steps to perform are:

1. Download a new version of the script utltzuv2.sql from metalink which checks your current data for any rows affected by the updates to the time zone files.

2. Run the utltzuv2.sql script to check your data.

3. Save existing time zone data as found by utltzuv2.sql as varchar2.

4. Apply the patch for the Database time zone fixes.

5. Restore this to the timestamp columns.

6. Apply the Database time zone fixes to the clients and middle-tiers that connect to this database, if required.

7. When using JVM apply the patch for the JVM time zone fixes.
The above document is just an overview of the Day light saving changes and the patch apply process. Do refer to Oracle’s documentation before applying a patch.

For any DST FAQ, please refer to 402742.1 on metalink.