Thanks to all for your input. I had not thought about
this problem before until it came up in conversation
the other day. I can see where this would be a
problem for point-in-time recovery. I'm looking at my
options now, what I will do is get a hot backup right
after the time change just to play it safe.
Thanks, Mike
- Ashish Shah <ar_shah_at_yahoo.com> wrote:
> 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: 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.
> .
>
> --- dgoulet_at_vicr.com wrote:
> > Mike,
> >
> > Nothing, all of my instances have functioned
> > through both the spring and
> > fall time changes without a burp. Now if your
> > changing the timezone, or the
> > actual clock setting (especially backwards) that's
> a
> > different matter.
> >
> > Dick Goulet
> >
> > ____________________Reply
> > Separator____________________
> > Author: Michael Parry <parrymd_at_yahoo.com>
> > Date: 10/5/00 7:56 AM
> >
> > Oh wise list, I've read that you should shutdown
> the
> > database and get a good backup for a time change.
> > What does the rest of the world do? What do 24x7
> > shops do? What do you recommend?
> >
> > Thanks, Mike
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! Photos - 35mm Quality Prints, Now Get 15
> > Free!
> > http://photos.yahoo.com/
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Michael Parry
> > INET: parrymd_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author:
> > INET: dgoulet_at_vicr.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX:
> > (858) 538-5051
> > San Diego, California -- Public Internet
> > access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
Received on Fri Oct 06 2000 - 10:50:32 CDT
