Archivelog Mode Failure Scenarios
Effective file drills for media failures
Contents

Introduction
Loss of a Temporary Tablespace Datafile
Loss of an Index Tablespace Datafile
Loss of a Data Tablespace Datafile
Loss of a Rollback Segment Tablespace Datafile
Loss of an Unarchived Online Redolog File (a member) in a Log Group
Loss of all Unarchived Online Redolog Files (all members) in a Log Group

Introduction   This document identifies media failure scenarios and their recovery procedures for a 7.1 database running in archivelog mode. It is assumed that regular, comprehensive online backups occur. These backups must include all data files, archived redo log files, and control files. In addition, the control file should be backed up anytime the schema of the database changes.
(Back To Table of Contents)
Loss of a Temporary Tablespace Datafile   It is possible for a user to create a segment in the temporary tablespace. Before following the temporary tablespace datafile recovery procedure, make sure no user objects exist in this tablespace. If any data, index or rollback segments exist in the damaged datafile, recover it as per the associated scenarios for that segment type.

Attention: Do not perform this procedure if any user objects or rollback segments exist in the damaged datafile.

Oracle uses the temporary tablespace to process sorts. No permanent data is stored there as a result of sort processing. The temporary space used during sort processing is only meaningful during the lifetime of the transaction. If this space is damaged, transactions using this space will have to be resubmitted but no damage will occur to permanent data. For this reason, we can consider dropping and recreating the temporary tablespace when a media failure occurs.
Perform a full offline backup.  
Mount the database SQLDBA> startup mount
Put the datafile offline SQLDBA> alter database datafile '<filename>' offline;
Open the database SQLDBA> alter database open;
Put the tablespace offline SQLDBA> alter tablespace '<tablespace name>' offline;
Examine tablespace status SQLDBA> select tablespace_name, status from dba_tablespaces;
If offline, drop tablespace SQLDBA> drop tablespace '<tablespace name>';
Recreate tablespace.  
Shutdown the database SQLDBA> shutdown;
Perform a full offline backup.  
(Back To Table of Contents)
Loss of an Index Tablespace Datafile   The database will be available during datafile recovery. However, indexes stored in the datafile will be unavailable until is is recovered, so performance will be degraded. Online recovery is possible as long as media recovery locks can be acquired on data files. The datafile must be taken offline for online recovery. Separate terminal sessions can be used to perform parallel recovery.
 Perform a full offline backup  
Mount the database SQLDBA> startup mount
Put the datafile offline SQLDBA> alter database datafile '<filename>' offline;
Open the database SQLDBA> alter database open;
Restore the datafile from the most recent backup  
Rename the datafile  SQLDBA> alter database rename file '<old filename>' to '<new filename>'
All data files that are to be recovered must be online during a complete media recovery  
Examine damaged datafile SQLDBA> select substr(name, 1, 50) name, status from v$datafile;
Put datafile online if offline SQLDBA> alter database datafile '<filename>' online;
Recover the datafile SQLDBA> recover datafile '<filename>';
Apply redo, you are interactively prompted to accept/specify redolog files  
Bring the datafile online SQLDBA> alter database datafile '<filename>' online;
Shutdown the database SQLDBA> shutdown;
Perform a full offline backup  
 Another method of recovering an index tablespace datafile involves dropping and recreating the tablespace, and recreating all of the indexes it contains.
(Back To Table of Contents)
Loss of a Data Tablespace Datafile   The database will be available during datafile recovery. However, objects stored in the datafile will be unavailable. Online recovery is possible as long as media recovery locks can be acquired on data files. The datafile must be taken offline for online recovery. Separate terminal sessions can be used to perform parallel recovery.
 Perform a full offline backup  
Mount the database SQLDBA> startup mount
Put the datafile offline SQLDBA> alter database datafile '<filename>' offline;
Open the database SQLDBA> alter database open;
Restore the datafile from the most recent backup  
Rename the datafile SQLDBA> alter database rename file '<old filename>' to '<new filename>'
All data files that are to be recovered must be online during a complete media recovery  
Examine damaged datafile SQLDBA> select substr(name, 1, 50) name, status from v$datafile;
Put datafile online if offline SQLDBA> alter database datafile '<filename>' online;
Recover the datafile SQLDBA> recover datafile '<filename>';
Apply redo, you are interactively prompted to accept/specify redolog files  
Bring the datafile online SQLDBA> alter database datafile '<filename>' online;
Shutdown the database SQLDBA> shutdown;
Perform a full offline backup  
(Back To Table of Contents)
Loss of a Rollback Segment Tablespace Datafile
Perform a full offline backup  
Edit the init.ora file commenting out the damaged rollback segment.  
Mount the database SQLDBA> startup mount
Take the damaged datafile offline SQLDBA> alter database datafile '<filename>' offline;
Open the database SQLDBA> alter database open;
Restore the datafile from the most recent backup.  
Recover the datafile SQLDBA> recover datafile '<filename>' ;
Apply redo, you are interactively prompted to accept/specify redolog files.  
Bring the datafile online SQLDBA> alter database datafile '<filename>' online;
Edit the init.ora file uncommenting the commented rollback segment  
Shutdown the database SQLDBA> shutdown;
Perform a full offline backup  
 (Back To Table of Contents)
Loss of an Unarchived Online Redolog File (a Member) in a Log Group
Perform a full offline backup
Create a new log group that resembles as closely the damaged log group
Examine all log groups
If damaged log group is 'available', drop it
At this point you can stop since the new log group results in the same number of log groups as existed before the failure. You could also recreate the dropped log group and remove the new log group if you were unable to create the new log group with the appropriate settings (i.e. size, members, names).
Shutdown the database SQLDBA> shutdown;
Perform a full offline backup  
(Back To Table of Contents)
Loss of all Unarchived Online Redolog Files (all Members) in a Log Group   Incomplete recovery must be performed and data will be lost. The database will not be available until recovery is complete.
 Perform a full offline backup  
Restore all data files from their most recent backup (online or offline)  
Mount the database SQLDBA> startup mount
Perform Incomplete Recovery SQLDBA> recover database until cancel;
Apply redo, you are interactively prompted to accept/specify redolog files  
Cancel recovery Enter CANCEL when prompted to apply the lost log file.
Open the database resetting logs SQLDBA> alter database open resetlogs;
Shutdown the database SQLDBA> shutdown;
Perform a full offline backup  
(Back To Table of Contents)