Monday 11 August 2008

Oracle Database : Space used in the flash recovery area

Here is a small script adopted from the book "RMAN Recipes for Oracle 11g" by Darl Kuhn, Sam R. Alapati and Arup Nanda. This script when run in from SQL Plus, will display the amount of used space in the current database flash recovery area.

SET PAGESIZE 66;
SET LINESIZE 80;

REPHEADER PAGE CENTER 'Flash Recovery Area Usage';

COLUMN FILE_TYPE FORMAT a20;
COLUMN FILE_TYPE HEADING 'File Type';

COLUMN USED_MB HEADING 'Used MBytes';
COLUMN USED_MB FORMAT 999999.99;
COLUMN RECLAIMABLE_MB HEADING 'Reclaimable Mbytes';
COLUMN RECLAIMABLE_MB FORMAT 9999999.99;

COLUMN NUMBER_OF_FILES HEADING 'Number of files';

BREAK ON REPORT
COMPUTE SUM LABEL 'Totals:' OF USED_MB RECLAIMABLE_MB ON REPORT;

SELECT
  rau.file_type,
  rfd.space_used * rau.percent_space_used / 1024 / 1024 as USED_MB,
  rfd.space_reclaimable * rau.percent_space_reclaimable / 1024 / 1024 as RECLAIMABLE_MB,
  rau.number_of_files as NUMBER_OF_FILES
FROM
  v$recovery_file_dest rfd, v$flash_recovery_area_usage rau;

Hre is a sample output run against a newly created 11g database

                            Flash Recovery Area Usage

File Type            Used MBytes Reclaimable Mbytes Number of files
-------------------- ----------- ------------------ ---------------
CONTROL FILE              716.17                .00               1
REDO LOG                11649.70                .00               3
ARCHIVED LOG            31957.11             391.11              10
BACKUP PIECE            79272.10                .00               2
IMAGE COPY                   .00                .00               0
FLASHBACK LOG                .00                .00               0
FOREIGN ARCHIVED LOG         .00                .00               0
                     ----------- ------------------
Totals:                123595.09             391.11

The concept is very simple. Oracle provides two views for retrieving information about the flash recovery area. One is the v$recovery_file_dest that contains information about the flash recovery area associated with the current database. A select * on this view will give us an output like the following :

SQL> select * from v$recovery_file_dest;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/u01/app/oracle/flash_recovery_area
 2147483648 1668797952          93845504              16

This view provides the total number of used and reclaimable bytes from the flash recovery area. The next view named v$flash_recovery_area_usage, provided percentage information about each type of file that can be stored in the flash recovery area. Doing a select * on this view gives up the following results.

SQL> set linesize 100;
SQL> select * from v$flash_recovery_area_usage

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        ,45                         0               1
REDO LOG                           7,32                         0               3
ARCHIVED LOG                      20,08                      4,37              10
BACKUP PIECE                      49,81                         0               2
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

... so the next logical thing in order to get something meaningful out of these is to join them and this is what the initial script does. Since the v$recovery_file_dest view has only one column, all columns of the v$flash_recovery_area_usage view refer this hence the no need for a where clause in the script's sql.