Re: Cannot allocate new log - checkpoint not complete

Rich,

If you're not using Oracle to mirror the redo logs, the minimum number of disks for this configuration is 2.

Personally, I like 4 disks. Mirror via Oracle each redo group to an alternate pair of mirrors. Redundancy and performance.

Jared

On Monday 07 April 2003 11:49, Rich Holland wrote:
> I've seen I/O problems in the past with this configuration. The main
> reason is that with all redo logs on the same physical device, the
> database is frantically writing redo log information, and when a
> checkpoint occurs, it switches to the next redo log file, and then the
> archiver tries to copy the previous one off the disk, so you end up
> thrashing with the log writer and archiver competing for I/O on the same
> spindle.
>
> The minimal configuration I've seen used required 3 separate disks and a
> "round robin" where at any given time, only two were in use (redo log
> writing to one, archiver reading from the previous one).
>
> Rich
>
> > -----Original Message-----
> > From: root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf Of
> > DENNIS WILLIAMS
> > Sent: Friday, April 04, 2003 10:44 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Cannot allocate new log - checkpoint not complete
> >
> >
> > Fermin
> > I would definitely go for your second configuration. I
> > don't think there is a problem with several redo logs on the
> > same device, since Oracle is only writing to one log at a
> > time. Actually, if I could devote two devices to redo logs, I
> > would mirror the redo logs. You can easily recover from the
> > loss of a data file, but not easily from the loss of an
> > online redo log. And while we're on the subject, mirror your
> > control files as well.
> > The problem I've experienced with placing redo logs on the
> > same device is data files is that when your database
> > experiences a really large number of transactions, Oracle is
> > trying desperately to write changed data blocks to disk at
> > the same time it is trying to write redo data. At some point,
> > Oracle can't service new transactions until something
> > completes. From the outside you experience this as a
> > "freeze". In this situation I find it very handy to submit a
> > STATSPACK snapshot, or run a query that will reveal the
> > system wait events.
> > You've received a lot of excellent advice on this subject
> > from some really excellent people (and so-so advice from me).
> > I would highly recommend that you print all the replies and
> > study them to understand what these people are really saying.
> >
> > Dennis Williams
> > DBA, 40%OCP, 100% DBA
> > Lifetouch, Inc.
> > dwilliams_at_lifetouch.com
> >
> >
> > -----Original Message-----
> > Sent: Friday, April 04, 2003 4:14 AM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > So do you think the following distribution will
> > contribute to a better performance:
> >
> > data datafiles - device a
> > index datafiles - device b
> > redolog1 - device c
> > redolog2 - device d
> > redolog3 - device c
> >
> > instead of:
> >
> > data datafiles - device a
> > index datafiles - device b
> > redolog1 - device c
> > redolog2 - device d
> > redolog3 - device a
> >
> > Because I only have 5 devices available.
> >
> >
> >
> > -----Mensaje original-----
> > De: root_at_fatcity.com [mailto:root_at_fatcity.com]En nombre de
> > Connor McDonald Enviado el: viernes, 04 de abril de 2003 11:49
> > Para: Multiple recipients of list ORACLE-L
> > Asunto: RE: Cannot allocate new log - checkpoint not complete
> >
> >
> > Its not really a particular redo log that is the
> > issue. You've used up redo's (say) 1, 2, 3 and you
> > want to cycle around to 1 but the checkpoint that
> > would free up redo 1 is not yet finished.
> >
> > Thus its not a single redo log that is the problem -
> > the IO rate of the checkpoint is not sufficient quick
> > to avoid the redo cycling around...If one of your
> > redo's is on common datafile disk, this could
> > contribute to this
> >
> > hth
> > connor
> >
> > --- Fermin Bernaus Berraondo <fbernaus_at_sammic.com>
> > wrote: >
> >
> > > Dennis,
> > >
> > > This is our actual distribution:
> > >
> > > Datafiles belonging to data in a separate disk,
> > > name it /baandata
> > > Datafiles belonging to index in a separate disk,
> > > name it /baanindex
> > >
> > > And 3 redolog files, two of them in another two
> > > separate disks, and the third one located in the
> > > same device as the data files (/baandata).
> > >
> > > All of them are mirrored disks.
> > >
> > > Your comment makes sense, but if keeping datafiles
> > > and one of the redolog files in the same device
> > > should affect performance, then I wonder why the
> > > "cannot allocate new log, checkpoint not complete"
> > > message is affecting to the 3 redolog files and not
> > > only to the one located in that datafile device.
> > >
> > > I did not think on this. Anyway I have no more
> > > disks in which I can split the redologs...
> > >
> > > I can not wait for your comments!
> > >
> > > Regards,
> > >
> > > Fermin.
> > >
> > > -----Mensaje original-----
> > > De: root_at_fatcity.com [mailto:root_at_fatcity.com]En
> > > nombre de DENNIS
> > > WILLIAMS
> > > Enviado el: jueves, 03 de abril de 2003 17:04
> > > Para: Multiple recipients of list ORACLE-L
> > > Asunto: RE: Cannot allocate new log - checkpoint not
> > > complete
> > >
> > >
> > > Fermin - Connor's reply sparked an idea. By any
> > > chance do you have your redo
> > > logs on the same device as your data files?
> > >
> > > Dennis Williams
> > > DBA, 40%OCP, 100% DBA
> > > Lifetouch, Inc.
> > > dwilliams_at_lifetouch.com
> > >
> > >
> > > -----Original Message-----
> > > Sent: Thursday, April 03, 2003 5:04 AM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Basically as the message suggests the redo cannot be
> > > recycled until the checkpoint has completed flushing
> > > out the cache.
> > >
> > > A *workaround* is to add redo log (size or number)
> > > but
> > > its really a heads-up about your I/O subsystem not
> > > being up to keep up under stress.
> > >
> > > hth
> > > connor
> > >
> > > --- Fermin Bernaus Berraondo <fbernaus_at_sammic.com>
> > > wrote: >
> > >
> > > > I think I am having problems with my redologs.
> > > > Under normal circumstances no errors arise, but if
> > >
> > > I
> > >
> > > > do a massive import of data as I was doing last
> > > > night, this is what alertSID.log shows from time
> > >
> > > to
> > >
> > > > time:
> > > >
> > > > Wed Apr 2 23:29:52 2003
> > > > Thread 1 advanced to log sequence 557295
> > > > Current log# 3 seq# 557295 mem# 0:
> > > > /baandata/oradata/baan/redobaan03.log
> > > > Wed Apr 2 23:31:11 2003
> > > > Thread 1 cannot allocate new log, sequence 557296 Checkpoint not
> > > > complete
> > > > Current log# 3 seq# 557295 mem# 0:
> > > > /baandata/oradata/baan/redobaan03.log
> > > > Wed Apr 2 23:31:50 2003
> > > >
> > > > In that exact time, everything freezes and the
> > > > database is dead until a new redolog can be used.
> > > >
> > > > I have 3 redologs 50 Mb each. I've read that the
> > > > error is because too much data is trying to get
> > >
> > > into
> > >
> > > > the redologs and all of them are full, Oracle does
> > > > not have the time to reuse a redolog and has to
> > >
> > > wait
> > >
> > > > until the redolog is ready to be reused.
> > > >
> > > > So the solution seems to make these redolog files
> > > > bigger or to create new ones. What are the side
> > > > effects of one or the other? will performance
> > >
> > > under
> > >
> > > > normal work be penalised?
> > > >
> > > > ..............................................
> > > > Fermín Bernaus Berraondo
> > > > Dpto. de Informática
> > > > SAMMIC, S.A.
> > > > fbernaus_at_sammic.com
> > > > http://www.sammic.com
> > > > Telf. +34 - 943 157 331
> > > > Fax +34 - 943 151 276
> >
> > ..............................................
> >
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.net
> > > > --
> > > > Author: Fermin Bernaus Berraondo
> > > > INET: fbernaus_at_sammic.com
> > > >
> > > > Fat City Network Services -- 858-538-5051
> > > > http://www.fatcity.com
> > > > San Diego, California -- Mailing list and
> > >
> > > web
> > >
> > > > hosting services
> >
> > ---------------------------------------------------------------------
> >
> > > > To REMOVE yourself from this mailing list, send an
> > > > E-Mail message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > > 'ListGuru') and in
> > > > the message BODY, include a line containing: UNSUB
> > > > ORACLE-L
> > > > (or the name of mailing list you want to be
> > >
> > > removed
> > >
> > > > from). You may
> > > > also send the HELP command for other information
> > > > (like subscribing).
> > >
> > > =====
> > > Connor McDonald
> > > web: http://www.oracledba.co.uk
> > > web: http://www.oaktable.net
> > > email: connor_mcdonald_at_yahoo.com
> > >
> > > "GIVE a man a fish and he will eat for a day. But
> > > TEACH him how to fish,
> > > and...he will sit in a boat and drink beer all day"
> > >
> > > __________________________________________________
> > > Yahoo! Plus
> > > For a better Internet experience http://www.yahoo.co.uk/btoffer
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.net
> > > --
> > > Author: =?iso-8859-1?q?Connor=20McDonald?=
> > > INET: hamcdc_at_yahoo.co.uk
> > >
> > > Fat City Network Services -- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California -- Mailing list and web
> > > hosting services
> >
> > ---------------------------------------------------------------------
> >
> > > To REMOVE yourself from this mailing list, send an
> > > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be removed
> > > from). You may
> > > also send the HELP command for other information
> > > (like subscribing).
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.net
> > > --
> > > Author: DENNIS WILLIAMS
> > > INET: DWILLIAMS_at_LIFETOUCH.COM
> > >
> > > Fat City Network Services -- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California -- Mailing list and web
> > > hosting services
> >
> > ---------------------------------------------------------------------
> >
> > > To REMOVE yourself from this mailing list, send an
> > > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be removed
> > > from). You may
> > > also send the HELP command for other information
> > > (like subscribing).
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.net
> > > --
> > > Author: Fermin Bernaus Berraondo
> > > INET: fbernaus_at_sammic.com
> > >
> > > Fat City Network Services -- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California -- Mailing list and web
> > > hosting services
> >
> > ---------------------------------------------------------------------
> >
> > > To REMOVE yourself from this mailing list, send an
> > > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be removed
> > > from). You may
> > > also send the HELP command for other information
> > > (like subscribing).
> >
> > =====
> > Connor McDonald
> > web: http://www.oracledba.co.uk
> > web: http://www.oaktable.net
> > email: connor_mcdonald_at_yahoo.com
> >
> > "GIVE a man a fish and he will eat for a day. But TEACH him
> > how to fish, and...he will sit in a boat and drink beer all day"
> >
> > __________________________________________________
> > Yahoo! Plus
> > For a better Internet experience
> > http://www.yahoo.co.uk/btoffer
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: =?iso-8859-1?q?Connor=20McDonald?=
> > INET: hamcdc_at_yahoo.co.uk
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in the message BODY, include a line containing: UNSUB
> > ORACLE-L (or the name of mailing list you want to be removed
> > from). You may also send the HELP command for other
> > information (like subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Fermin Bernaus Berraondo
> > INET: fbernaus_at_sammic.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in the message BODY, include a line containing: UNSUB
> > ORACLE-L (or the name of mailing list you want to be removed
> > from). You may also send the HELP command for other
> > information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: DENNIS WILLIAMS
> > INET: DWILLIAMS_at_LIFETOUCH.COM
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in the message BODY, include a line containing: UNSUB
> > ORACLE-L (or the name of mailing list you want to be removed
> > from). You may also send the HELP command for other
> > information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).