log file switch (checkpoint incomplete)

Applies to:

Oracle Server - Enterprise Edition - Version: to
Information in this document applies to any platform.

Error messages in alert log:
Thread cannot allocate new log, sequence
Checkpoint not complete


ORACLE Instance - Can not allocate log, archival required
Thread cannot allocate new log, sequence

From the AWR Report we can see what issue is more frequent:

a) DBWR has not finished checkpointing (log file switch (checkpoint incomplete) )

b) ARCH has not finished copying the redo log file to the archive destination (Archival required)

c) Wait event in session is log file switch (checkpoint incomplete).
These error messages are addressed in a similar way. For any other issue, DBA has to Analyze AWR Report.


 Add REDO log files to groups or enlarge the existing REDO log files.

A good way to determine the optimal size of redo log files is to look into the alert.log file for messages about how frequently the log sequence# are changing, log check pointing and any incomplete redo log switch completion messages. When you examine your alert.log for log switches, you should do it when there are heavier loads in the database.Every time Oracle does a log switch, it is recorded in the alert.log.

Redo log switching should occur approximately every 20-30 minutes to get better database performance. If they switch too frequently, you will need to check the current size of redo log files from V$LOG and based on how frequently it is switching on an average, you will need to create redo log groups with bigger size and then switch the redo log a few times with "ALTER SYSTEM SWITCH LOGFILE;" so that the CURRENT redo log is one of the bigger redo log file and then drop the redo log groups with smaller size with ALTER DATABASE DROP LOGFILE ... command. You should start with a minimum of 100 MB redo log files for any production database.

Steps to add Redo Log files: http://www.dbas-oracle.com/2011/08/how-to-resize-andor-add-redo-logs.html

No comments:

Post a Comment