Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: alert_inst.log: confusing message

Re: alert_inst.log: confusing message

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 17 Apr 2001 22:41:11 +0200
Message-ID: <29apdtstuevuk5vojn6kjef90jnuhlhvmp@4ax.com>

On Tue, 17 Apr 2001 22:07:57 +0200, "Michiel" <cube_at_REMOVE.dds.nl> wrote:

>Hi,
>
>I have a question about some message in my log files, perhaps anyone can
>help me
>
>first; basic info:
>oracle info: 8.1.6r2 on Linux (SuSe 6.4) kernel 2.2.14, 512Mb memory
>oracle_home: mount point /u01, 55% used (stable in size)
>oradata: mount point /u02, 26% used (growing in size)
>
>
>I found a not-so-normal message in my
>/u01/app/oracle/admin/inst01/bdump/alert_inst01.log
>(${ORACLE_BASE}/admin/${ORACLE_SID}/bdump/alert_${ORACLE_SID}.log)
>
>contents:
>...
>Thread 1 cannot allocate new log, sequence 8986
>Checkpoint not complete
> Current log# 3 seq #8985 mem# 0: /u02/oradata/inst01/redo03.log
>Thread 1 advanced to log sequence 8986
> Current log# 1 seq #8986 mem# 0: /u02/oradata/inst01/redo01.log
>Thread 1 advanced to log sequence 8987
> Current log# 2 seq #8986 mem# 0: /u02/oradata/inst01/redo02.log
>Thread 1 cannot allocate new log, sequence 8988
>Checkpoint not complete
>...
>
>Although these messages are not repeated a lot (about 20 times), they still
>are repeated several times which confuses.
>
>Since these messages have no time-stamp within the file, I can only guess
>when they have occured: most-likly during update statements of 3 tables
>(about 500 rows per table updated, using 3 cursors, each 3updates in 1
>transaction, thus 500 transactions); the update statements have been
>processed
>
>mount point /u02 (where the data is located) has 1.3Gb (26%) used of a total
>of 5.4Gb
>
>some .ora settings:
>open_cursors = 400
>db_block_buffers = 2048
>shared_pool_size = 70000000
>large_pool_size = 20000000
>log_checkpoint_interval = 10000
>log_checkpoint_timeout = 1800
>processes = 100
>log_buffer = 163840
>db_block_size = 8192
>compatible = 8.0.5
>
>If any out here has some hints or solutions (or links); thnx in advance then
>Michiel
>
>

This is a common problem.
What happens is the size of your updates (in bytes) exceeds the capacity of *all* your online redo log files. Hence, either you have too few of them (minimal 4 is reasonable) or they are too small. (the default is 200k, which is clearly ridiculous) I usually start out with 4 redo log groups of 5M each (and mirrored of course).
The message in itself is a warning, your update will simply hang the database for a while and that's about it, so you might experience an overall deteroriation of performance during your update. You can simpy change the redolog configuration by issuing alter database drop logfile group
followed by
alter database add logfile group
If the logfile is active (which can be determined from v$log) simply issue
alter system switch logfile.
If you have any further queries, let's continue the discussion off-line and in Dutch

Hope this helps,

Sybrand Bakker, Oracle DBA Received on Tue Apr 17 2001 - 15:41:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US