ORA-01804: failure to initialize timezone information

This error encountered during 12c upgrade. My source database was running in 11.2.0.3 with DSTv17 and i was upgrading database to 12.1.0.2. I had to updgrade DST to v18 as part of the upgrade. AFter DST upgrade i was getting the error below while connecting to sqlplus and while running autoconfig as well.

Error :

ORA-01804: failure to initialize timezone information

SP2-0152: ORACLE may not be functioning properly

The first thing to do when you hit ORA-1804 is to check which timezone file Oracle attempts to use:
If the ORA_TZFILE environment variable is set then that points to the file that will be used.
If ORA_TZFILE is not set then until Oracle 9.2.0.5 $ORACLE_HOME/oracore/zoneinfo/timezone.dat is the default file that is used, since the 9.2.0.5 patchset (and all higher versions up to 11.1.0.7) the default file is $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
Since 11.2 the .dat files have a prefix indicating the DST version. Hence there are NO timezone.dat or timezlrg.dat in 11.2 and higher . 

For example timezlrg_4.dat is the DSTv4 "large" file, timezlrg_11.dat is the DSTv11 "large" file.
The first thing to do is to check if ORA_TZFILE is set and if it is, remove it and restart the database and listener. Oracle will then try to load $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat or, from 11.2 onwards, the $ORACLE_HOME/oracore/zoneinfo/timezlrg_XX.dat file with XX being the higher number available in the $ORACLE_HOME/oracore/zoneinfo/ directory.

If for some reason the file that Oracle wants to use is not available then this is the cause of the problem.
If the file is present then an alternative reason for the error could be that Oracle cannot load the file because of permission problems.

If all above is correct then it might be a .dat file that is corrupt.

In my scenario, timezlrg.dat file which is set to ORA_TZFILE was not present. From 11g onwards we have version introduced in file name. So i updated the file name as below in ad8idbux.env as mentioned below :

bash-3.2$ echo $ORA_TZFILE
/d02/DEV/oratest/12.1.0/oracore/zoneinfo/timezlrg.dat
bash-3.2$ ls -ltr /d02/DEV/oratest/12.1.0/oracore/zoneinfo/timezlrg.dat
/d02/DEV/oratest/12.1.0/oracore/zoneinfo/timezlrg.dat: No such file or directory

You can find timezone files present under the location /d02/DEV/oratest/12.1.0/oracore/zoneinfo. Update the highest version available. I updated timezlrg_17.dat

---------------------In ad8idbux.env file---------------------

if test "%s_database%" = "db112" -o "%s_database%" = "db121" ; then
     ORA_TZFILE=""
else
     ORA_TZFILE="$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat"
fi
export ORA_TZFILE


1. This is resolved by editing the template ad8idbux.env in location .../oracle/db/tech_st/11.2.0/appsutil/template changing:

From:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg.dat

To:
set ORA_TZFILE=%s_db_oh%\oracore\zoneinfo\timezlrg_17.dat

2. Running AutoConfig after this change means the environment file is created with the ORA_TZFILE value as timezlrg_14.dat and AutoConfig completes successfully


References :
Autoconfig Failed On 12c Database With "ORA-01804: failure to initialize timezone information" (Doc ID 1908836.1)
AutoConfig Encounters A ORA-1804 Error: Failure To Initialize Timezone Information (Doc ID 1917923.1)
AutoConfig Encounters A ORA-1804 Error: Failure To Initialize Timezone Information (Doc ID 1917923.1)




Note : these .dat files are only read by Oracle, never written to, so if these files become corrupt then the reason for they becoming corrupt is at OS level or I/O subsystem.
So while copying / reinstalling those files might solve the symptom (the ORA-1804) , the root cause is non-Oracle and a deep diagnostic of the filesystem/ I/O subsystem is strongly advised.
Also there is a big chance these .dat files are not the only files who became corrupt.

Because these timezone files are static and can only be updated by Oracle development the solution is the same in both cases:
Find a different database (same RDBMS and (!) DST version & platform) that does not have these problems (check for example by selecting from V$TIMEZONE_NAMES)
Copy the *.dat files from that system over to the failing system
Restart all Oracle processes
or Reinstall the Oracle software.