Re: How to apply archive logs on an cold backup

Howard, I just wish you had been my tutor when I was doing my Oracle Backup and Recovery course! Thank you for a very clear and precise description of the way to recover from this sort of scenario. Today I created myself a little database to play with and tested out some of these scenarios, and this, in conjunction with your input via this group has certainly improved MY understanding, and probably that of a few others I wouldn't be surprised.

My apologies if my earlier posts have clouded the issue.

Try and avoid that asteroid as I doubt a piece of Bonsai shrubbery would make such valuable contributions to this group!!

Regards,

Tim Kearsley

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<4136dbc2$0$17675$afc38c87_at_news.optusnet.com.au>...
> Tim Kearsley wrote:
>
> > 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?
>
> What is the *fundamental* failure scenario here? Fundamentally, you've lost
> your current redo log. Therefore, you are immediately into incomplete
> recovery, and it is therefore utterly impossible to get the database back
> to the "current time". As near as your rate of log switching allows, yes.
>
> > 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?
>
> See, this is why precision is so important. What do you mean by 'restore the
> backup'. You said a sentence or two before that the backup contains the
> controlfiles, datafiles and online logs. So are you restoring all of those
> things?
>
> But if you are, why are you then going to create a new control file, since
> you've already got one that agrees with the data files and redo logs? To
> try and force the application of the archives? But if that's what you
> wanted to do, the binary control file is sufficient already to make that
> happen.
>
> More to the point, perhaps, the "backup" you took was all at Monday's SCN
> (say). You've just restored all of Monday's datafiles. If you run the
> tracescript, you will create a Monday Controlfile. But you've already *got*
> a Monday controlfile, because that was included in your Monday backup!
>
> It is very difficult to give meaningful answers to recovery scenarios that
> are not described 1000% precisely.
>
> So, I take it you actually mean you would restore the data files alone?
>
> If so, the trace script will create a control file that agrees with your
> data files, re-create empty online logs at that point, and you won't get a
> chance to apply redo because the database is now consistent. At time
> Monday.
>
> The correct recovery procedure for this sort of scenario is simply to
> restore the control files and data files from your backup, and issue the
> command 'recover database until cancel using backup controlfile'. You could
> do an 'until time' if you know those sorts of detail, but usually it's an
> until cancel when lost redo logs are involved.
>
> > My question is absolutely straightforward
>
> It's not, actually, as I hope I've made clear. The recovery scenario itself
> is perfectly clear: disaster recovery from total loss of everything. But
> what you are suggesting you do at any one point is not clear to me from
> your description at all.
>
> But given a total loss scenario, the recovery scenario is equally simple, in
> principle: restore your binary control file, all your data files, and do an
> until cancel incomplete recovery, with the 'using backup controlfile'
> clause, because that's what it is.
>
> You don't restore the online redo logs (which you shouldn't have in your
> backup anyway, but it's the fact that you don't ever restore them that
> makes it pointless to back them up in the first place). And it is the lack
> of online logs that triggers the non-matching SCN scenario that is needed
> to provoke recovery.
>
> > - do you need that clause or not?
>
> If you do the recovery as I've described, then yes. Because it involves
> restoring the binary backup of the control file.
>
> If you insist on re-creating your control file via the trace script, which
> will need editing before you try it, since it doesn't contain the right
> recovery clauses, you still need to use the 'ubcf' clause because you have
> no other way of persuading Oracle that you know better than it when to stop
> recovering.
>
> > Regardless of whether the scenario
> > above is likely, should never happen, etc., etc., is that simple
> > question answerable?
>
> Yes, and far more simply than I have here, provided you are completely
> precise about what it is you are proposing to do. As it is, the recovery
> process that I would do is dead simple to describe.
>
> > 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.
>
> I could be pedantic and point out that what you've just written here is not
> what you wrote either before or above, where you include the online redo
> logs in your backup. Which are definitely not required to be restored.
> Ever.
>
> The definition of a cold backup is straightforward: a copy of certain
> database files (control files, datafiles and archive logs) taken whilst the
> instance is closed or in nomount state.
>
> > 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!
>
> You might just want to reconsider how you joined this thread, then.
>
> But whatever. Can I go back to the point I first made (I think) to the
> original poster? One can come up with all sorts of weird and wonderful
> failure scenarios, just as one could ask whether, if I get struck by an
> asteroid on a May morning and that causes my DNA to be flung into orbit
> before it descends back to Earth, arriving on a mad scientist's desk, would
> it be possible to turn me into a piece of bonzai shrubbery for an
> ornamental garden. I suppose it could happen, but really.... we are talking
> vanishingly small probabilities.
>
> So what I'm saying is, step away from this scenario or that scenario,
> because however outlandish and subtle you think they are, recovery always
> works the same way:
>
> restore what is at fault from a backup
> recover the database using the appropriate command
> open the database using the appropriate command
>
> Cutting through the Poirot-esque subtleties of your post, therefore, the
> question is: what do I restore when I lose everything? To which the answer
> is: everything that is correctly in your backup (ie, control files and data
> files only). What command do you then use to recover the database?
>
> Well, build it up:
>
> recover database or recover tablespace or recover datafile?
> It must be recover database, because you're not open (so no tablespaces are
> available), and you've restored all datafiles, so there's more than one of
> them.
>
> [silence], until cancel, until time, or until SCN?
> It cannot be [silence] because you're not able to do a complete recovery
> It could be any of the three, but user errors are usually until time, and
> missing redo is usually until cancel. You lost your redo log, so use the
> until cancel one.
>
> Finally: using backup controlfile (because you've just restored a binary
> backup of the controlfile)
>
> Opening then becomes a choice of:
>
> alter database open or alter database open resetlogs?
> And you have no choice in that matter. It has to be the resetlogs option,
> because you performed an incomplete recovery.
>
> I can't make it much simpler than that. Sorry.
>
> Regards
> HJR
Received on Thu Sep 02 2004 - 17:04:14 CDT