1. Observation
I do select :
SELECT dat,num FROM
(SELECT TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24:Mi') dat,
COUNT(1) num
FROM V$LOGHIST
WHERE trunc(FIRST_TIME) = trunc ( SYSDATE )
GROUP BY TO_CHAR(FIRST_TIME,'DD-MON-YYYY HH24:Mi')
HAVING COUNT(1) >= 8
ORDER BY dat desc;
As a result, I 've found that redo logs switch more than 8 every minutes,
2. Get information about DB configuration
set markup html on spool on
SPOOL logswitch.HTML
set echo on
set pagesize 30;
select systimestamp from dual;
select * from v$version;
select name,LOG_MODE from v$database;
select * from v$instance_recovery;
select OPTIMAL_LOGFILE_SIZE from v$instance_recovery;
show parameter log_buffer;
show parameter log_checkpoint_interval;
show parameter log_checkpoint_timeout;
show parameter fast_start_mttr_target;
show parameter archive_lag_target;
show parameter filesystem
show parameter disk
select name,value from v$parameter where name like '%log_archive%' and value <> 'enable';
select name from v$controlfile;
select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member
from v$logfile lf, v$log lg
where lg.group# = lf.group#
order by 1, 2;
select to_char(first_time,'YYYY-MON-DD') "Date", to_char(first_time,'DY') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23" ,
count(*) Total
from v$log_history
group by to_char(first_time,'YYYY-MON-DD'), to_char(first_time,'DY')
order by to_date(to_char(first_time,'YYYY-MON-DD'),'YYYY-MON-DD')
/
select to_char(COMPLETION_TIME,'DD/MON/YYYY') Day,
trunc(sum(blocks*block_size)/1048576/1024,2) "Size(GB)",count(sequence#) "Total Archives"
from
(select distinct sequence#,thread#,COMPLETION_TIME,blocks,block_size from v$archived_log)
group by to_char(COMPLETION_TIME,'DD/MON/YYYY')
order by to_date(to_char(COMPLETION_TIME,'DD/MON/YYYY'),'DD/MON/YYYY')
;
spool off
set markup html off spool off
3. we got output
SQL> select lg.group#,lg.bytes/1024/1024 mb, lg.status, lg.archived,lf.member
2 from v$logfile lf, v$log lg
3 where lg.group# = lf.group#
4 order by 1, 2;
GROUP# MB STATUS ARC MEMBER
1 50 ACTIVE YES /data01/oradata/TWCMS/redo01.log
1 50 ACTIVE YES /data01/oradata/TWCMS/redo01a.log
2 50 ACTIVE YES /data01/oradata/TWCMS/redo02a.log
2 50 ACTIVE YES /data01/oradata/TWCMS/redo02.log
3 50 ACTIVE YES /data01/oradata/TWCMS/redo03.log
3 50 ACTIVE YES /data01/oradata/TWCMS/redo03a.log
4 50 ACTIVE YES /data01/oradata/TWCMS/redo04.log
4 50 ACTIVE YES /data01/oradata/TWCMS/redo04a.log
5 50 ACTIVE YES /data01/oradata/TWCMS/redo05.log
5 50 ACTIVE YES /data01/oradata/TWCMS/redo05a.log
6 50 CURRENT NO /data01/oradata/TWCMS/redo06a.log
6 50 CURRENT NO /data01/oradata/TWCMS/redo06.log
As a result, redo logs file is very small (50 mb).
4. action to be taken
Reviewed the uploaded files and found the most of the redo logs are always ACTIVE and the no of log switches are at particular times.
This is due to small redo log file size 50MB. I would request you to resize redo log file size to 500MB and monitor your database.
