Scope and Application
__If after applying the DST patches a “select * from dba_scheduler_jobs;” or other TSLTZ data gives “ORA-01882: timezone region %s not found”, please make sure you have the error using the sqlplus found in the oracle_home on the server (!!! this is important !!!). IF and ONLY IF that is the case run the fix script found below directly on the server.
If you do NOT have a ORA-01882 when using the sqlplus found in the oracle_home on the server but you DO have it when connecting from a remote client then it means the remote client has not been updated to V3 or higher. In that case running the script will NOT solve the issue, you then need to update the client’s DST version.
For windows clients see Note 417893.1 How To Apply The V4 DST Patches To Windows Clients or Servers__
Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade
What has changed?
ORA-01882: timezone region %s not found
Which time zone region IDs have been updated?
check __Note:414590.1 on metalink.
Oracle Server - Enterprise Edition - Version: 184.108.40.206 to 10.2.0.1
Information in this document applies to any platform.
This note explains that the internal region IDs that Oracle uses for 7 time zones were updated in the “version-3” time zone files (patch 4689959, and also in the “version-4” files, patch 5632264). After these patches have been installed any “old” data which uses these 7 time zones will not be recognized by Oracle and result in a ORA-1882 error.
In order to circumvent any problems special actions need to be taken when the v3 or v4 time zone file patches are applied, which are explained in this note. This note will also explain a set of actions to take if these problems are detected after the new time zone files are installed.
These 7 time zones are:
This issue only affects the database time zone definitions from the Oracle “time zone files”. This affects the TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE types (both in stored data and in PL/SQL), and the TZ_OFFSET function. This issue does not affect the time zone definitions in the Oracle JVM in the database in any way.
When Oracle stores TIMESTAMP WITH TIME ZONE data it stores a “region ID” number specific for each time zone with the timestamp data.
The region ID for the 7 mentioned time zones have changed since the “version-3” time zone files (patch 4689959, and also in the “version-4” files, patch 5632264) compared to the original Oracle time zone definitions in Oracle9i and 10g (time zone file definitions v1 and v2 respectively).
If data using these 7 time zones is stored in a database, and the v3 or v4 time zone files are installed, then the original region ID will no longer be recognized, leading to a error:
The 7 time zones for which the region ID has been updated are:
Region ID in v1/v2
(as stored in TSTZ data)
Region ID sioce v3
(as stored in TSTZ data)
Time zones missing from utltzuv2.sql script
Actions to take before applying the new time zone files
Actions if time zone files are already upgraded
The first thing that should be done before anything else is to make sure that these time zones can be found by the utltzuv2.sql script. Because these regions are (at the moment) missing from the script a manual action is needed to add these.
For information on the exact updates to make please see:
Note 414826.1 Missing Updated Time Zones In utltzuv2.sql and timezdif.csv files
When you update time zone files you should always use the utltzuv2.sql script to find TIMESTAMP WITH TIME ZONE (TSTZ) data which is affected by the changes, and deal with this by storing it as VARCHAR2, and re-storing it as TSTZ after the new time zone files are installed. This is a normal action which should be done for TSTZ data as documented in these notes:
Note 396670.1 Usage of utltzuv2.sql before updating time zone files in Oracle 9
Note 396671.1 Usage of utltzuv2.sql before updating time zone files in Oracle 10
If the manual updates to the utltzuv2.sql or timezdif.csv files, as per the above mentioned note Note 414826.1, are made, then the 7 time zones for which the region ID has changed, will be found by the utltzuv2.sql script.
Under normal circumstances Note 396670.1 and Note 396671.1 explain that you do not have to take the TSTZ -> VARCHAR2 -> TSTZ actions for all data, but this is only needed when the timestamps are in the “affected time frame”.
However, for the 7 time zones discussed here, you have to take all data through these TSTZ -> VARCHAR2 -> TSTZ actions. When this is done all data will be stored correctly using the new region IDs, and there will be no future problems.
If you have already upgraded you database time zone files to version-3 (patch 4689959) or version-4 (patch 5632264), and you find that you receive ORA-1882 errors when querying old data, then it is likely that this data is stored using the old (and now incorrect) region IDs.
In order to resolve this problem you can use the following script, which should be run connected SYS AS SYSDBA.
NOTE1: This script should only be used when you have already upgraded to the new time zone files, and you want to fix the ORA-1882 errors you (might) receive there. This script will not do anything for this data if you are still using the original time zone files.
NOTE2: The logic of this script is to find all data which causes a ORA-1882. The bytes that are stored in those rows are then investigated, and if any of the the 7 known “updated” time zone regions are discovered then the data is updated with it’s original value. This will cause the data to be stored using the new region IDs and it will be accessible again afterwards.
Something that needs to be noted is that for data which originally used either the EST or MST time zones, these are updated to EST5EDT and MST7MDT respectively. This is done because in the new time zone file definitions EST and MST are redefined to be no longer affected by DST. Therefore updating this data with the original value would change the meaning of the data. In general you are also advised to change the usage of the PST and CST time zones in case these are updated in future time zone file updated. Because these time zones are not affected by this “redefinition issue” at this moment they are not handled by this script. In Note 414586.1 you can find a generic script to also update those columns to use better time zones. However, if you run into ORA-1882 errors you should always use the note below first to fix these errors, before other updates can be done.
NOTE3: This script (obviously) updates live data in your database when problem data is found. You will therefore have to check if there are any for-update triggers which you might need to disable. This needs to be judged on a case by case basis because certain triggers might still be needed whereas others need to be disabled.
Also note that for existing data between the old and new start- and end-dates of DST the data is adjusted by 1 hour to retain the original meaning of the data. This might cause some migration of data if the timestamp column is used as a partition key.__
fix1882.sql (11 KB)