Media Recovery is Required or Not ?

When you start your database, Oracle uses the SCN information in the control files and

datafile headers to determine which one of the following will occur:

• Starting up normally
• Performing crash recovery
• Determining that media recovery is required

On start-up, Oracle checks the instance thread status to determine whether crash recovery is required. When the database is open for normal operations, the thread status is OPEN. When Oracle is shut down normally (normal, immediate, or transactional), a checkpoint takes place, and the instance thread status is set to CLOSED.

When your instance abnormally terminates (such as from a shutdown abort command), the thread status remains OPEN because Oracle didn’t get a chance to update the status to CLOSED. On start-up, when Oracle detects that an instance thread was abnormally left open, the system monitor process will automatically perform crash recovery. This query demonstrates how a single instance of Oracle would determine whether crash recovery is required:

SELECT
a.THREAD#, b.open_mode, a.status,
CASE
WHEN ((b.open_mode='MOUNTED') AND (a.status='OPEN')) THEN 'Crash Recovery req.'
WHEN ((b.open_mode='MOUNTED') AND (a.status='CLOSED')) THEN 'No Crash Rec. req.'
WHEN ((b.open_mode='READ WRITE') AND (a.status='OPEN')) THEN 'Inst. already open'
ELSE 'huh?'
END STATUS
FROM v$thread a,
v$database b,
v$instance c
WHERE a.THREAD# = c.THREAD#;

Oracle will start up normally if the SCN information in the control files matches the SCNs in the corresponding datafiles. If the checkpoint SCN in the datafile is less than the corresponding SCN in the control file, Oracle will throw a media recovery error. For example, if you restored a datafile from a backup, Oracle would detect that the SCN in the datafile is less than the corresponding SCN in the control file. Therefore, a recovery is required to apply changes to the datafile to catch it up to the SCN in the control file.

The following SQL query demonstrates the internal checks that Oracle performs to determine
whether media recovery is required:

SELECT a.NAME,a.checkpoint_change#,b.checkpoint_change#,
CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Recovery'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
ELSE 'what the ?'
END STATUS,
CASE
WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'No action Required'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Perform Media Recovery'
WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Restore a newer control file'
ELSE 'what the ?'
END DBA_ACTION
FROM v$datafile a, -- control file SCN for datafile
v$datafile_header b -- datafile header SCN
WHERE a.FILE# = b.FILE#;

---The V$DATAFILE_HEADER view uses the physical datafile on disk as its source.
The V$DATAFILE view uses the control file as its source.