The wait event “logfile switch checkpoint incomplete” indicates that Oracle needs to reuse a redo log file, but the current checkpoint position is still in that log.
In this scenario, the oracle db will wait until the checkpoint position passes that log.
When the database waits on checkpoints, redo generation is stopped until the log switch is done.
SQL> select group#, thread#, member, archived, status, BYTES/1024/1024 MB from v$log
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
—— ——- ———————————————————————- ———- ———- ———
1 1 /logfile_location/redo01.log YES ACTIVE 50
2 1 /logfile_location/redo02.log NO CURRENT 50
3 1 /logfile_location/redo03.log YES ACTIVE 50
We can see two are in ACTIVE and one is CURRENT, no log is in either INACTIVE or UNUSED state. i.e it is trying to do a log switch. But not able to do see.
So I just added two additional redo log files:
SQL> alter database add logfile group 4 (‘/logfile_location/redo04.log’) size 50M;
SQL> alter database add logfile group 5 (‘/logfile_location/redo05.log’) size 50M;
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
—— ——- ———————————————————————- ———- ———- ———
1 1 /logfile_location/redo01.log YES ACTIVE 50
2 1 /logfile_location/redo02.log YES ACTIVE 50
3 1 /logfile_location/redo03.log YES ACTIVE 50
4 1 /logfile_location/redo04.log NO CURRENT 50
5 1 /logfile_location/redo05.log No UNUSED 50
So, if you find log file switch wait events, then add additional redo log groups to fix it.
You can even add redologs of higher size. and drop the old redo logs.
