We have a client that runs an application that, for whatever reasons, does NOT like daylight saving time. For that reason, the Oracle server is kept in Eastern Standard Time and does not change with the rest of the eastern United States when DST begins and ends every year. They accomplish this with a custom /etc/localtime file. However, they left /etc/sysconfig/clock set to "TZ=America/New_York," which would prove fateful as I shall point out. So, with the custom localtime file, the "date" command as well as selecting sysdate or systimestamp would always return the current time in Eastern Standard Time. When it is Daylight Saving Time, as it is right now, this would be one hour behind "real" time as we consider it.



Now, we recently upgraded this client from Oracle Grid Infrastructure (for single-instance ASM) from 11.2.0.1 to 11.2.0.2. The next business day, the client alerted us that their date fields were coming back in Eastern Daylight Time. While this time was still technically right, they needed the time in the EST timezone.

I first set about trying to duplicate the problem. I was able to see the same incorrect results when I connected to the database remotely (e.g. via sqlplus or Oracle SQL Developer over TNS), but not locally (i.e. "sqlplus / as sysdba"). Then I duplicated the problem when connecting locally via TNS, meaning I was going through the listener. So we had narrowed it down to only connections going through the listener. I hadn't considered the listener to be aware of timezones, so this was rather mind-boggling for me.

Here is an example of the incorrect results we saw:

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-JUN-11 07.38.31.711902 PM -04:00

SQL> !date
Tue Jun 14 18:38:33 EST 2011

The system date always returned the value we wanted, but now SYSTIMESTAMP (and SYSDATE, and any other date values stored in the database) were coming back in EDT.

My Pythian colleague Marc Fielding found My Oracle Support document ID 1209444.1, which had these two eye-opening lines:
1. For 11.2.0.1, shell environment variable TZ is set correctly for grid user and root.
2. For 11.2.0.2 and above, TZ entry in $GRID_HOME/crs/install/s_crsconfig__env.txt sets to correct time zone.

Looking in the s_crsconfig_*_env.txt file on these hosts, I saw this:

TZ=America/New_York

The workaround was to change the TZ value in this file to "EST5" and perform a quick restart the HAS daemon:

# /etc/init.d/ohasd stop
# /etc/init.d/ohasd start

After doing this, I liked what I was seeing:

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-JUN-11 06.42.16.384596 PM -05:00

SQL> !date
Tue Jun 14 18:42:19 EST 2011


Marc and I believe that the Grid Infrastructure installer grabs the value in /etc/sysconfig/clock when setting up the env file in question. We've asked the client to ensure that /etc/sysconfig/clock is always properly set in the future.

We imagine that most places don't try to fight Daylight Saving Time this way, but the bug also applies if you are doing any kind of timezone slight-of-hand, like telling your database it is in US Central time when the server might be in US Pacific time. So if your organization is doing this, be sure to double check the crsconfig file after 11.2.0.2 installation!