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).
Received on Fri Apr 04 2003 - 09:43:39 CST
