How to track logfile switch
- From: Yavor Ivanov <Yavor_Ivanov@xxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Oct 2008 16:50:14 +0300
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:
select l.THREAD#, l.SEQUENCE#, l.FIRST_TIME, l.NEXT_TIME, l.BLOCKS *
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
HH24:MI')
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?
Regards,
Yavor Ivanov
Other related posts:
