Oracle Database Daylight Saving Time Update Guide

Published February 2007

New update: Sun notified us on Friday, March 9 via Sun Alert 102836 that there is an error in their DST patch for one of the previously released versions of the JDK. This error may affect Oracle Database customers on version 10.1 of the database (no other versions are impacted), who have installed the DST Oracle JVM patch; and all Application Server JDK users. In both situations, you are only impacted if you use Hawaiian, Mountain or Eastern time zones using the three letter acronym for the time zone, such as HST, MST, or EST. The likely impact is that any dates may be out by 1 hour for the period between the new start of DST and the previous start of DST.

If you think this is likely to cause a problem, you can easily resolve it. Database users should check note 418617.1 on metalink.oracle.com, while Application Server JDK users should check note 418702.1 on metalink.oracle.com, for instructions as how to resolve this problem.

On March 11th, 2007, the new daylight saving time rules in the United States and Canada begin. These new rules may require that you patch your Oracle8i, Oracle9i, Oracle Database 10g databases and Oracle clients because of the changes to these rules. Earlier Oracle releases do not require daylight saving time patches.

Oracle recommends that all customers update to the current daylight saving time information by applying the patches Oracle provides. The purpose of this document is to help you decide if you need to patch your environment before March 11th.

If your Oracle database is not properly patched, the TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) and TIMESTAMP WITH TIME ZONE (TSTZ) data types, the TZ_OFFSET function, and various built-in Java classes in the Oracle JVM, can be incorrect by one hour during the time between the old and new daylight saving time start dates and end dates. This may or may not cause a problem to your systems. If it does, you will need to apply the relevant patches before the change in daylight saving time. If it does not, you should still patch your environment, but you may do so at your next scheduled maintenance window.

• When Do I Need to Patch My Environment?
• Where Do I Get the Patches?
• How Do I Patch My Environment?

1 When Do I Need to Patch My Environment?

If you do not use time zone data, use the updated time zones, or do not care that the time zone data you use (either directly or through capabilities such as the scheduler) may be incorrect by an hour, you can probably choose to apply the daylight saving time patches at your next scheduled maintenance window. If you do use time stamp data, and cannot afford to have this data off by one hour, then you will need to patch the relevant systems before the change in daylight saving time. OracleMetaLink Note 359145.1 provides information to help you determine which systems need to be patched as a matter of priority.

If you need to patch now, then there are a few decisions that you must make before patching your environment.

Oracle JVM

Oracle database releases 8.1.7, 9.0.1, 9.2, 10.1 and 10.2 have a JVM installed, which may have time zone data configured.

If Oracle JVM is installed and configured with time zone data now, then you need to install the latest Oracle JVM patch. OracleMetaLink Note 416860.1 contains information to help you determine if it is configured. If Oracle JVM is not installed and configured, Oracle recommends that you do not install the Oracle JVM patch at this time.

Oracle Database Time Zone Files

Oracle database releases 9.0.1, 9.2, 10.1 and 10.2 may use time zone data files. If time zone files are used, then you should install the latest version available before March 11th. Refer to OracleMetaLink Note 359145.1 to help you determine time zone file usage.

All customers starting to patch now should use the latest (version 4) time zone files referenced in OracleMetaLink Note 359145.1 unless you have a strong reason to apply the earlier version (version 3). OracleMetaLink Note 412160.1 provides a list of updated time zones.

Generally, if you are starting now, the only reason you would apply the version 3 patches is because the version 3 patch is required as part of a certified solution or required by regulations or compliance with your corporate standards, or because the version 4 patch is not yet available for your operating system and release.

---

Important: All your databases and clients must use the same time zone data files. That is, all databases and client should use version 4 or all should use version 3. Do not mix them, otherwise you risk corrupting your time stamp data by having it modified by different rules in different parts of your network.

---

2 Where Do I Get the Patches?

Oracle patches and OracleMetaLink documents are available on OracleMetaLink. To access OracleMetaLink, you need to provide the support identifier you received when you purchased your Oracle product.

To download Oracle patches and patch sets:

1. Log on to OracleMetaLink. http://metalink.oracle.com
2. Select Patch Number from the Quick Find search menu at the top of the OracleMetaLink page. Enter the patch number in the field and click Go.

3 How Do I Patch My Environment?

Complete the following steps to patch your environment:

• Step 1: Determine Which Clients To Patch
• Step 2: Patch Oracle JVM
• Step 3: Download the Time Zone Patches
• Step 4: Shut Down the Database
• Step 5: Backup any Time Stamp Data
• Step 6: Apply the Time Zone Patch to the Database
• Step 7: Restart the Database
• Step 8: Restore TSTZ Data
• Step 9: Manually Correct TZLST Data
• Step 10: Start Patching the Clients with the New Time Zone Files
• Step 11: Restart the Database

Step 1: Determine Which Clients To Patch

Oracle clients release 8.1.7 and lower do not require the time zone files. Oracle clients release 9.x and 10.x need the new time zone files if the database uses the new time zone files. If the Oracle clients are not using time zone data, then the clients may be patched at a later time. Otherwise, the clients must be updated at the same time as the database.

Refer to OracleMetaLink Notes 396426.1 and 402316.1 for more information about patching Oracle clients and middle-tier components.

Step 2: Patch Oracle JVM

Oracle recommends that you apply the Oracle JVM patch if you have configured Oracle JVM in your database. This patch is not needed for Oracle clients or middle-tier components.

---

Important: Refer to OracleMetaLink Notes 359145.1, 397770.1, and 414742.1 for information about checking for Oracle JVM installations, determining whether or not a patch is needed, and which patch is needed for your Oracle release.

---

Download and apply patch 5865568. This patch is available for releases 8.1.7.4, 9.2.0.4, 9.2.0.5, 9.2.0.6, 9.2.0.7, 9.2.0.8, 10.1.0.3, 10.1.0.4, 10.1.0.5, 10.2.0.2, and 10.2.0.3. If a patch is not available for your current release, upgrade to the next supported release and then apply the 5865568 patch.

Step 3: Download the Time Zone Patches

Download the correct version of the utltzuv2.sql script. Also download the time zone file patch for your release and operating system but do not install until step 6. Refer to the matrix in OracleMetaLink Note 359145.1 and download the correct patches.

Step 4: Shut Down the Database

Shut down the database and restart in migrate mode. Complete the remaining steps.

Step 5: Backup any Time Stamp Data

Applying new time zone files in a database might affect already existing time stamp data in the database. Install and run the correct version of the utltzuv2.sql script.

Typically, this script should complete in less than 15 minutes. The time frame depends on the amount of time stamp data that is stored.

---

Important: Do not install the time zone patch at this time. Run the utltzuv2.sql, interpret the results, and patch as directed in the OracleMetaLink Notes.

---

Refer to OracleMetaLink Notes 359145.1, 396670.1, and 396671.1 for more information about running the script, interpreting the results, and backing up and restoring the TSTZ data.

Step 6: Apply the Time Zone Patch to the Database

After interpreting the results of the utltzuv2.sql script, patch the database as directed in the OracleMetaLink Notes. Follow the instructions in the patch README.

Step 7: Restart the Database

Restart the database in migrate mode.

Step 8: Restore TSTZ Data

If you saved TSTZ data as mentioned in "Step 5: Backup any Time Stamp Data", then you need to restore this data to the TSTZ columns from where they originated. Refer to the OracleMetaLink Notes in step 5.

Step 9: Manually Correct TZLST Data

Refer to OracleMetaLink Note 406410.1.

Step 10: Start Patching the Clients with the New Time Zone Files

If the client or middle-tier requires a patch, then apply the same patch as used in the database. There is no need to run the utltzuv2.sql script on the clients. The clients simply need to have the same version of the time zone files as the database to which they connect (when data in the affected time frame is used).

Time zone patches can be applied to a client or middle-tier in the same way as a database:

1. If the client or middle-tier software is of a patch set level for which the correct time zone patch exists, then install this in the usual way using OPatch.
2. If the time zone patch you need does not exist for the client (for example, if you've never installed a patch set on the client software), then install the time zone files manually using the method described in OracleMetaLink Note 396387.1.

Step 11: Restart the Database

Restart the database after completing the previous steps.

---

Important: Do not allow unpatched clients to connect to the database. Doing so may corrupt your time stamp data by having it modified by different rules in different parts of your network.

---