Dealing with: ORA-01804: failure to initialize timezone information

This article explains how to solve the Oracle database error “ORA-01804: failure to initialize timezone information” using the Oracle database version 12cR1 (12.1.0.2).

Table of Contents:

ORA-01804: During the backup process.
Check the existing Time Zone version.
Set the ORA_TZFILE environment variable

1. ORA-01804: During the backup process:

We have received this error while making a full Oracle database backup

a. Full backup Offline :

[oracle@ol-7u4 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue May 29 08:46:48 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: WADHAH (DBID=152152954, not open)
RMAN> backup database;
Starting backup at 29-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=248 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_example_fjsdj7rq_.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_system_fjsd8gwo_.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_sysaux_fjsd3wrb_.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_undotbs1_fjsdg2w5_.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_users_fjsdfz2d_.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAY-18
channel ORA_DISK_1: finished piece 1 at 29-MAY-18
piece handle=/home/oracle/app/oracle/fast_recovery_area/WADHAH/backupset/2018_05_29/o1_mf_nnndf_TAG20180529T084659_fjt1cms8_.bkp tag=TAG20180529T084659 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-MAY-18
channel ORA_DISK_1: finished piece 1 at 29-MAY-18
piece handle=/home/oracle/app/oracle/fast_recovery_area/WADHAH/backupset/2018_05_29/o1_mf_ncsnf_TAG20180529T084659_fjt1d46f_.bkp tag=TAG20180529T084659 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-MAY-18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of REFAF command on default channel at 05/29/2018 08:47:18
ORA-01804: failure to initialize timezone information

b. The error: ORA-01804

At the end of our backup, we have received an error:

RMAN-03009: failure of REFAF command on default channel at 05/29/2018 08:47:18
ORA-01804: failure to initialize timezone information

2. Check the existing Time Zone version:

a. Check the Oracle database version:

$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue May 29 08:37:59 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
SQL>

b. Check the existing Time Zone version:

SQL> select value$ "Time Zone Version" from PROPS$ where name='DST_PRIMARY_TT_VERSION';
Time Zone Version
--------------------------------------------------------------------------------
18

Note: The Time zone version is 18

c. Check the existing Time Zone configuration file:

The configuration files of the time zone are located in $ORACLE_HOME/oracore/zoneinfo

$ ls -l /home/oracle/app/oracle/product/12.1.0/dbhome_1/oracore/zoneinfo/
total 17780
drwxr-xr-x. 2 oracle oinstall 4096 May 28 22:19 big
drwxr-xr-x. 2 oracle oinstall 4096 May 28 22:19 little
-rw-r--r--. 1 oracle oinstall 7179 Apr 16 2014 readme.txt
-rw-r--r--. 1 oracle oinstall 34454 Apr 16 2014 timezdif.csv
-rw-r--r--. 1 oracle oinstall 792894 Dec 3 2013 timezlrg_10.dat
-rw-r--r--. 1 oracle oinstall 787272 Dec 3 2013 timezlrg_11.dat
-rw-r--r--. 1 oracle oinstall 785621 Dec 3 2013 timezlrg_12.dat
-rw-r--r--. 1 oracle oinstall 782475 Dec 3 2013 timezlrg_13.dat
-rw-r--r--. 1 oracle oinstall 791430 Dec 3 2013 timezlrg_14.dat
-rw-r--r--. 1 oracle oinstall 791476 Dec 3 2013 timezlrg_15.dat
-rw-r--r--. 1 oracle oinstall 800913 Dec 3 2013 timezlrg_16.dat
-rw-r--r--. 1 oracle oinstall 779003 Dec 3 2013 timezlrg_17.dat
-rw-r--r--. 1 oracle oinstall 781669 Dec 3 2013 timezlrg_18.dat
-rw-r--r--. 1 oracle oinstall 493675 Dec 3 2013 timezlrg_1.dat
-rw-r--r--. 1 oracle oinstall 507957 Dec 3 2013 timezlrg_2.dat
-rw-r--r--. 1 oracle oinstall 527717 Dec 3 2013 timezlrg_3.dat
-rw-r--r--. 1 oracle oinstall 531137 Dec 3 2013 timezlrg_4.dat
-rw-r--r--. 1 oracle oinstall 587487 Dec 3 2013 timezlrg_5.dat
-rw-r--r--. 1 oracle oinstall 586750 Dec 3 2013 timezlrg_6.dat
-rw-r--r--. 1 oracle oinstall 601242 Dec 3 2013 timezlrg_7.dat
-rw-r--r--. 1 oracle oinstall 616723 Dec 3 2013 timezlrg_8.dat
-rw-r--r--. 1 oracle oinstall 801410 Dec 3 2013 timezlrg_9.dat
-rw-r--r--. 1 oracle oinstall 345637 Dec 3 2013 timezone_10.dat
-rw-r--r--. 1 oracle oinstall 345356 Dec 3 2013 timezone_11.dat
-rw-r--r--. 1 oracle oinstall 345024 Dec 3 2013 timezone_12.dat
-rw-r--r--. 1 oracle oinstall 344425 Dec 3 2013 timezone_13.dat
-rw-r--r--. 1 oracle oinstall 344448 Dec 3 2013 timezone_14.dat
-rw-r--r--. 1 oracle oinstall 344448 Dec 3 2013 timezone_15.dat
-rw-r--r--. 1 oracle oinstall 343044 Dec 3 2013 timezone_16.dat
-rw-r--r--. 1 oracle oinstall 341718 Dec 3 2013 timezone_17.dat
-rw-r--r--. 1 oracle oinstall 341718 Dec 3 2013 timezone_18.dat
-rw-r--r--. 1 oracle oinstall 274427 Dec 3 2013 timezone_1.dat
-rw-r--r--. 1 oracle oinstall 274900 Dec 3 2013 timezone_2.dat
-rw-r--r--. 1 oracle oinstall 286651 Dec 3 2013 timezone_3.dat
-rw-r--r--. 1 oracle oinstall 286264 Dec 3 2013 timezone_4.dat
-rw-r--r--. 1 oracle oinstall 286310 Dec 3 2013 timezone_5.dat
-rw-r--r--. 1 oracle oinstall 286217 Dec 3 2013 timezone_6.dat
-rw-r--r--. 1 oracle oinstall 286815 Dec 3 2013 timezone_7.dat
-rw-r--r--. 1 oracle oinstall 302100 Dec 3 2013 timezone_8.dat
-rw-r--r--. 1 oracle oinstall 351525 Dec 3 2013 timezone_9.dat

3. Set the ORA_TZFILE environment variable:

a. Set environment variable ORA_TZFILE:

Note: My Oracle Home: /home/oracle/app/oracle/product/12.1.0/dbhome_1

$ export ORA_TZFILE=/home/oracle/app/oracle/product/12.1.0/dbhome_1/oracore/zoneinfo/timezlrg_18.dat

b. Make a RMAN backup:

[oracle@ol-7u4 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue May 29 08:52:00 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: WADHAH (DBID=152152954, not open)
RMAN> backup database;
Starting backup at 29-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_example_fjsdj7rq_.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_system_fjsd8gwo_.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_sysaux_fjsd3wrb_.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_undotbs1_fjsdg2w5_.dbf
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/WADHAH/datafile/o1_mf_users_fjsdfz2d_.dbf
channel ORA_DISK_1: starting piece 1 at 29-MAY-18
channel ORA_DISK_1: finished piece 1 at 29-MAY-18
piece handle=/home/oracle/app/oracle/fast_recovery_area/WADHAH/backupset/2018_05_29/o1_mf_nnndf_TAG20180529T085209_fjt1objs_.bkp tag=TAG20180529T085209 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-MAY-18
channel ORA_DISK_1: finished piece 1 at 29-MAY-18
piece handle=/home/oracle/app/oracle/fast_recovery_area/WADHAH/backupset/2018_05_29/o1_mf_ncsnf_TAG20180529T085209_fjt1ov38_.bkp tag=TAG20180529T085209 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-MAY-18
RMAN> exit
Recovery Manager complete.
[oracle@ol-7u4 ~]$

Conclusion:

The envirenment variable ORA_TZFILE solves the Oracle database error “ORA-01804: failure to initialize timezone information”.

Dealing with: ORA-01804: failure to initialize timezone information

1. ORA-01804: During the backup process:

2. Check the existing Time Zone version:

3. Set the ORA_TZFILE environment variable:

Conclusion:

Leave a Reply Cancel reply

Oracle ACE Pro

Oracle Certified Expert

Oracle Cloud Infrastructure

MySQL: Administrator Certified Professional

PostgreSQL: Certified Associate

Recent Posts