Script to figure out total number of log switch occurred

Script to find out total number of archive logs generated for hourly, daily, monthly & yearly.

This will help you to figure out:
– The transnational work load on your database.
– The total number of archive log files being generated.
– To schedule or Re-schedule your RMAN transnational log backup policy.

// Script to find-out hourly log switch count:
set pages 1000
select to_char(COMPLETION_TIME,'dd mon yyyy hh24') as Hour,
thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by to_char(COMPLETION_TIME,'dd mon yyyy hh24'),thread# order by 1;
// Script to find-out daily log switch count:
 SELECT to_char(first_time, 'dd-mon-yyyy') as "Date", 
 count(*) as "Daily log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'dd-mon-yyyy');

OR

SELECT A.*, Round(A.Count*B.AVG/1024/1024) Daily_average_MB
 FROM ( SELECT To_Char(First_Time,'YYYY-MM-DD') Day,
 Count(1) Count,
 Min(RECID) Min,
 Max(RECID) Max
 FROM v$log_history
 GROUP BY To_Char(First_Time,'YYYY-MM-DD')
 ORDER BY 1 DESC ) A,
 ( SELECT Avg(BYTES) AVG,
 Count(1) Count#,
 Max(BYTES) Max_Bytes,
 Min(BYTES) Min_Bytes
 FROM v$log ) B;

// Script to find-out monthly log switch count:

 SELECT to_char(first_time, 'yyyy-mon') as "Year:Month",
 count(*) as "Daily log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'yyyy-mon')
 ORDER BY 1;
// Script to find-out yearly log switch count:
 SELECT to_char(first_time, 'yyyy') as "Year", 
 count(*) as "Yearly log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'yyyy');

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have a easy life ahead.

Leave a Reply