Re: ïôî.: ïôî.: How to track logfile switch

I think it might be beneficial to know some things. I believe it was already stated that the redo logs were 512MB (or something large) but the archived redo logs were quite small. It sounds to me like some frequent checkpointing is going on and archiver can't keep up with the amount of redo log groups you have.

What are the following parameters set to: log_checkpoint_interval
log_checkpoint_timeout
fast_start_mttr_target

I seem to recall fast_start_mttr_target being auto-tunable in 10gR2.

Bradd Piontek
  "Next to doing a good job yourself,

        the greatest joy is in having someone
        else do a first-class job under your
        direction."

• William Feather

2008/10/3 Bort, Guillermo <guillermo.bort_at_eds.com>

> Alter system are not DML. DML are regular old fashioned SQL such as
> insert, update and delete. As someone else said in the list, every DML
> generates redo information and when the redo log file becomes full a log
> switch automatically occurs. This is basic transaction processing in Oracle.
> I would go with increasing the size of redo log buffer and maybe redo log
> files to have less than four log switches per hour during normal operation.
> And take it from there. I reckon the frequency of the problem will decrease
> greatly or the problem may even dissapear.
>
>
>
> HTH
>
>
>
> *Guillermo Alan Bort*
>
> *EDS - ITO *DBA Main Group
>
>
>
> *From:* Yavor Ivanov [mailto:Yavor_Ivanov_at_stemo.bg]
> *Sent:* Friday, October 03, 2008 1:00 PM
> *To:* Bort, Guillermo; Bobak, Mark; oracle-l
> *Subject:* ïôî.: ïôî.: How to track logfile switch
>
>
>
> This sound promising. What DML could cause log switch (other than "alter
> system switch logfile" and "alter system archive log ...")?
>
>
>
> The issue happens during normal daily load.
>
>
>
>
>
> Regards,
>
> Yavor Ivanov
> ------------------------------
>
> *ïÔ:* Bort, Guillermo [guillermo.bort_at_eds.com]
> *éÚÐÒÁÔÅÎÉ:* 03 ïËÔÏÍ×ÒÉ 2008 Ç. 18:45
> *äÏ:* Yavor Ivanov; Bobak, Mark; oracle-l
> *ôÅÍÁ:* RE: ïôî.: How to track logfile switch
>
> It may be silly to mention this, however log switches can be caused by
> valid dml processing not just alter system switch logfile. I've found that
> increasing redo log size and redo log buffer often helps lower the number of
> Checkpoint not Complete. Do you notice this issue during periods of high
> transaction load or batch load? Also, do you have fast_start_mttr_target set
> to a static value?
>
>
>
> HTH
>
>
>
> *Guillermo Alan Bort*
>
> *EDS - ITO *DBA Main Group
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Yavor Ivanov
> *Sent:* Friday, October 03, 2008 12:36 PM
> *To:* Bobak, Mark; oracle-l
> *Subject:* ïôî.: How to track logfile switch
>
>
>
> The value of ARCHIVE_LAG_TARGET is 0
>
>
>
> Unfortunately the message "Thread X cannot allocate new log,
> sequence XXXX" is sometimes followed by "Checkpoint not complete". So I
> cannot ignore it easily. Yesterday there was a short but noticeable database
> hang at the same time when this happened.
>
>
>
> Regards,
>
> Yavor Ivanov
>
> [SNIP]
>

--
http://www.freelists.org/webpage/oracle-l