In an Oracle cluster will sysdate always return a consistent answer?

Question

In an Oracle cluster (more than one machine co-operating to serve one database) will the "sysdate" function always return a consistent answer? Even if the servers' Operating System clock reports inconsistent values?

Answer 1

Use NTP to sync time across all your servers (Oracle and otherwise) and ensure that doesn't happen. Inconsistent system clocks are a recipe for disaster.

I would guess that sysdate would return inconsistent results in the scenario you describe.

Answer 2

I spent a (little bit) of time looking for an answer to this, but couldn't find one, but, given that sysdate is just returning the date/time from the operating system, I suspect dmitriy is correct.

Answer 3

I would strongly suspect that SYSDATE is OS-linked too. Be very watchful of the reason why you need to use it. If have any logic which implements incremental tracking of events (e.g. you're doing incremental exports) and you must ensure no items left out as well as no duplication, base the tracking on sequential IDs rather than SYSDATE.

In fact, this is true even for non-cluster systems, as SYSDATE can sometimes change (time savings, sysadmin errors...).

Answer 4

SYSDATE is node OS related; if it was guaranteed correct across the cluster, then the nodes would have to sync every time you called SYSDATE. In a clustered environment, ordered sequences are expensive; best avoided if at all possible. An ordered sequence will guarantee you uniqueness and order - however, you could still get gaps if processing fails after selecting from the sequence and before committing the transaction.

We use a few workarounds:

1. Generally, sequences are set to unordered with large cache sizes (25,000) to reduce inter cluster communication.
2. Use NTP to time-sync the nodes (they can still be incorrect,+/- nanoseconds, so you cannot rely on this)
3. For auditing style logs, we use systimestamp (timestamp(6)) as a unique identifier - and live with the fact that it is possible (although extremely unlikely) that the logs could appear out of order (this is also possible with normal processing, depending on when Commits occur)

4. Where an ordered sequence is required, and there can be gaps - use an ordered sequence (try to avoid in a clustered environment as "cache" doesn't help)

5. Where an ordered sequence is required - but there can be no gaps - we have our own sequences table, lock the record, get the number and then keep the record locked until the user commits; this will cause everyone else trying to get the same sequence to wait until the user's transaction is fully committed - avoid doing this if at all possible.