RAC: Timezone differences in CRS and Database logs

Hello All,

We have got an issue about time zone differences in alert logs , crs logs with system timestamp and with system timestamp.

	 System date time 
	[oracle@TESTRAC1]/ # date
	Tue Apr 24 04:10:11 CEST 2013
        

But database Alert log shows,

  	Thread 1 advanced to log sequence 235 (LGWR switch)
	Current log# 11 seq# 235 mem# 0: +DATA01/db/onlinelog/redot01g01m01.rdo
	Current log# 11 seq# 235 mem# 1: +FRA01/db/onlinelog/redot01g01m02.rdo
	Fri Apr 24 45:04:50 2013
	Archived Log entry 475 added for thread 1 sequence 234 ID 0x984ece99 dest 1:
	[oracle@TESTRAC1 trace]$ date
	Fri Apr 24 06:46:16 IST 2013
    	 Note: Two hours ahead of the database time and the host time
        

Few checks before start actual resolution

1) Our database timezone showing correct timestamp only.

	SQL> select systimestamp from dual;
	SYSTIMESTAMP
	---------------------------------------------------------------------------
	24-APR-13 04.13.03.105848 AM +02:00
        

2) Hardware clock checked and its fine

	[oracle@TESTRAC1] # hwclock
	Tue 24 Apr 2013 04:13:43 AM CEST  -0.072343 seconds
        

3) Ran the cluster verification utility for timesynchronisation issues and its passed

        ./cluvfy cluvfy comp clocksync

	Verifying Clock Synchronization across the cluster nodes
	Checking if Clusterware is installed on all nodes...
	Check of Clusterware install passed

	Checking if CTSS Resource is running on all nodes...
	Check: CTSS Resource running on all nodes
	  Node Name                             Status
	  ------------------------------------  ------------------------
	  TESTRAC1                          	passed
	  TESTRAC2                          	passed
	Result: CTSS resource check passed


	Querying CTSS for time offset on all nodes...
	Result: Query of CTSS for time offset passed

	Check CTSS state started...
	Check: CTSS state
	  Node Name                             State
	  ------------------------------------  ------------------------
	  TESTRAC1                          	Observer
	  TESTRAC2                          	Observer
	CTSS is in Observer state. Switching over to clock synchronization checks using NTP
	Starting Clock synchronization checks using Network Time Protocol(NTP)...

	NTP Configuration file check started...
	The NTP configuration file "/etc/ntp.conf" is available on all nodes
	NTP Configuration file check passed

	Checking daemon liveness...

	Check: Liveness for "ntpd"
	  Node Name                             Running?
	  ------------------------------------  ------------------------
	  TESTRAC1                          	yes
	  TESTRAC2                         	 yes
	Result: Liveness check passed for "ntpd"
	Check for NTP daemon or service alive passed on all nodes

	Checking NTP daemon command line for slewing option "-x"
	Check: NTP daemon command line
	  Node Name                             Slewing Option Set?
	  ------------------------------------  ------------------------
	  TESTRAC1                         	 yes
	  TESTRAC2                         	 yes
	Result:
	NTP daemon slewing option check passed

	Checking NTP daemon's boot time configuration, in file "/etc/sysconfig/ntpd", for slewing option "-x"

	Check: NTP daemon's boot time configuration
	  Node Name                             Slewing Option Set?
	  ------------------------------------  ------------------------
	  TESTRAC1                        	  yes
	  TESTRAC2                         	 yes
	Result:
	NTP daemon's boot time configuration check for slewing option passed

	Checking whether NTP daemon or service is using UDP port 123 on all nodes

	Check for NTP daemon or service using UDP port 123
	  Node Name                             Port Open?
	  ------------------------------------  ------------------------
	  TESTRAC1                         	 yes
	  TESTRAC2                        	  yes

	NTP common Time Server Check started...
	NTP Time Server ".GPS." is common to all nodes on which the NTP daemon is running
	Check of common NTP Time Server passed

	Clock time offset check from NTP Time Server started...
	Checking on nodes "[TESTRAC1, TESTRAC2]"...
	Check: Clock time offset from NTP Time Server

	Time Server: .GPS.
	Time Offset Limit: 1000.0 msecs
	  Node Name     Time Offset               Status
	  ------------  ------------------------  ------------------------
	  TESTRAC1 	 0.125                     passed
	  TESTRAC2  	0.274                     passed
	Time Server ".GPS." has time offsets that are within permissible limits for nodes "[TESTRAC1, TESTRAC2]".
	Clock time offset check passed
	Result: Clock synchronization check using Network Time Protocol(NTP) passed
	Oracle Cluster Time Synchronization Services check passed
	Verification of Clock Synchronization across the cluster nodes was successful.
        

4) CTSSD is in observed mode, means our cluster using ntp

So everything is intact, but where is the problem, here you go about the issue and its resolution.

Resolution Steps

This is due to the installation of cluster using a stack that we have in place in our company where that is built in HK
and the timezone parameter set as HK and Oracle install maintains a file called s_crsconfig_TESTRAC1_env for timezone setting for node.
We just run a script while input a parameter file for cluster installation, it will install the crs automatically for given nodes.
But the parameter hardcoded was in HK timezone and where in we installed for India.

1) Check the time zone parameter of the linux operating system

	[root@TESTRAC1 install]# cat /etc/sysconfig/clock
	ZONE="Asia/Kolkata"
	UTC=false
	[root@TESTRAC1 install]#
	

2) Chek the s_crsconfig_TESTRAC1_env.txt file values, make sure the OS and this Time Zone entries are same.

	[root@TESTRAC1 ~]# cd /u01/gi/oragrid/grid/11.2.0.3/crs/install

	[root@TESTRAC1 install]# cat s_crsconfig_TESTRAC1_env.txt
	### This file can be used to modify the NLS_LANG environment variable, which det
	### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JA
	### Do not modify this file except to change NLS_LANG, or under the direction of

	TZ=Asia/Hong_Kong
	NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
	TNS_ADMIN=
	ORACLE_BASE=
	[root@TESTRAC1 install]#

	NOTE: Here OS Time Zone is {Asia/Kolkata} and CRS timezone is {Asia/Hong_Kong}, now we need to update the CRS timezone as {Asia/Kolkata}
	

3) Take the backup of s_crsconfig_TESTRAC1_env.txt file and update the TZ value as OS timezone.

	[root@TESTRAC1 install]# cp s_crsconfig_TESTRAC1_env.txt s_crsconfig_TESTRAC1_env.txt_bkp
	root@TESTRAC1 install]# vi s_crsconfig_TESTRAC1_env.txt
	[root@TESTRAC1 install]# 
	[root@TESTRAC1 install]# cat s_crsconfig_TESTRAC1_env.txt
	### This file can be used to modify the NLS_LANG environment variable, which determines the charset to be used for messages.
	### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8
	### Do not modify this file except to change NLS_LANG, or under the direction of Oracle Support Services

	TZ=Asia/Kolkata 
	NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
	TNS_ADMIN=
	ORACLE_BASE=
	[root@TESTRAC1 install]#
	

4) Bounce the HAS on 2 nodes one after other

	[root@TESTRAC1 install]# cd /u01/gi/oragrid/grid/11.2.0.3/bin

	[root@TESTRAC1 bin]# ./crsctl stop has

	[root@TESTRAC1 bin]# ./crsctl start has
	CRS-4123: Oracle High Availability Services has been started.
	

Post Checks
On node 1
~~~~~~~~
Check the time of the OS and Database and make sure both are same.

	[oracle@TESTRAC1]/ # date
	Tue Apr 30 06:10:11 CEST 2013
	

	Thread 1 advanced to log sequence 489 (LGWR switch)
	  .....
	Fri Apr 26 06:11:50 2013
	...
	
	

Same steps for 2nd node as well.

Hope this helps.

-Thanks
Geek DBA