Re: how to recover a datafile deleted and need to recover without backup file...using file# from v$datafile?

Holger Baer wrote:
> fitzjarrell_at_cox.net wrote:
> >
> > Yes, you are 'screwed', to put it bluntly, as the error messages
> > attest. Without a valid backup and the requisite archivelogs (or the
> > complete set of archivelogs since the original creation of your deleted
> > file) your missing datafile will remain missing, period.
> >
> > Your next option is to shutdown your instance and perform a COLD backup
> > of what you have. DO NOT OMIT THIS STEP. Next, search the newsgroup
> > for steps on recreating your controlfiles as this MAY help you get your
> > instance up and running by rebilding them minus this missing
> > datafile(although I can't guarantee such success). You could also
> > contact Oracle support, but I expect they'd be asking you to restore
> > the file from a current backup, which, of course, is impossible as you
> > don't have one.
> >
> > Basically you now have a useless collection of datafiles, controlfiles
> > and redologs consuming space on your Windows server. I believe your
> > only SUCCESSFUL path of 'recovery' will be to recreate the database
> > (you DO have the scripts for that, I hope) and apply the most recent
> > export (which you also have, I hope) and restore to that point. You
> > will have lost data in the process, but a few days data loss may be
> > much better than losing an entire database to negligence.
> >
> >
> > David Fitzjarrell
> >
>
> We don't know what was in the tablespace yet, as well as he didn't tell
> if there are missing archived logs, so I wouldn't call it a useless
> collection of datafiles. He still can drop/recreate the entire tablespace
> if the data was not that important.
>
> I must admit that I don't understand why he should recreate the control-
> files, given the scenario is not clear at all. Besides, if the tablespace
> consisted of more than one datafile, Oracle will know, so that won't
> help either.
>
> He's probably screwed as it is. Why screw him some more?
>
> Cheers
> Holger

I am not trying to screw anyone, Holger, and I'm sorry you think that I am. In RARE cases I have been able to get a database back up by recreating the controlfile after first eliminating the missing file from the list. You apparently failed to note my statment that recreating the controlfile would likely NOT be a successful endeavour. If that was confusing to you, I can only imagine it more confusing to the OP.

I also offered the suggestion to recreate his database as probably his only successful option. As he does NOT possess a backup it is quite likely the archivelogs do not exist, either. Also, since his missing file is 2 gig in size it would be a logical assumption it's a single-file tablespace. And, if he cannot get the database to mount he can't drop the tablespace. His database appears to be pretty useless to me.

I apologise if my statements were taken in a 'you're screwed so let me see if I can screw you further' context. Nothing could be further from the truth.

David Fitzjarrell Received on Wed Jun 08 2005 - 07:59:07 CDT