Oracle timezone mystery

The post title says mystery as it took bit time to identify the real issue. So, to start with we were suppose to change OS and DB timezone to CST as per customer request. Well pretty straight process change OS settings -> DB Settings -> If needed register with CRS. But after all this also it was not converted to CST.

Local Connection

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , 
 to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

TO_CHAR(SYSDATE,'DD TO_CHAR(SYSTIMESTAMP,'DD/MM/YYYYHH24:MI:SSTZR')
------------------- ----------------------------------------------------
10/07/2015 11:41:22 10/07/2015 11:41:22 -05:00

Remote Connection

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS'), 
 to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

TO_CHAR(SYSDATE,'DD TO_CHAR(SYSTIMESTAMP,'DD/MM/YYYYHH24:MI:SSTZR')
------------------- ----------------------------------------------------
10/07/2015 12:41:54 10/07/2015 12:41:54 -04:00

 

So local connection (BEQ) was giving correct time, but when you connect through TNS it was still giving eastern time. So, obviously need to check few configuration files.

cat /u01/app/11.2.0.3/grid/crs/install_crsconfig_<host>_env.txt

TZ=America/Chicago
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
TNS_ADMIN=
ORACLE_BASE=

cat /etc/sysconfig/clock
ZONE="America/Chicago"
UTC=true 
ARC=false

So far everything looks good, so there is something wrong somewhere and after checking further found this

export TZ=UTC
date
Fri Jul 10 20:31:38 UTC 2015

export TZ=America/New_York
date
Fri Jul 10 16:36:08 EDT 2015

export TZ=America/Chicago
date 
Fri Jul 10 16:33:32 EDT 2015

export TZ=America/Matamoros
date
Fri Jul 10 15:34:08 CDT 2015


export TZ=America/Chihuahua
date
Fri Jul 10 14:40:52 MDT 2015

export TZ=America/Chihuahua
date
Fri Jul 10 14:40:52 MDT 2015

Now when you look zoneinfo 

cd /usr/share/zoneinfo/America
ls -ltra
total 584
-rw-r--r-- 2 root root 3519 Feb 1 02:45 Chicago
-rw-r--r-- 1 root root 1966 Mar 21 08:56 Yellowknife
-rw-r--r-- 1 root root 2288 Mar 21 08:56 Yakutat
-rw-r--r-- 2 root root 2865 Mar 21 08:56 Winnipeg
-rw-r--r-- 2 root root 2067 Mar 21 08:56 Whitehorse
-rw-r--r-- 15 root root 156 Mar 21 08:56 Virgin


So only Chicago has older timestamp so maybe it’s corrupted, well on all other box the file size is different than this.

So from above after changing to CST(Chicago) still my time was showing me  EDT, Well further looking to zoneinfo files only Chicago has different timestamp than others. So, checking on other server size was also different for this file and seems like that only file was corrupted. Once replaced with good file the issue got fixed. no need to reboot or restart.