Re: Lost rollback segment datafile, database will not start

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.

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.

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.