The term media recovery means the restoration of files that have been lost or damaged, owing to the failure of the underlying storage media (usually a disk of some sort) or accidental removal of files. Usually, you know that media recovery is required through an error such as the following:

ORA-01157: cannot identify/lock data file 1 – see DBWR trace file ORA-01110: data file 1: ‘/u01/dbfile/db23c/system01.dbf’

The error may be displayed on your screen when performing DBA tasks, such as stopping and starting the database. Or, you might see such an error in a trace file or the alert.log file. It is also possible that since the file has not been written to or because of the OS, the error might be delayed in appearing.

If you do not notice the issue right away, with a severe media failure, the database will stop processing transactions, and users will start calling you.

To understand how Oracle determines that media recovery is required, you must first understand how Oracle determines that everything is OK.

When Oracle shuts down normally, part of the shutdown process is to flush all modified blocks in memory to disk, mark the header of each data file with the current SCN, and update the control file with the current SCN information.

On startup, Oracle checks to see if the SCN in the control file matches the SCN in the header of the data files. If there is a match, then Oracle attempts to open the data files and online redo log files. If all files are available and can be opened, Oracle starts

normally. This also means while Oracle is running and available, there is a possibility for corruption or a file to be removed that will not be noticed until that file is read or written to or Oracle is stopped and started.

The following query compares the SCN in the control file for each data file with the SCN in the data file headers:

If the control file SCN values are greater than the data file SCN values, then media recovery is most likely required.

This would be the case if you restored a data file from a backup and the SCN in the restored data file had an SCN less than the data file in the current control file.

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

You can also directly query the V$DATABASE_HEADER for more information. The ERROR and RECOVER columns report any potential problems. For example, a YES or NULL value in the RECOVER column indicates that there is a problem:

SQL> select file#, status, error, recover from v$datafile_header;