Re: Can this be recovered?

"Ste" <schen88_at_comcast.net> wrote in message news:SKudnYRWuoKtso3cRVn-rg_at_comcast.com...
> If all 3 controlfiles and 6 redo log files are lost due to media failure,
> can this database be recovered and how? There are not backup for those
> files.
>
> Please help.
>
> Thanks.
>
> Ste

Yes, the database can be recovered, assuming it is in archivelog mode, of course.

The loss of the online redo logs is irrelevant in a sense. One of those 6 logs was the current log, and the loss of a current redo log always means that you'll have to do an INcomplete recovery -usually, a 'recover database until cancel' one. And of course you will have lost any committed transactions contained within that last current log. But, their loss doesn't prevent recovery.

The loss of the control files is rather more awkward, because without a controlfile, it is impossible to get into the mount state to actually begin recovery. But, this too is not terminal because you can simply recreate the missing control files using the CREATE CONTROLFILE command. Trouble is, doing that is not actually very "simple", because the syntax for that command can be quite convoluted. If you can, on another database, issue a 'alter database backup controlfile to trace' command, and then locate the tracefile produced and have a look at its contents, you'll see what I mean... though, if you can do that, you can effectively use the generated trace file as a template for what you need to recover the original database's control file. Basically, all you need do to it is change the database name and the paths and names of all the data files and redo logs it references. So it's fiddly, but do-able.

The over-arching strategy here, therefore, would be:

startup nomount
CREATE CONTROLFILE....
alter database mount
recover database until cancel
[apply redo]
cancel
alter database open resetlogs

The tricky bit, in the absence of an existing trace file backup of the correct controlfile, is getting step 2 correct. I would strongly advise that  before trying anything else at all, you backup every single bit of what's left of your database, and put that backup out of the way, safe and secure, particularly since step 2 could either work well, or screw things up badly.

If you do manage to recover your database, find out why no-one was taking backups of the control files. They should have been. Online redo logs should never be backed up, so that's not a problem... but find out why no-one has implemented multiplexing of the redo logs. They should have done.

And if you're not in archivelog mode after all, then no you can't recover this database without a whole closed database backup.

Regards
HJR Received on Tue Aug 03 2004 - 19:49:23 CDT