Loss Of Rollback Segment Datafile
Open Database Recovery
Case(a) : Recovery By Recreating the Tablespace.
We should check the v$rollstat view for any active transactions. Rollback segments which do not have any active transactions can be taken offline.
Transactions which have status 'PENDING OFFLINE'. could pose a problem as the active transactions in these rollback segments must first be either committed or rolled back. We should contact the users holding these active transactions and direct them to commit (preferably) or rollback. Otherwise we have to kill the sessions holding the active transactions.
Once all the rollback segments have been taken offline and dropped, we should drop the rollback segment tablespace with the including contents option, then recreate the tablespace and the rollback segments.
Case(b) : Recovery by Restoring a Backup.
If we have detected the loss of a rollback segment datafile and the database is still up and running, we should not shut it down but try and recover by restoring the backup file with the database open.
SVRMGR> ALTER DATABASE DATAFILE '/ekdw3/ORACLE/betarbs01.dbf' OFFLINE;
Restore the datafile from the backup.(rollback segment datafile).
SVRMGR> RECOVER DATAFILE '/ekdw3/ORACLE/beta/rbs01.dbf'
Apply arch redo log files
SVRMGR> ALTER DATABASE DATAFILE '/ekdw3/ORACLE/beta/rbs01.dbf' ONLINE;