RE: ОТН.: ОТН.: How to track logfile switch

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@xxxxxxxx] 
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@xxxxxxx]
Изпратени: 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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]

Other related posts: