GI Timezone

Lately I had a weird problem with one of my customers regarding timezone in the database. They copied a database server from US (eastern time zone) to Europe (GMT) and changed all the OS setting to the new timezone. The application uses “sysdate” to insert data to the database, and they saw that the sysdate returns the wrong time. When I checked that, it seemed that when we used sqlplus locally on the server everything worked perfectly, sysdate, current_timestamp and systimestamp all returned the correct times with GMT. In SQL developer, however, even when running from the same timezone (GMT) the result of sysdate and systimestamp were still eastern time and not the GMT (current_timestamp returned the correct time).

This is strange as sysdate and systimestamp should be only affected by the OS, and the OS was configured correctly. So, what can affect the timezone difference? These are the things that came to my mind:

• OS configuration
• DBTIMEZONE
• TZ environment variable

So I checked:

• The OS is configured correctly
• DBTIMEZONE is not configured correctly (as it takes the timezone from the system when the database is created), but it shouldn’t affect anything but “timestamp with timezone” datatypes (which we don’t use anyway)
• TZ environment variable was not configured

So where is the problem?

 Analysis

As far as I know, these are the 3 options that might be relevant, but none of them was the issue. So I looked at the internet and at Oracle MOS. All the notes/posts talked about different timezones and explained why locally you get different time than remotely, which makes sense, I already knew it, and it not the issue I’m having! I had to investigate it myself, so I checked each topic separately.
I understand quite a lot about the linux OS, but I’m still no sysadmin. So I contacted Baruch Osoveksiy (one of the guys at my company who is a brilliant linux sysadmin) and consulted him. We checked both /etc/localtime and /etc/sysconfig/clock, both were fine. He said that everything seems OK and he can’t think of anything that can cause this.
The next thing was DBTIMEZONE. The DBTIMEZONE, as I said, should affect only “timestamp with timezone” data types and definitely not sysdate. But I didn’t know where the problem came from, so I had to try that as well. Changing the DBTIMEZONE is not possible if there are any “timestamp with timezone” data types in the database, and it sets the time zone that these columns values will be stored in. I didn’t want to play with that on the production server, so I asked for a copy of one of the European servers and tested that on it. As expected, I managed to change the time zone, as we don’t use “timestamp with timezone” columns, but it didn’t fix the problem.
The last thing was the TZ. I checked several times and it wasn’t configured in any place. I know that setting the TZ and then starting the listener might cause this scenario. The thing is that in these servers we are using ASM, hence Oracle GI with the Oracle restart stack. This means that the process that starts the listener is the crs. So I had to check the environment of the running listener. In linux we can do that by finding the pid of the listener (ps -ef|grep lsnr) and the environment variables can be found in the file /proc/<pid>/environ (strings /proc/<pid>/environ will show you everything, as it is a binary file). When I did that I found that the TZ is indeed configured to US/Eastern. Now I knew where this issue comes from.
Once I found the problem, it was much easier to find the solution. A short search in MOS returned node# 1209444.1 (How To Change Timezone for Grid Infrastructure).

Solution

The solution is described in the note. Once we install GI (11.2.0.2 and up) it stores the timezone in the file $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt. If we move the server to a different timezone we need to backup this file, update it (with a correct, supported timezone) and restart the crs resources. For some reason, in my case, restarting the database and listener wasn’t enough, or I simply missed something. I ended up restarting the entire crs (with “crsctl stop has” and “crsctl start has”) and that solved the problem.