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

16 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
  9. I am continually amazed by the amount of information available on this subject. What you presented was well researched and well worded in order to get your stand on this across to all your readers. visit this site

    ReplyDelete
  10. I'm extremely inspired along with your writing skills as neatly as with the format in your blog. Is that a paid subject or did you customize it yourself? Anyway stay up the excellent quality writing, it's rare to look at a great blog like this one today.. Bathroom Mirror

    ReplyDelete
  11. This is my first time visit here. From the tons of comments on your articles, I guess I am not only one having all the enjoyment right here! keep it up thanks it's time to must know about it best family road trip vehicle for more details.

    ReplyDelete
  12. Simply desire to say your article is as amazing. The clarity in your post is simply excellent and i could assume you are an expert on this subject. Fine with your permission allow me to grab your feed to keep updated with forthcoming post. Thanks a million and please keep up the gratifying work. best PHP expert writer

    ReplyDelete
  13. Your content are truly amazing and beneficial facts right here It’s time to avail this Warehousing and Storage Services for more details.

    ReplyDelete