This discussion is archived
11 Replies Latest reply: Jan 29, 2013 2:07 AM by 987766 RSS

sysdate is wrong on one instance

555356 Newbie
Currently Being Moderated
I have a RAC with two instances. SYSDATE returns correct on one instance (EDT) but wrong on the other (PDT) with 3 hour difference. The two servers time are the same (EDT) and they are located in Toronto. If I select sysdate directly on the 2nd instance server, it's also correct. Only if I connect to the 2nd instance remotely, then the time returns wrong. Any thought?
  • 1. Re: sysdate is wrong on one instance
    sb92075 Guru
    Currently Being Moderated
    Oracle obtains date/time from OS
  • 2. Re: sysdate is wrong on one instance
    555356 Newbie
    Currently Being Moderated
    I understand but the OS time is the same.
  • 3. Re: sysdate is wrong on one instance
    sb92075 Guru
    Currently Being Moderated
    If I select sysdate directly on the 2nd instance server, it's also correct.
    Only if I connect to the 2nd instance remotely, then the time returns wrong.
    Then why is this an Oracle problem & not a remote client problem?

    using CUT & PASTE will allow us to see what you actually did & how Oracle responds.
  • 4. Re: sysdate is wrong on one instance
    555356 Newbie
    Currently Being Moderated
    Following sessions are from same app server w06apu004



    SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;



    TO_CHAR(SYSDATE,'D

    ------------------

    11-SEP-09 11:12:17



    SQL> select instance_name from v$instance;



    INSTANCE_NAME

    ----------------

    W06P2



    SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;



    TO_CHAR(SYSDATE,'D

    ------------------

    11-SEP-09 11:13:10



    SQL> select instance_name from v$instance;



    INSTANCE_NAME

    ----------------

    W06P2



    SQL>



    SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;



    TO_CHAR(SYSDATE,'D

    ------------------

    11-SEP-09 14:11:42



    SQL> select instance_name from v$instance;



    INSTANCE_NAME

    ----------------

    W06P1



    SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;



    TO_CHAR(SYSDATE,'D

    ------------------

    11-SEP-09 14:13:04



    SQL> select instance_name from v$instance;



    INSTANCE_NAME

    ----------------

    W06P1
  • 5. Re: sysdate is wrong on one instance
    jgarry Guru
    Currently Being Moderated
    z3k2 wrote:
    I have a RAC with two instances. SYSDATE returns correct on one instance (EDT) but wrong on the other (PDT) with 3 hour difference. The two servers time are the same (EDT) and they are located in Toronto. If I select sysdate directly on the 2nd instance server, it's also correct. Only if I connect to the 2nd instance remotely, then the time returns wrong. Any thought?
    Some thoughts:

    1. What version are you on?

    2. What exact syntax are you using to select the sysdate?

    3. What kind of client are you using?

    4. What timezone settings are on the client?

    5. What timezone settings are on the environment you are connecting to?

    6. What timezone settings are on the environments that work?

    7. Has this only been during daylight time or very recently?

    I suspect researching 4 or 5 will answer your question. 1 is also possible if you have screwed up patching java only in your client or connection environment on certain 10g versions and are using java (this shows up in EM and you can probably find descriptions of it by googling, though I think it is only one hour off).

    I once saw a strange version of this problem, only certain connections were mountain time. It turned out to be one certain server environment using csh, which defaults to mountain time, while everything else was ksh with TZ set or propagating Pacific time.
  • 6. Re: sysdate is wrong on one instance
    555356 Newbie
    Currently Being Moderated
    10.2.0.3
    tz settings are the same on both nodes.
    It only happens recently and there is nothing changed.
  • 7. Re: sysdate is wrong on one instance
    181444 Expert
    Currently Being Moderated
    Usually when you say nothing has changed that means something was changed but you are unaware of what someone did.

    I am going to suggest that when connected to each session pull the NLS parameters from v$nls_parameters and compare them. Be sure to post them.

    HTH -- Mark D Powell --

    PS Also check at the OS level if any time of Oracle environment setting script is used plus make sure to check the default database time zone.

    Edited by: MarkDPowell on Sep 14, 2009 6:36 PM
  • 8. Re: sysdate is wrong on one instance
    555356 Newbie
    Currently Being Moderated
    the problem has been fixed by rebooting the node 2 server. but I have no idea why it's like that.
  • 9. Re: sysdate is wrong on one instance
    Franck Pachot Journeyer
    Currently Being Moderated
    Hi,
    If OS time was the same, then the TZ environment variable set when the instance was started was probably different.
    Maybe your instance was started from a shell that had a bad TZ variable.

    If problem happen again, you can check it with:

    connect / as sysdba
    var s varchar2(4000);
    exec dbms_system.get_env('TZ', :s);
    print s;


    Regards,
    Franck.
  • 10. Re: sysdate is wrong on one instance
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    MOS Doc 368539.1 (Incorrect Sysdate returned in a RAC instance when connected through a listener) has a possible explanation

    HTH
    Srini
  • 11. Re: sysdate is wrong on one instance
    987766 Newbie
    Currently Being Moderated
    Franck Pachot wrote:
    Hi,
    If OS time was the same, then the TZ environment variable set when the instance was started was probably different.
    Maybe your instance was started from a shell that had a bad TZ variable.

    If problem happen again, you can check it with:

    connect / as sysdba
    var s varchar2(4000);
    exec dbms_system.get_env('TZ', :s);
    print s;


    Regards,
    Franck.
    I tried everything which were suggested in this post as well as from other websites. Here is the scenario:

    1. Installed oracle server on a computer in SGT.
    2. 'SELECT TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss') FROM DUAL' from sqlplus command prompt returned the right time.
    3. Changed the system timezone to IST and updated the time.
    4. 'SELECT TO_CHAR(SYSDATE,'dd-mm-yyyy hh24:mi:ss') FROM DUAL' from sqlplus command prompt returned the right time but connecting from SQLDeveloper or Oracle JDBC and executing the statement gave time 2:30 hours ahead.
    5. Tried selecting sessiontimezone and returned same from all.
    6. Tried executing the following and output was "Asia/Kolkata"

    connect / as sysdba
    var s varchar2(4000);
    exec dbms_system.get_env('TZ', :s);
    print s;

    7. SQL> select dbtimezone from dual;

    DBTIME
    ------
    +00:00

    8. Modified /etc/sysconfig/clock as mentioned in [http://davidalejomarcos.wordpress.com/2011/02/25/sysdate-returns-wrong-time-time-with-timezone/]
    9. Referring to AZ's above post, did a poweroff of the server and started the listener and service and Worked!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points