Home » RDBMS Server » Server Administration » log file sync issue / archive file size issue (oracle 11g/r2/unix)
log file sync issue / archive file size issue [message #609457] Fri, 07 March 2014 01:39 Go to next message
balaji123
Messages: 29
Registered: October 2009
Location: sanfrancisco
Junior Member
how to find out log file sync having issue ?

also how to find archive file size issue? Please advise.

thanks in advance.
Re: log file sync issue / archive file size issue [message #609460 is a reply to message #609457] Fri, 07 March 2014 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
how to find out log file sync having issue ?


AWR/StatsPack report will tell you.

Quote:
also how to find archive file size issue?


It is more "log file" then "archive file", the latter does not matter.
Check alert.log file for message like "checkpoint not complete", "cannot allocate new log"...
Check v$loghist (or the like v$log_history...), avoid more than 5-10 log switches per hour (just a rule of thumb).

Re: log file sync issue / archive file size issue [message #609461 is a reply to message #609460] Fri, 07 March 2014 02:11 Go to previous messageGo to next message
balaji123
Messages: 29
Registered: October 2009
Location: sanfrancisco
Junior Member
i ran sql to identify log file sync issue.
select
e.sid
,e.event
,e.time_waited
,e.time_waited_micro
from
v$session_event e
,v$session s
where
e.sid=s.sid
and s.program like '%LGWR%'
order by
3
;

SID EVENT TIME_WAITED TIME_WAITED_MICRO
1801 latch: redo writing 0 2822
1801 Disk file operations I/O 1 7808
1801 os thread startup 1 13561
1801 KSV master wait 56 559133
1801 ARCH wait for netserver start 100 1000823
1801 log file single write 334 3336734
1801 log file sequential read 614 6136717
1801 control file sequential read 2291 22911340
1801 control file parallel write 5129 51292891
1801 LGWR wait on LNS 21333 213333623
1801 log file parallel write 4129019 41290185371
1801 events in waitclass Other 5162060 51620602574
1801 LGWR real time apply sync 9312341 93123410076
1801 rdbms ipc message 22764167 227641665233

i do see bigger number on log file parallel write ? looks like some issue ..

2. sql for v$loghist

with in an hour +20 files switches , is it good ?

select * from v$loghist order by 3 desc

thread sequence first_change first_time switch_change
2 128184 13626447354661 3/6/2014 11:51:22 PM 13626449908837
4 197021 13626447350367 3/6/2014 11:51:20 PM 13626449909335
1 130735 13626445312333 3/6/2014 11:46:50 PM 13626449911297
4 197020 13626445308954 3/6/2014 11:46:48 PM 13626447350367
3 163577 13626442151002 3/6/2014 11:40:24 PM 13626449909749
4 197019 13626442146372 3/6/2014 11:40:21 PM 13626445308954
2 128183 13626439524617 3/6/2014 11:35:21 PM 13626447354661
4 197018 13626439519909 3/6/2014 11:35:18 PM 13626442146372
1 130734 13626437892003 3/6/2014 11:31:25 PM 13626445312333
4 197017 13626437506949 3/6/2014 11:31:22 PM 13626439519909
3 163576 13626435003494 3/6/2014 11:27:21 PM 13626442151002
4 197016 13626434996316 3/6/2014 11:27:17 PM 13626437506949
2 128182 13626431936815 3/6/2014 11:21:56 PM 13626439524617
4 197015 13626431935985 3/6/2014 11:21:56 PM 13626434996316
1 130733 13626430228837 3/6/2014 11:19:00 PM 13626437892003
4 197014 13626430219917 3/6/2014 11:18:58 PM 13626431935985
3 163575 13626428935264 3/6/2014 11:16:03 PM 13626435003494
4 197013 13626428934791 3/6/2014 11:16:03 PM 13626430219917
2 128181 13626426885908 3/6/2014 11:12:03 PM 13626431936815
4 197012 13626426878204 3/6/2014 11:12:00 PM 13626428934791
1 130732 13626424001070 3/6/2014 11:07:47 PM 13626430228837
4 197011 13626423994615 3/6/2014 11:07:44 PM 13626426878204
3 163574 13626421494808 3/6/2014 11:02:27 PM 13626428935264
4 197010 13626421493371 3/6/2014 11:02:26 PM 13626423994615
Re: log file sync issue / archive file size issue [message #609470 is a reply to message #609461] Fri, 07 March 2014 03:12 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
i ran sql to identify log file sync issue.


select total_waits, total_timeouts, time_waited, average_wait
from v$system_event where event='log file sync';


Quote:
i do see bigger number on log file parallel write ? looks like some issue ..


You must run an AWR/StatsPack report. (AWR only if you are licensed for, StatsPack is free but you have to install it.)

Quote:
with in an hour +20 files switches , is it good ?


Run the following query instead:
Column nop   noprint
Column total format 999G990 heading "Total"
Column h00   format a03     heading "H00" trunc
Column h01   format a03     heading "H01" trunc
Column h02   format a03     heading "H02" trunc
Column h03   format a03     heading "H03" trunc
Column h04   format a03     heading "H04" trunc
Column h05   format a03     heading "H05" trunc
Column h06   format a03     heading "H06" trunc
Column h07   format a03     heading "H07" trunc
Column h08   format a03     heading "H08" trunc
Column h09   format a03     heading "H09" trunc
Column h10   format a03     heading "H10" trunc
Column h11   format a03     heading "H11" trunc
Column h12   format a03     heading "H12" trunc
Column h13   format a03     heading "H13" trunc
Column h14   format a03     heading "H14" trunc
Column h15   format a03     heading "H15" trunc
Column h16   format a03     heading "H16" trunc
Column h17   format a03     heading "H17" trunc
Column h18   format a03     heading "H18" trunc
Column h19   format a03     heading "H19" trunc
Column h20   format a03     heading "H20" trunc
Column h21   format a03     heading "H21" trunc
Column h22   format a03     heading "H22" trunc
Column h23   format a03     heading "H23" trunc
Select trunc(first_time) nop, 
       to_char(first_time, 'Dy DD/MM/YYYY') jour,
       count(*) total,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'fm999'),3),'  0','   ') h00,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'fm999'),3),'  0','   ') h01,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'fm999'),3),'  0','   ') h02,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'fm999'),3),'  0','   ') h03,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'fm999'),3),'  0','   ') h04,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'fm999'),3),'  0','   ') h05,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'fm999'),3),'  0','   ') h06,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'fm999'),3),'  0','   ') h07,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'fm999'),3),'  0','   ') h08,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'fm999'),3),'  0','   ') h09,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'fm999'),3),'  0','   ') h10,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'fm999'),3),'  0','   ') h11,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'fm999'),3),'  0','   ') h12,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'fm999'),3),'  0','   ') h13,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'fm999'),3),'  0','   ') h14,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'fm999'),3),'  0','   ') h15,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'fm999'),3),'  0','   ') h16,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'fm999'),3),'  0','   ') h17,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'fm999'),3),'  0','   ') h18,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'fm999'),3),'  0','   ') h19,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'fm999'),3),'  0','   ') h20,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'fm999'),3),'  0','   ') h21,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'fm999'),3),'  0','   ') h22,
       replace(lpad(to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'fm999'),3),'  0','   ') h23
from v$log_history
where first_time > add_months(sysdate,-1)
group by trunc(first_time), to_char(first_time, 'Dy DD/MM/YYYY')
order by 1
/

But if you really have "in an hour +20 files switches", you should at least double the size of you redo logs.

Previous Topic: pin oracle table rows
Next Topic: Pre-upgrade warning (11.2.0.1 to 11.2.0.4)
Goto Forum:
  


Current Time: Fri Mar 29 09:00:39 CDT 2024