Database Reference
In-Depth Information
Oracle compares the SCN in the control file with the SCN in the data file header. You can check the SCN in the
data file header by querying
V$DATAFILE_HEADER
; for example,
select file#, fuzzy, checkpoint_change#
from v$datafile_header
where file#=4;
FILE# FUZ CHECKPOINT_CHANGE#
---------- --- ------------------
4 YES 3502285
Note that the SCN recorded in
V$DATAFILE_HEADER
is less than the SCN in
V$DATAFILE
for the same data file.
If you attempt to open your database, Oracle throws an error stating that media recovery is required (meaning that
you need to apply redo) to synchronize the SCN in the data file with the SCN in the control file. The
FUZZY
column
is set to
YES
. This indicates that redo must be applied to the data file before it can be opened for use. Here is what
happens when you try to open the database at this point:
SQL> alter database open;
alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery...
Oracle doesn't let you open the database until the SCN in all data file headers matches the corresponding SCN in
the control file.
Step 3. Issue the Appropriate RECOVER Statement
The archive redo logs and online redo logs have the information required to catch up the data file SCN to the control
file SCN. You can apply redo to the data file that needs media recovery by issuing one of the following SQL*Plus
statements:
•
RECOVER DATAFILE
•
RECOVER TABLESPACE
RECOVER DATABASE
Because only one data file in this example needs to be recovered, the
RECOVER DATAFILE
statement is
appropriate. However, keep in mind that you can run any of the previously listed
RECOVER
statements, and Oracle will
figure out what needs to be recovered. In this particular scenario, you may find it easier to remember the name of the
tablespace that contains the restored data file(s) than to remember the data file name(s). Next, any data files that need
recovery in the
USERS
tablespace are recovered:
•
SQL> recover tablespace users;
