TimeZone issue

All posts relating to Oracle database administration.

Moderator: Tim...

TimeZone issue

Postby Ashok » Mon Mar 26, 2012 8:51 pm

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
Ashok
Member
 
Posts: 45
Joined: Mon Dec 12, 2005 4:23 am

Re: TimeZone issue

Postby Tim... » Tue Mar 27, 2012 9:22 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: TimeZone issue

Postby Ashok » Tue Mar 27, 2012 11:22 am

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
Ashok
Member
 
Posts: 45
Joined: Mon Dec 12, 2005 4:23 am

Re: TimeZone issue

Postby Tim... » Tue Mar 27, 2012 12:00 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: TimeZone issue

Postby Ashok » Tue Mar 27, 2012 1:18 pm

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
Member
 
Posts: 45
Joined: Mon Dec 12, 2005 4:23 am

Re: TimeZone issue

Postby Ashok » Tue Mar 27, 2012 1:51 pm

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
Member
 
Posts: 45
Joined: Mon Dec 12, 2005 4:23 am

Re: TimeZone issue

Postby Ashok » Tue Mar 27, 2012 7:56 pm

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

Any other suggestion ,please?

-ashok
Ashok
Member
 
Posts: 45
Joined: Mon Dec 12, 2005 4:23 am

Re: TimeZone issue

Postby Tim... » Wed Mar 28, 2012 7:35 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17937
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: TimeZone issue

Postby Ashok » Wed Mar 28, 2012 8:30 pm

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
Ashok
Member
 
Posts: 45
Joined: Mon Dec 12, 2005 4:23 am


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron