Re: recovering oracle database missing control, undotbs and redo files

Hi mattma,

As your certain that you had a clean shutdown...you should be able to bring up the database. Here is what i would do:

1. open up init.ora file (if your not using spfile) and make sure to have all the control files in place (if not make copies of missing control file from an existing file) or you remove '../control03.ctl' from control_files parameter keeping others intact (../control01.ctl and ../control02.ctl).
2. comment out undo_tablespace parameter from init.ora
3. mount the database in restrict mode (startup mount pfile=../init.ora restrict)
4. query v$datafile to get the path/name of the undo datafile.
5. drop undo datafile(alter database datafile '../undotbs01.dbf' offline drop;)
6. open database in resetlog mode
7. drop tablespace undotbs_01;
8. create undo tablesapce (create undo tablespace undots1 datafile '../undotbs01.dbf' size xyzM autoextend on maxsize 2000M;)
9. assign newly created tablespace as the default undo tablespace (alter system set undo_tablespace = undots1;)
10. shutdown immeidate
11. uncomment undo_tablespace parameter and update with undots1
12. startup the database.

I really wouldn't worry about temp files cuz they are not required for opening up the database. so you can create them after #13 with (alter tablespace temp add tempfile '../temp01.dbf' reuse;)

Good luck.

hth,
-Stan

mattma wrote:

> Hi Richard,
>
> Thanks for taking a look at this. In response to your questions:
>
> 1. My only earilier backups were export .dmp files so they are useless
>
> 2. I think the disk crashed and the database remained up and running
> (is that possible?). I even think records were added to the database
> after the disk crash. When I noticed something was wrong with the
> disk later I properly shut down the database. This disk isn't used
> for much else so I didn't notice it died - and linux didn't seem to
> care that it died either.
> If it is not possible for the db to be up and running after one of the
> redo logs disappears, then the disk must have crashed after I shut
> down the database and os. Either way, I am certain that I shut it
> down and had no errors shutting it down.
>
> 3. The undotbs01.dbf file was probably the current undo tablespace.
>
> 4. This is NOT an exam question.
>
> If I get the database started again, is there a way I can get the data
> out in some other fashion?
>
> Thanks!
>
>
>
>
>
> Richard Foote <Richard.Foote_at_bigpond.com> wrote in message news:<3D6208D1.B8902C97_at_bigpond.com>...
>

>>Hi Mattma,
>>
>>A bit more info.
>>
>>You've lost the most recent backup. Do you have an earlier backup and
>>all the redo logs since the previous backup (please have an earlier
>>backup and the redo logs, pretty please) ?
>>
>>Was the database up and running when you had the disk crash or was it
>>shutdown (please be shutdown, pretty please)?
>>
>>Do you have your redo logs mirrored or was the lost redo log the only
>>member (please have a second member, pretty please)?
>>
>>Was the undo data file from the current undo tablespace (please be from
>>a non current undo tablespace, pretty please).
>>
>>This is for real and isn't some exam question ?
>>
>>The control file doesn't worry me, neither does the temp file. The redo
>>log is a bit of a concern and the lost undo file is real concern.
>>
>>Supply the above answers and we'll see what we can do.
>>
>>Cheers
>>
>>Richard
>>
>>