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 AUGUST 04, 2018

Applies to:

Oracle Database - Standard Edition - Version 9.0.1.0 and later
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

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.