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
Did you get an error when you made the rollback segment offline?
If so, what was it?
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'
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
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.
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
Click Here to Expand Forum to Full Width