Do I need a patch?
Because timezone data is embedded not only in Database tables but also in arguments to PL/SQL functions and Java code, there is no way for Oracle to definitively tell any customer that they do NOT need to patch. The only way to eliminate risk is to patch all your systems. We have provided a great deal of information in these documents which help you establish which of your systems need to be patched as a matter of priority. Based on the information provided, your analysis may lead you to believe that the impact on a system is low. It is ultimately your business decision to patch or not based on your analysis. Oracle recommends that you patch all your systems.
Are any OS patches needed for the database?
Yes, depending on the time zone in use on the OS. As documented in Note 357056.1 and other notes, the SYSDATE function (and similar functions) relies on the OS to provide the time. If the OS time is not correct, then SYSDATE will also not provide the correct time. Therefore if your OS is using US or Canadian time zones, it is important that the OS is patched so that it ‘knows’ to change the clock on the correct date.
Is Oracle7 affected?
There are no TIMESTAMP datatypes or Oracle JVM is Oracle7. The Oracle7 database (or Oracle7 client software) knows nothing about DST and is therefore not affected. Just like for any other versions, the OS information needs to be up to date of course. This is so that functions that rely on the OS (like SYSDATE), can continue to work correctly.
Is Oracle8 affected?
In Oracle 8.0 and 8.1 (also called Oracle8i) there are no TIMESTAMP datatypes, which means that there are no datatypes with built-in knowledge of time zones. Of course the OS time needs to be correct for the SYSDATE function to work, so the correct DST patches need to be installed on the OS, but SYSDATE and the DATE datatype do not know about time zones itself. Therefore there is no Oracle time zone file patch to be applied on Oracle 8.0 or Oracle 8.1.
The Oracle JVM was introduced in Oracle 8.1 (8i), and a patch exists for version 8.1.7.4 (see table 3 in Note 359145.1).
Is an Oracle8 client affected when connected to a Oracle9 or Oracle10 database?
No, On a connection between a Oracle8 client and a higher level database, the DST aware functionality can not be used because of client-side limitations. Therefore none of the normal functionality that can be used on this connection will be affected by these issues, and no patch can or should be applied on the Oracle8 clients.
Is the TIMESTAMP datatype affected? Does TIMESTAMP use a default Time Zone?
No. The only datatypes with time zone knowledge are the TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE types, but despite the similar names these are distinct from the TIMESTAMP datatype.
The TIMESTAMP datatype works, just like the DATE datatype, without any time zone knowledge. There is no such thing as a default time zone that it might get converted to. Also the TIMESTAMP is not automatically given the session- or database time zone. A TIMESTAMP is purely an extended form of a DATE datatype (with fractional seconds). It is therefore, just like the DATE datatype, completely unaffected by this issue.
This section contains questions regarding the updated time zone files, and the scripts that help when these are applied.
Is the time zone file patch applied correctly to the database?
You can test whether the patch is applied correctly by running the following query from a sqlplus session. Run this from the same home as the database:
SQL> select case to_number(to_char(to_timestamp_tz
2 ('20070311 00:00:00 US/EASTERN','YYYYMMDD HH24:MI:SS TZR')
3 + to_dsinterval('0 08:00:00'),'HH24'))
4 when 8 then 'The patch has not been applied'
5 when 9 then 'The patch has been applied correctly'
6 else 'Error' end "TZTEST (RUN FROM DB HOME)"
7 from dual;
TZTEST (RUN FROM DB HOME)
------------------------------------
The patch has been applied correctly
Is the time zone file patch applied correctly to the client?
You can test whether the client and server are in synch with each other using the following query. Make sure you connect to a database on which you have first successfully run the previous test (question 201) on the server itself, then run this test from a client you want to test:
alter session set nls_timestamp_tz_format='HH24';
col T1 format a2
col T2 format a2
select to_timestamp_tz('20070311 06:00:00 US/EASTERN','YYYYMMDD HH24:MI:SS TZR') T1,
to_timestamp_tz('20070311 06:00:00 US/EASTERN','YYYYMMDD HH24:MI:SS TZR')
at time zone 'GMT' T2
from dual;
The result of this query are 2 numbers, which indicate whether the patch has been applied correctly to the client and server in the following combination:
T1 T2 Result 06 10 The patch is applied correctly on both the client and the server 06 11 The patch is not applied on the client, and also not on the server 05 10 The patch is not applied on the client, but is applied on the server 07 11 The patch is applied correctly on the client, but not on the server
Any other combinations should not occur, contact Oracle support if they do
Do the one-off patches need to be reapplied after a patchset is installed?
No, For the time zone patches the version-3 files are part of the 10.1.0.6 and 10.2.0.3 patchsets, and no other patchsets (including Oracle9 patchsets) contain any time zone files. So if you for example installed the version-3 files on a 9.2.0.7 database, and you apply the 9.2.0.8 patchset, then this will not affect these files in any way.
For the OJVM patch the situation is roughly the same, this fix is part of the 9.2.0.8, 10.1.0.6 and 10.2.0.3 patchsets, and no lower patchsets contain OJVM updates that would overwrite the OJVM patch when it’s installed as a one-off. So a 9.2.0.6 installation with OJVM patch will not be required to reinstall this fix when 9.2.0.7 is installed (and of course 9.2.0.8 delivers the same patch as was already applied, so again no action would be needed there either).
Which version of the time zone files is currently in place?
In Oracle10 you can do a simple query to see the current version of the time zone files:
SQL> SELECT version FROM v$timezone_file; VERSION ---------- 3
In Oracle9 this is view is not available and we have to base the query on the amount of available time zones.
We can use the following in all versions of the database:
SQL> select case COUNT(DISTINCT(tzname)) 2 when 183 then 1 3 when 355 then 1 4 when 347 then 1 5 when 377 then 2 6 when 186 then case COUNT(tzname) when 636 then 2 when 626 then 3 else 0 end 7 when 185 then 3 8 when 386 then 3 9 when 387 then case COUNT(tzname) when 1438 then 3 else 0 end 10 when 391 then case COUNT(tzname) when 1457 then 4 else 0 end 11 when 392 then case COUNT(tzname) when 1458 then 4 else 0 end 12 when 188 then case COUNT(tzname) when 637 then 4 else 0 end 13 when 189 then case COUNT(tzname) when 638 then 4 else 0 end 14 else 0 end VERSION 15 from v$timezone_names; VERSION ---------- 4
References:
Note:402742.1 USA 2007 DST Changes: Frequently Asked Questions for Oracle Database Patches
Note:359145.1 Impact of 2007 USA daylight saving changes on the Oracle database
