Estimating Flashback retention size (FRA)

Early last year there was a dearth of information out there about estimating FRA before turning it on on an existing production database (not a new one).  Since then, lots of advice has appeared online.  Since we haven’t implemented Flashback yet on any database, we need to analyze how much additional space would be necessary, and provide a cost/benefit analysis for management on space (ie. cost) vs. the risks flashback alleviates.  I created the following formula to provide our space estimates.  Note that we tend to be generous with space, we backup to non-SAN disk over NFS before going to tape so we wouldn’t put rman backups in the FRA, and we’re on 10g.  We also do a lot of capacity tracking and trending using a daily storage collector and custom code that uses jfreecharts and some homegrown algorithms (more on that in another post).

This is just my shot at this from various sources.  Feel free to poke holes.

—————————————————————————————–
To estimate flashback size:

FRA = (REDO + FB_BUFFER) * FB_RETENTION + ARCH_ON_DISK + ARCH_BUFFER

where (substitute any time unit instead of days)

REDO = 1 day of redo
FB_RETENTION = flashback retention in days
FB_BUFFER = 1 day of redo * 20%
ARCH_ON_DISK = 1 day archive logs * days between arch cleanups
ARCH_BUFFER = 1 extra day of archive logs

Note: 1 day of REDO = 1 day of ARCH

Based on:

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28viewingMode=1143&bmDocType=HOWTO&bmDocDsrc=DOCUMENT&bmDocTitle=CALCULATE%20THE%20VOLUME%20OF%20FLASHBACK%20LOG%20GENERATION&from=BOOKMARK&bmDocID=761126.1%29%29

http://forums.oracle.com/forums/thread.jspa?messageID=3968667&tstart=0

——————————————————————————————-
Calculating REDO and ARCH per day (handles RAC):

-- not grouped

SELECT Start_Date,
 Start_Time,
 Num_Logs,
 Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),
 2) AS Mbytes,
 Vdb.NAME AS Dbname
 FROM (SELECT To_Char(Vlh.First_Time,
 'YYYY-MM-DD') AS Start_Date,
 To_Char(Vlh.First_Time,
 'HH24') || ':00' AS Start_Time,
 COUNT(Vlh.Thread#) Num_Logs
 FROM V$log_History Vlh
 GROUP BY To_Char(Vlh.First_Time,
 'YYYY-MM-DD'),
 To_Char(Vlh.First_Time,
 'HH24') || ':00') Log_Hist,
 V$log Vl,
 V$database Vdb
 WHERE Vl.Group# = 1
 ORDER BY Log_Hist.Start_Date,
 Log_Hist.Start_Time
/

-- by day

SELECT start_Date,
 sum(   Round(Num_Logs * (Vl.Bytes / (1024 * 1024)), 2)) AS Mbytes
 FROM (SELECT To_Char(Vlh.First_Time,
 'YYYY-MM-DD') AS Start_Date,
 To_Char(Vlh.First_Time,
 'HH24') || ':00' AS Start_Time,
 COUNT(Vlh.Thread#) Num_Logs
 FROM V$log_History Vlh
 GROUP BY To_Char(Vlh.First_Time,
 'YYYY-MM-DD'),
 To_Char(Vlh.First_Time,
 'HH24') || ':00') Log_Hist,
 V$log Vl,
 V$database Vdb
 WHERE Vl.Group# = 1
 GROUP BY start_date
 ORDER BY start_date
/

-= Example =- 

START_DATE     MBYTES
---------- ----------
2010-01-19      31100
2010-01-20      36100
2010-01-21      16500

Based on: http://oraqa.com/2006/02/20/how-to-monitor-the-amount-of-redo-generated-per-hour/

—————————————————————————————–
Calculating ARCH per day – alternate method, not preferred

1) grab some sample days of alert log history (make sure only one day is in the file)
2) grep “advancing to” alertlogonedayfile.txt > logswitches.txt
3) wc -l logswitches.txt

ARCH_PER_DAY = LOGSW_PER_DAY * REDO_LOG_SIZE

Repeat for each node if using RAC and add them together.

—————————————————————————————–
Example

XXXXPR – RAC database

REDO = 1 day of redo = 108G was highest found in 1 month log history
FB_RETENTION = flashback retention in days = let’s try 7 days retention
FB_BUFFER = 1 day of redo * 20% = 22G
ARCH_ON_DISK = 1 day archive logs * days between arch cleanups = 120G * 2 days (really 1.5 or so) = 240G
ARCH_BUFFER = 1 extra day of archive logs = 240G

FRA = (108G + 22G) * 7 + 240G + 240G = 1271G or 1.4T

Current ARCH and REDO mount sizes

/u02 = 300G
/u03 = 10G

Additional space required: 1.1T (basic space, no room for growth)

With growth room: 1.6T