Force Log Switches

You can force the log switches using the following statements,

SQL> Alter System Switch LogFile;
SQL> Alter System Archive Log Current;

The difference between the two statements is that the first statement issues a log switch and the control immediately returns to the user. The user can perform other activities while the log file is being archived at the background. However the second statement will not return the control to the user until the current logfile is archived. It issues a log switch and the user session hangs until the log is archived.

A number of DBAs also confuse with the following statement,

SQL> Alter System Archive Log all;

The above statement will not issue a log switch. It will archive all the non-archived log files. If you have no logfiles that are unarchived, then issuing the above statement returns the error message,

ORA-00271 there are no logs that need archiving.

Thanks
If you like the post vote for me by clicking the following button

Log File Switch Completion (Checkpoint Incomplete)

The cause for this wait event is same as mentioned here. When you see the log file switch completion wait event you will most likely see the checkpoint incomplete wait event. During the log switch a checkpoint occurs. The checkpoint signals the DBWr to write the dirty buffers to the data files.

The difference between log file switch completion and log file switch completion (Checkpoint Incomplete) wait event is, in the case of former wait event the users wait for the Log writer background process (LGWr) to complete its work (log switch). In the case of latter wait event the users wait for the Database Writer background process (DBWr) to complete its work (checkpoint).

Tuning option

Increase the size of redo log files. Increase the number of redo log groups.

Thanks
If you like the post vote for me by clicking the following button

log file switch completion

Whatever changes you make in the database, the changes are recorded in the online redo log files. These are important files for recovery in the event of a crash. There must be atleast two redo log groups in the database. The online redo log files are used in a circular fashion, i.e., when the current redo log file is full the changes are recorded in the next available online redo log file. When the last redo log file is full the changes are then recorded in the first redo log file overwriting the already available redo changes. If you have enabled the archiving then the online redo log file must have been archived before it is overwritten.


A log switch occurs when the current online redo log file is full. It enables the LGWr process to close the current redo log file and open the next available redo log file and start writing the changes in that file. A checkpoint occurs during the log switch which enables the DBWr to write the dirty buffers to be flushed to the data files.

When the redo log file size is small then the log file gets filled frequently causing log switches to occur more frequently. When a user session waits for log file switch completion wait event, it means the LGWr has not completed its work.

Tuning Option

Increase the size of the online redo log file. Check out the v$log_history view to see how often the log switch has taken place. Size your log files so that the log switch occurs every 30 minutes. For eg., if your current log file size is 50MB and log switch occurs every 5 minutes then increase the file size to 300MB.

Thanks