Oracle Database Timezone Issue

Problem:

Post changes in Linux system timezone (in /etc/sysconfig/clock file ) from ZONE="America/New York" to ZONE="Asia/Kolkata" systimetamp over the DB link or TNS alias started reflecting old/wrong timezone 

sqlplus <username>@<TNS_Alias>/<password>;

SQL> select systimestamp,current_timestamp from dual;

SYSTIMESTAMP                            CURRENT_TIMESTAMP
-----------------------------------     -------------------------------------------
22-05-13 06:11:17.900646 AM -05:30      22-05-13 05:11:17.900654 PM +05:30 



Here , +05:30 was the correct time-stamp for Timezone "Asia/Kolkata"

Analysis:

It was identified that time zone of the database server was not matching with one of the Data guard setup in Linux zone configuration file /etc/sysconfig/clock

Required ZONE="Asia/Kolkata" 
But found ZONE="America/New York"

Proceeding further to match up the zones on all OS as 
"Asia/Kolkata"  DB server zone modification was done in Linux configuration file.


However , It was inappropriate method for zone modification in DB server which lead to differences in timezone at db level  as mentioned in Problem:

Also timezone difference was not observed when connected from sys/system as sysdba or any other DB user from local db server.

It was only observed for any user connections over the DB link or TNS.

Solution:

With reference to below MOS Note,


How To Change Timezone for 11gR2 Grid Infrastructure [ID 1209444.1] 

$GRID_HOME/crs/install/s_crsconfig_<hostname>_env.txt

has been modified as
  
TZ=GMT+05:30 
to 
TZ=Asia/Kolkata
 

Followed by all cluster services restarts , Resolved the issue 

No comments:

Post a Comment