Re: How to apply archive logs on an cold backup

Well this is interesting now! I thought this was quite straight-forward, but obviously not....

I'll try again.

You have a database which you run in ARCHIVELOG mode. Every so often, you shut it down and take an offline backup. This consists of all the data files, the control files and, lets be contentious, the redo logs.  Because you're a sensible chappie, you also backup the control file to trace, so that you have a way of recreating the control files if necessary.

Now, one day, something catastrophic happens and you lose every data file, the control files and the redo logs. Happily, your archived redo logs generated since the cold backup you took two days ago are all safe.

My question, which I thought was simple, is this:

Given that all you have to recover from is the offline backup (defined above) and the archive logs, how would you recover the database back to the current time, or as near to it as possible?

I thought you had to restore the offline backup, create new control files with your trace script and then apply the archive logs using the BACKUP CONTROLFILE clause? My question is absolutely straightforward - do you need that clause or not? Regardless of whether the scenario above is likely, should never happen, etc., etc., is that simple question answerable?

I'm sorry if I was imprecise - I thought use of the term "cold backup" implied taking a backup of all files necessary for a restore - i.e. data files *and* control files.

As for me "trying to get you to say, 'sometimes a 'using backup controlfile'...", I'm not trying to get you to say anything, I'm trying to improve my understanding!

Regards,

Tim Kearsley

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<41362ad6$0$12422$afc38c87_at_news.optusnet.com.au>...
> Tim Kearsley wrote:
>
> > Hi Howard,
> >
> > Perhaps you can enlighten me a bit in this:
> >
> > Assumptions are:
> >
> > Database is in ARCHIVELOG mode
> > Something catastrophic has happened and all data files, redo logs and
> > control files are lost
> > All you have is a cold backup taken two days ago
> > You have all archive logs from the cold backup point to the current
> > time
> > You have a backup of the control file to trace
>
> Yes. Go on...
>
> > 1) If you restore the cold backup,
>
> You've got to be a lot more precise than this. What is in the cold backup?
> Are you implying that it contains the control files and the online redo
> logs? So that, in the list of assumptions above, you should say "The backup
> to trace is in addition to a binary backup of the control file"?
>
> The reason I question this is because in archivelog mode, you never back up
> the online redo logs. (Actually, there is no need to in noarchivelog mode
> either, but that's another story). So at a stroke, what you are about to
> write would never happen. But...
>
> > am I right in thinking that if you
> > issue a RECOVER DATABASE... command, Oracle will reply with a "No
> > recovery needed" (might not be exactly the right wording) message?
> > Presumably this is because the SCN in the data file headers and
> > control file are consistent?
>
> If you restore the binary control files, all data files and all online redo
> logs from a closed, whole backup (ie, cold), then recovery will not be
> required, because indeed everything agrees as to SCN.
>
> You've effectively just cloned your database.
>
> > 2) In the same scenario as above, assuming you have archive logs
> > available from the time of the cold backup up to "now", how would you
> > roll that cold backup forward? It was in this case that I thought you
> > had to use a RECOVER .... USING BACKUP CONTROLFILE, after creating a
> > new control file from a script?
>
> Of course. You're wanting to perform recovery with a backup controlfile.
> Strangely enough, that requires the use of the 'using backup controlfile'
> command.
>
> If you're trying to say, 'That's the OP's situation!', I'd remind you of the
> point I made at the beginning of this post: if you're in archivelog mode,
> you shouldn't be taking backups of your online redo logs in the first
> place, meaning that this entire scenario would never actually arise.
>
> > 3) If, as you said in another post in this thread, the CREATE
> > CONTROLFILE script takes SCNs from the data files and writes them to
> > the control file, why, in this scenario is that any different from the
> > situation in 1)? Surely everything is consistent again?
>
> I don't follow that question, I'm afraid.
>
> > Apologies if I'm ignorant.
>
> You are not. But you are imprecise in your list of assumptions. And include
> a gem of an assumption (that you backup your online redo logs) which should
> not apply.
>
> If you are trying to get me to say, 'sometimes a 'using backup controlfile'
> is needed to effect recovery', then you don't have to be so subtle about
> it, because I've already written that the original poster has exactly the
> same recovery options as anyone else. And that those options depend on his
> failure/recovery scenario. And yes, sometimes that will mean 'using backup
> controlfile' is needed, *if* his recovery actions consist, in part, of
> restoring a binary version of his control file.
>
> But the relevance of that to a poster who writes as if that is the ONLY
> recovery option the guy has is, what, exactly?
>
> Regards
> HJR
Received on Thu Sep 02 2004 - 02:26:51 CDT