Oracle DST patches important for daylight savings
All Oracle shops must be concerned about the possibility of
logical data corruption that may result from
failing to apply the DST patches. Also see these
Oracle notes on the DST patch.
According to Oracle MOSC, there are some important patches
that need to be applied to accommodate the USA changes in Daylight
Savings Time (DST) before Match 13, 2007.
has further notes on this important patch:
This can impact your systems several different ways:
UNIX, Linux, and Mac OS use the
zoneinfo utility which allows a
single time zone to have multiple daylight saving
time rules to handle changes from year to year. The
zoneinfo database is a collaborative
compilation of information about the world's time
zones. New editions of the database are published as
changes warrant, with the latest edition being the
2006g edition (2006-05-08) which lists 387 primary
time zones and contains the new time changes. This
can be obtained from your O/S vendor.
This issue will affect Oracle Server - Enterprise
Edition – Versions 8.1 to 10.2. Depending on the
version of your database, the impact will differ.
short, a database patch and a JVM may need to be
applied. Once applied, a script provided by Oracle
called utltzuv2.sql will need to be run. This will
tell you if any code in the db needs to be modified.
OTN group also has these details for DST patches on Solaris:
I applied patches to 10gr2 on solaris 8, 10.
1. There is a patch that got two files: utltzuv2.sql and
2. Using opatch, apply the above patch while the db is on.
3. Then replace java stored procedure for file separator with
dynamic pl/sql, if you do not have OJVM installed.
4. You must add 4 entries to timezdif.csv
The points 3 and 4 are explained in some note.
5. run @?/rdbms/admin/utltzuv2
6. It lists affected columns and tables.
7. Not all affected columns contain affected data, that is, the
data that is between March 11 and April 2. So, you have to check
whether these columns contains affected data (call it,
eliminating false positives)
12. If you have affected data, then you must backup that data
with (rowid, varchar2).
13. Then download another patch that contains *.dat files. Apply
this patch, while db is running. It just replaces a couple of
files in $ORACLE_HOME/oracore/zoneinfo/*
14. run the test query in a new session. You will see that new
sessions are reading updated *.dat files. However, the server
will not read it, so you have to bounce the server
15. restore the affected data.
Next, OJVM patch:
1. check whether OJVM is installed or not, using a query given
in a MOSC note
2. the above query tells you whether OJVM is fully installed or
partially installed or not-installed at all.
3. If OJVM is not all installed, don't apply the patch
4. If OJVM is fully or partially installed, apply the relevant
5. for 10gr2, there are 2 scripts (pre-patch script, and post
6. After applying the patch, apply the first script.
7. Bounce the database (because you applied the patch while the
db is up)
8. If OJVM is fully installed, run the post-patch script; if it
is partially installed, don't run postpatch script.
9. for dbs < 10gr2, there are different set of instructions,
Here are the
MOSC notes relating to the DST patches:
2007 DST Changes: Frequently Asked Questions for Oracle
Daylight Saving Time (DST) Compliance for Oracle Server
Daylight Saving Time Changes For Oracle Applications
2007 Daylight Saving Time Changes - Webcasts
the Oracle RDA 4.7 New Feature: Daylight Saving Time Tool