|Oracle® Database Backup and Recovery Basics
10g Release 2 (10.2)
|PDF · Mobi · ePub|
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.
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
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.
Start SQL*Plus and connect to the target database. For example, issue the following to connect to
% 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.
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 indicates whether a file needs media recovery, and
ERROR indicates whether there was an error reading and validating the datafile header.
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.
ERROR column is
NULL and the
RECOVER column is
YES, then the file requires media recovery (and may also require a restore from backup).
V$DATAFILE_HEADERonly 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
You can also query
V$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 use
V$RECOVER_FILEwith 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
To find datafile and tablespace names, you can also perform useful joins using the datafile number and the
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# ;
ERROR column identifies the problem for each file requiring recovery.
See Also:Oracle Database Reference for information about the V$ views
Recovery 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>
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
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.
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
UNAVAILABLE command (described in "Marking a Backup AVAILABLE or UNAVAILABLE") to set the backup status to UNAVAILABLE. If you then run
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.
PREVIEW lets you identify any backups that are stored remotely, and
RECALL is used to request that backups needed for a
RESTORE operation but that are stored remotely be recalled from remote storage.
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
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;
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
If the detailed report produced by
PREVIEW provides more information than is needed, use the
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
RESTORE DATABASE PREVIEW SUMMARY; RESTORE TABLESPACE users PREVIEW SUMMARY; RESTORE DATAFILE 3 PREVIEW SUMMARY; RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW SUMMARY;
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
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
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
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
PREVIEW command until no backups needed for the restore are reported as remote.
You can append
RECALL to any
PREVIEW command, as in these examples:
RESTORE TABLESPACE users PREVIEW RECALL; RESTORE DATAFILE 3 PREVIEW RECALL;
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:
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.
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.
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:
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 "
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