One or more datafiles damaged

This page describes the procedure to follow when one or more database datafiles are damaged:

• Restore an intact copy of the datafile from backup (tape)
• Check the directory of archive logs for the number of archived log files. There should be a sufficient number of files for recovery. All the archived logfiles with timestamp after the latest hot backup should be present. If they are not present restore them from tape to the archive directory.
• Start sqlplus:

sqlplus
connect SYS AS SYSDBA
SQL> shutdown abort
Oracle Instance Aborted
SQL> startup mount
Oracle Instance Started
Database Mounted
SQL> select * from v$recover_file;

• This will give you a list of files on which media recovery will be done. Take a printout of this.

SQL> select * from v$recovery_log;

• This will give you the list of archived log files which will be applied to the database for a rollforward. This list is very important. Check in the archive log directory to ascertain the presence of all the files. If some file is not present on the archive directory, copy them from tape. If some file is not present at all, then you will have to perform incomplete recovery.

SQL> recover database ( in case of regular recovery )

• If you perform incomplete recovery, enter recover database until cancel. SQL*Plus will prompt you for files to apply. Just keep on pressing RETURN till the absent file is prompted. Enter CANCEL. After some time, the message Media Recovery Complete will appear. If due to any reason the archived log files are not found in the proper directory, SQL*Plus will ask for the logfile with the sequence no and the name. You must copy the file to the archive directory and then give the name to SQL*Plus. If the files are present and still SQL*Plus prompts for the filename, just enter AUTO at the prompt of file name and the appropriate log files will be applied automatically.

SQL> alter database open
Statement Processed

• If you have performed incomplete recovery, enter alter database open resetlogs.

SQL> shutdown
Database Closed
Database Dismounted
Oracle Instance Shutdown
SQL> startup mount
Oracle Instance Started
Database Mounted
SQL> alter database backup controlfile to /u01/ORADATA/archive/prodcontrolfile.backup reuse;
Statement Processed
SQL> Alter database open
Statement Processed
SQL> exit

• At this point it is advisable to run a hot backup or, better still, a cold backup.