Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ORACLE-L Digest -- Volume 2001, Number 299

Re: ORACLE-L Digest -- Volume 2001, Number 299

From: Eric D. Pierce <>
Date: Fri, 26 Oct 2001 09:50:15 -0700
Message-ID: <>

sorry if this is a duplicate

ORACLE-L Digest -- Volume 2001, Number 299
> > -----Original Message-----
> > Sent: Thursday, October 25, 2001 10:25 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Are there any issues with Oracle that I should know about when the
> > clocks roll back this weekend?
> >
> > Rick Stephenson
> > Oracle/Sybase Database Administrator
> >
> > Ovid Technologies, Inc.
> > 9350 South 150 East, Suite 300
> > Sandy, UT 84070
> > (801) 304-3000 ext. 2593

Doc ID: Note:1013279.6
Creation Date: 18-OCT-1995
Last Revision Date: 23-OCT-1998

 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.  .

   Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and    Terms of Use.

---end--- Received on Fri Oct 26 2001 - 11:50:15 CDT

Original text of this message