Shell:extract More from Listener.log (Analysis listening log)

There are two recent problems with insufficient database connections, usually reserved for some session increase, but in some special cases such as connection storm, if there is no rate limit in the monitoring, a huge impact on the database may cause the database hang or ora-20 or ora-18 errors. For hang and with a lack of process, AWR, ASH may not be able to ascend, even the database can not log in or do not succeed in SSD, this time LISTENER.LOG became the "solve" the key clue. The following records share some of the scripts for analyzing listener.log. (note: May vary slightly under different Unix)

Count the number of session requests per hour in a day

# fgrep "13-jan-2015" Anbob_listener.log  |fgrep "establish" |awk ' {print $ "" $ $} ' |awk-f: ' {print '} ' |sort |un iq-c2978 13-jan-2015 002883 13-jan-2015 013025 13-jan-2015 022181 13-jan-2015 032131 13-JAN-2015 042269 13-JAN-2015 05184 3 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 18 4921 13-jan-2015 194020 13-jan-2015 203315 13-jan-2015 212418 13-jan-2015 222227 13-jan-2015 23

The number of session requests per minute of the specified hour

# fgrep "13-jan-2015:" Anbob_listener.log  |fgrep "establish" |awk ' {print $ "" $ $} ' |awk-f: ' {print $ ":" $ $} ' |sort |uniq-c  94 13-jan-2015 11:00am  13-jan-2015 11:01-  13-jan-2015 11:02 119 13-jan-2015 11:03  56 13 -jan-2015 11:04 127 13-jan-2015 11:05, 13-jan-2015 11:06,  13-jan-2015 11:07,  13-jan-2015 11:08  67 1 3-jan-2015 11:09 103 13-jan-2015 11:10,  13-jan-2015 11:11,  13-jan-2015 11:12 ...

Specifies the number of session requests per second of an hour

# fgrep "13-jan-2015" Anbob_listener.log  |fgrep "establish" |awk ' {print $ "" $ $ "|awk-f: ' {print $ ':" $ ":" $ |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-ja N-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 ...

The number of connection creation failures per minute in the specified hour

#fgrep "11-jan-2015:" Anbob_listener.log |awk  ' {if ($NF! = 0) Print $} ' |awk ' {print $ "" $ $} ' |awk-f: ' {pri NT $ ":" $ |sort} ' |uniq-c 474 11-jan-2015 11:38 11-jan-2015  11:39

The number of requests per IP within the specified hour

#fgrep "11-jan-2015:" Anbob_listener.log|fgrep "establish" |awk-f* ' {print $} ' |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  136.142.29.17  136.142.29.20  136.142.26.35  23 136.142.29.29 ...

Number of requests per IP in the specified minutes

#fgrep "11-jan-2015" anbob_listener.log|fgrep "establish" |awk-f* ' {print $} ' |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 ...

The number of IP requests per hour for the entire day

Fgrep "09-jan-2015" anbob_listener.log|fgrep "establish" |awk-f* ' {print $ "" $ $} ' |awk-f= ' {print $ "" $4  } ' |sed -e ' s/......$//g ' | awk ' {print $ ' "$ $" "$4} ' |cut-b-14,21-|sort |uniq-c    1 09-jan-2015 136.142.21.172  136 09-jan-2015 01. 142.21.85  09-jan-2015 136.142.26.131   5 09-jan-2015 136.142.26.133  01 09-jan-2015 136.142.26.1365113 09-jan-2015 136.142.26.24  09-jan-2015 136.142.26.34   6 09-jan-2015 01 136.142.29.141  09-jan-2015 136.142.29.148  09-jan-2015 136.142.29.149 09-jan-2015   2 136.142.29.151   6 09-jan-2015 136.142.29.156   6 09-jan-2015 136.142.29.157   2 09-09-jan-2015 JAN-2015 136.142.29.162  09-jan-2015 136.142.29.164   4 09-jan-2015 136.142.29.17   4 09-jan-2015 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.A Nbob.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 a 6col program heading ' Program ' for A25 trunccol username form a15col osuser form a10col idle form A30 heading "Idle" Col te Rminal form a12col logon_time form A18col machine for A15 trunccol RN for 9999col service_name for A30set lines pages 1000break on Reportcompute sum of CNT in Reportselect username,status,count (*) CNT from v$session GROUP by Username,statu  S/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/ttit Le-center ' Displays tHe top 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), max) | | ' 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 ' disp Lays active session ' skip 2select sid,serial#,username,event,program,machine,sql_id,blocking_session from v$session where status= ' ACTIVE ' and username are not null;ttitle off

• This article is from: Linux Tutorial Network

Shell:extract More from Listener.log (Analysis listening log)