About User-Managed Media Recovery

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.

Preconditions of Performing User-Managed Recovery

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.

Applying Logs Automatically with the RECOVER Command

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.

Automating Recovery with SET AUTORECOVERY

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*Plus RECOVER 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.

Automating Recovery with the AUTOMATIC Option of the RECOVER Command

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.

Recovering When Archived Logs Are in the Default Location

Recovering 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.

Recovering When Archived Logs Are in a Nondefault Location

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

Resetting the Archived Log Destination

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
```

Overriding the Archived Log Destination

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.

Responding to Unsuccessful Application of Redo Logs

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.

Interrupting User-Managed Media 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.