🧩 How to Fix ORA-01113: File Needs Media Recovery

🧩 Introduction

The error ORA-01113: file needs media recovery usually occurs when an Oracle database datafile is inconsistent or requires recovery. This happens after an unexpected shutdown, hardware failure, or when restoring a datafile from a backup. As a DBA, knowing how to diagnose and fix this issue is critical for restoring database availability.

Cause of the Error

The database detects that one or more datafiles are not in sync with the control file or redo logs. Oracle will not allow you to open the database until recovery is performed.

Steps to Resolve the Error

Step 1: Identify the Affected File

Run the following query to check which datafiles require recovery:

SELECT file#, name, status 

FROM v$datafile 

WHERE status = 'RECOVER';

This will list the datafiles that need recovery.

Step 2: Perform Media Recovery

If you have all required archive logs available, run:

RECOVER DATAFILE 'path_to_datafile';

➡️ Replace path_to_datafile with the actual datafile path.

Step 3: Open the Database

Once recovery is complete, open the database with:

ALTER DATABASE OPEN;

Alternative: Open with RESETLOGS

If recovery was incomplete and you had to use backup control files, you might need to open the database with:

ALTER DATABASE OPEN RESETLOGS;

⚠️ Note: Always make a backup after opening with RESETLOGS, as the redo log history is reset.

Example Scenario

Suppose datafile /u01/oradata/ORCL/users01.dbf shows as needing recovery. The recovery steps would be:

RECOVER DATAFILE '/u01/oradata/ORCL/users01.dbf';

ALTER DATABASE OPEN;

Prevention Tips

Always perform clean shutdowns (SHUTDOWN IMMEDIATE/TRANSACTIONAL).

Regularly back up archive logs and datafiles.

Use Oracle Recovery Manager (RMAN) for automated and reliable recovery.

Conclusion

The ORA-01113 error highlights the importance of proper backup and recovery strategies. With careful diagnosis and recovery, you can quickly restore database availability and prevent data loss.