You can often use the table V$RECOVER_FILE to determine which files to recover. This view lists all files that need to be recovered, and explains why they need to be recovered.
The following query displays the file ID numbers of datafiles that require media recovery as well as the reason for recovery (if known) and the SCN/time when recovery needs to begin:
SQL> SELECT * FROM v$recover_file;
FILE# ONLINE ERROR CHANGE# TIME
---------- ------- ------------------ ---------- ---------
14 ONLINE 0
15 ONLINE FILE NOT FOUND 0
21 OFFLINE OFFLINE NORMAL 0
|
Note: The view is not useful if the control file currently in use is a restored backup or a new control file created since the media failure occurred. A restored or re-created control file does not contain the information Oracle needs to fill V$RECOVER_FILE accurately. |
You can create tables and indexes using the CREATE TABLE AS SELECT statement. You can also specify that Oracle create them as unrecoverable. When you create a table or index as unrecoverable, Oracle does not generate redo log records for the operation. Thus, you cannot recover objects created unrecoverable, even if you are running in ARCHIVELOG mode.
|
Note: If you cannot afford to lose tables or indexes created unrecoverable, then take a backup after the unrecoverable table or index is created. |
Media recovery with the USING BACKUP CONTROLFILE option checks for read-only files. You cannot recover a read-only file. To avoid this error, take datafiles from read-only tablespaces offline before doing recovery with a backup control file.
Use the correct version of the control file for the recovery. If the tablespace will be read-only when the recovery is complete, then the control file must be from a time when the tablespace was read-only. Similarly, if the tablespace will be read-write at the end of recovery, it should be read-write in the control file.
If a media failure permanently damages one or more datafiles of a database, you must restore backups of the damaged datafiles before you can recover the damaged files. If you cannot restore a damaged datafile to its original location (for example, you must replace a disk, so you restore the files to an alternate disk), then you must indicate the new locations of these files to the control file of the associated database
If a datafile is damaged and no backup of the file is available, you can still recover the datafile if:
|
Note: You cannot re-create any of the datafiles for the SYSTEM tablespace by using the CREATE DATAFILE clause of the ALTER DATABASE statement because the necessary redo data is not available. |
All archived redo log files required for the pending media recovery eventually need to be on disk so that they are readily available to Oracle.
|
View |
Description |
|
V$LOG_HISTORY |
Lists all of the archived logs, including their probable names, given the current archived log file naming scheme as set by the initialization parameter LOG_ARCHIVE_FORMAT. |
|
V$RECOVERY_LOG |
Lists only the archived redo logs that Oracle needs to perform recovery. It also includes the probable names of the files, using LOG_ARCHIVE_FORMAT. |
Oracle uses these basic media recovery SQL*Plus statements, which differ only in the way the system determines the set of files to be recovered:
Each statement uses the same criteria to determine whether files are recoverable. Oracle prevents two recovery sessions from recovering the same file and prevents media recovery of a file that is in use.
RECOVER DATABASE performs media recovery on all online datafiles that require redo to be applied. For example, issue the following at the SQL prompt to recover the whole database:
RECOVER DATABASE
To perform media recovery on an entire database, the database must be mounted EXCLUSIVE and closed.
RECOVER TABLESPACE performs media recovery on all datafiles in the tablespaces listed. For example, enter the following at the SQL prompt to recover tablespace TBS_1:
RECOVER TABLESPACE tbs_1
The tablespaces must be offline to perform the recovery. Oracle indicates an error if none of the files require recovery.
RECOVER DATAFILE lists the datafiles to be recovered. For example, enter the following at the SQL prompt to recover datafile
/oracle/dbs/tbs_22.f:
RECOVER DATAFILE '/oracle/dbs/tbs_22.f'
The database can be open or closed, provided that you can acquire the media recovery locks. If the database is open in any instance, then datafile recovery can only recover offline files.
During complete or incomplete media recovery, Oracle applies redo log files to the datafiles during the roll forward phase of media recovery. Because rollback data is recorded in the redo log, rolling forward regenerates the corresponding rollback segments. Rolling forward proceeds through as many redo log files as necessary to bring the database forward in time.
As a log file is needed, Oracle suggests the name of the file. For example, if you are using SQL*Plus, it returns the following lines and prompts:
ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread# ORA-00289: Suggestion : logfile ORA-00280: Change #### for thread # is in sequence # Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]
Oracle suggests archived redo log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_1 or LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT and using information from the control file.
In some cases, you may want to override the current setting for the destination parameter as a source for redo log files. For example, assume that a database is open and an offline tablespace must be recovered, but not enough space is available to mount the necessary redo log files at the location specified by the destination parameter.
% cp /disk1/oracle/arc_dest/* /disk2/temp
SET LOGSOURCE "/disk2/temp"
RECOVER TABLESPACE offline_tbsp
Consider overriding the current setting for the destination parameter when not enough space is available to mount all the required log files at any one location.
When using SQL*Plus, use the following statement to automate the application of the default filenames of archived redo logs needed during recovery:
SET AUTORECOVERY ON
No interaction is required when you issue the RECOVER statement, provided that the necessary files are in the correct locations with the correct names.
If you execute SET AUTORECOVERY OFF, which is the default option, then you must enter the filenames manually, or accept the suggested default filename.
Use parallel block recovery to tune the roll forward phase of media recovery. In parallel block recovery, Oracle uses a "division of labor" approach to allocate different processes to different data blocks while rolling forward, thereby making the procedure more efficient. For example, if the redo log contains a substantial number of entries, spawned process 1 takes responsibility for one part of the log file, process 2 takes responsibility for another part, process 3 takes responsibility for a third part, etc.
|
Note: Typically, recovery is I/O-bound on reads to data blocks. Consequently, parallelism at the block level may only help recovery performance if it increases total I/Os, for example, by bypassing operating system restrictions on asynchronous I/Os. Systems that have efficient asynchronous I/O typical see little improvement from using parallel block recovery. |
Use the following SQL*Plus RECOVER statement to perform parallel media recovery:
RECOVER PARALLEL ... ;
The PARALLEL clause of the RECOVER statement has the following options:
|
DEGREE integer |
Specifies the number of recovery processes used to apply redo entries to datafiles on each instance. |
|
DEGREE DEFAULT |
Indicates that twice the number of datafiles being recovered is the number of recovery processes to use. |
|
INSTANCES integer |
Specifies the number of instances to use for parallel recovery. The number of recovery processes specified with DEGREE is used on each instance. Thus, the total number of recovery processes is the integer specified with DEGREE multiplied by the integer specified with INSTANCES. INSTANCES is only pertinent for the Oracle Parallel Server. |
|
INSTANCES DEFAULT |
Has operating system-specific consequences. For more information about the default behavior of the INSTANCES DEFAULT specification, see the Oracle8i Parallel Server Documentation Set: Oracle8i Parallel Server Concepts; Oracle8i Parallel Server Setup and Configuration Guide; Oracle8i Parallel Server Administration, Deployment, and Performance manual. |
For example, to specify that 5 recovery processes should operate during recovery, specify as follows:
RECOVER DEGREE 5 ... ;
In a different scenario, assume that you are recovering 10 datafiles. Issue the following statement to specify that 20 processes should perform recovery:
RECOVER DEGREE DEFAULT;
|
Note: The RECOVERY_PARALLELISM initialization parameter specifies the number of concurrent recovery processes for instance or crash recovery only. Media recovery is not affected by this parameter. |