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. |
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
