Checkpoint not complete in Oracle

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

Checkpoint is occurred for synchronizes the modified data blocks in memory with the data files on disk. It used for maintained the consistency in the Data & faster database recovery process.

A checkpoint events occurred in following sanrious:
– At each switch of the redo log files.
– When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
– When the size in bytes corresponding to :
(LOG_CHECKPOINT_INTERVAL* size of IO OS blocks)
is written on the current redo log file.
– Directly by the ALTER SYSTEM SWITCH LOGFILE command.
– Directly with the ALTER SYSTEM CHECKPOINT command.

Error in Alert Log:
Warning show in the alert log file when checkpoint is not completed in Oracle Database means that oracle want to reuse a redo log files, but the current checkpoint position is still active or inuse. So it wait for it.

Solution

1) Increase the redo log file size.
Oracle recommends the log switch occurred every 20 min and having 2 log members is good.
Note: you can analyzed the data from alert log how frequently your log switch occurred in your database.
Click for Increase the size of Redo files

2) Add more groups in Redo log
Error: Thread cannot allocate new log, sequence
Adding More groups of Redo log may help in delay the waiting time.

3) Add more DBWR write process.

4) Following Parameters has great role:
FAST_START_MTTR_TARGET: FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance.
Views:
V$INSTANCE_RECOVERY.ESTIMATED_MTTR shows the current estimated mean time to recover (MTTR) in seconds.
V$INSTANCE_RECOVERY.TARGET_MTTR shows the effective MTTR target in seconds enforced by the system.
V$MTTR_TARGET_ADVICE shows the number of I/Os resulted by the current workload under the current MTTR setting and the estimated number of I/Os that would be resulted by the current workload under other MTTR settings.

LOG_CHECKPOINT_INTERVAL: Parameter specifies the maximum number of redo blocks the incremental checkpoint target should lag the current log tail.
Carefully set this parameter. It has great roll in it. If we set it large then long time is expected to recover the database in case of system crash. If we set it low then more checkpoint and may cause performance issues.
Example of setting value:
Value can be defined as : operating system block size is 512 bytes.
We set the parameter : LOG_CHECKPOINT_INTERVAL = 10,000
Then as defined incremental checkpoint target should not lag the current log tail by more than 5,120,000 (5M) bytes. If the size of your redo log is 20M then you have 4 checkpoint in each log.
Note: If FAST_START_MTTR_TARGET is used , set its value to 0 or not use it.

LOG_CHECKPOINT_TIMEOUT parameter specifies the maximum number of seconds
the incremental checkpoint target should lag the current log tail. It means how long a dirty buffer in buffer cache can remain dirty.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.