Oracle time stamp showing wrong timezone connecting TNS remote

Oracle time stamp showing wrong timezone connecting TNS remote

Time stamp problem:-

sqlplus system/****@ORCL

SQL>alter session set nls_date_format=’dd-mm-yyyy hh:mi:ss’;

SQL>select instance_name from v$instance;

SQL>select   sysdate,   current_timestamp,   systimestamp,   localtimestamp from   dual;

The above query will show different time .

How to Fix:-

1.1                               Stop the apps and DB cluster/HAS.

su – root

cd /orasw/app/grid/product/11.2.0/grid/bin

./crsctl stop crs –f

su – grid

cd /orasw/app/grid/product/11.2.0/grid/bin
./crsctl stop has

1.2                               Correct the TZ setting

Correct the TZ setting in the file $GRID_HOME/crs/install/s_crsconfig_<hostname>_env.txt.

Please comment the old TZ value(#TZ=GMT-08:00) in s_crsconfig_<hostname>_env.txt.

Add new TZ value(TZ=US/Pacific).Do not modify any other value.
su – root
cd /orasw/app/grid/product/11.2.0/grid/crs/install/
vi s_crsconfig_<hostname>_env.txt
TZ=US/Pacific
#TZ=GMT-08:00

1.3                               Restart the cluster and DB/HAS

 

su – root
cd /orasw/app/grid/product/11.2.0/grid/bin
./crsctl start crs

For single instance HAS

su – grid

cd /orasw/app/grid/product/11.2.0/grid/bin
./crsctl start has

Checking timestamp now:-

sqlplus system/****@ORCL

SQL>alter session set nls_date_format=’dd-mm-yyyy hh:mi:ss’;

SQL>select instance_name from v$instance;

SQL>select   sysdate,   current_timestamp,   systimestamp,   localtimestamp from   dual;

The above query will show same time .