In this Document

Applies to:

Symptoms

When you manually switch the online redo logs with an alter system command as below.

SQL> alter system switch logfile;
System altered.

The Alert log will display the following messages.

Thread 1 cannot allocate new log, sequence 207
Checkpoint not complete

- or -

Thread 1 cannot allocate new log, sequence 746
Private strand flush not complete

Changes

Implemented a scheduler job to manually switch the online redo logs every 15 minutes.

Cause

When you switch logs, all private strands have to be flushed to the current log before the switch is allowed to proceed.

Solution

This is expected behavior according to development.  Following is an extract from unpublished Bug 5241081 on why this can occur during manual log switch.

"Technically all the strands need to be flushed when the log switch is being initiated and hence the messages for checkpoint not complete and private strand flush not complete are synonymous. The crux is that the strand still have transactions active which need to be flushed before this redo can be overwritten, would recommend letting Oracle perform the checkpointing by default and tune fast_start_mttr_target to achieve what one is looking for."

This issue is very easy to duplicate on any database running in archivelog mode.  When the online logs are manually switched with no sessions connected, the problem messages are not displayed in the alert log.

SQL> alter system switch logfile;
System altered.

Alert log shows the following.

Thread 1 advanced to log sequence 206
Current log# 3 seq# 206 mem# 0: +GROUP01/oasm/onlinelog/group_3.258.609176403
Current log# 3 seq# 206 mem# 1: +GROUP01/oasm/onlinelog/group_3.263.609176613 

Now connect to another session (using scott/tiger schema object in the example) and leave an update TX active.

SQL> update emp set sal = sal * 1;
14 rows updated.
... do not commit and keep session active

Then manually switch the logs in the original session, and now the alert log shows the following messages. 

Thread 1 cannot allocate new log, sequence 207
Checkpoint not complete
Current log# 3 seq# 206 mem# 0: +GROUP01/oasm/onlinelog/group_3.258.609176403
Current log# 3 seq# 206 mem# 1: +GROUP01/oasm/onlinelog/group_3.263.609176613

Or the following.

Thread 1 cannot allocate new log, sequence 85
Private strand flush not complete
Current log# 3 seq# 84 mem# 0: C:\ORACLE\ORA11\ORADATA\ORCL11\REDO03.LOG

This is not a problem, but expected behavior. So when you manually switch logs on a DB with any activity at all, you will likely see these messages written to the alert log.  This can also occur when have archive_lag_target set.

References