Determine the size of FRA to be configured before you enable archivelog mode for oracle db
Method: Probe your existing DB's alert log to find out the same.
Query 1:
alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';
set lines 200
set pages 3000
select min(cast(ORIGINATING_TIMESTAMP as date)),max(cast(ORIGINATING_TIMESTAMP as date)),count(1)
from V$DIAG_ALERT_EXT
where MESSAGE_TEXT like 'Thread%advanced%';
Actual output:
MIN(CAST(ORIGINATING MAX(CAST(ORIGINATING COUNT(1)
-------------------- -------------------- ----------
06/APR/2023 17:50:50 14/SEP/2023 14:05:13 30
Query 2:
with t as
(
select max(bytes) byts
from v$log
), u as
(
select min(cnt) mncnt,max(cnt) mxcnt,avg(cnt) acnt
from
(
select trunc(cast(ORIGINATING_TIMESTAMP as date),'DD')
,count(1) cnt
from V$DIAG_ALERT_EXT
where MESSAGE_TEXT like 'Thread%advanced%'
group by trunc(cast(ORIGINATING_TIMESTAMP as date),'DD')
)
)
select u.mncnt min_cnt
,u.mncnt*t.byts/1024/1024 min_sz_mb
,u.mxcnt max_cnt
,u.mxcnt*t.byts/1024/1024 max_sz_mb
,acnt*t.byts/1024/1024 avg_sz_mb
from t,u;
Actual output:
SQL> with t as
(
2 3 select max(bytes) byts
from v$log
), u as
(
select min(cnt) mncnt,max(cnt) mxcnt,avg(cnt) acnt
from
(
select trunc(cast(ORIGINATING_TIMESTAMP as date),'DD')
,count(1) cnt
from V$DIAG_ALERT_EXT
where MESSAGE_TEXT like 'Thread%advanced%'
group by trunc(cast(ORIGINATING_TIMESTAMP as date),'DD')
)
)
select u.mncnt min_cnt
,u.mncnt*t.byts/1024/1024 min_sz_mb
,u.mxcnt max_cnt
,u.mxcnt*t.byts/1024/1024 max_sz_mb
,acnt*t.byts/1024/1024 avg_sz_mb
from t,u; 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
MIN_CNT MIN_SZ_MB MAX_CNT MAX_SZ_MB AVG_SZ_MB
---------- ---------- ---------- ---------- ----------
1 50 27 1350 500
SQL>
How much now?
So we ideally shoot for max size or avg size by the number of days to set our FRA. Its upto you.
Total FRA size to keep 7 days archive in disk: 7*1350=9450 MB
To keep the FRA utilized @ 80% max: 9450*100/80=11812.5 MB
So I will set db_recovery_file_dest_size = 12 GB (only to host archvielog)
If your looking to host backups, you have additional rules to follow.
YouTube Video:
No comments:
Post a Comment