Re: backup and recovery procedures / resetlog

"Bruno Jargot" <see_at_reply.to.invalid> wrote in message news:j6u3iug99ovph04iapmhrtd0fbv994c61u_at_4ax.com...
> I'm currently writing procedures for the backup and recovery of
> databases.
>
> I read carefully the excellent paper of Howard J. Rogers but I've some
> questions.
>
> 1- Before doing a incomplete recovery, Howard strongly recommends a
> backup in order to be able to redo the recovery if necessary.
>
> I don't understand why I could not redo the recovery.
>

Because you issue 'resetlogs'. amd that puts the control file into a new incarnation. To repeat the recovery, you'd want to restore all the datafiles -which are now from a different incarnation than the controlfile. You therefore can't re-perform the recovery.

However, if you take a precautionary backup before the recovery, then to repeat the recovery, you need only restore the control file and the online redo logs from the precautionary backup, and all the datafiles from the proper backup -at which point your database is back exactly where it was before you did the first recovery... so naturally, you can repeat it.

> I've a backup control file (trace) with each backup.

Now that's fine. It means you can re-construct a controlfile of the appropriate incarnation, which will allow something resembling the first recovery to take place. However, since the controlfile is rarely more than a few megabytes in size, it seems an awful lot of trouble to go to.

It also doesn't address the redo log issue. The redo logs you have after a failed incomplete recovery are also in a new incarnation, so they are useless for a repeat performance. You haven't got a backup of those, so you can't restore them to the way they were. Now that's not a terminal problem: the 'resetlogs' command will actually create you a new set of functioning logs. BUT, if the old logs contained transactions you wanted to re-play, you can't.

>If I want to redo
> the recovery, I should be able to use the backup control file and play
> again each archivelog until the resetlog. No ?

Absolutely. But what about your current redo log? If you are sure it doesn't contain *anything* you want to replay, then copying the logs is, I suppose, optional. But otherwise, you'll lose it for good.

> If I do a backup of only the redolog files before the resetlog, I
> should have no loss. No ?

You need to back them up before the recovery, not the resetlogs. But now you seem to be saying you'll take a backup of the redo logs. And you've already said you've got a backup of the controlfile (albeit of the tracefile variety), so in fact you've got exactly what I said you needed!

>
> If I don't backup the Control Files before the resetlogs, what are the
> consequence for a incomplete recovery (except the need of using the
> backup controlfiles) ?
>
> 2- After a resetlog, Howard recommends to do a *cold* backup. A hot
> backup is not sufficient ? Why ?
>

I'm sure I didn't say it was "not sufficient". A hot backup *when it's finished* will be just as good as a cold one. The trouble is that the resetlogs you've just issued has rendered all prior backups and archives useless (without a great deal of fiddling around). Therefore your database has no protection from another failure. If you're doing a hot backup, it implies that your users are doing work on the database. That means they are performing transactions for which there is no ready recovery mechanism available. If you take a new COLD backup, then you *do* have a backup that can be restored and recovered in the event of a subsequent failure.

A lot of people take the risk: they start a hot backup cycle immediately after a resetlogs. That's fine, but only when the entire backup is completed can they say that recovery from any future failure is guaranteed.

Regards
HJR
>
> Thank you very much in advance for you advices,
>
> --
> Bruno Jargot
Received on Tue Jul 02 2002 - 15:45:01 CDT