What is media recovery?
While Instance Recovery is the "self-healing" that happens after a simple crash, Media Recovery is the heavy-duty restoration required when your hardware actually fails.
If a disk dies, a file is accidentally deleted, or a controller malfunctions, your physical Data Files are gone. Since the "brain" (the Instance) no longer has a "body" (the Database) to talk to, you have to perform Media Recovery to bring the data back from the dead.
Media recovery is necessary whenever a physical integrity issue occurs. Common scenarios include:
A hard drive failure (lost .dbf files).
Accidental deletion of a datafile at the OS level.
Data corruption discovered by the database.
Unlike Instance Recovery, which happens automatically every time you reboot after a crash, Media Recovery is a manual process initiated by a Database Administrator (DBA).
You can only perform a full Media Recovery if your database is running in ARCHIVELOG mode.
NOARCHIVELOG: If a disk dies, you can only restore your last full backup. Any work done between the backup and the crash is lost forever.
ARCHIVELOG: Oracle saves every filled Redo Log to a permanent location (Archive Logs). This allows you to "replay" every second of history since the backup was taken.
Media Recovery follows a simple but powerful logic: Restore + Recover.
You go to your backups (usually via RMAN) and grab the last "good" copy of the missing file. You place this file back onto the disk. At this point, the file is old—it might be from last night or last week.
This is where the magic happens. Oracle looks at the SCN (System Change Number) on the restored file and realizes it’s behind the rest of the database.
Oracle fetches the Archived Redo Logs and the Online Redo Logs.
It "replays" every transaction that happened to that specific file from the time of the backup until the exact moment the disk failed.
The file is brought "forward in time" until it matches the SCN of the rest of the database.
Depending on what files you have left, you can perform two types of Media Recovery:
| Type | Goal | Result |
| Complete Recovery | Recover to the very last transaction before the failure. | Zero Data Loss. |
| Incomplete Recovery | Recover to a specific point in time (PITR) in the past. | Data lost after that specific point (used for human errors like DROP TABLE). |
| Feature | Instance Recovery | Media Recovery |
| Who starts it? | Oracle (Automatically) | DBA (Manually) |
| Tools Used | SMON process | RMAN or SQL*Plus |
| Required Files | Online Redo Logs | Backups + Archived Redo Logs |
| Typical Cause | Power failure / SHUTDOWN ABORT | Disk failure / File deletion |
OPEN RESETLOGSIf you have to perform an Incomplete Recovery (going back to a point in time), you must open the database using:
ALTER DATABASE OPEN RESETLOGS;
This tells Oracle to start a new "incarnation" of the database and reset the log sequence numbers, essentially creating a new timeline.
Peer Tip: The most important command for any Oracle DBA to ensure they can survive a media failure is:
SQL> ARCHIVE LOG LIST;If the output says "No Archive Mode," you are flying without a parachute!