NTP Time sync difference in microseconds between RAC nodes.

[pandora_1]

Hi,

We have two Oracle RAC nodes running hpux11.23 on rp7420, NTP is configured with linux NTP server. Sync in minutes and seconds are looking fine but in Oracle systimestamp there is microseconds difference between two instances running on RAC.

This is causes issue with external application fetching data with timestamp.

can we overcome this issue from hpux so that there is no difference in sync in microsecond also on all RAC nodes.

Thanks in advance.

[Bill Hassell]

128 milliseconds is the threshold for sync accuracy in NTP although with a stable path end-to-end, the servers should be accurate to less than 1ms. Your application is seriously flawed to require microsecond accuracy between different servers. You will have microsecond delays going through longer or shorter cables, through switches, and non-deterministic delays through routers.

What is the exact specification required for sync accuracy? Is it 100us, or less than 10us. 1us is foolish since processing delays in the computer make the actual time clock value very difficult to measure to 1us microsecond accuracy. The Oracle system timestamp does not a deterministic reporting mechanism so you can expect delays simply requesting the current value (with microsecond accuracy). You know that if someone opens the door on a rack of servers that the airflow will be affected and the internal temperature of the system clocks will be affected, right? NTP will correct timing but is it very careful to do things slowly and avoid jumps in time.

You can eliminate all the uncontrollable delays by wiring all the clusters together with the same length of crossover LAN cables (no switches, no routers) and designate one of the RAC servers as the NTP server for all the other servers in the cluster. Be sure to use IP addresses and not hostnames to eliminate unpredictable DNS and ARP response times. Then watch ntpq -p on each of the servers looking for 0.00 dispersion and offset.

If one of the servers is rebooted, you may require a convergence time of several minutes to hours before the system reaches microsecond accuracy. And to make sure the systems don't drift even slightly, the cluster may have to be partially shutdown if someone opens a cabinet door to look at cables. You'll need to design the data center to never allow temperature variations around the RAC systems.

[Steven E. Protter]

Shalom,

If the HP-UX servers are using the same time source, there should be no problem. Time differences of less than an hour should sync up accurately within a short time period.

I believe this is an Oracle RAC issue, not a OS issue.

SEP

[Michael Steele_2]

Hi

I am reading this as I believe SEP is reading this, "...Sync in minutes and seconds are looking fine but in Oracle systimestamp there is microseconds difference between two instances running on RAC..."

...that time sync in hp-ux on the HP servers are fine, but time sync on the same servers for Oracle are a problem.

Question: Is this only an Oracle problem or does it exist at the HP-UX layer?

[rick jones]

I am not an NTP expert, but based on my perusing the posts in comp.protocols.time.ntp, getting time sync down to small numbers of microseconds can be a non-trivial thing.

There is virtually *no* way that you will have the two systems with the very same idea of time down to small numbers of microseconds.

The jitter introduced by NIC interrupt coalescing on both the HP-UX and Linux system alone probably precludes microsecond-level synchronization.

How often does this fetching take place, and do you *really* need timestamps down to the microsecond???

[Kapil Jha]

guys...hold on please,
as far as I know oracle does not have any clock in it, it takes the timestamp from UNIX server only.
Am i right?

And how did you get that u have ms time difference in 2 nodes.

BR,
Kapil+

[Michael Steele_2]

microsecond = 10 ** -6

And you want to sync an hpux and linux server down to 10 to the minus 6th of one second.

Is that correct?

[pandora_1]

Hi ,

10:00:01:000001, the last six digit is microsecond and its not negative value.
10â 3 s ms millisecond
10â 6 s µs microsecond

As per Bill, One of Node1 in RAC is made as NTP Master Server (which syncs from a Linux Server)and Node2(Slave) in RAC I have configured NTP to fetch time from NODE1. I would need to analyse the difference now. Hope this setup is correct, please advise. I have pasted the outpur of ntpq -p from two nodes

NODE1:
==============================================================================
*rcspdb01. hodc001. n 3 u 53 64 377 0.17 -1.483 2.03
remote refid st t when poll reach delay offset disp
==============================================================================
*rcspdb01. hodc001. n 3 u 55 64 377 0.17 -1.483 2.03
remote refid st t when poll reach delay offset disp
==============================================================================
*rcspdb01. hodc001. n 3 u 57 64 377 0.17 -1.483 2.03
remote refid st t when poll reach delay offset disp
==============================================================================
*rcspdb01. hodc001. n 3 u 59 64 377 0.17 -1.483 2.03

----------
NODE2:

==============================================================================
*docbpdb1 rcspdb01. 4 u 18 64 377 0.23 -0.754 0.95
remote refid st t when poll reach delay offset disp
==============================================================================
*docbpdb1 rcspdb01. 4 u 20 64 377 0.23 -0.754 0.95
remote refid st t when poll reach delay offset disp
==============================================================================
*docbpdb1 rcspdb01. 4 u 22 64 377 0.23 -0.754 0.95
remote refid st t when poll reach delay offset disp
==============================================================================
*docbpdb1 rcspdb01. 4 u 24 64 377 0.23 -0.754 0.95

[Andrew C Fieldsend]

According to the man page for HP-UX 11.31, the default resolution for all time-related system calls on HP-UX is 10ms, unless High Resolution Timer Support has been enabled in the kernel.

If you haven't enabled this (it is disabled by default, and didn't exist prior to 11.31), then I suspect that the microsecond differences you are seeing are not real time differences.

Also, the Wikipedia article on NTP (not necessarily the best reference, but the easiest to find) indicates that NTPv4 "can usually maintain time to within 10 milliseconds (1/100 s) over the public Internet, and can achieve accuracies of 200 microseconds (1/5000 s) or better in local area networks under ideal conditions". Two nodes of a cluster is probably close to ideal conditions, but would still only expect to maintain accuracy to a few hundred microseconds.

[Michael Steele_2]

Exactly. The offsets you are seeking to overcome are insignificant.