alertlog timestamp different with system date in oracle rac

1.) There were no time different shown when we connect as os authentication method.

2.) When we connect the database using Listener time different shown as below

Os configured with time zone MYT.
Oracle Cluster configured with GMT+08:00.

SYSDATE CURRENT_TIMESTAMP SYSTIMESTAMP LOCALTIMESTAMP
———————————————————————————————————————————————————————————
08-06-2015 04:17:13 08-JUN-15 08.17.13.904725 PM +08:00 08-JUN-15 04.17.13.904724 AM -08:00 08-JUN-15 08.17.13.904725 PM

[oracle@kl-aaa-db-01 ~]$ cd /u01/11.2.0/grid/crs/install/
[oracle@kl-aaa-db-01 install]$ cat s_crsconfig_kl-aaa-db-01_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=GMT+08:00
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
TNS_ADMIN=
ORACLE_BASE=

Analysis is that there are 4 hours different between the database and operating system.

MOP Summary
Blow action item on Node2 and Confirm if succeed then follow same guideline on Node1.

Method-1

#Backup the database

#Login su – oracle and check pre activity status
connect the database using listener e.g. sqlplus sys/****@orcl[1-2] as sysdba

alter session set nls_date_format='dd-mm-yyyy hh:mi:ss';
select sysdate, current_timestamp, systimestamp, localtimestamp from dual;

./srvctl getenv listener -l LISTENER -t 'TZ'

#Login root
su – root

#Stop the local Custer resource
/u01/11.2.0/grid/bin/crsctl check crs
/u01/11.2.0/grid/bin/crsctl stop crs

#Comment the line having defined the time zone “TZ=GMT+08:00”.
cd /u01/11.2.0/grid/crs/install/
vi s_crsconfig_kl-aaa-db-01_env.txt

#Start the local Custer resource
/u01/11.2.0/grid/bin/crsctl start crs
/u01/11.2.0/grid/bin/crsctl check crs

Method-2

srvctl setenv listener -l listener -t 'TZ=Asia/Kolkata'
srvctl setenv database -d orac -t 'TZ=Asia/Kolkata'

srvctl stop listener
srvctl stop database -c orac
#Login su – oracle and check post activity status
connect the database using listener e.g. sqlplus sys/****@orcl[1-2] as sysdba

alter session set nls_date_format='dd-mm-yyyy hh:mi:ss';
select sysdate, current_timestamp, systimestamp, localtimestamp from dual;

Oracle MetaLink Note: How_to_Change_Timezone_for_11gR2_GI_1209444
http://www.oracle-ckpt.com/2013/06/20/alert-log-timestamp-different-with-system-date-in-rac/
https://minersoracleblog.wordpress.com/2014/09/19/changing-timezone-settings-on-oracle-database-servers/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.