TimeZone issue

[Ashok]

Hi
I am having issue in displaying right sysdate and systimestamp. We want our DBs to have GMT timezone.OS time is set correctly.
When i query in the DB Server .it gives right value ;but when i query from any tools (toad/sqlpplus) in the client side: it gives wrong timestamp value .

Can you please guide me on this issue?

From Server:

Code: Select all

select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
26-MAR-12 08.49.10.610056 PM +00:00

SQL>SELECT SESSIONTIMEZONE,dbtimezone FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
DBTIME
------
+00:00
+00:00



From TOAD in client side:

Code: Select all

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
27-MAR-12 01.50.25.838052 AM +05:00
SQL> SELECT SESSIONTIMEZONE,dbtimezone FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
DBTIME
------
-04:00
+00:00


-Ashok

[Tim...]

Hi.

Does your client PC have a different timezone setting to your server? If so, then this is a perfectly valid result. In the UK we've moved to daylight savings time, so I now get this:

Code: Select all

sys@db11g> select sessiontimezone, dbtimezone from dual;

SESSIONTIMEZONE                                                             DBTIME
--------------------------------------------------------------------------- ------
+01:00                                                                      +00:00

1 row selected.

sys@db11g>

The database was set to use GMT, but I am mow in DST so there is a discrepancy of 1 hour between my time and GMT.

The client software can tell the timezone from a number of places. Added to that, there are a number of ways to set it manually.

http://docs.oracle.com/cd/E11882_01/ser ... SQLRF53062

Cheers

Tim...

[Ashok]

Thanks Tim.
But if you look at my query results: I am Eastern Time Zone in USA. It shows in server correct time ;but from client it shows GMT+5. From the same tool (TOAD), when i queried another 11g database in another server, that shows both server and client side correct time in GMT. But when i query the DB in the new server , it shows correctly in server ;but not from DB. If it's issue in client software, it should show wrong in all other DBs if i am right. But that's not the case for me. Sorry !
-ashok

[Tim...]

Hi.

Yes. I would expect some consistency.

Do you get the same result when you query from your client PC using SQL*Plus, rather than TOAD?

Here is the MOS FAQ on time zones:

https://support.oracle.com/CSP/main/art ... d=340512.1

Cheers

Tim...

[Ashok]

Hi Tim,
Yes. I queried both from SQLplus and TOAD and other tools like SQLFLY (sqlfly.com) as well. I get the same results.
It's totally confusing

Thanks
Ashok

[Ashok]

May be i will try to set the environment variable TZ in the server as UTC or GMT and restart the listener.

Thanks
Ashok

[Ashok]

It did not help either. Setting TZ environment variable and restarted the lsitener

Any other suggestion ,please?

-ashok

[Tim...]

Hi.

You could set the session leve time_zone parameter. If this works ok you can do it with a logon trigger. Not ideal, but it would force the setting.

Cheers

Tim...

[Ashok]

Tim,

after so much struggle,it's fixed

Solution:

1. We have to update the TZ variable as TZ=GMT on each node on these 2 files : s_crsconfig_<hostname>_env.txt and crsconfig_params under /u01/app/11.2.0/grid/crs/install/ i.e $GRID_HOME/crs/install
2. Perform a quick restart the HAS daemon:

# /etc/init.d/ohasd stop
# /etc/init.d/ohasd start

3. crsctl stop cluster
4.crsctl start cluster

Thanks
Ashok