After a recent upgrade to Enterprise Manager 12c 12.1.0.5, we noticed that the few Oracle Databases in release 10g were no more connectable:
On MOS there are a few notes about the error: ORA-00604: error occurred at recursive SQL level 1 ORA-01882: timezone region not found, the most relevant being Doc ID 1513536.1 and Doc ID 1934470.1.
The problem is due to the time zone table on the target database that doesn’t contain the timezone requested by the client. But who’s the client? In our case, all the target agents were correctly set to Europe/Zurich, but the timezone table of the target database contained it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select version from v$timezone_file; VERSION ---------- 4 SQL> SELECT TZname, TzAbbrev FROM V$TIMEZONE_NAMES where TZname like 'Europe/Zurich'; TZNAME TZABBREV ------------------ --------------------- Europe/Zurich LMT Europe/Zurich BMT Europe/Zurich CET Europe/Zurich CEST |
So what was causing the problem?
The upgrade process of the OMSes from 12.1.0.3 to 12.1.0.5, without the presence of a specific TZ environment variable, set the OMS timezone to Europe/Vaduz. I figured it out after searching deep and large, inside the WLS product properties:
|
1 2 3 4 5 6 |
$ pwd /u01/app/oracle/Middleware_12105/wlserver_10.3 $ cat .product.properties ... USER_TIMEZONE=Europe/Vaduz ... |
Indeed, that timezone was not present in the timezone table version 4:
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> sELECT TZname, TzAbbrev FROM V$TIMEZONE_NAMES where TZname like 'Europe/Vaduz'; no rows selected SQL> alter session set time_zone='Europe/Zurich'; Session altered. SQL> alter session set time_zone='Europe/Vaduz'; ERROR: ORA-01882: timezone region not found |
After setting explicitly the TZ to Europe/Zurich on the OMS servers and restarting the OMSes, everything was fine again.
HTH
—
Ludo
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Pingback: ORA-01882: timezone region not found while connecting to an EM12c target - Ludovico Caldara - Blogs - triBLOG