Recover In A Flash

Recover in a Flash By Arup Nanda Reduce database recovery time, using the Oracle flash recovery area. 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. Flash Recovery Area 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. Setting Up 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 -------------CONTROLFILE .00 ONLINELOG .00 ARCHIVELOG 664.86 BACKUPPIECE 573.23 IMAGECOPY .00 FLASHBACKLOG 6.07 ORA-19809: limit exceeded ORA-19804: cannot reclaim

RECLAIMABLE NUMBER_OF_FILES ------------------------.00 0 .00 0 547.20 34 520.73 16 .00 0 .00 1 for recovery files 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." Image Copy

Oracle Data Guard and the FRA If you are familiar with Oracle Data Guard, you might wonder how using this FRA recovery method is different. Oracle Data Guard maintains physical or logical standby Backup sets are the Oracle RMAN default backups, in databases that are kept synchronized with the primary which only the used blocks in the datafiles are captured database through the transfer and application of redo data. in the backup files. Oracle RMAN image copies are These standby databases are geared toward disaster exact copies of the datafiles, with all the blocks—used recovery and should not replace your backup-andor not. Oracle RMAN takes this image copy while the recovery operations. For example, if you lose a file, you database is up and running, and the database need not can restore that file from the physical standby database, be put into any special mode. Here is how to make an but that approach may take time, depending on the state Oracle RMAN image copy backup: and location of the standby database, and is the same as a traditional recovery solution. 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. Instant Recovery 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.

Oracle Data Guard, however, allows fast failover/switchover (role transition) to a standby database running on separate servers and storage, which may be geographically separated, maintaining data availability, in the event the primary database site goes down for any reason. The FRA, on the other hand, is local to the database server, so although it provides quick access for recovery purposes, it is prone to the same failure as the local site. For Oracle Data Guard, applications that utilize the database must be reconnected to use the new primary database in case of a role transition. Because the FRA is local to the database, no application reconnection is needed. Oracle Data Guard provides a predictable recovery time (time to perform role transition), versus the time to recover FRA image copies, which is gated by the amount of redo application needed to bring the image copies up-to-date with the rest of the database.

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 Switchback 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'; ...

In case of a failure, you save valuable time by quickly using the image copy of the datafile in the FRA, and there is no need to restore it first. The same concept can be applied to the entire database as well. If the original location of all the datafiles is damaged, you can easily switch the entire database to the copy stored in the FRA. To switch to the FRA copy, issue the following, which directs the whole database to use all the latest image copies in the FRA location as its datafiles: 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. Back Up to Tape 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; }

Conclusion 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.