11 Replies Latest reply: Jul 28, 2011 5:52 AM by malhi RSS

    Checkpoint not complete ............

    malhi
      We are using 11gr1 our OS is OE Linux. I am getting following in alert.log. Due to these error our streaming is suffering. Can any brother help me to understand and resolve this issue.
      Thread 1 cannot allocate new log, sequence 19174
      Checkpoint not complete
        Current log# 6 seq# 19173 mem# 0: /u01/app/oracle/oradata/scba/redo6.log
      2011-07-28 10:11:51.698000 +05:00
      Thread 1 advanced to log sequence 19174
        Current log# 2 seq# 19174 mem# 0: /u01/app/oracle/oradata/scba/redo02.log
      RFS[7]: Archived Log: '/u01/app/oracle/arch2/scba/1_35237_720894410.dbf'
      RFS LogMiner: Registered logfile [/u01/app/oracle/arch2/scba/1_35237_720894410.dbf] to LogMiner session id [286]
      2011-07-28 10:12:04.093000 +05:00
        • 1. Re: Checkpoint not complete ............
          asifkabirdba
          If this message comes frequently then your redo log file size is smaller. Check the redo log file size and count the log file switch per hour. If the log file switch count is more that 10 the increase the redo log file size. Increase the file size that redo log file switch within 5-8.


          Regards
          Asif Kabir
          • 2. Re: Checkpoint not complete ............
            Helios-GunesEROL
            Hi;

            Please see:

            Checkpoint Not Complete In Alert.log Due To Setting Of Archive_lag_target [ID 435780.1]
            Checkpoint Tuning and Troubleshooting Guide [ID 147468.1]

            Regard
            Helios
            • 3. Re: Checkpoint not complete ............
              Rodriguez
              I think size of redo.log files are small.And tou have to increase size redo files or increase the frequency of checkpoint occurence .And post please log_checkpoint_interval and log_checkpoint_timeout parameters value
              • 4. Re: Checkpoint not complete ............
                malhi
                We have 8 redo file, size of each is 25 MG. How can i count the log file switch per hour.
                • 5. Re: Checkpoint not complete ............
                  asifkabirdba
                  25 MB is too small. Using the script find the log switch frequency hour.


                  -- show redo log frequency map


                  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;



                  After finding the log switch frequency change the file size that log switch frequency will 5-8 per hour.


                  Regards
                  Asif Kabir
                  • 6. Re: Checkpoint not complete ............
                    Helios-GunesEROL
                    Hi;

                    Try below query;

                    select substr(time,1,5) day,

                    to_char(sum(decode(substr(time,10,2),'00',1,0)),'99') "00",

                    to_char(sum(decode(substr(time,10,2),'01',1,0)),'99') "01",

                    to_char(sum(decode(substr(time,10,2),'02',1,0)),'99') "02",

                    to_char(sum(decode(substr(time,10,2),'03',1,0)),'99') "03",

                    to_char(sum(decode(substr(time,10,2),'04',1,0)),'99') "04",

                    to_char(sum(decode(substr(time,10,2),'05',1,0)),'99') "05",

                    to_char(sum(decode(substr(time,10,2),'06',1,0)),'99') "06",

                    to_char(sum(decode(substr(time,10,2),'07',1,0)),'99') "07",

                    to_char(sum(decode(substr(time,10,2),'08',1,0)),'99') "08",

                    to_char(sum(decode(substr(time,10,2),'09',1,0)),'99') "09",

                    to_char(sum(decode(substr(time,10,2),'10',1,0)),'99') "10",

                    to_char(sum(decode(substr(time,10,2),'11',1,0)),'99') "11",

                    to_char(sum(decode(substr(time,10,2),'12',1,0)),'99') "12",

                    to_char(sum(decode(substr(time,10,2),'13',1,0)),'99') "13",

                    to_char(sum(decode(substr(time,10,2),'14',1,0)),'99') "14",

                    to_char(sum(decode(substr(time,10,2),'15',1,0)),'99') "15",

                    to_char(sum(decode(substr(time,10,2),'16',1,0)),'99') "16",

                    to_char(sum(decode(substr(time,10,2),'17',1,0)),'99') "17",

                    to_char(sum(decode(substr(time,10,2),'18',1,0)),'99') "18",

                    to_char(sum(decode(substr(time,10,2),'19',1,0)),'99') "19",

                    to_char(sum(decode(substr(time,10,2),'20',1,0)),'99') "20",

                    to_char(sum(decode(substr(time,10,2),'21',1,0)),'99') "21",

                    to_char(sum(decode(substr(time,10,2),'22',1,0)),'99') "22",

                    to_char(sum(decode(substr(time,10,2),'23',1,0)),'99') "23"

                    from v$log_history

                    group by substr(time,1,5)

                    ;



                    Regard
                    Helios
                    • 7. Re: Checkpoint not complete ............
                      malhi
                      Thank for your this query.
                      Following is the query result, now what should be the size of redo.
                      28/07     0     0     0     0     0     0     0     2     367     404     691     391     44     0     0     0     0     0     0     0     0     0     0     0
                      27/07     0     0     0     0     0     0     0     1     194     186     392     66     57     1     20     0     15     1     1     0     0     0     0     0
                      26/07     0     0     0     0     0     0     0     0     149     181     299     196     66     10     1     0     1     0     1     0     0     0     0     0
                      25/07     0     0     0     0     0     0     0     40     229     170     392     54     185     34     1     6     4     41     340     176     67     0     0     0
                      23/07     0     0     0     0     0     0     0     1     226     170     412     63     21     1     0     1     1     0     0     0     0     0     0     0
                      22/07     0     0     0     0     0     0     0     2     169     176     316     164     81     0     0     2     0     1     0     0     0     0     0     0
                      21/07     0     0     0     0     0     0     0     2     225     172     393     54     57     1     0     1     0     0     1     0     0     2     0     1
                      Edited by: malhi on Jul 28, 2011 12:43 AM
                      • 8. Re: Checkpoint not complete ............
                        asifkabirdba
                        Increase the redo log file size to 2 GB and check if the message comes frequently then increase accordingly.


                        Regards
                        Asif Kabir

                        -- mark your helpful post as correct/helpful and close the threads.

                        Edited by: asifkabirdba on Jul 28, 2011 1:58 PM
                        • 9. Re: Checkpoint not complete ............
                          malhi
                          You mean size of each redo file equal to 2GB.
                          • 10. Re: Checkpoint not complete ............
                            Fran
                            You mean size of each redo file equal to 2GB.
                            Yes, each redo log = 2GB and check tomorrow how many redolog was written each hour.
                            Like they told you they must be between 5-8 each hour.
                            • 11. Re: Checkpoint not complete ............
                              malhi
                              Please have a look on the result of this query.......
                              SELECT target_mttr, estimated_mttr, optimal_logfile_size
                                FROM v$instance_recovery;
                              
                              TARGET_MTTR,ESTIMATED_MTTR,OPTIMAL_LOGFILE_SIZE
                              30                   16           145
                              It is suggesting 145 MB.