Check Archive Log Generation


Redo log script to see how many in day.
select to_char(first_time, 'DD-MON-YYYY'),
count(*)
from v$loghist
where first_time > '01-JAN-2010' /* or any other date that may be relevant */
Group by to_char(first_time, 'DD-MON-YYYY')
Order by count(*) asc;

==============================
==============================

select inst_id, to_char(completion_time, 'DD HH24') day_hour, count(*)
from gv$archived_log
where completion_time > sysdate - 7
group by inst_id, to_char(completion_time, 'DD HH24')
order by inst_id, to_char(completion_time, 'DD HH24')
/


Actual Space consumed on redo archive location


Per day


select trunc(COMPLETION_TIME,'DD') Day, thread#, dest_id,
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives_Generated from v$archived_log where dest_id=1
group by trunc(COMPLETION_TIME,'DD'),thread#, dest_id order by 1 desc;


Per hour


select trunc(COMPLETION_TIME,'HH') Hour,thread# , 
round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,
count(*) Archives from v$archived_log 
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 desc;

No comments:

Post a Comment