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

From: Quarkman <quarkman_at_nowhere>
Date: Sat, 5 Jul 2003 07:19:17 +1000
Message-ID: <3f05ef4d$0$727$afc38c87@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 Note all files have the same SCN (otherwise called a Checkpoint Change Number in the relevant view).

SQL>ALTER TABLESPACE USERS BEGIN BACKUP; So one tablespace has been put into hot backup mode.

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 261726 Notice that USERS has actually gotten ahead of the rest of the database. The 'begin backup' has caused a small checkpoint to take place, meaning DBWR's just flushed to that one tablespace, and CKPT has updated the datafile header for that one tablespace's datafile.

Now do some transactional activity:

INSERT INTO SCOTT.EMP VALUES (1,'SMITH',500); COMMIT; Now cause a major checkpoint to occur:

SQL> ALTER SYSTEM CHECKPOINT;
SYSTEM ALTERED. And a final check on the SCNs:

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 261740
UNDOTBS 2 261740
INDX 3 261740
TOOLS 4 261740
USERS 5 261726 Now it's USERS that is stuck in a time-warp, and the other tablespaces which have (apparently) advanced ahead of it. Actually, scott.emp is stored in USERS, and a bit of block dumping will reveal that the newly-inserted record is genuinely stored on disk, so the contents of the relevant datafile are actually at 261740 as well... but it's SCN is frozen.

~QM Received on Fri Jul 04 2003 - 16:19:17 CDT