3
Performing User-Managed Restore Operations

This chapter describes how to recover a database, and includes the following topics:

About User-Managed Restore Operations

To restore a file is to replace it with a backup file. Typically, you restore a file when a media failure or user error has damaged or deleted the original file. The following files are candidates for restore operations:

Datafiles
Control files
Archived redo logs
Server parameter file

In each case, the loss of a primary file and the restore of a backup has the following implications for media recovery.

If you lose . . .	Then . . .
One or more datafiles	You must restore them from a backup and perform media recovery. Recovery is required whenever the checkpoint SCN in the datafile header does not match the checkpoint SCN for the datafile that is recorded in the control file.
All copies of the current control file	You must restore a backup control file and then open the database with the `RESETLOGS` option. If you do not have a backup, then you can attempt to re-create the control file. If possible, use the script included in the `ALTER` `DATABASE` `BACKUP` `CONTROLFILE` `TO` `TRACE` output. Additional work may be required to match the control file structure with the current database structure.
One copy of a multiplexed control file	Copy one of the intact multiplexed control files into the location of the damaged or missing control file and open the database. If you cannot copy the control file to its original location (for example, because the disk drive cannot be salvaged), then edit the initialization parameter file to reflect a new location. Then, open the database.
One or more archived logs required for media recovery	You must restore backups of these archived logs for media recovery to proceed. You can restore either to the default or to a nondefault location. If you do not have backups, then you must performing incomplete recovery up to a point before the first missing log and open `RESETLOGS`.
The server parameter file	If you have a backup of the server parameter file, then restore it. Alternatively, if you have a backup of the client-side initialization parameter file, then you can restore a backup of this file, start the instance, and then re-create the server parameter file.

Note:

Restore and recovery of Oracle-managed files is no different from restore and recovery of user-named files.

Keeping Records For Use in a Restore Scenario

One of the most important aspects of user-managed backup and recovery is keeping records of all current database files as well as the backups of these files. For example, you should have records for the location of the following files:

Datafiles
Control files
Online redo logs (note that online logs are never backed up)
Archived redo logs
Initialization parameter files
Password files
Networking-related files

Recording the Locations of Datafiles, Control Files, and Online Redo Logs

The following useful SQL script displays the location of all control files, datafiles, and online redo log files for the database:

SELECT NAME FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE;

Sample output follows:

NAME
--------------------------------------------------------------------------------
/oracle/dbs/tbs_01.f
/oracle/dbs/tbs_02.f
/oracle/dbs/tbs_11.f
/oracle/dbs/tbs_12.f
/oracle/dbs/t1_log1.f
/oracle/dbs/t1_log2.f
/oracle/dbs/cf1.f
/oracle/dbs/cf2.f

See Also:

Oracle9i Database Reference for more information on the V$ views

Recording the Locations of Archived Redo Logs

You can determine the location of the default archived log destinations by executing the following SQL script:

SELECT NAME, VALUE 
FROM V$PARAMETER 
WHERE NAME LIKE log_archive_dest%
AND VALUE IS NOT NULL
/

NAME                               VALUE
---------------------------------- -------------------------------------------
log_archive_dest_1                 LOCATION=/oracle/work/arc_dest/arc
log_archive_dest_state_1           enable

Determine the format for archived logs by running SHOW as follows:

SHOW PARAMETER LOG_ARCHIVE_FORMAT

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
log_archive_format                   string  r_%t_%s.arc

To see a list of all the archived logs recorded in the control file, issue this query:

SELECT NAME FROM V$ARCHIVED_LOG;

NAME
--------------------------------------------------------------------------------
/oracle/work/arc_dest/arcr_1_110.a
/oracle/work/arc_dest/arcr_1_111.a
/oracle/work/arc_dest/arcr_1_112.a
/oracle/work/arc_dest/arcr_1_113.a

Recording the Locations of Backup Files

It is not enough to merely record the location of backup files: you must correlate the backups with the original files. If possible, name the backups with the same relative filename as the primary file. Whatever naming system you use, keep a table containing the relevant information. For example, you could keep the following table as a record of database file locations in case of a restore emergency.

Datafile Number	Tablespace	Backup Filename
`0` (control file)	`0` (control file)	`/dsk3/backup/cf.f`
`1`	`SYSTEM`	`/dsk3/backup/tbs_01.f`
`2`	`undo`	`/dsk3/backup/tbs_02.f`
`3`	`temp`	`/dsk3/backup/tbs_11.f`
`4`	`users`	`/dsk3/backup/tbs_12.f`

Determining Which Datafiles Require Recovery

You can use the dynamic performance view V$RECOVER_FILE to determine which files to restore in preparation for media recovery. 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 and time when recovery needs to begin:

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 after the media failure occurred. A restored or re-created control file does not contain the information Oracle needs to fill V$RECOVER_FILE accurately.

Query V$DATAFILE and V$TABLESPACE to obtain filenames and tablespace names for datafiles requiring recovery. For example, enter:

SELECT d.NAME, t.NAME AS tablespace_name
FROM V$DATAFILE d, V$TABLESPACE t 
WHERE t.TS# = d.TS# 
AND d.FILE# IN (14,15,21);  # use values obtained from V$RECOVER_FILE query

NAME                               TABLESPACE_NAME
---------------------------------- ----------------
/oracle/dbs/tbs_14.f               TBS_1  
/oracle/dbs/tbs_15.f               TBS_2  
/oracle/dbs/tbs_21.f               TBS_3

You can combine these queries in the following SQL*Plus script (sample output show in the following example):

COL df# FORMAT 999
COL df_name FORMAT a20
COL tbsp_name FORMAT a10
COL status FORMAT a7
COL error FORMAT a10

SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS 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#
/

Sample output follows:

 DF# DF_NAME              TBSP_NAME  STATUS  ERROR          CHANGE# TIME
---- -------------------- ---------- ------- ---------- ----------- ----------
  14 /oracle/dbs/tbs_14.f TBS_1      OFFLINE OFFLINE              0  
                                             NORMAL

  15 /oracle/dbs/tbs_15.f TBS_2      OFFLINE OFFLINE              0
                                             NORMAL

  21 /oracle/dbs/tbs_21.f TBS_3      OFFLINE OFFLINE              0
                                             NORMAL

Restoring Datafiles

If a media failure permanently damages one or more datafiles of a database, then you must restore backups of these 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.

If you are restoring an Oracle file on a raw disk or partition, then the procedure is basically the same as when restoring to a file on a file system. However, you must be aware of the naming conventions for files on raw devices (which differ depending on the operating system), and use an operating system utility that supports raw devices.

See Also:

"Making User-Managed Backups to Raw Devices" for an overview of considerations when backing up and restoring files on raw devices

To restore backup datafiles to their default location:

Determine which datafiles to recover by using the techniques described in "Determining Which Datafiles Require Recovery".
If the database is open, then take the tablespaces containing the inaccessible datafiles offline. For example, enter:
```
ALTER TABLESPACE users OFFLINE IMMEDIATE;
```
Copy backups of the damaged datafiles to their default location using operating system commands. For example, to restore tbs_24.f on UNIX you might issue:
```
% cp /disk2/backup/tbs_24.bak /disk1/oracle/dbs/tbs_24.f
```
Recover the affected tablespace. For example, enter:
```
RECOVER TABLESPACE users
```
Bring the recovered tablespace online. For example, enter:
```
ALTER TABLESPACE users ONLINE;
```

Re-Creating Datafiles When Backups Are Unavailable

If a datafile is damaged and no backup of the file is available, then you can still recover the datafile if:

All archived log files written after the creation of the original datafile are available
The control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database)

To re-create a datafile for recovery:

Create a new, empty datafile to replace a damaged datafile that has no corresponding backup. For example, assume that the datafile /disk1/users1.f has been damaged, and no backup is available. The following statement re-creates the original datafile (same size) on disk2:
```
ALTER DATABASE CREATE DATAFILE '/disk1/users1.f' AS '/disk2/users1.f';
```
This statement creates an empty file that is the same size as the lost file. Oracle looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.
Perform media recovery on the empty datafile. For example, enter:
```
RECOVER DATAFILE '/disk2/users1.f'
```

All archived redo logs written after the original datafile was created must be mounted and reapplied to the new, empty version of the lost datafile during recovery.

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.

Restoring and Re-Creating Control Files

If a media failure has affected the control files of a database (whether control files are multiplexed or not), then the database continues to run until the first time that an Oracle background process needs to access the control files. At this point, the database and instance are automatically shut down.

If the media failure is temporary and the database has not yet shut down, avoid the automatic shutdown of the database by immediately correcting the media failure. If the database shuts down before you correct the temporary media failure, however, then you can restart the database after fixing the problem and restoring access to the control files.

The appropriate recovery procedure for media failures that permanently prevent access to control files of a database depends on whether you have multiplexed the control files. The following sections describe the appropriate procedures:

Losing a Member of a Multiplexed Control File

Use the following procedures to recover a database if a permanent media failure has damaged one or more control files of a database and at least one control file has not been damaged by the media failure.

Copying a Multiplexed Control File to a Default Location

Assuming that the disk and file system containing the lost control file are intact, then you can simply copy one of the intact control files to the location of the missing control file. In this case, you do not have to alter the CONTROL_FILES initialization parameter setting.

To replace a damaged control file by copying a multiplexed control file:

If the instance is still running, then shut it down:
```
SHUTDOWN ABORT
```
Correct the hardware problem that caused the media failure. If you cannot repair the hardware problem quickly, then you can proceed with database recovery by restoring damaged control files to an alternative storage device, as described in "Copying a Multiplexed Control File to a Nondefault Location".
Use an intact multiplexed copy of the database's current control file to copy over the damaged control files. For example, to replace bad_cf.f with good_cf.f, you might enter:
```
% cp /oracle/good_cf.f /oracle/dbs/bad_cf.f
```
Start a new instance and mount and open the database. For example, enter:
```
STARTUP
```

Copying a Multiplexed Control File to a Nondefault Location

Assuming that the disk and file system containing the lost control file are not intact, then you cannot copy one of the "good" control files to the location of the missing control file. In this case, you must alter the CONTROL_FILES initialization parameter to indicate a new location for the missing control file.

To restore a control file to a nondefault location:

If the instance is still running, then shut it down:
```
SHUTDOWN ABORT
```
If you cannot correct the hardware problem that caused the media failure, then copy the intact control file to alternative locations. For example, to copy good_cf.f to new_cf.f you might issue:
```
% cp /oracle/dbs/good_cf.f /oracle/dbs/new_cf.f
```
Edit the parameter file of the database so that the CONTROL_FILES parameter reflects the current locations of all control files and excludes all control files that were not restored. For example, assume the initialization parameter file contains:
```
CONTROL_FILES = '/oracle/dbs/good_cf.f', '/oracle/dbs/bad_cf.f'
```
Then, you can edit it as follows:
```
CONTROL_FILES = '/oracle/dbs/good_cf.f', '/oracle/dbs/new_cf.f'
```
Start a new instance and mount and open the database. For example, enter the following in SQL*Plus:
```
STARTUP
```

Losing All Members of a Multiplexed Control File When a Backup Is Available

Use the following procedures to restore a backup control file if a permanent media failure has damaged all control files of a database and you have a backup of the control file. When a control file is inaccessible, then you can start the instance, but not mount the database. If you attempt to mount the database when the control file is unavailable, you see this error message:

ORA-00205: error in identifying controlfile, check alert log for more info

You cannot mount and open the database until you make the control file accessible again. If you restore a backup control file, then you must open the database with the RESETLOGS option.

As indicated in Table 3-1, the procedure for restoring the control file depends on whether the online redo logs are available.

Table 3-1 Scenarios When Control Files Are Lost

Status of Online Logs	Status of Datafiles	Response
Available	Current	If the online logs contain redo necessary for recovery, then restore a backup control file apply the logs during recovery. Hence, you must specify the filename of the online logs containing the changes in order to open the database. After recovery, open `RESETLOGS`.
Unavailable	Current	If the online logs contain redo necessary for recovery, then you must re-create the control file. Because the logs are inaccessible, open `RESETLOGS`.
Available	Backup	Restore a backup control file, perform complete recovery, and then open `RESETLOGS`.
Unavailable	Backup	Restore a backup control file, perform incomplete recovery, and then open `RESETLOGS`.

Restoring a Backup Control File to the Default Location

If possible, restore the control file to its original location. In this way, you avoid having to specify new control file locations in the initialization parameter file.

To restore a backup control file to its default location:

If the instance is still running, shut it down:
```
SHUTDOWN ABORT
```
Correct the hardware problem that caused the media failure.
Restore the backup control file to all locations specified in the CONTROL_FILES initialization parameter. For example, if /dsk1/oracle/dbs/cf1.f and /dsk2/cf2.f are the control file locations listed in the server parameter file, then use an operating system utility to restore the backup control file to these locations:
```
% cp /backup/cf.bak /dsk1/oracle/dbs/cf1.f 
% cp /backup/cf.bak /dsk2/cf2.f 
```
Start a new instance and mount the database. For example, enter:
```
STARTUP MOUNT 
```
Begin recovery by executing the RECOVER command with the USING BACKUP CONTROLFILE clause. Specify UNTIL CANCEL if you are performing incomplete recovery. For example, enter:
```
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
```
Apply the prompted archived logs. If you then receive another message saying that the required archived log is missing, it probably means that a necessary redo record is located in the online redo logs. This situation can occur when unarchived changes were located in the online logs when the instance crashed.

For example, assume that you see the following:
```
ORA-00279: change 55636 generated at 06/08/2000 16:59:47 needed for thread 1
ORA-00289: suggestion : /oracle/work/arc_dest/arcr_1_111.arc
ORA-00280: change 55636 for thread 1 is in sequence #111
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
```
You can specify the name of an online redo log and press Enter (you may have to try this a few times until you find the correct log):
```
/oracle/dbs/t1_log1.f
Log applied.
Media recovery complete.
```
If for some reason the online logs are not accessible, then you can cancel recovery without applying the online logs. Note that if all datafiles are current, and redo is located in the online logs that is required for recovery, then you cannot open the database without applying the online logs. If the online logs are inaccessible, then you must re-create the control file (refer to "Losing All Current and Backup Control Files").
Open the database with the RESETLOGS option after finishing recovery:
```
ALTER DATABASE OPEN RESETLOGS;
```
Immediately back up the database as a precautionary measure, as described in "Making User-Managed Backups of the Whole Database".

Restoring a Backup Control File to a Nondefault Location

If you cannot restore the control file to its original place because the media damage is too severe, then you must specify new control file locations in the server parameter file. A valid control file must be available in all locations specified by the CONTROL_FILES initialization parameter. If not, then Oracle prevents you from the mounting the database.

To restore a control file to a nondefault location:

Follow the steps in "Restoring a Backup Control File to the Default Location", except after step 2 add the following step:

Edit all locations specified in the CONTROL_FILES initialization parameter to reflect the new control file locations. For example, if the control file locations listed in the server parameter file are as follows:

CONTROL_FILES = '/dsk1/oracle/dbs/cf1.f', '/dsk2/cf2.f'

You can change the initialization parameter to read:

CONTROL_FILES = '/dsk3/tmp/cf1.f', 'dsk3/tmp/cf2.f'

Losing All Current and Backup Control Files

If all control files have been lost or damaged by a permanent media failure, but all online redo logfiles remain intact, then you can recover the database after creating a new control file. Note that this procedure does not require you to open the database with the RESETLOGS option.

Depending on the existence and currency of a control file backup, you have the options listed in Table 3-2 for generating the text of the CREATE CONTROLFILE statement. Note that changes to the database are recorded in the alert_SID.log, so check this log when deciding which option to choose.

Table 3-2 Options for Creating the Control File (Page 1 of 2)

If you . . .	Then . . .
Executed `ALTER` `DATABASE` `BACKUP` `CONTROLFILE` `TO` `TRACE` `NORESETLOGS` after you made the last structural change to the database, and if you have saved the SQL command trace output	Use the `CREATE` `CONTROLFILE` statement from the trace output as-is.
Performed your most recent execution of `ALTER` `DATABASE` `BACKUP` `CONTROLFILE` `TO` `TRACE` before you made a structural change to the database	Edit the output of `ALTER` `DATABASE` `BACKUP` `CONTROLFILE` `TO` `TRACE` to reflect the change. For example, if you recently added a datafile to the database, then add this datafile to the `DATAFILE` clause of the `CREATE` `CONTROLFILE` statement.
Backed up the control file with the `ALTER` `DATABASE` `BACKUP` `CONTROLFILE` `TO` `filename` statement (not the `TO` `TRACE` option)	Use the control file copy to obtain SQL output. Copy the backup control file and execute `STARTUP` `MOUNT` before `ALTER` `DATABASE` `BACKUP` `CONTROLFILE` `TO` `TRACE` `NORESETLOGS`. If the control file copy predated a recent structural change, then edit the trace output to reflect the structural change.
Do not have a control file backup in either `TO` `TRACE` format or `TO` `filename` format	Create the `CREATE` `CONTROLFILE` statement manually (see Oracle9i SQL Reference).

Note:

If your character set is not the default US7ASCII, then you must specify the character set as an argument to the CREATE CONTROLFILE statement. The database character set is written to the alert log at startup. The character set information is also recorded in the BACKUP CONTROLFILE TO TRACE output.

To create a new control file:

Start the database in NOMOUNT mode. For example, enter:
```
STARTUP NOMOUNT
```

Create the control file with the CREATE CONTROLFILE statement, specifying the NORESETLOGS option (refer to Table 3-2 for options). The following example assumes that the character set is the default US7ASCII:

CREATE CONTROLFILE REUSE DATABASE SALES NORESETLOGS ARCHIVELOG
     MAXLOGFILES 32
     MAXLOGMEMBERS 2
     MAXDATAFILES 32
     MAXINSTANCES 16
     MAXLOGHISTORY 1600
LOGFILE
     GROUP 1 (
       '/diska/prod/sales/db/log1t1.dbf',
       '/diskb/prod/sales/db/log1t2.dbf'
     )  SIZE 100K
     GROUP 2 (
       '/diska/prod/sales/db/log2t1.dbf',
       '/diskb/prod/sales/db/log2t2.dbf'
     )  SIZE 100K,
DATAFILE
     '/diska/prod/sales/db/database1.dbf',
     '/diskb/prod/sales/db/filea.dbf';

After creating the control file, Oracle mounts the database.

Recover the database as normal (without specifying the USING BACKUP CONTROLFILE clause):
```
RECOVER DATABASE
```
Open the database after media recovery completes:
```
ALTER DATABASE OPEN;
```
Note that a RESETLOGS is not necessary.

Immediately back up the control file. The following SQL statement backs up a database's control file to ?/dbs/cf.bak:

ALTER DATABASE BACKUP CONTROLFILE TO '?/dbs/cf.bak' REUSE;

Restoring Archived Redo Logs

All archived redo log files generated between the time a restored backup was created and the target recovery time are required for the pending media recovery. The archived logs will eventually need to be on disk so that they are available to Oracle during the recovery.

To restore necessary archived redo logs:

To determine which archived redo log files are needed, query V$ARCHIVED_LOG and V$RECOVERY_LOG. If a datafile requires recovery, but not backup of the datafile exists, then you need all redo generated starting from the time when the datafile was added to the database.

View Description

View	Description
`V$ARCHIVED_LOG`	Lists all the filenames for all the archived logs.
`V$RECOVERY_LOG`	Lists only the archived redo logs that Oracle needs to perform media recovery. It also includes the probable names of the files, using `LOG_ARCHIVE_FORMAT`. Note: This view is only populated when recovery is required for a datafile. Hence, this view is not useful in the case of a planned recovery such as a user error.

V$ARCHIVED_LOG

Lists all the filenames for all the archived logs.

V$RECOVERY_LOG

Lists only the archived redo logs that Oracle needs to perform media recovery. It also includes the probable names of the files, using LOG_ARCHIVE_FORMAT.

Note: This view is only populated when recovery is required for a datafile. Hence, this view is not useful in the case of a planned recovery such as a user error.

If space is available, then restore the required archived redo log files to the location specified by LOG_ARCHIVE_DEST_1. Oracle locates the correct log automatically when required during media recovery. For example, enter:
```
% cp /disk2/arc_backup/*.arc /disk1/oracle/dbs/arc_dest
```
If sufficient space is not available at the location indicated by the archiving destination initialization parameter, restore some or all of the required archived redo log files to an alternate location. Specify the location before or during media recovery using the LOGSOURCE parameter of the SET statement in SQL*Plus or the RECOVER ... FROM parameter of the ALTER DATABASE statement in SQL. For example, enter:
```
SET LOGSOURCE /disk2/temp   # set location using SET statement
DATABASE RECOVER FROM '/disk2/temp';  # set location in RECOVER statement itself
```

After an archived log is applied, and after making sure that a copy of each archived log group still exists in offline storage, delete the restored copy of the archived redo log file to free disk space. For example, after making the log directory your working directory, enter:

% rm *.arc

See Also:

Oracle9i Database Reference for more information about the data dictionary views, and "Performing User-Managed Media Recovery: Overview" for an overview of log application during media recovery

3 Performing User-Managed Restore Operations

About User-Managed Restore Operations

Keeping Records For Use in a Restore Scenario

Recording the Locations of Datafiles, Control Files, and Online Redo Logs

Recording the Locations of Archived Redo Logs

Recording the Locations of Backup Files

Determining Which Datafiles Require Recovery

Restoring Datafiles

Re-Creating Datafiles When Backups Are Unavailable

Restoring and Re-Creating Control Files

Losing a Member of a Multiplexed Control File

Copying a Multiplexed Control File to a Default Location

Copying a Multiplexed Control File to a Nondefault Location

Losing All Members of a Multiplexed Control File When a Backup Is Available

Table 3-1 Scenarios When Control Files Are Lost

Restoring a Backup Control File to the Default Location

Restoring a Backup Control File to a Nondefault Location

Losing All Current and Backup Control Files

Table 3-2 Options for Creating the Control File (Page 1 of 2)

Restoring Archived Redo Logs

3
Performing User-Managed Restore Operations