Question: I am
getting the message "Thread 1 cannot allocate new log,
sequence" in my alert log.
/u01/app/oracle/mydb/redo16.log
Thu Jan 14
22:12:55 2016
Thread 1 cannot allocate new log,
sequence 12345
Checkpoint not complete
What do I do to fix this problem?
Answer:
The "checkpoint not complete"
messages are generated because the logs are switching so
fast that the checkpoint associated with the log switch
isn't complete.
During that time, Oracle's LGWR process has filled up the
other redo log groups and is now waiting for the first
checkpoint to successfully execute. Remember, Oracle
will halt processing until the checkpoint completes
successfully!
Usually you see this "Thread
1 cannot allocate new log, sequence" error in
your alert log:
/u01/app/oracle/mydb/redo16.log
Thu
Jan 14 22:12:55 2016
Thread 1 cannot allocate new log,
sequence 12345
Checkpoint not complete
Oracle has tips on avoiding the
Thread 1 cannot allocate new log,
sequence error, and
Oracle suggests
that you might consider setting archive_lag_target
to zero to reduce "Thread 1 cannot allocate new log,
sequence" (log checkpoint not complete) messages:
alter system set archive_lag_target=0
scope=both;
Overall Oracle
performance can be dramatically improved by increasing the
log sizes so that logs switch at the recommended interval of
15 to 30 minutes (during peak DML processing times).
Identify the
current size of the redo log members from v$log.
Next, you
record the number of log switches per hour and increase the
size of the log to allow Oracle to switch at the recommended
rate of under 6 log switches per hour.
Also see my notes
on how to
monitor online redo log switches.
For example, if the database redo log
size is 1 megabyte and you are switching logs every 1
minute, you will need to increase the log size to 30
megabytes in size to allow it to switch every 30 minutes.
For related details see "Manual Log
Switching Causing "Thread 1 Cannot Allocate New Log" Message
in the Alert Log" (MOSC
ID 435887.1)
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
