Re: alter system checkpoint in hot backup script. Why

"Guy Dallaire" <gd-newsgroups_at_spamex.com> wrote in message news:ulUrb.1104$IK2.109764_at_news20.bellglobal.com...
> Hello,
>
> While visiting http://www.geocities.com/lydian_third/ (Which by the way
is
> a REALLY GOOD resource) I noticed in an hot backup article, that the
author
> is doing:
>
> alter tablespace ... begin backup;
> host copy ...
> alter tablespace ... end backup;
> alter system checkpoint; <- This puzzles me
>
> Why are we issuing a checkpoint ? In the article, is says that it is to
> force its header SCN back into synchronisation with the rest of the
> database. I don't see the point (no pun intended) of the checkpoint here.
> The author really knows what he's doing, I'm sure, but I'd like to know
why
> this is necessary/beneficial.
>
> Isn't oracle supposed to know that the datafile is out of backup mode and
> automatically start updating the header when needed ?

As the author, I take it this means I didn't write very clearly :-( It's a while since I wrote that article, and the site's not mine, so I don't read things on it as closely as I should.

Anyways: the point is this. Yes, if you wait long enough, then at the next checkpoint, the header of the just-backed-up file will be brought into synchronisation with the rest of the database all on its own and without any effort on your part. But one assumes that you are not checkpointing like crazy (otherwise performance would be none too good). Therefore, there is a window of, say, 5 minutes or more, where it is possible that your instance will crash, before the headers got resynchronised. At which point, when you start up the instance, it will moan about the data files not being in synch. and the database therefore needing recovery... which it actually doesn't, but you'll probably panic and start restoring and recovering anyway (and the last person to do pretty much exactly this unnecessary recovery of a database posted here recently: he botched it up and lost data as a result).

So a forced synchronisation with a spurious checkpoint is just a safety measure.

There is another aspect to the safety that I didn't mention in that paper. You've just taken a hot copy of a datafile. It is possible that whilst it was being copied, some transactions affected that file (thus generating redo). Were you to lose all your online redo logs, that backup copy of the datafile might well not be useful, because the redo required to get it consistent is now missing. By forcing a checkpoint, you force DBWR to flush buffers to disk. Anytime DBWR wants to write to disk, we kick LGWR off first. Therefore, the redo for the transactions that affected the data file being copied is now in the redo logs. Doesn't help much, I suppose, if you now lose all your online logs. But hopefully there'll be a log switch in there at some point, and then the redo needed to make your recent backup actually useful is safe.

In other words, by forcing a checkpoint, you go some way to trying to ensure that the redo needed to make the recent backup useable is safe on disk, and not just floating around the instance. And once it's on disk, it's likely to be archived shortly (lots of people force a log switch at the end of the entire backup for precisely that same reason).

Personally, I tend not to bother with the checkpoint myself. There'll be another one along shortly anyway. But for the terminally paranoid, it makes some sense.

In short, yes, Oracle will update the header "when needed" all of its own accord. But your backup might be useless if a failure intervenes before it has a chance to do so.

Regards
HJR Received on Mon Nov 10 2003 - 17:30:40 CST