Thursday, June 18, 2015

How to estimate space needed for archive logs

We are back in LinkedIn and this time in Oracle senior DBA group discussion. The question is:

How to calculate necessary space on File System for put in archive log mode an database oracle 11g, currently is not in archive log mode, thanks

Space needed for your archive log location(s) can be estimated by following script, which gets average redo log file size (which should be same for all files BTW) and basically multiplies that by number of log file switches per day:

SELECT log_hist.*,
ROUND(log_hist.num_of_log_swithes * log_file.avg_log_size / 1024 / 1024) avg_mb
FROM
(SELECT TO_CHAR(first_time,'DD.MM.YYYY') DAY,
COUNT(1) num_of_log_swithes
FROM v$log_history
GROUP BY TO_CHAR(first_time,'DD.MM.YYYY')
ORDER BY DAY DESC
) log_hist,
(SELECT AVG(bytes) avg_log_size FROM v$log) log_file;

Archive log files usually go into same location where your “hot” backups go, like FRA. Space needed there depends on your backup strategy and retention policy. If you are deleting archive log files after each successful full or incremental backup, space needed should be count of days between backups plus some good reserve. If you are backing archive log files from archive log destination to some other device, than you should estimate needed space based on that.

No comments:

Post a Comment