Archived Log entry 19900 added for thread 1 sequence 19187 ID 0x9b87c8af dest 1:
Thread 1 cannot allocate new log, sequence 19189
Checkpoint not complete
Current log# 4 seq# 19188 mem# 0: /u01/app/oracle/oradata/ORCL/redo04.log
Thread 1 advanced to log sequence 19189 (LGWR switch)
Cause: The "checkpoint not complete" messages are generated because the logs are switching so fast that the checkpoint associated
with the log switch isn’t complete.During that time, Oracle’s LGWR process has filled up the other redo log groups and is now
waiting for the first checkpoint to successfully execute.
or
In this situation either DBWR writes slowly or log switch happens before the log is completely full or log file is small.
Solution:
Step1: Check the redo log status
SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 CURRENT
Step2: Drop the group1 which is "INACTIVE" and recreate with bigger size.
SQL> alter database drop logfile group 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('/data01/oradata/orcl/redo01a.log','/data02/oradata/orcl/redo01b.log')
SIZE 400M BLOCKSIZE 512 REUSE;
Step3: Repeat step 1 and 2 until drop and create rest of the two redologs
Step4: Using following query you can find the no of log switches per hour.
select to_char(first_time,'YYYY-MM-DD') Day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
sum(1) "TOTAL_IN_DAY"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD')
order by to_char(first_time,'YYYY-MM-DD') desc
Note: In a busy production environment, it is important to ensure that the frequency of redo log switches is not more than 5 per hour.
