Re: Is it possible to recover just one datafile/tablespace "until cancel"?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Fri, 03 Oct 2003 06:15:30 +1000
Message-Id: <3f7c881e$0$15134$afc38c87@news.optusnet.com.au>

Stan Brown wrote:

> In <ysJeb.1862$x67.605_at_bignews4.bellsouth.net> "Burt Peltier"
> <burttemp1ReMoVeThIs_at_bellsouth.net> writes:
>

>>--
>>"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
>>news:3f7a5a50$0$13656$afc38c87_at_news.optusnet.com.au...
>>> Burt Peltier wrote:
>>>
>>> > If all you lost were archived redo logs, you should not have had to do
>>> > much , I think.
>>> >
>>> > Not sure what you did first, but the correct course of action might
>>> > have been real simple. Of course, once you do 1 wrong command, the
>>> > correct command may no longer work. Good reason to do a cold backup
>>> > before doing any recovery.
>>> >
>>> > I once had a database crash in the middle of a hot backup. It looked
>>like
>>> > I needed to do some kind of recover.
>>> >
>>> > But, I think (been a while - doing this from poor memory) all I had to
>>do
>>> > was 'recover database' . Basically, because the datafiles were in
>>> > "begin backup" mode, Oracle needed the current REDO log just to "sync"
>>> > things
>>up
>>> > and take the datafiles out of backup mode.
>>> >
>>>
>>> This disaster just continues to run and run, doesn't it?
>>>
>>> No, Burt. You didn't need to do 'recover database' at all (and if you'd
>>just
>>> lost your archive logs, it wouldn't have worked in any case). All you
>>> need

>

>>I suppose it has been a while and I could be mistaken, but ...

>

>>Assuming the hot backup did not run for a long time and all the recently
>>archived REDO are still intact (not over-written since being archived)
>>because Oracle has not yet "cycled" thru all the online REDO logs,
>>wouldn't the most recent REDO needed for a "recover database" in this case
>>have been available and so Oracle would not have had to go to look in the
>>archived log dest?

>
> Could be. However in this case, it _did_ run for a while (although I would
> not have thought it would have _done_ anything withut being able to write
> to the archivelog files). It wnet from about 23:00 of Wednesday till about
> 10:00 the next day, before I did a dbshut on it.
>

>>> to do if you crash in the middle of a hot backup is 'alter database
>>> datafile x end backup'. That causes the headers of the files to be
>>> re-synchronised, and therefore an 'alter database open' would have
>>> worked. Of course there might be more than one file in hot backup mode,
>>> so a quick check of v$backup before doing anything is always in order.

>

>>Good info ... I will try to remember this . Thanks.

>
> YI am pretty certain I will remeber this!
>

>>>
>>> The tragedy of Stan's predicament is that this is *not* what he did, but
>>> proceeded instead to muck about with incomplete recoveries on some
>>> files, and not on others, followed by Lord knows what else. And as you
>>> so wisely put it, once you've embarked upon a failed recovery, the
>>> correct recovery procedures are unlikely to work when they are
>>> (eventually) tried. The suggestion of a cold backup before starting any
>>> recovery is an excellent one -though often not practical in a production
>>> 24x7 environment :-(
>>>

>

>>Good point. Instead of a cold backup, could you just copy the REDO logs
>>and control files (while the database is down) ... so you could then copy
>>these back to try again:
>>- copy REDO and control files while the database is shutdown
>>- ** do failed recover wrong commands **
>>- recover all datafiles from hot backup (assuming you have these)
>>- restore REDO and control files from just copied versions before 1st
>>failed recover
>>- try recover again

>
>
> Mmm, too bad we did not come up with this ealrier. I _di_ have these
> files.

It is true. If you want to be able to repeat a recovery, then you need the control files and the online redo logs from the time immediately before recovery began, because you can always source the data files from the backup tape again.

But it wouldn't have helped you Stan. By the time you posted your very first post, you'd already tried experiementing with various recovery commands, thus changing the contents of the control file.

It would also not have been much use in your case, since the salient feature of your saga was the slightly unnerving revelation that your backup strategy doesn't actually work. If you don't have data files to restore from disk or tape in the first place, taking any sort of measures that would allow you to repeat a recovery is a waste of time, isn't it? You have to be *able* to restore/recover before you start wanting to *repeat* restores/recovers.

Regards
HJR  
>
> Thanks to veryone that helped me with this disaster!
>
Received on Thu Oct 02 2003 - 15:15:30 CDT