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

Hi Mattma,

Did you get your database recovered ?

Stan has got things pretty well covered with his reply.

Only things I would add would be to take a backup of the DB first, especially if you are going to blow away your redo logs.

I'm not sure if your database is in archivelog or noarchivelog mode. If in archivelog mode, you'll have problems with the offline drop command (just take it offline and drop it later).

Not sure if your redo logs are mirrored. If so, you could drop and recreate the stuffed member rather than issuing resetlogs. Saves you resetting the DB (if in archivelog mode).

Remember to backup the database (especially if you have resetlogs and archivelog).

And remember to back them up away from existing database files ;)

Good Luck

Richard
"Stan" <stan0074_at_yahoo.com> wrote in message news:3D632AD8.3000002_at_yahoo.com...
> 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;
> 9. create undo tablesapce (create undo tablespace undots1 datafile
> '../undotbs01.dbf' size xyzM autoextend on maxsize 2000M;)
> 10. assign newly created tablespace as the default undo tablespace
> (alter system set undo_tablespace = undots1;)
> 11. shutdown immeidate
> 12. uncomment undo_tablespace parameter and update with undots1
> 13. 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
> >>
> >>
>
Received on Wed Aug 21 2002 - 21:49:42 CDT