Flash/Fast Recovery Area (FRA) in Oracle : In this article we will see Flash/Fast Recovery Area (FRA) in Oracle. How to Setting Initialization Parameters for Size and Location of the Flash Recovery Area.
What does the Oracle fast recovery area do?
The FRA is essentially a disk location for the storage of files related to the recovery process. These files in the FRA are managed by Oracle and RMAN automatically. Files that could be found in the FRA include:
- RMAN backups
- control file
- logs
- online redo log copies
- archived redo logs
- flashback logs
Setting Initialization Parameters for Size and Location of the Flash Recovery Area :
To enable the flash recovery area, you must set the two initialization parameters DB_RECOVERY_FILE_DEST_SIZE (which specifies the disk quota, or maximum space to use for flash recovery area files for this database) and DB_RECOVERY_FILE_DEST (which specifies the location of the flash recovery area).
Note :
- DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.
- In a RAC database, all instances must have the same values for these parameters.
- Initialization parameters can be specified by any of the following means:
- Include them initialization parameter file of the target database
- Specify them with the SQL statement ALTER SYSTEM SET
- Use the Database Configuration Assistant to set them
- To find out the current flash recovery area location, query V$RECOVERY_FILE_DEST.
Flash Recovery Area Size: DB_RECOVERY_FILE_DEST_SIZE :
This initialization parameter specifies the maximum storage in bytes of data to be used by the flash recovery area for this database.
Flash Recovery Area Location: DB_RECOVERY_FILE_DEST :
This parameter specifies a valid disk location for file creation, which can be a directory on a file system, or Automatic Storage Management disk group.
Adding a Flash Recovery Area to an Existing Database
To create a flash recovery area, you can set the necessary parameters in the initialization parameter file (PFILE) and restart the database. You can also set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters using ALTER SYSTEM. To add a flash recovery area to an open database, as shown in this example.
Note:
After you start SQL*Plus and connect to the database, set the size of the flash recovery area. For example, set it to 10 GB:
SQL>
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
SQL>
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
System altered.
SQL>
SQL>
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 10G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
Set scope=both make the change both in memory and the server parameter file. (Setting SID to “*” has no effect in a single-instance database; in a RAC database it causes the change to take effect across all instances.)
Set the location of the flash recovery area. For example, if the location is the file system directory /u01/app/oracle/flash_recovery_area, then you can do the following:
SQL>
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='/u01/app/oracle/flash_recovery_area/' SCOPE=BOTH SID='*';
System altered.
SQL>
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area/
db_recovery_file_dest_size big integer 10G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
If the flash recovery area location is an Automatic Storage Management disk group named disk1, for example, then you can do the following:
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';Query to see the size and destination of the FRA :
This V$RECOVERY_FILE_DEST view to find out the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area.
SQL>
SQL> set lines 200 pages 100
SQL> col NAME for a60
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
------------------------------------------------------------ ----------- ---------- ----------------- --------------- ----------
/u01/app/oracle/flash_recovery_area/ 1.0737E+10 0 0 0 0
SQL>
The V$FLASH_RECOVERY_AREA_USAGE view to find out the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files that are obsolete, redundant, or already backed up to tape.
SQL>
SQL> set lines 200 pages 100
SQL>
SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE 0 0 0 0
REDO LOG 0 0 0 0
ARCHIVED LOG 0 0 0 0
BACKUP PIECE 0 0 0 0
IMAGE COPY 0 0 0 0
FLASHBACK LOG 0 0 0 0
FOREIGN ARCHIVED LOG 0 0 0 0
AUXILIARY DATAFILE COPY 0 0 0 0
8 rows selected.
SQL>
Disabling the Flash Recovery Area
To disable the flash recovery area, set the DB_RECOVERY_FILE_DEST initialzation parameter to a null string. Use this SQL*plus statement to change the parameter on a running database:
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area/
db_recovery_file_dest_size big integer 10G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
SQL>
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
System altered.
SQL>
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 10G
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
I hope my written content will helped you. Your suggestions/feedback are most welcome orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.