Loss of Rollback Segment Datafile.
Note : Database is running in ARCHIVELOG mode
Closed Database Recovery
The recovery procedure in this case depends on whether the database was cleanly shutdown or not.
Case(a) : The rollback segment datafile was lost while database was running.
In this case as the database was not shutdown cleanly, uncomitted transactions would be
existing which need to be rolled back on startup. Thus the datafile cannot be taken offline
or dropped, but needs to be restored from a backup.
Restore : Only the related rollback segment datafile.
SVRMGR> STARTUP MOUNT
(We need to first comment out the ROLLBACK_SEGMENTS parameter in the init.ora file or an error would be returned like 'Rollback segment R01 not available.' )
also add the line
-- offline_rollback_segments
or -- corrupted_rollback_segments (r01,r02,r03 ...)
SVRMGR> ALTER DATABASE DATAFILE '/ekdw3/ORACLE/beta/rbs01.dbf' OFFLINE;
SVRMGR> ALTER DATABASE OPEN;
SVRMGR> RECOVER DATAFILE '/ekdw3/ORACLE/beta/rbs01.dbf';
Apply arch redo logs
SVRMGR> ALTER DATABASE DATAFILE '/ekdw3/ORACLE/beta/rbs01.dbf' ONLINE;
SVRMGR> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
R01 NEEDS RECOVERY
R02 NEEDS RECOVERY
R03 NEEDS RECOVERY
R04 NEEDS RECOVERY
Shutdown the database and remove the lines from the init.ora pertaining to
-- offline_rollback_segments
or -- corrupted_rollback_segments
Remove comment from rollback_segments in the init.ora.
Startup the database and check the status of the rollback segments from the view dba_rollback_segs
Bring them all online if not already showing status as ONLINE by the command
'ALTER ROLLBACK SEGMENT ….ONLINE;’
Case(b) : The rollback segment datafile was lost after the database was shutdown cleanly.
As in the case above comment out the ROLLBACK_SEGMENTS entry in the init.ora file.
If we get the ORA errors-604, -376 or -11110 we should add either of the following also in the init.ora :
_corrupted_rollback_segments = (R01,R02 ...)
or
_offline_rollback_segments = (R01,R02....)
SVRMGR> STARTUP MOUNT
SVRMGR> ALTER DATABASE DATAFILE '/ekdw3/ORACLE/beta/rbs01.dbf' OFFLINE DROP;
SVRMGR> ALTER DATABASE OPEN;
SVRMGR> DROP TABLESPACE RBS INCLUDING CONTENTS;
Recreate the tablespace, bring it online, create the rollback segments and bring them online.
Remove the parameters which you added to the init.ora and remove the comment from the ROLLBACK_SEGMENTS parameter to bring it back as it was before.