The Oracle (tm) Users' Co-Operative FAQ

How to Recover From a Lost Datafile That Belongs to the Rollback Tablespace


Author's name: Allan W. Tham

Author's Email: allanwtham@hotmail.com

Date written: Dec 15 2001

Oracle version(s): 8.1.7.0.0

The main concern in which the lost of a datafile in Rollback tablespace is that the active transactions in the rollback segments do not get lost. There are three scenarios to take care of.

Back to index of questions


There are three scenarios:
I. The database was cleanly shut down
II. The database was not cleanly shut down
III. The database is up and running

I. The database was cleanly shut down(All the committed data are written to disks)
     1. Comment out the ROLLBACK_SEGMENTS entry in init.ora
     2. Startup restrict mount
     3. Alter database datafile '/path/filename.dbf' offline drop;
     4. Alter database open
     5. Drop tablespace tablespace_name including contents;
     6. Recreate the rollback tablespace with all of its rollback segments.
         The segment name should correspond to ROLLBACK_SEGMENTS in init.ora
     7. Shutdown immediate
     8. Uncomment the ROLLBACK_SEGMENTS in init.ora
     9. Startup
     10. select segment_name, status from dba_rollback_segs just to make sure all rollback segments are online

II. The database was not cleanly shut down (there are active transactions in the rollback segments)
     1. Restore the corrupted/lost file from backup using OS cp command
     2. Startup mount
     3. Check the status of the datafile: select name, status from v$datafile;
         Online the datafile if it's OFFLINE by Alter database datafile '/path/filename.dbf' ONLINE
     4. select v1.group#, member, sequence#, first_change#
         from v$log v1, v$logfile v2
         where v1.group#=v2.group#;
     5. Recover datafile '/path/fileame.dbf'
     6. Logs will be prompted. Confirm it until you see "Media Recovery Complete".
         If you are asked to enter a non-existence archived log, enter the full path of
         a member of the redo group where the sequence number matches the one being
         prompted (from step 4) until you see "Media Recovery Complete"
     7. Alter database open

III. The database is up and running (Simpler)
     1.Create few additional rollback segments to handle the database activities.
         Eg. Create tablespace rbstemp datafile '/path/rbstemp01.dbf' size 50M'
               Create rollback segment xxx tablespace rbstemp
     2. Offline the lost datafile: Alter database datafile '/path/filename.dbf' offline
     3. Restore the lost datafile from backup using OS cp
     4. select v1.group#, member, sequence#, first_change#
         from v$log v1, v$logfile v2
         where v1.group#=v2.group#;
     5.Recover datafile '/path/filename.dbf'
     6. Logs will be prompted. Confirm it until you see "Media Recovery Complete".
         If you are asked to enter a non-existence archived log, enter the full path of
         a member of the redo group where the sequence number matches the one being
         prompted (from step 4) until you see "Media Recovery Complete"
     7. Online the datafile : Alter database datafile '/path/filename.dbf' online


Note: this recovery method is good for Archivelog mode ON.


Further reading: Oracle Backup and Recovery; Metalink Note 1013221.6


Back to top

Back to index of questions