Monday, July 22, 2013

Thread 1 cannot allocate new log & Checkpoint not complete

Users will see these messages when Oracle wants to reuse the redolog file, but checkpoint position is still in the log, Oracle must wait until the checkpoint completes.

Cause: In this situation either DBWR writes slowly or log switch happens before the log is completely full or log file is small.

Mon Apr 15 07:20:42 2013
Thread 1 advanced to log sequence 5021
Current log# 1 seq# 5021 mem# 0: /oracle/ORCL/redoA/redo01.log
Mon Apr 15 07:21:15 2013
Thread 1 cannot allocate new log, sequence 5022
Checkpoint not complete


If you have many updates in the system, you might need more redo groups. If you have fewer redo groups then adding more redo groups will help.

Use below syntax to add more redo groups

ALTER DATABASE ADD LOGFILE GROUP <Group No> ('<Redo log member 1 path’ ,'<redo log member 2 path>') size 500M;

If you have smaller redo log and if you see many log switches then increasing the redo size might help.

Step1: Switch logfile to make group 1 ‘INACTIVE’

SQL> Alter system switch logfile;
SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 CURRENT

Step2:- Drop the log group1 which is ‘INACTIVE’ and recreate with bigger size.

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1 ('/db01/ORCL/redoA/log1_m1.dbf',' /db01/ORCL/redoB/log1_m2.dbf') size 100M reuse;

Repeat step 1 and 2 until you drop and recreate all redo logs with bigger size.

It is a recommended to have 4-5 log switches per hour. You can use below Script to find the log switches on hourly basis.

set lines 120;
set pages 999;
SELECT to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
GROUP by to_char(first_time,'YYYY-MON-DD');

Click here to learn about "Private Strand Flush Not Complete"  message in alert.log

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

10 comments:

  1. Hello Satish,

    Will this scenario is a reason for primary and standby not in sync??

    ReplyDelete
  2. Contact us for Website Design or E-commerce website......


    websites design services

    ReplyDelete
  3. The software includes a wider range of queries that are currently available. The log file analysis software would perform all of the current queries. seo log analyzer

    ReplyDelete
  4. It is the kind of information I have been trying to find. Thank you for writing this information. It has proved utmost beneficial for me. traffic secrets

    ReplyDelete
  5. Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post. custom made patches

    ReplyDelete
  6. Thank you for a absolutely interesting blog. What else may additionally I profit that handy of data written in this kind of unadulterated right of get right of entry to? Ive a engagement that i am profitably now humming in the back of inspiration to, and i’ve been at the see out for such data. website

    ReplyDelete
  7. Your website is really cool and this is a great inspiring article. Thank you so much. website

    ReplyDelete
  8. Thank you for all your valuable hard work on this website. Betty takes pleasure in engaging in research and it’s really obvious why. I learn all relating to the lively way you render rewarding items on this blog and even improve contribution from the others on this concept so my princess is without a doubt starting to learn a lot of things. Take pleasure in the rest of the year. You have been doing a dazzling job. free Oracle courses

    ReplyDelete