PROBLEM:

Database response was very slow (almost frozen) with database spending most of the time in wait event “log file switch (checkpoint incomplete)’ Waits event”

ANALYSIS:

As per Oracle docs:

This event indicates that Oracle needs to reuse a redo log file, but the current checkpoint position is still in that log. In this case, Oracle must wait until the checkpoint position passes that log. When the database waits on checkpoints, redo generation is stopped until the log switch is done.

So I checked the status of redolog files:

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 add two additional redo log files:

As soon as, the additional redo log is added, all the stock transactions are cleared and the wait event is gone completed.

CONCLUSION:

So, if you find log file switch (checkpoint incomplete)’ Wait events, then add additional redo log groups to fix it.  You can even add redologs of higher size. (and drop the old ones). If still issue is there, then generate an AWR report and analyze.