Thread 1 cannot allocate new log, sequence

[odbi...@spamu.pl]

Hi,

I have 2 "Thread 1 cannot allocate new log, sequence" messages in my oracle's alert log. This messeges were few days ago and didn't come back. Is it
problem. Should I do something with it?

Thanks in advance

[ddf]

It might be, it might NOT be.

Check your redo log size

SELECT group# "GROUP",
bytes "SIZE",
status "STATUS"
FROM v$log;

and your redo log switch time

SELECT ROUND(AVG(1440 * (b.first_time - a.first_time)), 0) "Log switch
time - minutes"
FROM v$loghist a, v$log b
WHERE b.sequence# = a.sequence# + 1
AND a.sequence# = (SELECT MAX(sequence#) FROM v$loghist)
ORDER BY a.sequence#;

You're probably running that database in archivelog mode and the
error was generated because of an archiving issue. It could also be
generated by a 'checkpoint not complete' problem. Transactional
activity in your database may have hit a new 'high' at the point those
error messages appeared, causing Oracle to fall behind on it's
archiving of the older redo logs prior to re-use because the log
switch time decreased dramatically.

Of course without actually seeing your system and its activity level
all of this is simply guesswork.

David Fitzjarrell

[sri...@hotmail.com]

How many redo log groups do you have and whats their size and how
frequently do they switch ?
Do you also have Checkpoint not complete messages in the alert log ?
regards
srivenu

[HansP]

You should create bigger logfiles.
This can be done online.

Regards Hans-Peter

[odbi...@spamu.pl]

ddf pisze:

> On Oct 2, 8:52 am, "odbio...@spamu.pl" <odbio...@spamu.pl> wrote:
>> Hi,
>>
>> I have 2 "Thread 1 cannot allocate new log, sequence" messages in my oracle's alert log. This messeges were few days ago and didn't come back. Is it
>> problem. Should I do something with it?
>>
>> Thanks in advance
>
> It might be, it might NOT be.
>
> Check your redo log size
>
> SELECT group# "GROUP",
> bytes "SIZE",
> status "STATUS"
> FROM v$log;

Result:

GROUP SIZE STATUS
---------- ---------- ----------------
1 104857600 INACTIVE
2 104857600 CURRENT
3 104857600 INACTIVE
4 104857600 INACTIVE
5 104857600 INACTIVE

>
> and your redo log switch time
>
> SELECT ROUND(AVG(1440 * (b.first_time - a.first_time)), 0) "Log switch
> time - minutes"
> FROM v$loghist a, v$log b
> WHERE b.sequence# = a.sequence# + 1
> AND a.sequence# = (SELECT MAX(sequence#) FROM v$loghist)
> ORDER BY a.sequence#;

Result:

Log switch
time - minutes

-------------------------
8

>
> You're probably running that database in archivelog mode and the
> error was generated because of an archiving issue. It could also be
> generated by a 'checkpoint not complete' problem.

Yes.

It was "Checkpoint not complete"

[Mark D Powell]

I agree with David and Hans. Look at how often you switch redo logs
then perform some combination of adding more online redo logs or make
them bigger if you regularly see places where the online redo logs are
switching within only a couple of minutes. It is generally fine if
you have one short switch time every day as long as the other times
are more reasonable. But it you have an extended period where you are
constantly switching you need bigger logs.

HTH -- Mark D Powell --

[gazzag]

In addition to answers provided by others, this message can also be
indicative of an I/O subsystem bottleneck:

http://www.speakeasy.org/~jwilton/oracle/checkpoint-not-complete.html

HTH

-g

[odbi...@spamu.pl]

Resume:

Because it was incident I can do nothing. If situation repeat I should think about adding more online redo logs or make
them bigger.