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. 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
  8. 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
  9. Your content are truly amazing and beneficial facts right here It’s time to avail this Warehousing and Storage Services for more details.

    ReplyDelete