Thread 1 cannot allocate new log, sequence 80064 - Checkpoint not complete

[ganimede_dignan]

Hi,

from few days we have sometimes this message into alert. I've just try to increase the size of redo log file but without success, have you got any idea?

Thank you.

Wed Jul 30 00:08:05 2014

Beginning log switch checkpoint up to RBA [0x138bd.2.10], SCN: 4473445936

Thread 1 advanced to log sequence 80061 (LGWR switch)

  Current log# 1 seq# 80061 mem# 0: F:\ORACLE\PRD\ORIGLOGA\LOG_G11M1.DBF

  Current log# 1 seq# 80061 mem# 1: F:\ORACLE\PRD\MIRRLOGA\LOG_G11M2.DBF

Beginning log switch checkpoint up to RBA [0x138be.2.10], SCN: 4473445939

Thread 1 advanced to log sequence 80062 (LGWR switch)

  Current log# 2 seq# 80062 mem# 0: F:\ORACLE\PRD\ORIGLOGB\LOG_G12M1.DBF

  Current log# 2 seq# 80062 mem# 1: F:\ORACLE\PRD\MIRRLOGB\LOG_G12M2.DBF

Beginning log switch checkpoint up to RBA [0x138bf.2.10], SCN: 4473445942

Thread 1 advanced to log sequence 80063 (LGWR switch)

  Current log# 3 seq# 80063 mem# 0: F:\ORACLE\PRD\ORIGLOGA\LOG_G13M1.DBF

  Current log# 3 seq# 80063 mem# 1: F:\ORACLE\PRD\MIRRLOGA\LOG_G13M2.DBF

Wed Jul 30 00:08:06 2014

Archived Log entry 80064 added for thread 1 sequence 80061 ID 0x70061a1e dest 1:

Thread 1 cannot allocate new log, sequence 80064

Checkpoint not complete

Wed Jul 30 00:08:06 2014

Archived Log entry 80065 added for thread 1 sequence 80062 ID 0x70061a1e dest 1:

  Current log# 3 seq# 80063 mem# 0: F:\ORACLE\PRD\ORIGLOGA\LOG_G13M1.DBF

  Current log# 3 seq# 80063 mem# 1: F:\ORACLE\PRD\MIRRLOGA\LOG_G13M2.DBF

Wed Jul 30 00:08:08 2014

Archived Log entry 80066 added for thread 1 sequence 80060 ID 0x70061a1e dest 1:

Wed Jul 30 00:08:09 2014

Completed checkpoint up to RBA [0x138bd.2.10], SCN: 4473445936

Beginning log switch checkpoint up to RBA [0x138c0.2.10], SCN: 4473445946

Thread 1 advanced to log sequence 80064 (LGWR switch)

  Current log# 4 seq# 80064 mem# 0: F:\ORACLE\PRD\ORIGLOGB\LOG_G14M1.DBF

Completed checkpoint up to RBA [0x138c0.2.10], SCN: 4473445946

  Current log# 4 seq# 80064 mem# 1: F:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF

Archived Log entry 80067 added for thread 1 sequence 80063 ID 0x70061a1e dest 1:

Completed checkpoint up to RBA [0x138bf.2.10], SCN: 4473445942

Completed checkpoint up to RBA [0x138be.2.10], SCN: 4473445939

[ganimede_dignan]

It's an Oracle 11.2.0.3.0 on Windows 2008 R2

SQL> show parameter _SELFTUNE_CHECKPOINTING_LAG

SQL> show parameter _DISABLE_SELFTUNE_CHECKPOINTING

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_disable_selftune_checkpointing      boolean     TRUE

SQL> show parameter archive_lag_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target                   integer     0

SQL>

[ganimede_dignan]

========================================================

GENERIC OPATCH VERSION - FOR USE IN SAP ENVIRONMENT ONLY

========================================================

Oracle Installer di patch provvisorie versione 11.2.0.3.1

Copyright (c) 2012, Oracle Corporation. Tutti i diritti riservati.

Oracle Home       : I:\oracle\PRD\11203

Central Inventory : C:\Program Files\Oracle\Inventory

   from           : n/a

OPatch version    : 11.2.0.3.1

OUI version       : 11.2.0.3.0

Log file location : I:\oracle\PRD\11203\cfgtoollogs\opatch\opatch2014-07-30_00-4

7-57AM_1.log

Lsinventory Output file location : I:\oracle\PRD\11203\cfgtoollogs\opatch\lsinv\

lsinventory2014-07-30_00-47-57AM.txt

--------------------------------------------------------------------------------

Prodotti di livello superiore installati (1):

Oracle Database 11g                                                  11.2.0.3.0

Ci sono 1 prodotti installati in questa Oracle home.

Patch provvisorie (6) :

Patch  16167942     : applied on Sat May 18 11:50:42 CEST 2013

Unique Patch ID:  15646955

Patch description:  "ORACLE 11G 11.2.0.3 PATCH 15 BUG FOR WINDOWS (64-BIT AMD64

AND INTEL EM64)"

   Created on 01 Feb 2013, 00:06:51 hrs PST8PDT

   Bugs fixed:

     16167942, 15841373, 14252187, 13877007, 14249514, 13891981, 12637294

     14511876, 14309075, 13736413, 12862170, 13901201, 12819999, 16019955

     15996520, 14775679, 13902963, 15924589, 14525739, 14744263, 12838063

     15910002, 14751895, 14107475, 13262857, 14641969, 15938047, 15982436

     13924173, 14761411, 16042648, 13337530, 14810890, 13605675, 7335665

     12686027, 16011871, 13729351, 13923995, 12813641, 12985237, 14600776

     14565062, 13066936, 14510516, 14192781, 14164191, 14505752, 15834269

     13559463, 14702500, 12345305, 14467027, 14807785, 14590564, 13849733

     13916549, 14755945, 15869211, 13365021, 13972394, 14193629, 14808639

     14189694, 13848402, 14226599, 13621258, 13827380, 14762511, 14064573

     13777449, 15848120, 14546673, 14546638, 14546575, 14469008, 14263073

     14263036, 14258925, 14205448, 14023636, 14035825, 13685544, 14040433

     13593999, 13026410, 12964067, 12873183, 12857027, 12345082, 14589750

     14668670, 13791678, 13769577, 13658087, 14230270, 13890080, 13810393

     13551402, 14644185, 13686047, 12583611, 14488943, 14176879, 13727644

     13517951, 13361350, 14373728, 13602883, 13580513, 10219624, 14645769

     14007968, 14827344, 9153183, 15853081, 14138130, 14168099, 14810093

     13845120, 14277586, 14271305, 13879428, 13938166, 12558569, 10260842

     14569263, 14100232, 13912373, 13653178, 14242977, 14102704, 14001941

     13993634, 13634583, 14828076, 13645875, 14398795, 13879553, 12940620

     14663556, 12327035, 14207902, 12557401, 14653598, 14587310, 14224606

     14577426, 14386985, 13783957, 12656350, 11836374, 14485163, 14624988

     14582487, 14332938, 14166276, 14479390, 14372670, 14038163, 13535104

     13744724, 14245087, 14305767, 14731193, 14313519, 14500723, 12928700

     12571916, 14245825, 13642044, 9706792, 13693929, 13903046, 14581922

     14142884, 14393900, 9778542, 13115786, 13427062, 14164849, 13399711

     11686700, 14175146, 14499293, 13615767, 14625969, 14513934, 14320472

     14613223, 13040943, 12812148, 14360965, 13911821, 14140535, 14204172

     13949042, 14367567, 9949330, 14467202, 14323857, 13834065, 13632653

     14110275, 13549808, 14196818, 13819727, 9751365, 10029609, 11846912

     12646510, 12766337, 12826115, 12860701, 12865360, 12973529, 13031176

     13040909, 13061085, 13066239, 13071375, 13111783, 13114306, 13345188

     13376866, 13393115, 13406688, 13408107, 13433178, 13459147, 13531801

     13553502, 13603792, 13608532, 13622539, 13622968, 13738130, 13961338

     14054189, 13851432, 14063422, 13973307, 14119388, 14282903, 14517263

     13716797, 14465787, 14613900, 14304758, 14251904, 14214257, 14153867

     14009845, 13889047, 13888719, 13820621, 13697828, 12663376, 11675721

     12889329, 13410987, 13965075, 13869978, 13843080, 13825231, 13582411

     13550689, 13023609, 13000491, 10418841, 10317921, 13523527, 13498267

     14096743, 14010695, 13947200, 14259185, 14280458, 14276345, 14543814

     13804294, 13718279, 13612575, 13430938, 13384182, 13377816, 13250244

     12794305, 12693626, 12352406, 12879628, 13534412, 13524698, 13743987

     14207317, 13918644, 14198511, 14088346, 12791981, 13011409, 13038684

     12865902, 12768840, 14076523, 13632809, 14458246, 13359289, 12794025

     9952132, 13780816, 12950694, 14162791, 13093358, 14255600, 13936424

     14301592, 14050233, 13528725, 14500052, 10146616, 14409183, 14324057

     14313389, 5918695, 12430142, 13399435, 13913630, 13732226, 10084752

     13591624, 14237793, 14013094, 10229475, 13078786, 14052474, 13637058

     12646746, 13000553, 14254795, 14215010, 13922729, 13942694, 12898637

     14309390, 13958038, 10214323, 13710236, 14115858, 14240544, 14158332

     13073613, 11074304, 13868705, 12723295, 13853654, 14074606, 13784384

     14386810, 14223718, 14018752, 13258936, 13723052, 12898020, 10625145

     14052871, 13076238, 13992002, 13242070, 12955701, 12693573, 14026888

     13984965, 13366199, 13843286, 13616375, 12839247, 8547978, 13714926

     13023854, 13910420, 11837095, 12842909, 12959852, 13632140, 13780035

     13860201, 13711083, 13628002, 13397104, 12358083, 13743357, 13945708

     10384051, 13591484, 13051250, 13727853, 12959627, 14095820, 5144934

     13657605, 13582702, 12966665, 13463131, 13773133, 12918738, 13099577

     13690409, 13645917, 11708510, 14042380, 13627489, 13652437, 12733042

     13907462, 13608299, 13624984, 9095696, 12794090, 9706532, 13469158

     13947480, 13555112, 13399236, 12910565, 13790109, 12552578, 13896848

     13737746, 13080778, 13458016, 13873885, 12312133, 12731940, 12672969

     13355095, 13981051, 13467683, 13457582, 13370330, 13340388, 12976376

     12797420, 12658411, 10133521, 9761357, 13827394, 13729832, 13851978

     13559770, 13592937, 13592919, 13478856, 13559540, 13641696, 13729808

     13632530, 14100237, 11071989, 13786142, 13839641, 13041324, 13857364

     13853089, 13806545, 13726162, 13657366, 13617861, 13604057, 13483672

     12959140, 12914722, 12910033, 12771830, 13776758, 13339443, 12680491

     10114953, 12966774, 13965211, 12424121, 13478060, 13786778, 13579992

     13705338, 13603613, 13476583, 12745662, 9190186, 13530646, 13520452

     9019231, 13610777, 12905053, 10263668, 13550185, 13385346, 13448206

     12772404, 13911711, 11072246, 13104881, 13863326, 12889054, 13775960

     13777823, 13464002, 11824898, 13535622, 13916709, 12880299, 12918338

     12529945, 13811209, 14058362, 13724808, 13885389, 13649031, 13350245

     13553883, 13388104, 13635347, 13404129, 13031118, 12530140, 13023541

     13767921, 13787482, 13641076, 13807411, 13724992, 12965899, 13791443

     12879027, 13584130, 13495307, 12594032, 13709220, 13247965, 13243072

     13683125, 13652493, 12957127, 10215977, 13014128, 13040331, 12857222

     12977501, 12664456, 12405931, 13525554, 13492863, 13573521, 13873471

     13366268, 13352423, 13460353, 12709476, 13652088, 13886023, 13942723

     12919564, 12894807, 12829021, 12612118, 11063191, 13503598, 13482688

     13354082, 13484963, 13395403, 13542159, 12983611, 13718476, 12846562

     9659614, 13326736, 11846902, 11665727, 13257247, 13588248, 13544396

     13566938, 13615517, 13425727, 13037709, 12730342, 12349553, 13502441

     13258062, 13251796, 13247273, 12659561, 12639013, 12594616, 11772838

     13570057, 12403721, 12585543, 12784549, 12834800, 12975771, 13040171

     13058950, 13063120, 13077335, 13365700, 13382280, 13384397, 13440516

     13454210, 13457537, 13477790, 13496250, 13501787, 13505390, 13506110

     13524899, 13572659, 13594712, 13617627, 13326289, 13555974, 13035360

     13420224, 13419660, 13036331, 13332439, 13420174, 12583826, 13358781

     11883969, 13524237, 12867713, 13328193, 13516727, 11865420, 13502183

     13259364, 13371153, 12998795, 13413168, 12897902, 13044108, 12971242

     12834027, 12620823, 13420516, 12849377, 12975811, 12646784, 12829429

     12538907, 12950823, 12848480, 12823838, 13366202, 13002015, 12834777

     13001901, 13017428, 13082238, 12965049, 11840910, 12656535, 12617123

     13073340, 12925041, 12938841, 13023632, 13066371, 12942119, 11877623

     12810890, 12995950, 12765467, 12934171, 13070939, 12535346, 12985184

     13103913, 12765868, 12622441, 12876314, 12820045, 13038806, 13090686

     12923168, 13362079, 12591252, 12718090, 12873909, 10350832, 12795861

     13039908, 12976590, 13017584, 12627504, 13263435, 12847466, 12797765

     12758736, 12878750, 12861463, 13024624, 12764337, 12662040, 13068077

     12914824, 13074261, 12668341, 12597906, 12932852, 13045518, 12897651

     12960925, 12728585, 13019958, 12902661, 12886827, 12913474, 3522216

     12678920, 12885323, 13334158, 12947871, 12896850, 13001955, 12784559

     12827166, 9703627, 12772345, 12905058, 13345868, 10357727, 12827493

     13004894, 12780983, 12842804, 13146719, 12655301, 12960302, 13085732

     12979199, 12638117, 13357509, 12401111, 12857064, 8631856, 13035804

     13355963, 12845115, 12695029, 12990582, 12971775, 12867511, 12917230

     12582664, 12849688, 12950644, 12588744, 13011520, 12899169, 12823042

Patch  14658090     : applied on Sat May 18 11:40:34 CEST 2013

Unique Patch ID:  15859799.1

   Created on 27 Jan 2013, 08:31:45 hrs PST8PDT

   Bugs fixed:

     14658090

Patch  13575265     : applied on Sat May 18 11:40:14 CEST 2013

Unique Patch ID:  14606745

   Created on 27 Feb 2012, 02:10:47 hrs PST8PDT

   Bugs fixed:

     13575265

Patch  13508485     : applied on Sat May 18 11:39:34 CEST 2013

Unique Patch ID:  14395138

   Created on 21 Dec 2011, 13:23:38 hrs PST8PDT

   Bugs fixed:

     13508485

Patch  12325243     : applied on Sat May 18 11:38:51 CEST 2013

Unique Patch ID:  14243031

   Created on 7 Nov 2011, 11:57:40 hrs PST8PDT

   Bugs fixed:

     12325243

Patch  9584028      : applied on Sat May 18 11:38:31 CEST 2013

   Created on 22 Jun 2012, 11:39:40 hrs CEST

   Bugs fixed:

     9584028

--------------------------------------------------------------------------------

OPatch succeeded.

[ACE-SAP]

Hi

Extending the redo size is one solution, but not always the best one.

How often does that problem occurs ?

Did you set parameters LOG_CHECKPOINT_TIMEOUT, LOG_CHECKPOINT_INTERVAL or FAST_START_MTTR_TARGET  ?

What was the original/new redo size, what is the redo log switch frequency on your system?

Why/when did you set parameter _disable_selftune_checkpointing, it was mainly used in 10g, did not have to set it on 11g.

Regards

79341 - Checkpoint not complete

=> If "checkpoint not complete" occurs sporadically especially during times of high system load, you can increase redo log space or the number of DBWR processes.

Increasing the size of the redo log files is particularly useful if there are small time frames between the log switches (< 1 minute during peak load). The disadvantage of big redo log files is the lower checkpoint frequency and the longer time Oracle needs for an instance recovery.

=> Increasing the number of redo log groups is particularly useful if the log switch interval is already > 1 minute even in peak times.