Re: Corrupt rollback segments

Hi,

    I have tried offlining the rollback segments, but all I get is:

ORA-01598: rollback segment 'R01' is not online

I believe this is because the rollback segments status is 'NEEDS RECOVERY' and not running in archivelog mode means I've nothing to go back to.

Trying to create either a new tablespace or to create more rollback segments in an already existing tablespace yields the following message:

ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/ora81/m02/oradata/acs/rbs99.dbf'

Trying to drop the tablespace including contents gives me:

ORA-01548: active rollback segment 'R01' found, terminate dropping tablespace

I'm confused!

Any other ideas would be much appreciated.

Cheers

--
Mr. Lab
--
http://www.klingklang.co.uk
http://klingklang.dyndns.org
--
"Howard J. Rogers" <howardjr_at_www.com> wrote in message
news:398c4f33$1_at_news.iprimus.com.au...


>

> "Mr_Lab" <t.o.b.y_at_btinternet.com> wrote in message

> news:8ma8vt$3jn$1_at_neptunium.btinternet.com...

> > Hi,

> >     I have an 8.1.5 instance that has suffered file corruption to my

> > rollback tablespace. I've tried dropping the tablespace but keep

 getting:


> >

> > ORA-01548: active rollback segment 'R01' found, terminate dropping

> > tablespace

> >

>

> You can't drop a rollback segment tablespace if Oracle still thinks one of

> the rollback segments inside it is active.  You therefore have to offline

> the SEGMENTS first, and then drop the tablespace.  Offline-ing an active

> rollback segment will put it into 'PENDING OFFLINE' mode, which means the

> current transaction(s) can complete, but no new transactions will be

 allowed


> in.  When all current transactions have completed, the status goes to

> offline, and you're then ready for the drop.

>

> So, I would suggest you offline your segments, then shutdown abort your

> Instance, then startup again, and hopefully you'll discover that the

> *tablespace* can be dropped and re-created (you could also use the

> v$rollstat and v$session views to try and determine what specific sessions

> are using the relevant rollback segments, and just terminate those

 sessions,


> rather than shutting down the entire database).

>

> Whether any of this will work when you're dealing with corrupted segments

 is


> a moot point, of course, but give it a whirl, and let me know if it

 doesn't


> work.  There's a whole bag of other tricks we can try if this doesn't do

 the


> deed.