11 Replies Latest reply: Jan 29, 2013 4:07 AM by 987766 RSS

    sysdate is wrong on one instance

    555356
      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
          Oracle obtains date/time from OS
          • 2. Re: sysdate is wrong on one instance
            555356
            I understand but the OS time is the same.
            • 3. Re: sysdate is wrong on one instance
              sb92075
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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!!!