In this Document
APPLIES TO: Oracle Database - Enterprise Edition - Version 11.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 Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Information in this document applies to any platform. ***Checked for relevance on 02-Jul-2016*** SYMPTOMS Database startup fails with Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced ORA-01804: failure to initialize timezone information AND in the alert.log this message is seen: Failed to find timezone data file # -number- (DST_4) Daylight Saving Time feature is disabled for example Failed to find timezone data file # 17 (DST_4) Daylight Saving Time feature is disabled or Failed to find timezone data file # 14 (DST_4) Daylight Saving Time feature is disabled with often also an ORA-600 [qcisSetPlsqlCtx:tzi init] CHANGES This is mostly seen when restoring a database and opening it in a new $ORACLE_HOME. The -number- in the error indicates what missing DST patch needed for opening the database , for example: "Failed to find timezone data file # 14 (DST_4)" error means the database cannot find the RDBMS DSTv14 patch applied (normally installed by default) in order to open properly. "Failed to find timezone data file # 17 (DST_4)" error means the database n cannot find the RDBMS DSTv17 patch in the home in order to open properly. CAUSE In 11.2 and higher the DST version is a database property, so * if someone has set ORA_TZFILE to an incorrect file or location , has renamed file (for example renaming files to timezlrg.dat and timezone.dat - whihc should NOT be done !) or the files in $ORACLE_HOME/oracore/zoneinfo/ (unix) or %ORACLE_HOME%\oracore\zoneinfo\ (windows) are corrupted then this error my be seen after a restart of the database without any "changes" to the ORACLE_HOME
* if the database was ever updated to an RDBMS dst version that is higher than the one provided by default in the software installation you might see this when opening this database in an NEW non-patched $ORACLE_HOME. In the example of "Failed to find timezone data file # 14 (DST_4)" error the database was an RDBMS DSTv17 11.2.0.2 database restored to a new 11.2.0.2 home but the DSTv17 patch was not installed in the 11.2.0.2 home. Seen 11.2.0.2 has by default only up to DSTv14 included the DSTv17 patch was not found, hence the error. SOLUTION In 11.2 there are no timezlrg.dat and timezone.dat, this is normal and intended. Do NOT make any symbolic links for timezlrg.dat and timezone.dat or copy any of the files in \oracore\zoneinfo\ and rename them to timezlrg.dat and timezone.dat in 11.2 there should be NO timezlrg.dat and timezone.dat in $ORACLE_HOME/oracore/zoneinfo/ (unix) or %ORACLE_HOME%\oracore\zoneinfo\ (windows) Oracle 11.2.0.1 has by default all RDBMS DST updates from DSTv1 to DSTv11 included in the software installation. Oracle 11.2.0.2 has by default all RDBMS DST updates from DSTv1 to DSTv14 included in the software installation. Oracle 11.2.0.3 has by default all RDBMS DST updates from DSTv1 to DSTv14 included in the software installation. These files are found in $ORACLE_HOME/oracore/zoneinfo and have a prefix indicating the DST version. For example timezlrg_4.dat is the DSTv4 "large" file, timezlrg_11.dat is the DSTv11 "large" file. connect and check: conn / as sysdba select NAME, VALUE$ from SYS.PROPS$ where NAME like ('DST_%_TT_VERSION'); DST_SECONDARY_TT_VERSION -> should normally be 0 , if this has a value then also check if it is applied DST_PRIMARY_TT_VERSION -> required TZ file if DST_PRIMARY_TT_VERSION is for example "14" then check if $ORACLE_HOME/oracore/zoneinfo or %ORACLE_HOME%\oracore\zoneinfo * contains timezone_14.dat and timezlrg_14.dat if not then there is a serious issue on file system or someone removed files seen this is installed by default in 11.2.0.2 and higher * the files are readable for the ORACLE OS user , if not adjust permissions * there are no timezlrg.dat and timezone.dat files/copies/symbolic links
make sure to NOT have $ORA_TZFILE set in the environment (or registry on windows) and then start the database. if DST_PRIMARY_TT_VERSION is for example "17" (= higher than 14) then check if $ORACLE_HOME/oracore/zoneinfo or %ORACLE_HOME%\oracore\zoneinfo * contains timezone_17.dat and timezlrg_17.dat if not then apply the missing DSTv17 patch See note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches / H) Overview of what DST version is by default used / included in what Oracle RDBMS version and all DST patch numbers for the DST patch number required. / All DST patch numbers: * the files are readable for the ORACLE OS user , if not adjust permissions * there are no timezlrg.dat and timezone.dat files/copies/symbolic links There is NO need to run DBMS_DST or so. |