SCRIPT FOR CHECKING ARCHIVE LOG GENERATION ON ORACLE DATABASE

/////////////////////////On daily basis:

set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
-- As Table
SELECT TRUNC(completion_time) AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time) || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;



//////////////////////////On hourly basis:



set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24';
-- As Table
SELECT TRUNC(completion_time,'HH24') AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time,'HH24') || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;