首页 > 代码库 > Shell: extract more from listener.log(分析监听日志)
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(分析监听日志)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。