Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 2 (10.2) Part Number B14191-02 |
|
|
View PDF |
To perform recovery, Oracle recommends that you use the RECOVER
SQL statement in SQL*Plus. You can also use the SQL statement ALTER
DATABASE
RECOVER
, but the RECOVER statement is simpler in most cases.
To start any type of media recovery, you must adhere to the following restrictions:
You must have administrator privileges.
All recovery sessions must be compatible.
One session cannot start complete media recovery while another performs incomplete media recovery.
You cannot start media recovery if you are connected to the database through a shared server process.
Oracle recommends that you use the SQL*Plus RECOVER
command rather than the ALTER
DATABASE
RECOVER
statement to perform media recovery. In almost all cases, the SQL*Plus method is easier.
When using SQL*Plus to perform media recovery, the easiest strategy is to perform automatic recovery. Automatic recovery initiates recovery without manually prompting SQL*Plus to apply each individual archived log.
When using SQL*Plus, you have two options for automating the application of the default filenames of archived redo logs needed during recovery:
Issuing SET
AUTORECOVERY
ON
before issuing the RECOVER
command
Specifying the AUTOMATIC
keyword as an option of the RECOVER
command
In either case, no interaction is required when you issue the RECOVER
command if the necessary files are in the correct locations with the correct names. The filenames used when you use automatic recovery are derived from the concatenated values of LOG_ARCHIVE_FORMAT
with LOG_ARCHIVE_DEST_
n
, where n
is the highest value among all enabled, local destinations.
For example, assume the following initialization parameter settings are in effect in the database instance:
LOG_ARCHIVE_DEST_1 = "LOCATION=/arc_dest/loc1/" LOG_ARCHIVE_DEST_2 = "LOCATION=/arc_dest/loc2/" LOG_ARCHIVE_DEST_STATE_1 = DEFER LOG_ARCHIVE_DEST_STATE_2 = ENABLE LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc
In this case, SQL*Plus automatically suggests the filename /arc_dest/loc2/arch_%t_%s_%r.arc
(where %t
is the thread, %s
is the sequence and %r
is the resetlogs ID).
If you run SET
AUTORECOVERY
OFF
, which is the default option, then you must enter the filenames manually, or accept the suggested default filename by pressing the Enter key.
Run the SET
AUTORECOVERY
ON
command to enable on automatic recovery.
To automate the recovery using SET AUTORECOVERY:
Restore a backup of the offline datafiles. This example restores an inconsistent backup of all datafiles with an operating system utility:
% cp /backup/datafiles/*.dbf $ORACLE_HOME/oradata/trgt/
Ensure the database is mounted. For example, if the database is shut down, run:
STARTUP MOUNT
Enable automatic recovery. For example, in SQL*Plus run:
SET AUTORECOVERY ON
Recover the desired datafiles. This example recovers the whole database:
RECOVER DATABASE
The database automatically suggests and applies the necessary archived logs.
Open the database. For example:
ALTER DATABASE OPEN;
Note:
After issuing the SQL*PlusRECOVER
command, you can view all files that have been considered for recovery in the V$RECOVERY_FILE_STATUS
view. You can access status information for each file in the V$RECOVERY_STATUS
view. These views are not accessible after you terminate the recovery session.Besides using SET
AUTORECOVERY
to turn on automatic recovery, you can also simply specify the AUTOMATIC
keyword in the RECOVER
command.
To automate the recovery with the RECOVER AUTOMATIC command:
Restore a backup of the offline datafiles. This example restores a backup of all datafiles:
% cp /backup/datafiles/*.dbf $ORACLE_HOME/oradata/trgt/
Ensure the database is mounted. For example, if the database is shut down, run:
STARTUP MOUNT
Recover the desired datafiles by specifying the AUTOMATIC
keyword. This example performs automatic recovery on the whole database:
RECOVER AUTOMATIC DATABASE
The database automatically suggests and applies the necessary archived logs.
Open the database. For example:
ALTER DATABASE OPEN;
If you use an Oracle Real Application Clusters configuration, and if you are performing incomplete recovery or using a backup control file, then the database can only compute the name of the first archived redo log file from the first redo thread. You may have to manually apply the first log file from the other redo threads. After the first log file in a given thread has been supplied, the database can suggest the names of the subsequent logs in this thread.
See Also:
Your platform-specific Oracle documentation for examples of log file applicationRecovering when the archived logs are in their default location is the simplest case. As a log is needed, the database suggests the filename. If you are running nonautomatic media recovery with SQL*Plus, then the output is displayed in this format:
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 ]
For example, SQL*Plus displays output similar to the following:
ORA-00279: change 53577 generated at 11/26/02 19:20:58 needed for thread 1 ORA-00289: suggestion : /oracle/oradata/trgt/arch/arcr_1_802.arc ORA-00280: change 53577 for thread 1 is in sequence #802 Specify log: [<RET> for suggested | AUTO | FROM logsource | CANCEL ]
Similar messages are returned when you use an ALTER
DATABASE
...
RECOVER
statement. However, no prompt is displayed.
The database constructs suggested archived log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_
n
(where n
is the highest value among all enabled, local destinations) and LOG_ARCHIVE_FORMAT
and using log history data from the control file. The following are possible settings:
LOG_ARCHIVE_DEST_1 = 'LOCATION = /oracle/oradata/trgt/arch/' LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc SELECT NAME FROM V$ARCHIVED_LOG; NAME ---------------------------------------- /oracle/oradata/trgt/arch/arcr_1_467.arc /oracle/oradata/trgt/arch/arcr_1_468.arc /oracle/oradata/trgt/arch/arcr_1_469.arc
Thus, if all the required archived log files are mounted at the LOG_ARCHIVE_DEST_1
destination, and if the value for LOG_ARCHIVE_FORMAT
is never altered, then the database can suggest and apply log files to complete media recovery automatically.
Performing media recovery when archived logs are not in their default location adds an extra step. You have the following mutually exclusive options:
Edit the LOG_ARCHIVE_DEST_
n
parameter that specifies the location of the archived redo logs, then recover as usual.
Use the SET
statement in SQL*Plus to specify the nondefault log location before recovery, or the LOGFILE
parameter of the RECOVER
command
You can edit the initialization parameter file or issue ALTER
SYSTEM
statements to change the default location of the archived redo logs.
To change the default archived log location before recovery:
Use an operating system utility to restore the archived logs to a nondefault location. For example, enter:
% cp /backup/arch/* /tmp/
Change the value for the archive log parameter to the nondefault location. You can issue ALTER
SYSTEM
statements while the instance is started, or edit the initialization parameter file and then start the database instance. For example, while the instance is shut down edit the parameter file as follows:
LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/' LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc
Using SQL*Plus, start a new instance by specifying the edited initialization parameter file, and then mount the database. For example, enter:
STARTUP MOUNT
Begin media recovery as usual. For example, enter:
RECOVER DATABASE
In some cases, you may want to override the current setting for the archiving destination parameter as a source for redo log files.
To recover archived logs in a nondefault location with SET LOGSOURCE:
Using an operating system utility, copy the archived redo logs to an alternative location. For example, enter:
% cp $ORACLE_HOME/oradata/trgt/arch/* /tmp
Specify the alternative location within SQL*Plus for the recovery operation. Use the LOGSOURCE
parameter of the SET
statement or the RECOVER
...
FROM
clause of the ALTER
DATABASE
statement. For example, start SQL*Plus and run:
SET LOGSOURCE "/tmp"
Recover the offline tablespace. For example, to recover the offline tablespace users
do the following:
RECOVER AUTOMATIC TABLESPACE users
Alternatively, you can avoid running SET
LOGSOURCE
and simply run:
RECOVER AUTOMATIC TABLESPACE users FROM "/tmp"
Note:
Overriding the redo log source does not affect the archive redo log destination for online redo logs groups being archived.If you are using SQL*Plus's recovery options (not SQL statements), then each time the database successfully applies a redo log file, the following message is returned:
Log applied.
You are then prompted for the next log in the sequence or, if the most recently applied log is the last required log, terminates recovery.
If the suggested file is incorrect or you provide an incorrect filename, then the database returns an error message. For example, you may see something like:
ORA-00308: cannot open archived log "/oracle/oradata/trgt/arch/arcr_1_811.arc" ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3
Recovery cannot continue until the required redo log is applied. If the database returns an error message after supplying a log filename, then the following responses are possible.
Error | Possible Cause | Solution |
---|---|---|
ORA-27037 : unable to obtain file status |
Entered wrong filename.
Log is missing. |
Reenter correct filename.
Restore backup archived redo log. |
ORA-27047: unable to read the header block of file |
The log may have been partially written or become corrupted. | If you can locate an uncorrupted or complete log copy, then apply the intact copy and continue recovery.
If no copy of the log exists and you know the time of the last valid redo entry, then you use incomplete recovery. Restore backups and restart recovery. |
If you start media recovery and must then interrupt it, for example, because a recovery operation must end for the night and resume the next morning, then take either of the following actions:
Enter the word CANCEL
when prompted for a redo log file.
Use your operating system's interrupt signal if you must terminate when recovering an individual datafile, or when automated recovery is in progress.
After recovery is canceled, you can resume it later with the RECOVER
command. Recovery resumes where it left off when it was canceled.