| Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-03 |
|
|
View PDF |
| Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-03 |
|
|
View PDF |
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:
Because
V$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 RESTOREARCHIVELOG 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
