On a dev server I have had over the past few days very frequent log switches (as many as 200 per hour), plus "thread 1 cannot allocate new log" and "Checkpoint not complete" errors every few minutes. I had 3 50MB log files. Yesterday I upped to 3 256MB files, and the problem persists. I added one more 256MB, no luck. Log switches of course have dropped to 15-20 per hour which is still too high but not that extreme. "Checkpoint not complete" errors have almost disappeared but "thread 1 cannot allocate new log" errors persist. The system has 8 CPU threads (quad-core i7) and one DB writer as given by the cores/8 rule. Should I add one more DB writer? As this is a dev server it has a SATA drive which of course cannot support high throughput. Thanks for any advice!

  • You said it yourself - "SATA drive which of course cannot support high throughput. ". Redo activity is directly functional to the amount of DML activity experienced by the database. I'd say tat even 256MB redo logs are too small. And how many redo groups do you have? – EdStevens Mar 14 '19 at 21:16
  • I have 4 groups. Do I need more groups or larger files? – GI D Mar 15 '19 at 20:11
  • I'd go with larger files. Even 256mb is very, very small. Just by way of comparison, the smallest usb thumb drive I see at a major office supply is 8 GB and costs < $10 US. That's big enough for 32 of your 256mb redo logs. Disk is cheap. Don't be stingy. --edit-- there are counter arguments of course. Did you try googling "cannot allocate new log"? Then there is the question of 'what problem are you dealing with'? The existence of these messages, in itself, is just an observation, not a problem. – EdStevens Mar 15 '19 at 20:22
  • The cost of USB drives and drives generally has very little to do with Oracle config parameters. Yes, of course I googled the issue, and then came here hoping for a more educated response. Thanks for your answer. – GI D Mar 18 '19 at 7:52
  • My reference to USB drives and cost was meant to merely point out how incredibly cheap disk storage is today, and thus it is "penny wise and pound foolish" to keep your redo logs so very, very small. – EdStevens Mar 18 '19 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.