Sunday, May 22, 2016

How to detect and fix oracle database file corruption

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
  • 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.
I am discussing data file corruption here so, use V$RECOVER_FILE command to find actual reason:


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.
To re-create the data file, run the following command. This command will create an empty file same as damaged file.

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.

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

No comments:

Post a Comment