While RMAN makes carrying out most database restore and recovery tasks much simpler, you still have to plan your database restore and recovery actions based on which database files have been lost and your recovery goal.
RMAN can make most of the important decisions about the restore process for you, but you may want to preview and even override its decisions in some circumstances. For example, if you know a given backup is unavailable, due to a tape being stored offsite or a device being inaccessible, you can direct RMAN to not use that backup during the restore process.
RMAN provides tools to let you preview which backups will be used in a restore, and to validate the contents of the backups to ensure that they can be used in future restore operations.
The basic procedure for performing restore and recovery with RMAN is as follows:
Determine which database files must be restored from backup, and which backups (which specific tapes, or specific backup sets or image copies on disk) to use for the restore operation. The files to be restored may include the control file, SPFILE, archived redo log files, and datafiles.
Place the database in the state appropriate for the type of recovery that you are performing. For example, if you are recovering a single tablespace or datafile, then you can keep the database open and take the tablespace or datafile offline. If you are restoring all datafiles, then you must shut down the database and then mount it before you can perform the restore.
Restore lost database files from backup with the RESTORE
command. You may restore files to their original locations, or you may have to restore them to other locations if, for instace, a disk has failed. You may also have to update the SPFILE if you have changed the control file locations, or the control file if you have changed the locations of datafiles or redo logs.
Perform media recovery on restored datafiles, if any, with the RECOVER
command.
Perform any final steps required to make the database available for users again. For example, re-open the database if necessary, as happens when restoring lost control files, or bring offline tablespaces online if restoring and recovering individual tablespaces.
This outline is intended to encompass a wide range of different scenarios. Depending upon your situation, some of the steps described may not apply. (For example, you do not need to perform media recovery if the only file restored from backup is the SPFILE.) You will have to devise your final recovery plan based on your situation.
The methods of determining which files require restore or recovery depend upon the type of file that is lost. This section contains the following topics:
It is generally obvious when the control file of your database must be restored. The database shuts down immediately if any of the control file copies becomes inaccessible and reports an error if you try to start it without a valid control file at each location specified in the CONTROL_FILES
initialization parameter.
Loss of some but not all copies of your control file does not require recovery of the control file from backup. When one copy of the control file is lost, the database will automatically shut down. You can either copy an intact copy of the control file over the damaged or missing control file, or update the parameter file so that does not refer to the damaged or missing control file. Once the CONTROL_FILES
parameter references only present, intact copies of the control file, you can restart your database.
Note that if you restore the control file from backup, you must perform media recovery of the whole database and then perform an OPEN RESETLOGS
, even if no datafiles have to be restored.
When and how to recover depends on the state of the database and the location of its datafiles. To determine which if any files require media recovery, use the following procedure:
Start SQL*Plus and connect to the target database. For example, issue the following to connect to trgt
:
% sqlplus 'SYS/oracle@trgt AS SYSDBA'
Determine the status of the database by executing the following SQL query:
SELECT STATUS FROM V$INSTANCE;
If the status is OPEN
, then the database is open. However, some datafiles may require media recovery.
Query the V$DATAFILE_HEADER
view to determine the status of your datafiles. Run the following SQL statements to check the datafile headers:
COL FILE# FORMAT 999 COL STATUS FORMAT A7 COL ERROR FORMAT A10 COL TABLESPACE_NAME FORMAT A10 COL NAME FORMAT A30 SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME FROM V$DATAFILE_HEADER WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);
Each row returned represents a datafile that either requires media recovery or has an error requiring a restore. Check the RECOVER
and ERROR
columns. RECOVER
indicates whether a file needs media recovery, and ERROR
indicates whether there was an error reading and validating the datafile header.
If ERROR
is not NULL
, then the datafile header cannot be read and validated. Check for a temporary hardware or operating system problem causing the error. If there is no such problem, you must restore the file or switch to a copy.
If the ERROR
column is NULL
and the RECOVER
column is YES
, then the file requires media recovery (and may also require a restore from backup).
Note:
BecauseV$DATAFILE_HEADER
only reads the header block of each datafile, it does not detect all problems that require the datafile to be restored. For example, you cannot tell whether a datafile contains corrupt data blocks using V$DATAFILE_HEADER
.You can also queryV$RECOVER_FILE
to list datafiles requiring recovery by datafile number with their status and error information.
SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME FROM V$RECOVER_FILE;
Note:
You cannot useV$RECOVER_FILE
with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the datafiles. A restored or re-created control file does not contain the information needed to update V$RECOVER_FILE
accurately.To find datafile and tablespace names, you can also perform useful joins using the datafile number and the V$DATAFILE
and V$TABLESPACE
views. For example:
COL DF# FORMAT 999 COL DF_NAME FORMAT A35 COL TBSP_NAME FORMAT A7 COL STATUS FORMAT A7 COL ERROR FORMAT A10 COL CHANGE# FORMAT 99999999 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# ;
The ERROR
column identifies the problem for each file requiring recovery.
See Also:
Oracle Database Reference for information about the V$ viewsRecovery is not needed on any read-only tablespace during crash or instance recovery. During startup, recovery verifies that each online read-only datafile does not need media recovery, by checking whether the file was not restored from a backup taken before it was made read-only.
Note:
If you restore a read-only tablespace from a backup taken before the tablespace was made read-only, then you cannot access the tablespace until you perform media recovery on it.In situations requiring the recovery of your SPFILE or control file from autobackup, such as disaster recovery when you have lost all database files, you will need to use your DBID. Your DBID should be recorded along with other basic information about your database, as recommended in "Deciding Between ARCHIVELOG and NOARCHIVELOG Mode".
If you do not have a record of the DBID of your database, there are two places you can find it without opening your database.
The DBID is used in forming the filename for the control file autobackup. Locate that file, and then refer to "Configuring the Control File Autobackup Format" to see where the DBID appears in the filename.
If you have any text files that preserve the output from an RMAN session, the DBID is displayed by the RMAN client when it starts up and connects to your database. Typical output follows:
% rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Jun 12 02:41:03 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: RDBMS (DBID=774627068)
RMAN>
The RESTORE
command supports a PREVIEW
option, which identifies the backups (backup sets or image copies, on disk or sequential media like tapes) required to carry out a given restore operation, based on the information in the RMAN repository. Use RESTORE
... PREVIEW
when planning your restore and recovery operation, to ensure that all required backups are available or to identify situations in which you may want to direct RMAN to use or avoid specific backups.
For example, RESTORE
... PREVIEW
output may indicate that, during a RESTORE
operation, RMAN will request a backup from a tape during the restore process which you know is temporarily unavailable. You can then use the CHANGE
... UNAVAILABLE
command (described in "Marking a Backup AVAILABLE or UNAVAILABLE") to set the backup status to UNAVAILABLE. If you then run RESTORE
... PREVIEW
again, RMAN will show you the backups it would use to perform a restore operation without using the unavailable backup.
In some cases, a backup may be listed as AVAILABLE but it is in fact vaulted, that is, stored remotely and requires retrieval before it can be used in a restore. If RMAN selects such a backup for use in a restore operation, the operation fails with an error. RESTORE
... PREVIEW
lets you identify any backups that are stored remotely, and RESTORE
...PREVIEW
RECALL
is used to request that backups needed for a RESTORE
operation but that are stored remotely be recalled from remote storage.
RESTORE
... PREVIEW
can be applied to any RESTORE
operation to create a detailed report of every backup to be used in the requested RESTORE
operation, as well as the necessary target SCN for recovery after the RESTORE
operation is complete. Here are a few examples of RESTORE
commands using the PREVIEW
option:
RESTORE DATABASE PREVIEW; RESTORE TABLESPACE users PREVIEW; RESTORE DATAFILE 3 PREVIEW; RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW; RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW; RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;
RESTORE
... PREVIEW
output is in the same format as the output of the LIST
command. See Oracle Database Backup and Recovery Reference for details on interpreting the output of RESTORE
... PREVIEW
.
If the detailed report produced by RESTORE
... PREVIEW
provides more information than is needed, use the RESTORE
... PREVIEW
SUMMARY
option to suppress much of the detail about specific files used and affected by the restore process. Here are some examples of RESTORE
used with the PREVIEW
SUMMARY
option:
RESTORE DATABASE PREVIEW SUMMARY; RESTORE TABLESPACE users PREVIEW SUMMARY; RESTORE DATAFILE 3 PREVIEW SUMMARY; RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW SUMMARY;
RESTORE
... PREVIEW
SUMMARY
reports are in the same format as the output from the LIST SUMMARY
command. See Oracle Database Backup and Recovery Reference for details on interpreting the output of RESTORE
... PREVIEW SUMMARY
.
RESTORE
... PREVIEW
RECALL
can be used with any RESTORE operation, in cases a restore fails due to a needed backup being stored remotely.
The following command shows the output in a case where RESTORE
... PREVIEW
indicates that a needed backup is stored remotely:
RMAN> restore archivelog all preview; Starting restore at 10-JUN-05 using channel ORA_DISK_1 using channel ORA_SBT_TAPE_1 List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 31 12.75M SBT_TAPE 00:00:02 10-JUN-05 BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20050610T152755 Handle: 15gmknbs Media: /v1,15gmknbs List of Archived Logs in backup set 31 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 1 221154 06-JUN-05 222548 06-JUN-05 1 2 222548 06-JUN-05 222554 06-JUN-05 1 3 222554 06-JUN-05 222591 06-JUN-05 1 4 222591 06-JUN-05 246629 07-JUN-05 1 5 246629 07-JUN-05 262451 10-JUN-05 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 32 256.00K SBT_TAPE 00:00:01 10-JUN-05 BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20050610T153105 Handle: 17gmknhp_1_1 Media: /v1,17gmknhp_1_1 List of Archived Logs in backup set 32 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 6 262451 10-JUN-05 262547 10-JUN-05 1 7 262547 10-JUN-05 262565 10-JUN-05 List of remote backup files ============================ Handle: 15gmknbs Media: /v1,15gmknbs
The "List of remote backup files" at the end of the output identifies the backups that are stored remotely. In such a case, using RESTORE
ARCHIVELOG
ALL
PREVIEW
RECALL
initiates recall for the remote backups and produces the following output:
RMAN> restore archivelog all preview recall; Starting restore at 10-JUN-05 using channel ORA_DISK_1 using channel ORA_SBT_TAPE_1 List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 31 12.75M SBT_TAPE 00:00:02 10-JUN-05 BP Key: 33 Status: AVAILABLE Compressed: NO Tag: TAG20050610T152755 Handle: 15gmknbs Media: /v1,15gmknbs List of Archived Logs in backup set 31 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 1 221154 06-JUN-05 222548 06-JUN-05 1 2 222548 06-JUN-05 222554 06-JUN-05 1 3 222554 06-JUN-05 222591 06-JUN-05 1 4 222591 06-JUN-05 246629 07-JUN-05 1 5 246629 07-JUN-05 262451 10-JUN-05 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 32 256.00K SBT_TAPE 00:00:01 10-JUN-05 BP Key: 34 Status: AVAILABLE Compressed: NO Tag: TAG20050610T153105 Handle: 17gmknhp_1_1 Media: /v1,17gmknhp_1_1 List of Archived Logs in backup set 32 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 6 262451 10-JUN-05 262547 10-JUN-05 1 7 262547 10-JUN-05 262565 10-JUN-05 Initiated recall for the following list of remote backup files ========================================================== Handle: 15gmknbs Media: /v1,15gmknbs Finished restore at 10-JUN-05
You can repeat the RESTORE
... PREVIEW
command until no backups needed for the restore are reported as remote.
You can append RECALL
to any RESTORE
... PREVIEW
command, as in these examples:
RESTORE TABLESPACE users PREVIEW RECALL; RESTORE DATAFILE 3 PREVIEW RECALL;
The RESTORE
... VALIDATE
and VALIDATE
BACKUPSET
commands test whether you can restore from your backups. You can test the availability of usable backups for any desired RESTORE
operation, or test the contents of a specific backup for use in RESTORE
operations. The contents of the backups are actually read and validated for corruption to ensure that the objects to be restored can be restored from them. You have these options:
RESTORE
...
VALIDATE
tests whether RMAN can restore a specific object from a backup. RMAN chooses which backups to use.
VALIDATE BACKUPSET
tests the validity of a backup set that you specify.
See Also:
Oracle Database Backup and Recovery Reference for RESTORE
syntax
Oracle Database Backup and Recovery Reference for VALIDATE
syntax
You can enter any valid RESTORE
command specifying the VALIDATE
clause to test whether usable backups for that RESTORE
operation are available. When validating backups with RESTORE... VALIDATE
, the database can be mounted or open.
This example illustrates validating the restore of the backup control file, SYSTEM
tablespace, and all archived logs:
RESTORE CONTROLFILE VALIDATE; RESTORE TABLESPACE SYSTEM VALIDATE; RESTORE ARCHIVELOG ALL VALIDATE; RESTORE DATAFILE 4,5,6 VALIDATE;
Note:
You do not have to take datafiles offline when validating the restore of datafiles, because validation of backups of the datafiles only reads the backups and does not affect the production datafiles.If you see error messages in the output and the following message, then RMAN cannot restore one or more of the specified files from your available backups:
RMAN-06026: some targets not found - aborting restore
If you see an error message stack and output similar to the following, for example, then RMAN encountered a problem reading the specified backup:
RMAN-03009: failure of restore command on c1 channel at 12-DEC-01 23:22:30 ORA-19505: failed to identify file "oracle/dbs/1fafv9gl_1_1" ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3
If you do not see an error stack, then RMAN successfully tested restore of the specified objects from the available backups and should be able to restore these objects successfully during a real restore and recovery operation.
The BACKUP
VALIDATE
command requires that you know the primary keys of the backup sets that you want to validate.
To specify which backup sets to validate:
Find the backup sets that you want to validate by running LIST
commands, noting primary keys. For example:
LIST BACKUP;
Validate the restore of the backup sets, referencing them by the primary keys. This example validates the restore of backup sets 56 and 57:
VALIDATE BACKUPSET 56,57;
If the output contains the message " validation
complete
", then RMAN successfully validated the restore of the specified backup set. For example:
using channel ORA_DISK_1 channel ORA_DISK_1: starting validation of archive log backupset channel ORA_DISK_1: restored backup piece 1 piece handle=/oracle/dbs/0mdg9v8l_1_1 tag=TAG20020208T155604 params=NULL channel ORA_DISK_1: validation complete