Re: Lost rollback segment datafile, database will not start

"Howard J. Rogers" wrote:
>
> If he's in nomount stage, he won't be able to drop the tablespace. You can
> only talk tablespace language when you're fully open (Hence, if he follows
> your advice, he'll get an ORA-01109 error). And how on earth is he supposed
> to create a new tablespace in the nomount stage?? If he tries it, he'll get
> another ORA-01109. And it won't work in mount stage, either.

Fine - I asked to be corrected, like I said I've done this before - if I'd been going slower I'd have said mounted but not open. Who crapped in your lunch today?

>
> If you're going to offer advice, I think you need to do a minimum amount of
> checking that it is not total nonsense. Yours, in this case, unfortunately
> is. Particularly as it seems you have no idea of the difference between
> mount and nomount stages.

Arrogant tosser - try to help someone and a total stranger tosses insults. The key point I made was correct - he has to drop the tablespace. Thus you are simply wrong - it is not 'total nonsense'

You rightly mention backups and archivelogs which I didn't so hats off to you, but get an attitude lesson. Kids these days...

>
> The correct approach is:
>
> 1. Make sure your init.ora is not trying to bring the affected rollback
> segment(s) online automatically (remove them from the rollback_segments
> parameter setting if so)
>
> 2. Get the database to the MOUNT stage (startup mount)
>
> 3. Alter database datafile 19 offline drop
>
> 4. Alter database open
>
> 5. Drop tablespace X including contents
>
> 6. Create a new rollback tablespace and new rollback segments within that
> tablespace.
>
> 7. Shutdown immediate
>
> 8. Edit init.ora to ensure the new rollback segments are brought online
> automatically
>
> 9. Startup normally
>
> ...and that's not even contemplating things like restoring the datafile from
> backup and applying redo logs.
> HJR
> --
> --------------------------------------------------------------------------
> Opinions expressed are my own, and not those of Oracle Corporation
> Oracle DBA Resources: http://www.geocities.com/howardjr2000
> --------------------------------------------------------------------------
>
> "nomenclature" <nom_at_tonal.net> wrote in message
> news:39CB626B.F315ACF4_at_tonal.net...
> > Chuck Carson wrote:
> > >
> > > I created a rollback segment and then later deleted the datafile by
> > > accident. Here is what I get when trying to Start the DB:
> > >
> > > ORA-01157: cannot identify/lock data file 19 - see DBWR trace file
> > > ORA-01110: data file 19: '/u08/oradata/OPS/bigrbs201.dbf'
> > >
> > > This is a development DB and there was no activity during this time
> > > (probably 12 hours idle)
> > >
> > > How can I fix this?
> > >
> > > (This is Oracle 8.1.6R2 on Solaris 2.7)
> >
> > Try to startup nomount and drop the rollback tablespace,
> > create a new one and off you go.
> >
> > If you've deleted the datafile you are screwed
> > and have to drop the tablespace.
> >
> > If I'm wrong - please correct me!
> > Happened to me before but never again.
Received on Fri Sep 22 2000 - 11:25:33 CDT