Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re:Oracle and Daylight savings time.

Re:Oracle and Daylight savings time.

From: Ashish Shah <>
Date: Fri, 6 Oct 2000 07:22:38 -0700 (PDT)
Message-Id: <>

This I found from metalink.... Hope it helps..

Problem Description:

When the system clock is changed, forward in spring or back in fall, does this
have any potentially harmful effects for the database?  

Search Words: time date change timestamp saving




Solution Description:

Because Oracle tracks the sequence of events in the database using the System
Commit Number (SCN), changing the system clock for daylight savings time will
have no effect on database operation. The only point where the time change
can have potentially harmful effect is during time-based recovery.  

Time based recovery requires checking of the actual time the transaction was
recorded in the logfile. Every log record has a time stamp associated with
it. If the system manager for some reason changes the system clock, Oracle
Support recommends shutting down the database and taking a cold backup ( or a
hot backup if preferred). If for some reason a dba has to go back to a backup
which was taken prior to the system clock change and rollforward, recovery
works just fine except for time based recovery (Note that time based recovery
works fine if the system clock is moved forward in time). When the system
clock is changed backwards, its possible that there could be two redo records
with the same time stamp. If time based recovery is done in this scenario,
since ORACLE applies only redo entries that were written prior to a specified
time, ecovery stops when it finds the first redo record which has that
specified time.  

The following example will illustrate the problem:    

3pm 4pm 4.15 4.30 5pm-->4pm 4.16pm 4.30 5pm

cold/hot      T1      T2      T3     clock       T4   
 T5      T6 
backup                               change 


A cold backup was taken at 3pm. A transaction T1 was done at 4pm. So the redo
record has a time stamp of 4pm. Transaction T2 was done at 4.15pm and
transaction T3 at 4.30pm.  

At 5pm the system clock was changed backward, one hour. 16 minutes later (i.e;
at time 4.16pm) transaction T4 was done. Later on, the disk crashed and we
lost the databasefiles.  

Any recovery done to recover data upto certain time in the range R1 will not
recover the data in the range R1 but recovers only until corresponding time
before the system clock change.  

Say if the DBA decides to recover until T5(4:30pm) which is in time range R1.
DBA restores the backup from 3pm and does a recovery until 4:30 thinking it
would recover until T5. Actually the recovery is done until T3 and not T5. So
all the trasanctions entered after T3 will be lost. Recovery beyond 5pm or say
beyond range R1 should not cause any lose of data.  

  Although specifying a time in the interval 'clock change' to 'T6' will
  result in incomplete recovery to the first occurrence of the specified
  time it is still possible to recover to any point in time in this range
  using SCN based recovery (using the RECOVER UNTIL CHANGE clause).

  Following a time change where the clocks go back in time there is a
  window where INCOMPLETE recovery using TIME BASED recovery is affected.
  Recovery to a point in time within this window can be achieved using
  SCN (or CHANGE) based recovery.
  Recovery to a point in time after this window requires no special action.

Received on Fri Oct 06 2000 - 09:22:38 CDT

Original text of this message