Re: Is there any hope of geting a datafile in RECOVER mode backe?

In <3f789057$0$6610$afc38c87_at_news.optusnet.com.au> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> writes:

>Stan Brown wrote:
 

>> In <3F78774F.BE618C98_at_remove_spam.peasland.com> Brian Peasland
>> <dba_at_remove_spam.peasland.com> writes:
>>
>>>When you restore the datafile, do recovery UNTIL CANCEL as TurkBear
>>>indicated. You can reply CANCEL immediately if you wish, or you can roll
>>>forward through as many archived redo logs you have until the first one
>>>your are missing.
>>
>> Can I do "recover until cancel" on just one datafile?
>>
>> If so, what's the syntax? I've tried several different ones without
>> success so far.
>>
 

>Stan, you can keep starting new threads until the cows come home. But it
>isn't going to help you much more than you've been helped already because
>the fundmental issue here is that you don't know how to do recoveries or
>what the principles are, and you've been 'having a go' for several days,
>and who therefore knows what state that database is in?

I've said _several_ times that I don't know what I'm doing here. The only reason (at this point) that I start a new thread is when _I think_ I've made some progress, but have run up on a new stumbling block. So I'm thinking that perhaps a new person, who has the time to answer, will join a new thread quicker than one that a gru (like you for instance) has "taken ownership" of. Sorry if thats bad manners in this group.

>Your datafile 10 is in recover mode. It can't be brought online until it has
>been recovered. You are missing archives. You can't do a complete recovery
>if your are missing archive logs. Catch 22.

OK, incomplete recovery of this datafile would be just fine. I finally figured out why it was active at the time the system failed. I was thinking that it just had static data in it (which is what I need to recover). But upon examining my creation scripts, I discovered that it was the temporary table space for an active user also. So, incomplete would get all the static data, which is what I need.

>But there really isn't a mystery about this. I've pointed you to a link to
>my paper that goes through every one of these scenarios so that you could
>work it out for yourself, but the principles are easy to state. If you are
>contemplating an incomplete recovery because of the missing archives, then
>restore every single datafile, and do a 'recover database until cancel',
>then an open resetlogs.

Sorry, I mean to mention earlier. I went to the web site, and found that each "chapter" was a separate PDF file. Unfortunately the one on "incomplete recovery" seems to not be linked. I am pretty certain I emailed you on this about last Friday, but I think that email got lost. Apologies.

Also, all datafile whose gziped image was >2G were not stored correctly, due to yet another error on my part, so recovering _every datafile_ is impossible. File 10 however was <2G so this problem does not exist for it.

>If your controlfile has been buggered to death by your previous recovery
>efforts, then that becomes restore every single datafile and the
>controlfile and do a 'recover database until cancel using backup
>controlfile', then an open resetlogs.
 

>If you simply want to know how to get the database opened when datafile 10
>is screwed, but you are missing archives that would allow it to be
>recovered, but you don't want to do an incomplete recovery because of the
>loss of data elsewhere in the database that that implies, then take
>datafile 10 offline: alter database datafile 10 offline drop, followed by
>alter database open (if it will go to that stage).
 

>But you've been stuffing the database around for several days now, so I
>doubt that merely offlining the offending file is actually going to help
>you (though it is a matter of moments to try it and see).

I've taken 3 datafiles offline, and been able to open the database that way. I've then dropped 2 of the 3 datafiles (which contained indexes only anyway, and were >2G). So I can actually startup the database. If I can mange to do even an incomplete recovery of datafile 10, I will be in _great_ shape!

>I'm not getting at you having made mistakes, since you've freely admitted
>that this is all new to you. But you keep posting individual questions,
>such as the one that heads this thread. And your scenario is actually
>nothing to do with the questions you're asking. It's bigger than a single
>question here or there. The scenario is actually that a database wasn't
>backed up properly (or so I seem to remember from another of your posts),
>is missing some archive logs, and has had several failed recovery attempts
>made on it with no backup taken before those recovery attempts were made.

A correct statement of the situation. All of which is, in one way or another, my fault.

>If you'd said all of that in this post, the replies from others would have
>been rather different, I expect. And generally along the lines of 'you're
>stuffed. Contact Oracle Support. Be prepared to pay major sums of money to
>have Oracle extract what data they can from your datafiles.'
 

>The questions I'd ask you before proceeding further are:
 

>1. Do you have any valid backups taken before this disaster began

No.

>2. Are those backups hot or cold

What remnants I have are from a hot backup.

>3. Precisely which archives are missing

The latest ones (I don't know how many).

>4. If you have a precious good backup when the database was functional, and
>you restore its datafiles and controlfile, what happens when you try
>starting that restored database up. What, precisely, are the error messages
>you get? And what are the outputs from v$backup and v$recover_file?
>5. Have you read the Backup and Recovery paper at
>www.geocities.com/lydian_third/books
 

>Recovery, if it is possible at all, is almost certainly going to consist of
>going back to your last known good backup if you have one and recovering
>until the last known archivelog. It's easy when it's described like that,
>but you have got to know, just roughly, what you are doing.

ell I bow to your superior knowledge. But the hope I have is that, since I have the instance back up, there is some possibility of doing an incomplete recovery of just file 10.

Is there no hope for this?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
						-- Benjamin Franklin