How to estimate flashback destination space

Sometimes application team will ask DBA to enable ashback for x number of days. In such case, a DBA needs to estimate the ashback space required for x number of days in order to store the ashback logs. The flashback log size is same as archive log size generated in a database.

How to display date and time in query output
  • Check the archive generation size via below query
  • Take the average per day size of archives generated
  • Multiply the average archive size with x number of days
  • Ask storage team to add the required space for flashback file system

Check archive generation size via below query:

Backup QNAP Turbo NAS data using CloudBackup Station
sql">select to_char(COMPLETION_TIME,'DD-MON-YYYY') Arch_Date,count(*) No#_Logs, sum((BLOCKS*512)/1024/1024/1024) Arch_LogSize_GB from v$archived_log
where to_char(COMPLETION_TIME,'DD-MON-YYYY')>=trunc(sysdate-7) and DEST_ID=1 group by to_char(COMPLETION_TIME,'DD-MON-YYYY') order by to_char(COMPLETION_TIME,'DD-MON-YYYY');

Note: Take average size * 30 days to get 1 month flashback space size.

How to find memory used by Oracle








Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.