How to calculate FRA

 

How to calculate FRA

It is always questionable , what is the value should I set for Recovery area dest size ? 

Below is easiest way you can set FRA (Fast recovery area ) usage.

If database enabled Archivelog and  Flashback , it is recommended to set log_archive_dest parameter set to USE_DB_RECOVERY_FILE_DEST, So, generated archivelog and flashbacklog are easily measure by FRA.

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST';

System altered.

SQL> show parameter log_archive_dest_1;

NAME               TYPE        VALUE
------------------------------ ----------------------------------
log_archive_dest_1 string      location=USE_DB_RECOVERY_FILE_DEST

From my previous post fra-configuration-and-some-useful you can easily understand how to enable fast recovery area.

If there are multiple database instance on same server, and db_recovery_file_dest set to same location , then you have to proper calculate the FRA usage for each database instance and sum of all that db_recovery_file_dest_size value less than total size of db_recovery_file_dest.

For instance, db_recovery_file_dest set to +FRA and having 500G total in size. And I have 5 database instance on that server then total value of db_recovery_file_dest_size of each database instance must be less than 500G.

There are two mostly advisable way, you can easily calculate FRA usage :

Using OEM :

• Monitored database growth with OEM 
• Monitored redo generation through Data Guard console in OEM   
• Increased db_file_recovery_dest_sizeto match the growth of the database monthly 

Using sql :

• Need to check current FRA usagec(v$recovery_area_usage) details. 
• check ESTIMATED_FLASHBACK_SIZE using v$flashback_Database_logfile. 
• Check flashback statistics for generation ratio of flashback log and archive log using v$flahback_database_Stat.
• Now using step-3 check size of flashbacklog and archivelog for that perticular duration.

  

SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

SQL>  SELECT
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT,
  SPACE_USED ,
  SPACE_RECLAIMABLE ;
 
   
SQL> select * from v$recovery_area_usage;

col name for a60;
SQL>  select * from v$flashback_Database_logfile;

-- Check all flashback logfile Firt_TIme column from above sql. 

SQL> select * from v$flashback_database_log;

-- From above sql , you get estimated_flashback_size

SQL>  select BEGIN_TIME,END_TIME,FLASHBACK_DATA/1024/1024 flashback_Data, DB_DATA/1024/1024 db_Data, REDO_DATA/1024/1024 redo_Data , ESTIMATED_FLASHBACK_SIZE/1024/1024 ESTIMATED_FLASHBACK_SIZE from v$flashback_database_stat;

-- This sql you get mostly last 24hr flashback and archivelog estimate size, Just copy into excel sheet and do some mathematical stuff. 

 

SQL>  select trunc(completion_time) rundate
,count(*)  logswitch
,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from v$archived_log
group by trunc(completion_time)
order by;

-- Check number of log switch and generated archivelog size from above sql.

To Sum All,

You have to calculate generated archivelog and flashbacklog file size for one day and repeat same thing for couple of days. This way you get your  average recovery area usage. From this value you have to set your recovery area dest size accordingly.