RMAN> restore datafile 32, 33;
RMAN> recover datafile 32, 33;
RMAN> sql 'alter database datafile 32, 33 online';
■
Tip
Use the RMAN REPORT SCHEMA command to list datafile names and file numbers. You can also query the NAME and FILE# columns of V$DATAFILE to take names and numbers.
You can also specify the name of the datafile that you want to restore and recover. In this example, the mvdata01.dbf datafile is restored and recovered:
521
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
RMAN> sql "alter database datafile ''/ora01/dbfile/O11R2/mvdata01.dbf'' offline"; RMAN> restore datafile '/ora01/dbfile/O11R2/mvdata01.dbf';
RMAN> recover datafile '/ora01/dbfile/O11R2/mvdata01.dbf';
RMAN> sql "alter database datafile ''/ora01/dbfile/O11R2/mvdata01.dbf'' online";
■
Note
When using the RMAN SQL command, if there are single quote marks within the SQL statement, then you are required to use double quotes to enclose the entire SQL statement and to use two single quote marks where you would ordinarily just use one quote mark.
Restoring Datafile While Database Is Not Open
In this scenario, the database is first shut down and then started in mount mode. You can restore and recover any datafile in your database while the database is not open. This example shows restoring the datafile 1, which is associated to the SYSTEM tablespace:
RMAN> connect target /
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile 1;
RMAN> recover datafile 1;
RMAN> alter database open;
You can also specify the filename when performing a datafile recovery: RMAN> connect target /
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile '/ora01/dbfile/O11R2/system01.dbf';
RMAN> recover datafile '/ora01/dbfile/O11R2/system01.dbf';
RMAN> alter database open;
Restoring Datafiles to Non-Default Locations
Sometimes a failure will occur that renders the disks associated with a mount point inoperable. In situations like this, you will need to restore and recover the datafiles to a different location from where they originally resided. Another typical need for restoring datafiles to non-default locations is that you're restoring to a different database server where the mount points are completely different from the server where the backup originated.
Use the SET NEWNAME and SWITCH commands to restore datafiles to non-default locations. Both of these commands must be run from within an RMAN run{} block. You can think of using SET NEWNAME
and SWITCH as a way to rename datafiles (similar to the SQL*Plus ALTER DATABASE RENAME FILE
statement).
This example changes the location of datafiles 32 and 33:
RMAN> connect target /
RMAN> startup mount;
RMAN> run{
2> set newname for datafile 32 to '/ora02/dbfile/O11R2/mvdata01.dbf'; 522
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
3> set newname for datafile 33 to '/ora02/dbfile/O11R2/mvindex01.dbf'; 4> restore datafile 32, 33;
5> switch datafile all; # Updates repository with new datafile location.
6> recover datafile 32, 33;
7> alter database open;
8> }
This is a partial listing of the output:
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
Finished restore at 22-SEP-10
...
datafile 32 switched to datafile copy
input datafile copy RECID=92 STAMP=730375692 file name=/ora02/dbfile/O11R2/mvdata01.dbf datafile 33 switched to datafile copy
input datafile copy RECID=93 STAMP=730375692 file name=/ora02/dbfile/O11R2/mvindex01.dbf
...
Starting recover at 22-SEP-10
media recovery complete, elapsed time: 00:00:05
Finished recover at 22-SEP-10
database opened
If the database is open, you can place the datafiles offline and then set their new names for restore and recovery like so:
RMAN> run{
2> sql 'alter database datafile 32, 33 offline';
3> set newname for datafile 32 to '/ora02/dbfile/O11R2/mvdata01.dbf'; 4> set newname for datafile 33 to '/ora02/dbfile/O11R2/mvindex01.dbf'; 5> restore datafile 32, 33;
6> switch datafile all; # Updates repository with new datafile location.
7> recover datafile 32, 33;
8> sql 'alter database datafile 32, 33 online';
9> }
You should now see a message similar to the following:
starting media recovery
Finished recover at 22-SEP-10
sql statement: alter database datafile 32, 33 online
Performing Block Level Recovery
Block-level corruption is rare and is usually caused by some sort of I/O error. However, if you do have an isolated corrupt block within a large datafile, it’s nice to have the option of performing a block-level recovery. Block-level recovery is useful when a small number of blocks are corrupt within a datafile.
Block recovery is not appropriate if the entire datafile needs media recovery.
RMAN will automatically detect corrupt blocks whenever a BACKUP, VALIDATE, or BACKUP VALIDATE
command is run. Details on corrupt blocks can be viewed in the V$DATABASE_BLOCK_CORRUPTION view. In the following example, the regular backup job has reported a corrupt block in the output: ORA-19566: exceeded limit of 0 corrupt blocks for file...
Querying the V$DATABASE_BLOCK_CORRUPTION view indicates which file contains corruption: SQL> select * from v$database_block_corruption;
523
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 20 1 0 ALL ZERO
Your database can be either mounted or open when performing block level recovery. You do not have to take the datafile being recovered offline. You can instruct RMAN to recover all blocks reported in V$DATABASE_BLOCK_CORRUPTION as shown:
RMAN> recover corruption list;
If successful, the following message is displayed:
media recovery complete...
Another way to recover the block is to specify the datafile and block number like so: RMAN> recover datafile 5 block 20;
It's preferable to use the RECOVER CORRUPTION LIST syntax because it will clear out any blocks recovered from the V$DATABASE_BLOCK_CORRUPTION view.
■
Note
RMAN cannot perform block-level recovery on block 1 (datafile header) of the datafile.
Block-level media recovery allows you to keep your database available and also reduces the mean time to recovery since only the corrupt blocks are offline during the recovery. Your database must be in archivelog mode for performing block-level recoveries. In Oracle Database 11
g
, RMAN can restore the block from the flashback logs (if available). If the flashback logs are not available, then RMAN will attempt to restore the block from a full backup, a level 0 backup, or an image copy backup generated by BACKUP AS COPY command. After the block has been restored, any required archived redo logs must be available to recover the block. RMAN can’t perform block media recovery using incremental level 1 (or higher) backups.
■
Note
If you’re using Oracle Database 10g or Oracle9i Database, use the BLOCKRECOVER command to perform block media recovery. Block level recovery is not available in Oracle version 8.
Restoring Archive Redo Log Files
RMAN will automatically restore any archived redo log files that it needs during a recovery process. You normally don't need to manually restore archived redo log files. However, you may want to manually restore the archived redo log files if any of the following situations apply:
• You need to restore archived redo log files in anticipation of later performing a recovery; the idea is that if the archived redo log files are already restored, it will speed up the recovery operation.
524
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
• You’re required to restore the archived redo log files to a non-default location, either because of media failure or because of storage space issues.
• You need to restore specific archived redo log files because you want to inspect them via LogMiner.
If you’ve enabled a flash recovery area, then RMAN will by default restore archived redo log files to the destination defined by the initialization parameter DB_RECOVERY_FILE_DEST. Otherwise, RMAN uses the LOG_ARCHIVE_DEST_1 initialization parameter to determine where to restore the archived redo log files.
If you restore archived redo log files to a non-default location, RMAN knows the location they were restored to and automatically finds these files when you issue any subsequent RECOVER commands.
RMAN will not restore archived redo log files that it determines are already on disk. Even if you specify a non-default location, RMAN will not restore an archived redo log file to disk if the file already exists. In this situation, RMAN will simply return a message stating that the archived redo log file has already been restored. Use the FORCE option to override this behavior.
If you are uncertain of the sequence numbers to use during a restore of log files, you can query the V$LOG_HISTORY view or issue an RMAN LIST BACKUP command for more information.
■
Note
When restoring archived redo log files, your database can be either mounted or open.
Restoring to the Default Location
The following command will restore all archived redo log files that RMAN has backed up: RMAN> restore archivelog all;
If you want to restore from a specified sequence, use the FROM SEQUENCE clause. You may want to run this query first to determine the most recent log files and sequence numbers that have been generated: SQL> select sequence#, first_time from v$log_history order by 2;
This example restores all archived redo log files from sequence 68:
RMAN> restore archivelog from sequence 68;
If you want to restore a range of archived redo log files, use the FROM SEQUENCE and UNTIL SEQUENCE
clauses or the SEQUENCE BETWEEN clause, as shown here. The following commands restore archived redo log files from sequence 68 through (and including) sequence 78 using thread 1: RMAN> restore archivelog from sequence 68 until sequence 78 thread 1; RMAN> restore archivelog sequence between 68 and 78 thread 1;
By default, RMAN won’t restore an archived redo log file if it is already on disk. You can override this behavior if you use the FORCE like so:
RMAN> restore archivelog from sequence 1 force;
525
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
Restoring to a Nondefault Location
Use the SET ARCHIVELOG DESTINATION clause if you want to restore archived redo log files to a different location than the default. The following example restores to the non-default location of /ora01/archtemp.
This option of the SET command must be executed from within an RMAN run RMAN run{} block.
RMAN> run{
2> set archivelog destination to '/ora01/archtemp';
3> restore archivelog from sequence 68 force;
4> }
Restoring the Spfile
You might want to restore a spfile for several different reasons:
•
•
•
One scenario (this has happened to me more than once) is that you're using a spfile and one of the DBAs on your team does something inexplicable like this:
SQL> alter system set processes=1000000 scope=spfile;
The parameter is changed in the pfile on disk but not in memory. Several months later, the database is stopped for some maintenance. When attempting to start the database, you can't even get the instance to start in a NOMOUNT state. This is because a parameter has been set to a ridiculous value that will consume all memory on the box. In this scenario, the instance may hang or you might see this message:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
If you are using a recovery catalog, it’s a fairly simple procedure to restore the spfile: RMAN> connect target /
RMAN> connect catalog rmancat/foo@rcat
RMAN> startup nomount;
RMAN> restore spfile;
If you’re not using a recovery catalog, there are a number of ways to restore your spfile. The approach you take depends on several variables such as:
•
•
•
I’m not going to show every detail of all of these scenarios. Usually I determine the location of the backup piece that contains the backup of the spfile and do the restore like this: RMAN> startup nomount force;
RMAN> restore spfile from
'/ora01/fra/O11R2/autobackup/2010_09_18/o1_mf_s_730048900_69bcc8h2_.bkp'; 526
CHAPTER 19 ■ RMAN RESTORE AND RECOVERY
You should see a message similar to this:
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
In this example, I knew that a FRA was in use and located the latest backup file in the autobackup directory and used it.
■
Note
For a complete description of all possible spfile and control file restore scenarios, refer to RMAN Recipes for Oracle Database 11g (Apress, 2007).
Restoring a Control File
If you are missing one control file and you have multiple copies, then you can shutdown your database, and simply restore the missing or damaged control file by copying a good control file to the correct location and name of the missing control file (see Chapter 5 for details).
The following sections cover these specific scenarios when restoring a control file:
• Using a Recovery Catalog.
• Using an autobackup.
• Specifying a backup file name.
Using a Recovery Catalog