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

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Fri, 26 Oct 2001 09:50:15 -0700
Message-ID: <F001.003B5D76.20011026095020@fatcity.com>

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
> > Rick_Stephenson_at_ovid.com
> > Ovid Technologies, Inc.
> > 9350 South 150 East, Suite 300
> > Sandy, UT 84070
> > (801) 304-3000 ext. 2593

http://metalink.oracle.com

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: ONLY AFFECTS TIME-BASED RECOVERY

WARNINGS:

None.

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

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.

Note:
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).

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

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