Complete
database recovery brings the database back to its most current state. You can
recover the entire database, or a single tablespace or data file at a time. You
must have a current control file in order to perform complete database
recovery. You must also have backups available for all files in need of media
recovery, which is the case where the physical stored file has been damaged in
some way.
You must
have all the archive logs available, from the point in time the backups were
taken, to the present. If you do not have all of them, you can only recover to
the last point in time when redo is available. If no archive logs are required,
then only online redo logs are applied.
Query the
following views:
•
V$RECOVER_FILE: To see what files need media
recovery
•
V$RECOVERY_LOG: To see which archive logs are
required to perform recovery
User-managed
complete database recovery:
•
Recovers
the database to the most recent SCN
•
Can
be done with the entire database at once, or a data file or tablespace at a
time
•
Requires
a current control file
•
Requires
backups of all files to be recovered
•
Requires
all archive logs up to the present
Performing
Complete Closed Database Recovery: Overview
Under
certain circumstances, such as damage to a file belonging to the SYSTEM
tablespace, the instance shuts down automatically. Even if the instance keeps
running, and there are problems with other data files, you may decide there is
no value is keeping the database running; too many database objects are
affected. In that case, shut down the database to perform the recovery.
If the
database is still open, you can query the V$RECOVERY_FILE view to see which
data files are in need of recovery, and query V$RECOVERY_LOG to see which archive
logs are required. That will tell you what files need to be restored from
backup, if any.
Then shut
down the database. Look into the media failure to determine the cause of the
problem. Repair the problem so that you can restore the files from backup. For
example, you may need to replace a disk drive.
Now, you
can perform the recovery using the RECOVER command. Limit the scope of the
recovery to only what is needed, such as data file or tablespace. If needed,
recover the entire database. Then bring the data files online, and open the
database.
Identifying
Recovery-Related Files
In order to
determine which data files require recovery, query the V$RECOVER_FILE view. The
ERROR column indicates why the file requires recovery. If this column has any
value other than OFFLINE NORMAL, then it needs recovery. To see the whole
picture of which data files and tablespaces are affected, join V$DATAFILE and
V$TABLESPACE in this query. Here is an example:
SELECT r.FILE#, d.NAME
df_name, t.NAME tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE
t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#;
This tells
you the extent of the damage, helping you decide what the objects of the
RECOVER command should be.
The V$RECOVERY_LOG
view shows what archive log files are needed to perform the recovery. If the
list shows files that have since been moved off the default archive log
location, then you have to restore them to some location before performing
recovery.
After recording
the results of these queries, shut down the database.
Restoring
Recovery-Related Files
After
determining what data files and archive log files are required, restore them to
appropriate disk locations. Restore a data file by copying it from the backup
location. An example is:
$ cp
/disk2/backup/datafile/user01.dbf \
> $ORACLE_BASE/oradata/ORCL/datafile/user01.dbf
> $ORACLE_BASE/oradata/ORCL/datafile/user01.dbf
If any
archive logs are needed for recovery, check whether they are still in the
default disk location for archive logs. They may not be, if they have been
moved to tape or another disk drive, for example. If they have been moved, they
need to be restored, either to the default archive log location or to a
temporary location. If there is enough space available in the default location
(which is specified by the LOG_ARCHIVE_DEST_1 initialization parameter), then
restore them there. Otherwise, you can put them on some other disk location.
When it is time to restore, you can specify that alternate location to find
archive log files.
If you had to
move a data file, that fact has to be recorded in the control file. That is
done by renaming the file. Here is an example:
SQL> ALTER DATABASE
RENAME FILE
2> '?/oradata/ORCL/datafile/user01.dbf'
3> '/newdisk/ORCL/datafile/user01.dbf';
After all
files have been restored and renamed (if necessary), mount the database and
bring all the data files online. You can check the status of each data file by
querying the V$DATAFILE view. Bring the data files online by using a command
like the following:
SQL> ALTER DATABASE
DATAFILE \
2 > '/newdisk/ORCL/datafile/user01.dbf'
ONLINE;
Applying
Redo Data
Now, the
data files have been restored to some point in the past. The archive log files
have also been restored: either to their default location or to some other
location, for the purpose of this recovery only. You are ready to perform the
actual recovery step, which means the redo is applied and the data files are
brought up to the latest SCN. Do that using the SQL*Plus RECOVER command.
SQL>
RECOVER AUTOMATIC FROM '/u01/arch_temp' DATABASE;
If you do
not specify the AUTOMATIC option, then you are prompted for each redo log file
that is about to be applied. That gives you more control over the recovery
process. Typically, AUTOMATIC is used for full recovery.
If the
archive log files have been restored to some disk location other than the
default for the database, then you must specify the FROM clause. Supply the
directory where the files are stored, and the recovery process will look there
for the files.
Finally,
open the database. It is now fully recovered.
