Rollback Segment Needs Recovery 1

[THoey]

I have a rollback segment that I was trying to take offline and then remove. Now it says it is offline and it needs recovery. There were no updates or inserts going on at the time that I tried all of this (all data loads are done at night and only SELECT's are done during the day), so I think I would just like to continue to remove it, but Oracle won't let me. It says it is unavailable. When I try to place it online, it says it can't.

Any idea how to do this? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[rcurva]

The reasons could be a corrupted datafile, an offline tablespace or a corrupted rollback segment. You could try to check for any of these first.

This occurs during rollback when undo of modified blocks is being applied but these blocks are unavailable.

Robbie

 

[kindus]

I think you may have to use _corrupted_rollback_segment parameter which is undocumented.
1) Put all the rollback segments of the corrupted tablespace in _corrupted_rollback_segment parameter
2) bounce the database
3) drop the rollback segments, tablespace and data files
4) recreate new rollback segments in a different disk if there may be problem in the disk

Be sure to take complete backup of your database!! I think oracle will be there in case of any problem related to undocumented features.

good luck

 

[kindus]

I mnt oracle will not be there in case of any problem related to undocumented features.

 

[karluk]

Kindus might be right about what you will have to do, but this is way out of the scope of the kind of advice we can reasonably give on a public forum like tek-tips. Please don't do anything like this without working closely with Oracle support, and with their guidance and approval.

You may have already researched this error on Metalink. The most relevant document seems to be 28812.1. I would go through the steps outlined in that document to try to recover the rollback segment. If that fails, Oracle support can suggest other approaches, like _corrupted_rollback_segment, but these should really be your last resort.