Oracle | Thread 1 cannot allocate new log, Private strand flush not complete

Hello guys

Okay, I was silent for couple of months. I took a much needed break and back to work now. As few of you may already know, I am not a DBA (Certified), yet I have dealt with Oracle databases throughout my career & today was “another day” when I came across something new after restoring RMAN backup to a TEST environment.

Actually the entire “how to document” was provided by a APPS DBA friend (thanks to such geeks who are never bothered about someone else “learning the tricks” and challenging them! Geeks remain geeks) & without giving much attention to few elements, I “successfully” duplicated the 11g R2 database. Once after the database came online, I realized that the instance was pretty slow & immediately monitored the alert logs.

I started reading few entries like following:

Thread 1 cannot allocate new log, sequence 56
Private strand flush not complete
  Current log# 3 seq# 55 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo03a.log
  Current log# 3 seq# 55 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo03b.log
Beginning log switch checkpoint up to RBA [0x38.2.10], SCN: 5986177240123
Thread 1 advanced to log sequence 56 (LGWR switch)
  Current log# 4 seq# 56 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo04a.log
  Current log# 4 seq# 56 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo04b.log
Completed checkpoint up to RBA [0x38.2.10], SCN: 5986177240123
Thu Oct 04 12:14:14 2018
Beginning log switch checkpoint up to RBA [0x39.2.10], SCN: 5986177240998
Thread 1 advanced to log sequence 57 (LGWR switch)
  Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log
  Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log
Thread 1 cannot allocate new log, sequence 58
Private strand flush not complete
  Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log
  Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log
Thu Oct 04 12:14:25 2018
Beginning log switch checkpoint up to RBA [0x3a.2.10], SCN: 5986177241136
Thread 1 advanced to log sequence 58 (LGWR switch)
  Current log# 2 seq# 58 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo02a.log
  Current log# 2 seq# 58 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo02b.log
Thu Oct 04 12:14:47 2018
Thread 1 cannot allocate new log, sequence 59

I landed over a discussion at https://community.oracle.com/thread/364032?start=0&tstart=0 & few others, and at many places I read suggestions towards the redo log files getting filled too fast because of the smaller sizes allocated. I check the production instance, the redo log files were of size 1000M when the TEST instance log files were of size 100M!

So my next requirement was to resize the redo log files without damaging the database.

I came across an excellent post here https://uhesse.com/2010/01/20/how-to-change-the-size-of-online-redologs/ that explains how to create new redo log files and to drop the old ones without affecting the database (or users). The best part is, you don’t even have to take the database offline for any of the suggested activities.

So if you ever face such a situation, give it a try. You would be happy like me :)

regards,

rajesh

 

 

How to change the size of Online Redologs?

Uwe Hesse

change

Having just reviewed this article I can confirm that it is still valid also for 12c: The exact same commands can be used to change logfile sizes with that version.

If you deal with Oracle Databases for years on a daily basis like I do, you sometimes forget that things that seem elementary to you ain’t necessarily so for others, especially newbies in the Oracle realm. I noticed that when I saw some discussions in forums about redo logfiles and their sizes.

When I go back in time to 1998, that’s where I was the first time responsible for an Oracle Database (version 7, but some things never change). I inherited that Database from the previous DBA who did the setup, creating quite small log groups of a couple 100 KBs in size – which was not uncommon these days,  because Oracle templates mentioned that size as “medium”, if I…

View original post 813 more words