How to recover a corrupted datafile in Oracle

Recovering a corrupted datafile in Oracle involves several steps. The approach depends on whether you have a backup, are using Oracle Recovery Manager (RMAN), and whether you are running in ARCHIVELOG mode. Below is a comprehensive guide for different scenarios.

Prerequisites

1. ARCHIVELOG Mode: Ensure your database is in ARCHIVELOG mode to allow point-in-time recovery.
2. Backups: Ensure you have valid and recent backups of your database.

Recovery Scenarios

Using RMAN

1. Identify the Corrupted Datafile:

SELECT FILE#, NAME, STATUS 

FROM V$DATAFILE 

WHERE STATUS='RECOVER' OR STATUS='OFFLINE';

2. Restore and Recover the Datafile:

rman target /

In RMAN prompt:

RESTORE DATAFILE <file_number>; RECOVER DATAFILE <file_number>;

Replace <file_number> with the file number identified in the previous step.

3. Bring the Datafile Online:

ALTER DATABASE DATAFILE <file_number> ONLINE;

Without RMAN

1. Identify the Corrupted Datafile:

SELECT FILE#, NAME, STATUS FROM V$DATAFILE WHERE STATUS='RECOVER' OR STATUS='OFFLINE';

2. Take the Datafile Offline:

ALTER DATABASE DATAFILE '<file_name>' OFFLINE;

3. Restore the Datafile from Backup:

Copy the datafile from your backup location to the original datafile location.

4. Recover the Datafile:

RECOVER DATAFILE '<file_name>';

5. Apply Archived Redo Logs:

If prompted, apply the archived redo logs manually.

6. Bring the Datafile Online:

ALTER DATABASE DATAFILE '<file_name>' ONLINE;

Handling Datafile Corruption in Noarchivelog Mode

1. Shut Down the Database:

SHUTDOWN IMMEDIATE;

2. Restore the Datafile from Backup:

Copy the datafile from your backup location to the original datafile location.

3. Startup the Database:

        STARTUP MOUNT;         ALTER DATABASE OPEN;

Steps for Advanced Recovery

Using Flashback

1. Flashback Database: If Flashback Database is enabled, you can use it to revert the entire database to a point before the corruption occurred.

        FLASHBACK DATABASE TO SCN <scn_number>;

Replace <scn_number> with the SCN before the corruption.

Using Data Recovery Advisor (DRA)

1. Run DRA:

        EXECUTE DBMS_DATA_REPAIR.ADMINISTER_AUTO;

2. Review Recommendations:

        SELECT * FROM V$IR_REPAIR;

3. Execute Repair:

Follow the recommendations provided by DRA.

Preventative Measures

1. Regular Backups: Schedule regular backups using RMAN.
2. Monitoring: Regularly monitor datafile health using Oracle Enterprise Manager or custom scripts.
3. ARCHIVELOG Mode: Keep your database in ARCHIVELOG mode.
4. Flashback Technology: Enable and configure Flashback Database if possible.

Example RMAN Script for Recovery

rman target /

RUN {

   ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;

   RESTORE DATAFILE 4;

   RECOVER DATAFILE 4;

   SQL 'ALTER DATABASE DATAFILE 4 ONLINE';

   RELEASE CHANNEL ch1;

}

Example SQL*Plus Commands for Manual Recovery

-- Take the datafile offline

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' OFFLINE;

-- Restore the datafile from backup (manually copy the file)

-- Recover the datafile

RECOVER DATAFILE '/path/to/datafile.dbf';

-- Bring the datafile online

ALTER DATABASE DATAFILE '/path/to/datafile.dbf' ONLINE;

By following these procedures, you can effectively recover a corrupted datafile in Oracle, ensuring minimal data loss and downtime.