Today I had a weird issue on a 10G database. The database was not doing very much and although the redo log files where not full, the database performed a log switch every 10 minutes. And I kept seeing “Checkpoint not complete” in the alertlog. When I looked in the database I noticed that 2 out of 3 log groups where active and 1 current. So I was searching what made the database switch the logfile, although it was not full yet. After a while I found that the parameter archive_lag_target was set to 600 seconds, what explained the log switch every 10 minutes. And since the default value of log_checkpoint_timeout is 1800 seconds explained why the checkpoint not complete messages kept appearing. Solution to this issue in my case, add an extra log group, or even better yet set the log_checkpoint_timeout to the same value as archive_lag_target.

In my case it was not an option to disable the archive_lag_target, since this is an parameter that has been set from a business point of view. No more then 10 minutes loss of data in case op a failure.

Sample of the logfile:

<em>Wed Feb 17 18:31:43 2010
Thread 1 cannot allocate new log, sequence 66738
Checkpoint not complete
Current log# 1 seq# 66737 mem# 0: <ORADATA>ONLINELOGO1_MF_1_43F0DRVC_.LOG
Current log# 1 seq# 66737 mem# 1: <ORADATA>ONLINELOGO1_MF_1_43F0DSX9_.LOG
Thread 1 cannot allocate new log, sequence 66738
Private strand flush not complete
Current log# 1 seq# 66737 mem# 0: <ORADATA>ONLINELOGO1_MF_1_43F0DRVC_.LOG
Current log# 1 seq# 66737 mem# 1: <ORADATA>ONLINELOGO1_MF_1_43F0DSX9_.LOG
Wed Feb 17 18:31:49 2010
Thread 1 advanced to log sequence 66738 (LGWR switch)
Current log# 3 seq# 66738 mem# 0: <ORADATA>ONLINELOGO1_MF_3_43F0F1RW_.LOG
Current log# 3 seq# 66738 mem# 1: <ORADATA>ONLINELOGO1_MF_3_43F0F4HN_.LOG
Wed Feb 17 18:41:49 2010
Thread 1 cannot allocate new log, sequence 66739
Checkpoint not complete
Current log# 3 seq# 66738 mem# 0: <ORADATA>ONLINELOGO1_MF_3_43F0F1RW_.LOG
Current log# 3 seq# 66738 mem# 1: <ORADATA>ONLINELOGO1_MF_3_43F0F4HN_.LOG
Thread 1 cannot allocate new log, sequence 66739
Private strand flush not complete
Current log# 3 seq# 66738 mem# 0: <ORADATA>ONLINELOGO1_MF_3_43F0F1RW_.LOG
Current log# 3 seq# 66738 mem# 1: <ORADATA>ONLINELOGO1_MF_3_43F0F4HN_.LOG
Wed Feb 17 18:41:55 2010
Thread 1 advanced to log sequence 66739 (LGWR switch)
Current log# 2 seq# 66739 mem# 0: <ORADATA>ONLINELOGO1_MF_2_43F0DWF5_.LOG
Current log# 2 seq# 66739 mem# 1: <ORADATA>ONLINELOGO1_MF_2_43F0DZ1G_.LOG
Wed Feb 17 18:51:53 2010
Thread 1 cannot allocate new log, sequence 66740
Checkpoint not complete
Current log# 2 seq# 66739 mem# 0: <ORADATA>ONLINELOGO1_MF_2_43F0DWF5_.LOG
Current log# 2 seq# 66739 mem# 1: <ORADATA>ONLINELOGO1_MF_2_43F0DZ1G_.LOG
Thread 1 cannot allocate new log, sequence 66740
Private strand flush not complete
Current log# 2 seq# 66739 mem# 0: <ORADATA>ONLINELOGO1_MF_2_43F0DWF5_.LOG
Current log# 2 seq# 66739 mem# 1: <ORADATA>ONLINELOGO1_MF_2_43F0DZ1G_.LOG
Wed Feb 17 18:51:59 2010
Thread 1 advanced to log sequence 66740 (LGWR switch)
Current log# 1 seq# 66740 mem# 0: <ORADATA>ONLINELOGO1_MF_1_43F0DRVC_.LOG
Current log# 1 seq# 66740 mem# 1: <ORADATA>ONLINELOGO1_MF_1_43F0DSX9_.LOG