ОТН.: How to track logfile switch

  • From: Yavor Ivanov <Yavor_Ivanov@xxxxxxxx>
  • To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 3 Oct 2008 18:35:53 +0300

 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.

Yavor Ivanov

От: Bobak, Mark [Mark.Bobak@xxxxxxxxxxxx]
Изпратени: 03 Октомври 2008 г. 17:11
До: Yavor Ivanov; oracle-l
Тема: RE: How to track logfile switch

What’s the value of ARCHIVE_LAG_TARGET?

Mark J. Bobak
Senior Database Administrator, System & Product Technologies
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
+1.734.997.4059  or +1.800.521.0600 x 4059

ProQuest...Start here.

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Yavor Ivanov
Sent: Friday, October 03, 2008 9:59 AM
To: oracle-l
Subject: FW: How to track logfile switch

                Forgot to say the version. It is

Yavor Ivanov
Oracle Certified Master

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Yavor Ivanov
Sent: Friday, October 03, 2008 4:50 PM
To: oracle-l
Subject: How to track logfile switch

                Hello, gurus

                I have a strange problem. I am auditing a  system with 2 node 
Oracle RAC on Solaris. I found in the alert log the following ugly message:
Thread Х cannot allocate new log, sequence ХХХХ
                It happens may times. I started to dig inside the problem. I 
found that the system is setup with 2 redo logs per thread, each of them is 512 
MB. I do not think this is wise, but this is not the problem. I noticed in 
v$archived_log that the size of archived logs (BLOCKS * BLOCK_SIZE) is 
somewhere between 1 and 3 MB (!), far less than 512 MB. Log switch is happening 
every 1-5 minutes.
                Am I missing something? It is Friday afternoon here, and 
everything seems to me so strange that I started to think I am missing 
something very major. I am using the following query to get the size of the 
files generated for some period:
l.BLOCK_SIZE / 1024 / 1024
  from v$archived_log l
 where round(next_time, 'HH24') = to_date('03.10.2008 10:00', 'DD.MM.YYYY 

                Also I was hoping to catch who is making all this log switches 
using AFTER DDL ON DATABASE trigger, but the trigger does not fire for alter 
system switch logfile. Maybe this statement is not DDL. Can somebody see what 
am I missing?

Yavor Ivanov

Other related posts: