Re: What *really* happens at ALTER TBSP END BKP ?

From: Quarkman <quarkman_at_nowhere>
Date: Sat, 5 Jul 2003 07:21:32 +1000
Message-ID: <3f05efd4$0$727$afc38c87@news.optusnet.com.au>

"Quarkman" <quarkman_at_nowhere> wrote in message news:3f05ef4d$0$727$afc38c87_at_news.optusnet.com.au...
>
> "Daniel Nichols" <daniel.nichols_at_NOSPAMvirgin.net> wrote in message
> news:jlpbgvgcnvd0qeknpoi8q51s6spfb9fi9l_at_4ax.com...
> > Thanks Quarkman. The manual and other notes I've read implicitly imply
> > that datafiles are being written to when in hot backup mode but
> > nobody, for some reason, ever states this explicitly.
> >
> > You do say:
> > 1. some bytes are modified in the controlfile that indicates that CKPT
> > is
> > not to update the SCN in the header block of the datafiles for the
> > tablespace being backed up.
> >
> > The manual states that Oracle will stop recording checkpoint's to
> > datafiles but doesn't say that SCN's will not be written.
> >
> > The definitions I'm using are:
> >
> > System Change Number - SCN = A stamp that defines a committed version
> > of a database at a point in time.
> >
> > Checkpoint = A pointer indicating that all changes prior to the SCN
> > specified by a redo record have been written to the datafiles by DBWn.
> > Each redo record in the redo log describes a change or a set of atomic
> > changes to database blocks; a checkpoint for a redo entry confirms
> > that the changes described in previous redo entries have been written
> > to disk, not just to memory buffers. The background process CKPT
> > automatically records a checkpoint in the control file every three
> > seconds.
> >
> > Daniel.
>
> Hi Daniel:
>
> Try as I might, I can't make head nor tail of that definition of a
> checkpoint! I imagine it's come from Oracle's documentation somewhere, and
> therefore is correct, but it doesn't win any prizes for plain English,
does
> it?!!
>
> My 'working' definitions are:
>
> SCN : an ever-increasing sequence number that is used as a proxy for
> clock-time, and tells us the age of the database and its component
> datafiles.
> Checkpoint : the event where DBWR flushes dirty blocks to disk, and then
> tells CKPT to update the datafile headers with a new SCN (on the grounds
> that fresh writing to disk means the database has just got older).
>
> Some checkpoints are big (DBWR flushes all dirty blocks to disk when you
say
> 'shutdown normal', for example). Some are small (if you say 'alter
> tablespace X begin backup' then only the dirty buffers which belong to
that
> tablespace are flushed). Some are very small (if you say 'drop table Y',
> then only the dirty buffers belonging to that table are flushed). And the
> very small checkpoints don't involve asking CKPT to do its thing.
>
> Not totally precise, granted. But accurate enough.
>
> What your definition is getting at, and it's true enough, is that
> periodically a checkpoint marker is placed in the redo stream to indicate
> that a checkpoint event (ie, DBWR flushing) has taken place. That's so
> *instance* recoveries know where to start applying redo from -they don't
> need to apply everything in a log, only the redo that was generated after
> the time of the last checkpoint marker (because by definition only that
redo
> refers to dirty buffers which probably haven't been written to disk by
DBWR
> yet).
>
> But the checkpoint marker in the redo stream has no bearing on *media*
> recoveries (which is what you're gearing up to do by taking hot backups).
> Only the SCN in the header of the datafiles is looked at during those. You
> have to apply all redo generated since the last-known 'age' of the
datafile,
> and it's the SCN that tells us that age, as mentioned above.
>
> As a quick demo by way of answering your original question, try this:
>
> SQL> SELECT T.NAME, H.FILE#, H.CHECKPOINT_CHANGE#
> 2 FROM V$TABLESPACE T, V$DATAFILE_HEADER H
> 3 WHERE T.TS#=H.TS#;
>
> NAME FILE# CHECKPOINT_CHANGE#
> ------------------------------ ---------- ------------------
> SYSTEM 1 121923
> UNDOTBS 2 121923
> INDX 3 121923
> TOOLS 4 121923
> USERS 5 121923
>

My apologies. The above was captured at one time, and the rest was captured at another. The above should have read:

SQL> SELECT T.NAME, H.FILE#, H.CHECKPOINT_CHANGE# 2 FROM V$TABLESPACE T, V$DATAFILE_HEADER H 3 WHERE T.TS#=H.TS#; NAME FILE# CHECKPOINT_CHANGE#

------------------------------ ---------- ------------------

SYSTEM 1 261724
UNDOTBS 2 261724
INDX 3 261724
TOOLS 4 261724
USERS 5 261724 Then the rest of the demo makes sense!

~QM Received on Fri Jul 04 2003 - 16:21:32 CDT