Re: SCN tied to the time stamp while doing recovery

From: <fitzjarrell_at_cox.net>
Date: 30 Jan 2005 13:36:42 -0800
Message-ID: <1107121002.957612.250620@f14g2000cwb.googlegroups.com>

lambu999_at_yahoo.com wrote:
> Hi all,
>
> I was reading Backup and Recovery Handbook 8i (By Rama Velpuri) and
> I came across an interesting scenario in the case study.
>
> SCENARIO:
> ----------
>
> Time change from 2am in the morning back to 1am during day light
> savings time change. In the database, a mistake was made at 1:55am
> *after* the day light savings adjustment is made. Or even a
crash/media
> failure can occur around that time.
>
>
> When trying to recover we could easily end up recovering till the
> 1:54am *before* the time change. In other words we lose more than an
> hour's worth of transactions. This happens because apparently the SCN
> is tied to the time stamp in the log files.
>
> 1) Has anything changed in 9i to prevent such a scenario?
> 2) How do large companies that have hundreds/thousands of
> transactions per hour handle this situation currently? Do they really
> lose all the data?
> 3) Has any one really come across this scenario or is it too remote
> to bother about? (Murphy wouldnt think so)
> Thanks a lot to every one, as always,
> Ram.

It has nothing to do with the SCN, and everything to do with the time. Rama is describing time-based recovery, not change-based recovery. Recovering based upon the SCN will never have that problem, as explained in Note 1013279.6, from Metalink (quoted here in its entirety for those who haven't Metalink access):

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

As such, it doesn't matter if one SCN was written at 01:54 AM PRIOR to the time change and another SCN was written at 01:54 AM AFTER the time change, the two SCNs are completely different. Using change-based recovery will not produce such a result in that situation. David Fitzjarrell Received on Sun Jan 30 2005 - 15:36:42 CST