Re: Question on Incomplete Media Recovery

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 19 Oct 2004 05:25:16 +1000
Message-Id: <4174189d$0$10351$afc38c87@news.optusnet.com.au>

Rozz Williams wrote:

> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:<4172c899$0$10345$afc38c87_at_news.optusnet.com.au>...

>> Rozz Williams wrote:
>> 
>> > Hi,
>> > 
>> > Here is my scenario:
>> > 
>> > Disk A: Windows 2000 OS, Oracle install binaries, One controlfile
>> > Disk B: All database datafiles, online redo logs, archived redo logs,
>> > a copy of a controlfile.
>> > 
>> > The database is in ARCHIVELOG mode. A Hotbackup is taken every day.
>> > 
>> > Now, let's say Disk B crashes and I replace the Disk and restore from
>> > the previous day's hot backup. What do I need to do with the
>> > controlfiles to recover? Do I recover the database with "recover
>> > database until cancel using backup controlfile" or just "recover
>> > database until cancel"?
>> 
>> Answer your own question: do you have a backup control file? Or do you
>> have a surviving copy of your real, live controlfile on disk A?

>
> I will have both.

Well, yes, and I "have" another 45 birthdays to go before I kark it, too. That is not what I meant by the words "do you have". Specifically, are you currently, right now, *using* a backup control file or a surviving copy?????

You cannot *use* both simultaneously.

As is evident from the rest of your post, you know this. You know that a choice has to be made. And the answer to your original question simply depends on the choice that you, er, choose to make.

> The controlfile on Disk A is a surviving copy
> containing current information. The controlfile on B will be a
> restored copy from yesturday's backup.

Yes, I know that. And which do you think you should use? Possessing both is not the point. The issue is which one does the intelligent DBA choose to use, when he or she knows that using the old copy on disk B will involve a resetlogs, and the one on disk A doesn't?

>> More technically, would the checkpoint change number in the control file,
>> be ahead of, or the same as, the checkpoint change number in the data
>> files?

>
> Controlfile on Disk A will be ahead. > I believe controlfile on Disk B
> will also be ahead because the controlfile is backed up after all the
> datafiles and a log switch.
>

>> > I'm thinking the following:
>> > 1. delete the newly restored control file on Disk B
>> > 2. Copy the controlfile from Disk A to Disk B
>> > 3. startup mount
>> > 4. recover database until cancel
>> > 5. issue a "cancel" (because I do not have any archived redo logs from
>> > the last backup to the point of failure)
>> > 6. alter database open resetlogs
>> > 
>> > Please let me know if I am on the right track.
>> 
>> Your six points seem to me entirely correct.

>
> I was also thinking of the following:
>
> 1. delete the control file on Disk A

The essence of recovery is knowing when to stop digging the hole that is getting ever deeper!

You have a perfectly functioning controlfile on Disk A. Why on Earth would you even *consider* deleting it?

The other essence of recovery is to restore that, and *only* that, which has actually failed. Making something else fail yourself by employing the old 'delete *' technique is usually not smart!

> 2. Copy the newly restored controlfile from Disk B to Disk A
> 3. startup mount
> 4. recover database until cancel using backup controlfile
> 5. issue a "cancel" (because I do not have any archived redo logs from
> the last backup to the point of failure)
> 6. alter database open resetlogs

And you are happy issuing a command, are you, that requires an immediate shutdown of your database, a fresh, cold, complete backup, and the practical loss of all prior backups and archivelogs?

Let me put it this way: I wouldn't be.

Let me put it another way. Unless you absolutely, positively *have* to perform a resetlogs operation, one usually bends over backwards to make sure you don't perform one at all.

>> I would want to know why a multiplexed member of your redo logs isn't on
>> disk A, as well, though.

>
> I honestly don't like the setup myself, but as a consultant, I am not
> able to make any changes (because of all the change controls in
> place), just document what is there.
>
> If it makes you feel better, Disk B is actually a bunch of drives
> running RAID 5.

It doesn't (make me feel better, that is). RAID 5 as a destination for redo writing is a no-no. Datafiles, I could live with. Redo logs, not.

> I just called it Disk B for simplicity sake. I am
> actually trying to document an incomplete media recovery if more than
> one drive fails on the RAID unit. The only reason why this will be an
> incomplete recovery is because the archived redo logs are residing on
> the RAID unit.

And that's a no-no, too.

> Thanks for your help, it is greatly appreciated.

No problems.

Regards
HJR Received on Mon Oct 18 2004 - 14:25:16 CDT