5 Replies Latest reply: Aug 7, 2011 11:13 PM by Marc Fielding RSS

    Node Timestamp and Exadata

      We have a 3rd party application that is currently using triggers and a sequence to maintain transaction order. We would like to replace thie sequence logic with a timestamp. We are in the processing of migrating to Exada and had a question regarding timestamp sync on the different nodes. For Exadata, what is the maximum amount of time that two nodes will be out of sync. We have heard that it is 2 minutes nut have been unable to confirm.
        • 1. Re: Node Timestamp and Exadata
          During Exadata installation you have to provide the ipaddresses of 3 NTP servers and NTP is a must for Exadata. Assuming that your servers use NTp clock drift should never reach 2 minutes one properly synchronised.
          • 2. Re: Node Timestamp and Exadata
            Thanks, checked with Oracle support and according to them, check is done every 15 minutes to keep clocks in sync. This still indicates that there could be times when the clocks are out of sync so we will have to redesign our process.
            • 3. Re: Node Timestamp and Exadata
              Marc Fielding
              As robinsc says, all the cluster nodes run ntpd. ntpd maintains clock sync on a continuous basis. Although the exact sync distance can vary based on network congestion, CPU time starvation etc, I've rarely seen it above 0.1s in Exadata with nearby time source. You can find out your exact sync distance by running the "ntptrace" command on a database server.

              As I haven't seen the text of your SR with Oracle support I'm not sure where the 15 minute number comes from, but it may be related to the cluster time synchronization service (CTSS). CTSS generally functions as a back-up system to ntpd, and takes over if ntpd can't maintain time sync for any reason.

              Further references:

              • 4. Re: Node Timestamp and Exadata
                Thanks, below was the response from Oracle.

                Each machine has a different clock frequency and as a result a slightly different time drift. NTP computes this time drift every about 15 minutes, and stores this information in a "drift" file, it then adjusts the system clock based on this known drift as well as compares it to a given time-server the sys-admins sets up. This is the recommended approach.

                You can find the jitter value from below command for max time diffrence.

                [root@devdb01 ~]# ntpq -p
                remote refid st t when poll reach delay offset jitter
                *ds1000dsp01.ds. 2 u 78 1024 377 0.210 -7.371 12.191
                ds1000dsp02.ds. 3 u 117 1024 377 0.218 -0.176 7.882
                • 5. Re: Node Timestamp and Exadata
                  Marc Fielding
                  Ahhh. The ntp "drift" file is an estimate of systematic skew of a local system's clock. In other words, measuring if the local clock is consistently slow or fast, and by how much. In case the server is rebooted unexpectedly, the current drift information is written to disk every 15 minutes.

                  The clock sync itself, however, runs independently of the drift file write and doesn't wait 15 minutes between syncs.