Database Reference
In-Depth Information
Why Can't I Allocate a New Log?
I get this question all of the time. You are getting warning messages to this effect (this will be found in
alert.log
on
your server):
Thread 1 cannot allocate new log, sequence 1466
Checkpoint not complete
Current log# 3 seq# 1465 mem# 0: /.../...redo03.log
It might say
Archival required
instead of
Checkpoint not complete
, but the effect is pretty much the same.
This is really something the DBA should be looking out for. This message will be written to
alert.log
on the server
whenever the database attempts to reuse an online redo log file and finds that it can't. This happens when
DBWn
has
not yet finished checkpointing the data protected by the redo log or
ARCn
has not finished copying the redo log file
to the archive destination. At this point, the database effectively
halts
as far as the end user is concerned. It stops
cold.
DBWn
or
ARCn
will be given priority to flush the blocks to disk. Upon completion of the checkpoint or archival,
everything goes back to normal. The reason the database suspends user activity is that there is simply no place to
record the changes the users are making. Oracle is attempting to reuse an online redo log file, but because either the
file would be needed to recover the database in the event of a failure (
Checkpoint not complete
), or the archiver has
not yet finished copying it (
Archival required
), Oracle must wait (and the end users will wait) until the redo log file
can safely be reused.
If you see that your sessions spend a lot of time waiting on a “log file switch,” “log buffer space,” or “log file switch
checkpoint or archival incomplete,” you are most likely hitting this. You will notice it during prolonged periods of
database modifications if your log files are sized incorrectly, or because
DBWn
and
ARCn
need to be tuned by the DBA
or system administrator. I frequently see this issue with the “starter” database that has not been customized. The
“starter” database typically sizes the redo logs far too small for any significant amount of work (including the initial
database build of the data dictionary itself ). As soon as you start loading up the database, you will notice that the first
1,000 rows go fast, and then things start going in spurts: 1,000 go fast, then hang, then go fast, then hang, and so on.
These are the indications you are hitting this condition.
There are a couple of things you can do to solve this issue:
•
Make DBWn faster
. Have your DBA tune
DBWn
by enabling
ASYNC
I/O, using
DBWn
I/O slaves,
or using multiple
DBWn
processes. Look at the I/O on the system and see if one disk or a set of
disks is “hot” and you need to therefore spread the data out. The same general advice applies
for
ARCn
as well. The pros of this are that you get “something for nothing” here—increased
performance without really changing any logic/structures/code. There really are no
downsides to this approach.
•
Add more redo log files
. This will postpone the
Checkpoint not complete
in some cases and,
after a while, it will postpone the
Checkpoint not complete
so long that it perhaps doesn't
happen (because you gave
DBWn
enough breathing room to checkpoint). The same applies to
the
Archival required
message. The benefit of this approach is the removal of the “pauses”
in your system. The downside is it consumes more disk, but the benefit far outweighs any
downside here.
Search WWH ::
Custom Search