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,
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