RE: Cannot allocate new log - checkpoint not complete

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: Rich Holland
  INET: holland_at_guidancetech.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).