Shell: extract more from listener.log(分析監聽日誌)

標籤：

最近遇到了兩起資料庫連接數不足的問題， 通常都會預留一些會話增加的情況， 但在一些特殊情況下如串連風暴(logon storm)， 如果在監聽中沒有做rate限流，對資料庫來說巨大的衝擊可能會導致資料庫Hang 或 ora-20 或ora-18 錯誤。 對於Hang並伴有進程數不足的情況，AWR、ASH 都可能無法升成，甚至資料庫都無法登入或做SSD 都不成功， 這時候LISTENER.LOG 就成了“破案”時關鍵的線索。 下面記錄分享一些分析listener.log的一些指令碼.(Note:在不同UNIX下可能稍有變化)

統計一天內每小時的session請求數

# fgrep "13-JAN-2015 " anbob_listener.log  |fgrep "establish" |awk ‘{print $1 " " $2}‘ |awk -F: ‘{print $1 }‘ |sort |uniq -c2978 13-JAN-2015 002883 13-JAN-2015 013025 13-JAN-2015 022181 13-JAN-2015 032131 13-JAN-2015 042269 13-JAN-2015 051843 13-JAN-2015 062133 13-JAN-2015 073195 13-JAN-2015 084446 13-JAN-2015 094849 13-JAN-2015 104527 13-JAN-2015 113527 13-JAN-2015 123507 13-JAN-2015 134005 13-JAN-2015 144256 13-JAN-2015 154523 13-JAN-2015 164566 13-JAN-2015 175288 13-JAN-2015 184921 13-JAN-2015 194020 13-JAN-2015 203315 13-JAN-2015 212418 13-JAN-2015 222227 13-JAN-2015 23

指定的一小時每分鐘session請求數

# fgrep "13-JAN-2015 11:" anbob_listener.log  |fgrep "establish" |awk ‘{print $1 " " $2}‘ |awk -F: ‘{print $1 ":" $2 }‘ |sort |uniq -c  94 13-JAN-2015 11:00  44 13-JAN-2015 11:01  80 13-JAN-2015 11:02 119 13-JAN-2015 11:03  56 13-JAN-2015 11:04 127 13-JAN-2015 11:05  68 13-JAN-2015 11:06  66 13-JAN-2015 11:07  58 13-JAN-2015 11:08  67 13-JAN-2015 11:09 103 13-JAN-2015 11:10  53 13-JAN-2015 11:11  88 13-JAN-2015 11:12...

指定的一小時每秒session請求數

# fgrep "13-JAN-2015 11:30" anbob_listener.log  |fgrep "establish" |awk ‘{print $1 " " $2}‘ |awk -F: ‘{print $1 ":" $2 ":" $3 }‘ |sort |uniq -c  7 13-JAN-2015 11:30:00  3 13-JAN-2015 11:30:01  4 13-JAN-2015 11:30:02  4 13-JAN-2015 11:30:03  7 13-JAN-2015 11:30:04  2 13-JAN-2015 11:30:05  1 13-JAN-2015 11:30:06  8 13-JAN-2015 11:30:08  2 13-JAN-2015 11:30:09  3 13-JAN-2015 11:30:10  1 13-JAN-2015 11:30:11...

指定的一小時內每分鐘串連建立失敗數

#fgrep "11-JAN-2015 11:" anbob_listener.log |awk  ‘{ if ( $NF != 0 ) print $0 }‘|awk ‘{print $1 " " $2}‘ |awk -F: ‘{print $1 ":" $2 }‘ |sort |uniq -c 474 11-JAN-2015 11:38  10 11-JAN-2015 11:39

指定的一小時內每IP請求數

#fgrep "11-JAN-2015 11:" anbob_listener.log|fgrep "establish"|awk -F* ‘{print $3}‘|awk -F= ‘{ print $4}‘|sed -e ‘s/......$//g‘|sort |uniq -c|sort  1 136.142.26.139  2 136.142.10.212  2 136.142.21.171  8 136.142.21.172  13 136.142.26.133  13 136.142.29.17  14 136.142.29.20  18 136.142.26.35  23 136.142.29.29...

指定的分鐘內每IP請求數

#fgrep "11-JAN-2015 11:30" anbob_listener.log|fgrep "establish"|awk -F* ‘{print $3}‘|awk -F= ‘{ print $4}‘|sed -e ‘s/......$//g‘|sort |uniq -c|sort   1 136.142.26.35  1 136.142.29.149  1 136.142.29.156  1 136.142.29.17  2 136.142.30.189  3 136.142.26.133  4 136.142.26.136  4 136.142.29.157  7 136.142.29.20  9 136.142.29.22  10 136.142.26.34...

全天每小時每個IP請求數

fgrep "09-JAN-2015 " anbob_listener.log|fgrep "establish"|awk -F* ‘{print $1 " " $3}‘|awk -F= ‘{ print $1 " "  $4}‘|sed -e ‘s/......$//g‘| awk ‘{print $1 " " $2 " " $4}‘|cut -b-14,21- |sort |uniq -c    1 09-JAN-2015 01 136.142.21.172  66 09-JAN-2015 01 136.142.21.85  11 09-JAN-2015 01 136.142.26.131   5 09-JAN-2015 01 136.142.26.133  21 09-JAN-2015 01 136.142.26.1365113 09-JAN-2015 01 136.142.26.24  49 09-JAN-2015 01 136.142.26.34   6 09-JAN-2015 01 136.142.29.141  28 09-JAN-2015 01 136.142.29.148  49 09-JAN-2015 01 136.142.29.149  85 09-JAN-2015 01 136.142.29.150   2 09-JAN-2015 01 136.142.29.151   6 09-JAN-2015 01 136.142.29.156   6 09-JAN-2015 01 136.142.29.157   2 09-JAN-2015 01 136.142.29.162  58 09-JAN-2015 01 136.142.29.164   4 09-JAN-2015 01 136.142.29.17   4 09-JAN-2015 01 136.142.29.184 207 09-JAN-2015 01 136.142.29.192

-- file: session_rpt.sql-- Purpose: list of session information -- Author:  weejar-- Copyright:   (c) ANBOB - http://www.anbob.com.com - All rights reserved.promprom List of sessions (c)anbob.compromselect ‘anbob.com‘ author,to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) current_time,instance_name from v$instance/col sid form 99999col serial# form 99999col spid form a6col program heading ‘program‘ for a25 trunccol username form a15col osuser form a10col idle form a30 heading "Idle"col terminal form a12col logon_time form a18col machine for a15 trunccol rn for 9999col service_name for a30set lines 150 pages 1000break  on reportcompute sum of cnt on reportselect username,status,count(*) cnt from v$session group by username,status/select username,machine,count(*) cnt from v$session group by username,machine/select status,count(*) cnt from v$session group by status/select inst_id,service_name,count(*) cnt from gv$session group by  inst_id,service_name order by 1,2/ttitle -    center  ‘displays the top 50 longest idle times‘  skip 2 select  a.*from (  select sid,serial#,username,status, to_char(logon_time,‘dd-mm-yy hh:mi:ss‘) logon_time  , floor(last_call_et/3600)||‘ hours ‘    || floor(mod(last_call_et,3600)/60)||‘ mins ‘    || mod(mod(last_call_et,3600),60)||‘ secs‘ idle  , machine ,row_number() over(order by last_call_et desc ) rn  from v$session   where type=‘USER‘ ) awhere rn<= 50/ttitle offcolumn event heading ‘wait event‘ for a30 truncttitle -   center  ‘displays active session‘  skip 2select sid,serial#,username,event,program,MACHINE,sql_id,BLOCKING_SESSION from v$session where status=‘ACTIVE‘ and username is not null;ttitle off

• 本文來自：Linux教程網

Shell: extract more from listener.log(分析監聽日誌)