Last week I’ve faced an issue with a full Fast Recovery Area. The main cause was that an application script ran “amok”, lots of redo log changes happened, tons of archive logs were created and in the FRA there was no headroom for this case. After adding some storage the problem disappeared, but I want to get some statistics how often a redo log change happen and what was the archive log generation per day.
This statement displays in average in which timeframe the next redolog change happens:
SELECT
TRUNC(first_time),
ROUND(AVG(DELTA),1) "AVG Change/Minute"
FROM
(
select
sequence#,
first_time,
(first_time - lag(first_time) over (order by sequence#))*1440 delta
from V$LOGHIST
)
WHERE DELTA > 0
GROUP BY TRUNC(first_time)
ORDER BY TRUNC(first_time) desc;
As you can see in my table below, in my database every 15 minute a redo log change occur approximately.
[table]
Day,AVG Change/Minute
25.11.15 , 15.2
24.11.15 , 15.8
23.11.15 , 6
22.11.15 , 15.2
21.11.15 , 15.7
20.11.15 , 15
[/table]
Below the statement, it calculates the amount of archive logs which were generated per day. This is also interesting when creating or modifying your backup strategy.
SELECT
TO_CHAR(first_time,'DD-MON-YYYY') "Date",
ROUND(SUM(bytes)/1024/1024/1024) AS GBYTES
FROM
(
SELECT UNIQUE
blocks * block_size bytes,
thread#,
sequence#,
resetlogs_change#,
first_time
FROM V$ARCHIVED_LOG
)
GROUP BY TO_CHAR(first_time, 'DD-MON-YYYY')
ORDER BY 1 DESC;
At the 23-November the archive log creation increased dramatically, which results in the storage issue.
[table]
Day,Gbytes
25.11.15 , 9
24.11.15 , 58
23.11.15 , 180
22.11.15 , 61
21.11.15 , 59
20.11.15 , 62
[/table]
