needs recovery status of rollback segment

[anujpathak]

One of my rollback segment status in dba_rollback_segs is
'needs recovery'. My database is not in archive mode.

alter rollback segment rb23 offline;
alter rollback segment rb23 online;
drop rollback segment rb23;
all above written statement does not work

Kindly tell me how can i solve this problem

from Anuj Pathak

[tamilselvan]

Did you get an error when you made the rollback segment offline?
If so, what was it?

[anujpathak]

following message comes when we try to put this segment offline.
ORA-01598 rollback segment rb23 is not online

though the staus of this rollback segment is 'needs recovery'

[aracnid]

hi guys...
now, the rollback segment have 3 states: online, offline, needs recovery.
Ok, so the 3'd state meens that the rollback segment 'X' had a transaction active in it (active= non commited). Now
you have 3 possibilities:
1. try to release the transaction active (it remains in the datadictionary), by relesing in the DBA_2PC_PENDING view or
PENDING_TRANS$ table.
2. if nr.1 don't work you will have in the data dictionary for ever the segment 'X' with the nees recovery status until you recreate the database.
3. (at your own risc):
IF nr.1 DON'T WORK.
update manualy undo$ table change the values status$ to online (numeric). Run catalog.sql & catproc.sql , then try keep offline by command and finaly drop.

[denevge]

If you can afford to reboot the db :

reboot the db without using the rbs23, or use the parameter _corrupted_rollback_segs

drop & create rbs23

Hope this helps
Gert

[marist89]

http://otn.oracle.com/docs/products/...block.htm#3253