Checkpoint not complete
Checkpoint not complete messages in alert log file are generated due to the logs are switching rapidly fast that the checkpoint associated with the log switch isn’t complete
Note:
1. Oracle recommended rate of switching redo log inbetween 15 to 30 minutes.
2. Oracle’s LGWR process has filled up the other redo log groups and is now waiting for the checkpoint to complete.
3. Oracle stop working during the process of until checkpoint complete successfully.
Solution:
1. Increase the size of redo log
2. Change the parameter archive_lag_target to zero or 1800.
1. Increase the size of redo log
For size you need to check the log switch goint in minutes.
Example: Suppose you have 10 MB of redo log file size it switch every 1 minutes then you need at least 150 MB size (10 MB SIZE* 15 minutes) of redo log file to switch every 15 minutes.
-- Check size of redo log file
set line 200 pages 200
col member for a25
select g.group#,l.bytes/1024/1024 "Meg",g.member,l.status from v$log l,v$logfile g where l.group# = g.group#;
-- Check the count of switches in hour
set line 300
set pages 200
SELECT * FROM (
SELECT * FROM (
SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '999') "00:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '999') "01:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '999') "02:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '999') "03:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '999') "04:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '999') "05:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '999') "06:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '999') "07:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '999') "08:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '999') "09:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '999') "10:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '999') "11:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '999') "12:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '999') "13:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '999') "14:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '999') "15:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '999') "16:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '999') "17:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '999') "18:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '999') "19:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '999') "20:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '999') "21:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '999') "22:00"
, TO_NUMBER(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '999') "23:00"
FROM V$LOG_HISTORY
WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 8
-- Plan size accordingly
Check the hour base switches and plan according to get optimal size of redo according to switch occur every 15 to 30 minutes
2. SET ARCHIVE_LAG_TARGET
Oracle suggest for setting archive_lag_target to zero to reduce “checkpoint not complete” messages.
If archive is generated not in hour during the load on oracle is less then this parameter automatically switch in half an hour if you set it to 1800 value.
Note: Parameter is usefully to switch the archive log.
SQL> show parameter archive_lag_target;
NAME TYPE VALUE ------------------------ ----------- ------ archive_lag_target integer 900
--Set it zero
SQL> alter system set archive_lag_target=0 scope=both;
--OR
--set it enough value
SQL> alter system set archive_lag_target=1800 scope=both;
1800 is 30 minutes. Parameter value as second.
Do you by any chance know how to transpose/change that SQL so that the hours are on the left side, that is as rows and the dates are the columns?
For the SQL to
-- Check the count of switches in hour, how do I modify it so that the hours are displayed as rows and the date is displayed as columns.