You are enjoying holiday with family and your colleague start calling you about the data file corruption with no clear reason then, what you are doing to do next? You will handle the situation on call or may not.
Data file corruption comes under the media recovery. To start the recovery process, it is very important to determine actual reason of it.
Determine the actual reason of corruption: Determining Which Files Need Recovery
SQL> SELECT * FROM V$RECOVER_FILE;
The following query will display the status of files that needed media recovery with File ID, Status (ONLINE or OFFLINE), Error and Time etc.
Note: The Error filed shows NULL if a reason is unknown and OFFLINE NORMAL if recovery is needed.
Now, you get the corruption. Now, it’s time to take appropriate action to repair corrupt file.
ALTER DATABASE CREATE DATAFILE ‘DISK1:FILE1’ AS ‘DISK2:FILE2’;
The old location of data file is DISK 1 and file name is FILE1 and new location of a data file is DISK2 and file name is FILE2. Now, you can perform the media recovery on the empty file.
RECOVER DATAFILE ‘DISK2:FILE1’
Note: CREATE DATABASE clause cannot recreate the data file of SYSTEM tablespace.
I hope there is no doubts on the steps that I have mentioned above to repair the oracle database. One advice to all the readers is that always maintain a backup copy of your database. It provides an easy way to restore the database.
Data file corruption comes under the media recovery. To start the recovery process, it is very important to determine actual reason of it.
Determine the actual reason of corruption: Determining Which Files Need Recovery
- View V$RECOVER_FILE to determine which data files need recovery.
- View V$ARCHIVED_LOG for a list of all archived redo log files for the database.
- View V$RECOVERY_LOG for a list of all archived redo log files which required for the recovery.
SQL> SELECT * FROM V$RECOVER_FILE;
The following query will display the status of files that needed media recovery with File ID, Status (ONLINE or OFFLINE), Error and Time etc.
Note: The Error filed shows NULL if a reason is unknown and OFFLINE NORMAL if recovery is needed.
Now, you get the corruption. Now, it’s time to take appropriate action to repair corrupt file.
- Restore backup of damaged data files: If one or more data files are damaged by media failure then you must restore the backup of damaged data files before starting the recovery process. If you are unable to restore the damaged files to its original location in the memory, then you have to assign new locations of these files to the control file of associated database.
- Re-Creation of data files (Backup is unavailable): If backup is not available then, you can also recover the data file if:
- The Control file contains the name of the damaged data file.
- All archived log files are written the creation of data file.
ALTER DATABASE CREATE DATAFILE ‘DISK1:FILE1’ AS ‘DISK2:FILE2’;
The old location of data file is DISK 1 and file name is FILE1 and new location of a data file is DISK2 and file name is FILE2. Now, you can perform the media recovery on the empty file.
RECOVER DATAFILE ‘DISK2:FILE1’
Note: CREATE DATABASE clause cannot recreate the data file of SYSTEM tablespace.
- Restore achieved redo log files: Use query V$LOG_HISTORY and V$RECOVERY_LOG to determine that which archived redo files you need. You need this information from time when the data file was added to the database if, there is no backup of the data file is available.
- A third party tool can be helpful: Yes, a third party tool like Stellar Phoenix Oracle Database Recovery is able to repair corrupt data file (.DBF) of oracle database 9i, 10g, and 11g.
I hope there is no doubts on the steps that I have mentioned above to repair the oracle database. One advice to all the readers is that always maintain a backup copy of your database. It provides an easy way to restore the database.
