You can try the following:
1. Increasing the size of the log files.
2. Increase the number of redo log groups.
Additionally try to investigate the huge amount of redo-generation.
Check also the history of Redo log:
Code: Select all
/*
* Redo Log Info
*
*/
-- **************************************************************
-- redo log details
-- **************************************************************
SET LINESIZE 150
SET PAGESIZE 50
select group#, sequence#, members, archived, status, TO_CHAR(bytes/1024) || 'KB' as "SIZE"
from v$log
/
COL MEMBER FOR A50
select * from v$logfile
/
-- **************************************************************
-- redo log history
-- **************************************************************
clear break comp col
col day for a10
col " 00" for a5
col " 01" for a5
col " 02" for a5
col " 03" for a5
col " 04" for a5
col " 05" for a5
col " 06" for a5
col " 07" for a5
col " 08" for a5
col " 09" for a5
col " 10" for a5
col " 11" for a5
col " 12" for a5
col " 13" for a5
col " 14" for a5
col " 15" for a5
col " 16" for a5
col " 17" for a5
col " 18" for a5
col " 19" for a5
col " 20" for a5
col " 21" for a5
col " 22" for a5
col " 23" for a5
col " 24" for a5
set lines 190 pages 10
set veri off
set colsep ""
prompt
set heading on
prompt Tabular display of redo-log archiving history (logs/hour)
prompt
select to_char(first_time,'RRRR/MM/DD') 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"
from sys.v_$log_history
group by to_char(first_time,'RRRR/MM/DD')
/
set colsep " "