You would most probably get the ORA-1804 error when you try to set time zone on a session like:
SQL> alter session set time_zone = 'Europe/Istanbul'; alter session set time_zone = 'Europe/Istanbul' * ERROR at line 1: ORA-1804: failure to initialize timezone information
Or you may get the ORA-1804 error when you try to run datapatch command after you restore a database to a different ORACLE_HOME with a different PSU level and you want to reflect those changes on the database with the datapatch command.
Another occasion might even be trying to connect to the database from the middle-tier:
Even, the RMAN can complain about the timezone…
SO, WHAT IS GOING ON?
Oracle database gets the timezone information from two types of timezone files: timezlrg.dat and timezone.dat. You will see several versions of those files under the path: $ORACLE_HOME/oracore/zoneinfo.
Cause of the ORA-1804 is most probably; the database is unable to find those required files with the required version under the path $ORACLE_HOME/oracore/zoneinfo . This can even happen when you restore your database with the RMAN to run on a software with a lower DST version. Possible causes are listed below:
- The zone files (ex. timezone_28.dat and timezlrg_28.dat) are missing and the database is on DSTv28 level.
- The permission on the zonefiles might be wrong.
- There is a ORA_TZFILE environment parameter set that might be pointing to completely irrelevant path.
- The timezone files might be corrupt
- You restored the database to a different server with a different DST level, so there is a mismatch between DST levels.
SOLUTION
If the reason is the ORA_TZFILE environment variable set (I don’t think so but…) just unset it.
If the reason is the missing or corrupt timezone files, you can just copy and paste those files from a different database that is an exact copy of your database in terms of DST level and the RDBMS version.
The reason might be the missing timezone files for a restored database that is looking for a more resent DST version. For example, if you restore a database that has een already patched with DST_v28 to a database server that has only DST_v26, then the following query will return nothing:
SQL> SELECT FILENAME, VERSION FROM V$TIMEZONE_FILE; no rows selected
If that is the case, what we need to do is just download the correct DST patch and apply it using opatch. The relevant timezone files will be placed under the path $ORACLE_HOME/oracore/zoneinfo. (Doc ID 2478793.1)
You can run the following query to find the DST level the database is looing for and unable to find the files for:
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; PROPERTY_NAME VALUE ------------------------------ ------------------------------ DST_PRIMARY_TT_VERSION 28 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE
NOTE: Do not try to get the version of the DST using the registry$database view, it may not give the correct value since the registry$database table is only populated by the pre-upgrade information tool. By the way, you can fix the different Time Zone Version In registry$database and v$timezone_file issue with: (Doc ID 1255474.1)
SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file); SQL> commit;