My Oracle Support Banner

Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade (Doc ID 414590.1)

Last updated on FEBRUARY 02, 2022

Applies to:

Oracle Database - Standard Edition - Version 9.0.1.0 and later
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Information in this document applies to any platform.

Purpose

This note explains that the internal region IDs that Oracle uses for 7 time zones in DSTv1 and DSTv2 were updated in the DSTv3 and up. After an update from DSTV1 or DStV2 is done to DSTv3 or higher then any "old" data which uses these 7 time zones will not be recognized by Oracle and result in a ORA-1882 error.
Note that if you use a client that uses DSTv1 or DSTv2 as DST version and you connect to a database that has an DST version higher then 3 (like DSTv4 the "USA dst update" , DSTv11 or DSTv14) the same problem may arise, the solution is however totally different

In order to circumvent any problems special actions need to be taken when the DSTv3 or higher 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 affected time zones are: HST , EST , MST , EST5EDT , MST7MDT , CST6CDT ,PST8PDT

Scope

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.

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  - there is nothing wrong with the database in this case - , you then need to update the client's DST version.
This is typically seen when using "old" 10.2.0.2, 10.2.0.1 or lower (10gr1 , 9i) clients and connecting to 10.2.0.3, 10.2.0.4 ,10.2.0.5, 11.1.0.x or 11.2.0.x databases and selecting DBMS_SCHEDULER information.
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 the select gives NO ORA-01882 when using the sqlplus found in the oracle_home on the server then you need to
* or apply at least DSTv4 to those clients (For windows clients see <Note.417893.1> How To Apply The V4 DST Patches To Windows Clients or Servers )
* or update the version of the client
* or do not use this client to do the select

 

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 What has changed?
 Which time zone region IDs have been updated?
 Actions to take before applying the new time zone files
 Actions when having ORA-01882 after an upgrade to 11.2.0.1 or higher.
 Actions when having ORA-01882 after an update of the DST version on 9i, 10g or 11.1, apply of 10.2.0.3, 10.2.0.4, 10.2.0.5 patchset or upgrade to 10.2.0.3, 10.2.0.4, 10.2.0.5, 11.1.0.6 or 11.1.0.7.
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.