Shell: extract more from listener.log (分析oracle监听日志连接频率)

原创 anbob.com 2019-10-25

1676

一些特殊情况下如连接风暴(logon storm)，如果在监听中没有做rate限流，对数据库来说巨大的冲击可能会导致数据库Hang 或 ora-20 或ora-18 错误。对于Hang并伴有进程数不足的情况，AWR、ASH 都可能无法生成，这时候LISTENER.LOG 就成了“破案”时关键的线索。下面记录分享一些分析listener.log的一些脚本.(Note:在不同UNIX下可能稍有变化)

统计一天内每小时的session请求数

# fgrep "13-JAN-2015 " listener.log  |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 }' |sort |uniq -c
2978 13-JAN-2015 00
2883 13-JAN-2015 01
3025 13-JAN-2015 02
2181 13-JAN-2015 03
2131 13-JAN-2015 04
2269 13-JAN-2015 05
1843 13-JAN-2015 06
2133 13-JAN-2015 07
3195 13-JAN-2015 08
4446 13-JAN-2015 09
4849 13-JAN-2015 10
4527 13-JAN-2015 11
3527 13-JAN-2015 12
3507 13-JAN-2015 13
4005 13-JAN-2015 14
4256 13-JAN-2015 15
4523 13-JAN-2015 16
4566 13-JAN-2015 17
5288 13-JAN-2015 18
4921 13-JAN-2015 19
4020 13-JAN-2015 20
3315 13-JAN-2015 21
2418 13-JAN-2015 22
2227 13-JAN-2015 23

指定的一小时每分钟session请求数

  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|sort -n
 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 -n
   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" listener.log|fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort -n
   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请求数

 sed -n '/22-MAY-2019 12:01:00/,/22-MAY-2019 12:05:00/p' listener.log  |fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort -n
  19 133.96.43.75
  36 133.96.65.47
  53 133.96.66.41
  62 133.96.66.42
  80 133.96.65.48
  85 133.96.43.71
  96 133.96.65.41
 360 133.96.65.46
 384 133.96.65.86
 681 133.96.65.45

全天每小时每个IP请求数

fgrep "09-JAN-2015 " 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.136
5113 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

指定时间指定主机上不同主机USER的请求统计

$ fgrep "04-JUL-2018 15:"  listener.log|fgrep "133.96.65.45" | awk -F= '$11 ~ /USER/ {print $12 }' |  sed 's/).*$//'|sort|uniq -c|sort -n 
10815 taskmon

指定时间指定条件的PROGRAM的请求统计

$  fgrep "01-NOV-2018 " listener.log|sed 's/^.*PROGRAM//;s/).*$//;s/^.*:...\*//;s/).*$//;s/\*.*$//'|sort|uniq -c|sort -n
   1 sqlldr@kinjk3
2789 sqlplus
8025 sqlplus@kinjk3

Note:
PROGRAM=oracle的可能是DBLINK,有些使用sid连接的的可能program为空如hpux下面这条记录:

# hpux
01-NOV-2018 00:00:06 * (CONNECT_DATA=(SID=tbcsa1)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=133.96.65.86)(PORT=63438)) * establish * tbcsa1 * 0

指定时间段的PROGRAM的请求统计

# sed -n '/22-MAY-2019 09:01:00/,/22-MAY-2019 09:05:00/p' listener.log | fgrep "establish"|sed 's/^.*PROGRAM//;s/).*$//;s/^.*:...\*//;s/).*$//;s/\*.*$//'|sort|uniq -c|sort -n
    1  establish 
   2 =python
   5 =JDBC Thin Client
   6 =VpnOrderSyn
   6 =rman
  40 =IntClient
  79 =oracle
  95 =SynSrpFeedBackResult
2472 =

使用ADRCI一条命令收集每小时连接频率
注意:如果是多个监听时需要配置adr home

adrci exec='set home diag/tnslsnr ; show alert -term -p \"' "MESSAGE_TEXT like \'%establish%\' and ORIGINATING_TIMESTAMP > \'2019-08-31 00:00:00\'" '\"' | awk '/establish/{sub(/:.*/,"",$2);print "connection/hour at "$1,$2":00-"$2":59"}'|uniq -c

收集白名单
从监听日志里分析白名单的IP段(注意：日志里只是当前保留的日志时间段的访问记录，并非全部。)

cd /oracle/app/grid/diag/tnslsnr/$(hostname)/listener/trace
grep "establish" listener.log|awk -F* '{print $3}'|awk -F= '{print $4}'|sed -e 's/......$//g'|awk -F. '{print $1 "." $2 "." $3}'|sort|uniq | awk 'BEGIN{RS=EOF}{gsub(/\n/,".*,");print}'

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Shell: extract more from listener.log (分析oracle监听日志连接频率)

评论