Recover in a Flash

Reduce database recovery time, using the Oracle flash recovery area.

By Arup Nanda

January/February 2007

If you are using Oracle Recovery Manager (RMAN) as a backup tool for your Oracle database, you probably already know that you have two options for the backup location: disk and tape. If you choose the former, you may back up to any location available to the server, but you must make sure the location has enough space for the backups. You also have to remove the old backups to make room for the new ones, keep track of the redundant backups, and make sure that backups and archive logs are available.

To help manage disk backups, in Oracle Database 10g Release 1 and later, you can define a special disk area that serves as a location for all types of backups. This location is the flash recovery area (FRA). Oracle Database manages the space inside this area; keeps track of backups that are needed; and if necessary, deletes old ones to make room for new ones. By default, the Oracle RMAN backups (both regular and image copies), online redo logs, archived logs, control files, and flashback logs are created in the FRA. When new backups or files demand more room, Oracle Database automatically removes the nonessential backups, freeing the DBA from this chore. The files in the FRA are considered nonessential when they become obsolete according to the retention policy, or when they have already been backed up to tape with Oracle RMAN.

To set up the FRA, first decide on its location and size. To set /home/oracle/FRA as the location and 2GB as the size, you issue the following while logged in as the SYS user:

alter system set
db_recovery_file_dest_size = 2G;
alter system set
db_recovery_file_dest = '/home/oracle/FRA'; 

To ensure that the values are set after the database is restarted, put the following lines in the initialization parameter file:

db_recovery_file_dest_size = 2G
db_recovery_file_dest = '/home/oracle/FRA'

If you are setting up the FRA on an Oracle Real Application Clusters (Oracle RAC) database, the FRA location must be visible to all database nodes. So it must be one of the following: a shared file system, an NFS-mounted file system, or an Automatic Storage Management (ASM) disk group. If you use ASM, the parameter is set as

db_recovery_file_dest = '+DISKGROUP1'

You can check the values of the FRA parameters set by querying the V$RECOVERY_FILE_DEST data dictionary view:

select *
from v$recovery_file_dest;

For my example, the result shows that there are 51 files in the FRA (the NUMBER_OF_FILES column). To determine the file types, you can check the V$FLASH_RECOVERY_AREA_USAGE view. This view shows the used and reclaimable spaces of each type of file as percentages of this total space. To get a more useful picture, you can combine these two views in a single query, shown in Listing 1, which shows the total size of the files instead of percentages. As you can see from the output, there are 34 archived log files, 16 Oracle RMAN backup files, and 1 flashback log file. The nonessential backups that can be deleted show up as RECLAIMABLE. If there is not sufficient space, the Oracle RMAN backup will return with an error:

Code Listing 1: Space, by file type, consumed in the FRA

select file_type, space_used*percent_space_used/100/1024/1024 used,
space_reclaimable*percent_space_reclaimable/100/1024/1024 reclaimable, frau.number_of_files
from v$recovery_file_dest rfd, v$flash_recovery_area_usage frau;
FILE_TYPE       USED      RECLAIMABLE    NUMBER_OF_FILES
-----------     ----      -----------    ---------------
CONTROLFILE      .00              .00                  0
ONLINELOG        .00              .00                  0
ARCHIVELOG    664.86           547.20                 34
BACKUPPIECE   573.23           520.73                 16
IMAGECOPY        .00              .00                  0
FLASHBACKLOG    6.07              .00                  1

ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 104857600 bytes disk space from 1073741824 limit

To create enough space for the Oracle RMAN backups to complete successfully, either manually remove some backups or increase the size of the FRA. To see the list of image copies in the FRA made by Oracle RMAN, you can use the Oracle RMAN list copy of database command, shown in Listing 2.

Code Listing 2: RMAN report of image copy datafiles in the FRA

RMAN> list copy of database;
List of Datafile Copies
Key    File   S   Completion Time  Ckp SCN   Ckp Time     Name
----   ----   -   ---------------  -------   ---------    ------------------------------------------------------------- 
4404   1      A   26-SEP-06        1607862   26-SEP-06    /home/oracle/FRA/PRODB2/datafile/o1_mf_system_2kmqnygd_.dbf
4407   2      A   26-SEP-06        1607935   26-SEP-06    /home/oracle/FRA/PRODB2/datafile/o1_mf_undotbs1_2kmqqy2b_.dbf
4405   3      A   26-SEP-06        1607907   26-SEP-06    /home/oracle/FRA/PRODB2/datafile/o1_mf_sysaux_2kmqpcnz_.dbf
4408   4      A   26-SEP-06        1607939   26-SEP-06    /home/oracle/FRA/PRODB2/datafile/o1_mf_users_2kmqr57t_.dbf
4406   5      A   26-SEP-06        1607926   26-SEP-06    /home/oracle/FRA/PRODB2/datafile/o1_mf_example_2kmqqgto_.dbf

In addition to storing the backups of datafiles and flashback logs, the FRA can also be configured to store archived logs, control files, and online redo logs. For information on these storage options, see "Configuring the Flash Recovery Area: Advanced Topics."

Backup sets are the Oracle RMAN default backups, in which only the used blocks in the datafiles are captured in the backup files. Oracle RMAN image copies are exact copies of the datafiles, with all the blocks—used or not. Oracle RMAN takes this image copy while the database is up and running, and the database need not be put into any special mode. Here is how to make an Oracle RMAN image copy backup:

run {
  backup as copy
  database;
}

This command, when run from the Oracle RMAN command prompt, creates the copies of the datafiles in the FRA with an Oracle-generated name such as o1_mf_users_2kmqr57t_.dbf.

Image copies in the FRA become truly useful when you need an "instant recovery." Remember that these image copies are copies of the datafiles—a fact recorded in the Oracle RMAN catalog and the control file. In case of a disaster, you don't need to restore the file; you can use the copy as the principal datafile immediately.

Suppose that one of your datafiles has become corrupted and needs recovery. Traditionally, you follow this general approach:

1. Take the tablespace offline.
2. Restore the datafile from the backup.
3. Recover the datafile to the point of failure.
4. Place the tablespace online.

Step 2 may take a long time, depending on the size of the file, the speed of the underlying disks, the transfer rate from backup to the original datafile location, and the other processes running on the system. Suppose that on your system, it takes more than two hours to complete this step, making the tablespace data unavailable for the entire duration. This motivates you to take a look at minimizing recovery time. You consider using image copies to speed up the recovery.

Using image copies, step 2 in the recovery is replaced by "Instruct the database to use the copy of the datafile instead of the original." This reduces the time taken by the step from hours to seconds. Here is the description of the recovery process, assuming that the USERS tablespace has been damaged:

First, check the file ID (number) and name of the datafile of the tablespace. The output is shown in vertical format:

select file_id, file_name
from dba_data_files
where tablespace_name  = 'USERS';
FILE_ID : 4
NAME    : /home/oracle/oradata/PRODB2/
              users01.dbf

Connect to Oracle RMAN and complete the rest of the recovery activities, which are similar to the steps listed above except that Step 2 is now "Switch datafile 4 to the copy in the FRA." All the operations are shown in Listing 3.

Code Listing 3: RMAN operations to switch to the FRA

RMAN> sql 'alter tablespace users offline';
sql statement: alter tablespace users offline
RMAN> switch datafile 4 to copy;
datafile 4 switched to datafile copy "/home/oracle/FRA/PRODB2/datafile/o1_mf_users_2kmqr57t_.dbf"
RMAN> recover datafile 4;
Starting recover at 26-SEP-06
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 26-SEP-06
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online

After the tablespace is brought online, check the filename:

select name from v$datafile
where file# = 4;
NAME
----------------------------------
/home/oracle/FRA/PRODB2/datafile/
o1_mf_users_2kmqr57t_.dbf

Note that the filename is no longer /home/oracle/oradata/PRODB2/users01.dbf; rather, the copy in the FRA is shown as the original datafile. The tablespace becomes usable very quickly without a restore operation. Figure 1 shows original and copy datafile status before and after switching from a damaged datafile 4.

Figure 1. Using a copy of a datafile

Even though the datafile has been quickly brought online to minimize downtime, it is now in the backup location, which may be on slower disks than what the main database is on. You may not want to run the database with the datafile at this location for long; you would typically want to move the datafile back to the original location—/home/oracle/oradata/PRODB2/—as soon as it becomes available. You can use Oracle RMAN to accomplish this. Here is a summary of the steps:

1. Make an image copy of the datafile at the original location.
2. Take the tablespace offline.
3. Switch the datafile to the "copy" (however, in this case, the "copy" is at the original location).
4. Recover the tablespace.
5. Place the tablespace online.

These steps are presented in Listing 4. After the switchover, you can make sure the datafile is back in its original location:

select name from v$datafile
where file# = 4;
NAME
---------------------------------------
/home/oracle/oradata/PRODB2/users01.dbf

Code Listing 4: Switching back from the FRA to the original location

RMAN> backup as copy datafile 4 format '/home/oracle/oradata/PRODB2/users01.dbf';
Starting backup at 27-SEP-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/home/oracle/FRA/PRODB2/datafile/o1_mf_users_2kmqr57t_.dbf
output filename=/home/oracle/oradata/PRODB2/users01.dbf tag=TAG20060927T103710 recid=45 stamp=602246230
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-SEP-06
Starting Control File Autobackup at 27-SEP-06
piece handle=/home/oracle/FRA/PRODB2/autobackup/2006_09_27/
o1_mf_n_602246232_2ko34s42_.bkp comment=NONE
Finished Control File Autobackup at 27-SEP-06
RMAN> sql 'alter tablespace users offline';
...
RMAN> switch datafile 4 to copy; datafile 4 switched to datafile copy "/home/oracle/oradata/PRODB2/users01.dbf"
RMAN> recover datafile 4;
...
RMAN> sql 'alter tablespace users online';
...

RMAN> switch database to copy;

Note that you can also perform the above operations on the image copies in any location without using the FRA. However, using the FRA moves the burden of managing the space from the DBA to the database.

Although the backup to the FRA comes with great benefits, it is still not foolproof for normal disaster protection. Disks can fail, making these FRA backups disappear. Similarly, unlike tapes, disks cannot be removed easily and stored at a different location. Therefore, you still need to back up the FRA to tape. To do so, use the following command in RMAN. It backs up all contents of the FRA, including archived logs:

run {
  allocate channel c1 type sbt_tape;
  backup recovery area;
}

The primary objective of any backup design is to enhance the process of recovery—to make it faster and more reliable. Using the flash recovery area, DBAs can direct all backups to a single location that is managed by Oracle Database. Using the Oracle RMAN image copies in the FRA, DBAs can very quickly recover from damage to a datafile without using a traditional restore-and-recovery operation.

VIEW the Oracle RMAN switch to image copy demo