Wednesday, May 19, 2010

Mining Listener Logs

When is the last time you looked at the listener logs? Perhaps never. Not a very good idea. Listener logs contain a wealth of information on security events - it shows you the profile of hosts connecting to the database, the programs they are using and attempting to communicate but failed, among other things. But reading the listener logs is not exactly a pleasant experience. A while ago I wrote a series of articles on an online eZine called on how to create an external table to read the listener logs using simple SQL; but unfortunately has folded.

I have placed the articles on my website for your reference. As always, I would love to hear from you how you felt about these, stories of your own use and everything in between.

Mining Listener Logs Part 1
Mining Listener Logs Part 2
Mining Listener Logs Part 3


Anonymous said...

Good Stuff :) but I also wonder how you could make use of the listener logs to diagnose a network performance issue..

Unknown said...

@karlarao: Not directly; but you can devise a way to connect the time from the app server to the time in the listener. It's tricky since the time could be different on both. It should be a little easier with some sort of NTP program.

Alberto said...

Great job Arup, in the last month i'm very busy on a trouble DB-client connection, your advise are always amazing.

Anonymous said...

麗的線上小遊戲 38ga成人視訊 18成人影城0204movie 免費聊天交友fm358 視訊美女maxworld av女優影片情色文學 sexy亞洲禁果影城 美女視訊無碼影片 正宗自拍美女 aooyy 173liveshow視訊美女 msn 視訊聊天室 成人聊天室 性感沙灘34c 美女影片 布蘭妮貼圖片區 情色論壇,卡提諾王國 34c環球辣妹聊天室 90691 亞亞成人館-sex 八大娛樂網-視訊 天空部落格正妹 色妹妹免費情慾影片觀賞aa 視訊情色小站 2009 avhigh 免費做愛影片 免費視訊聊天室 夢中情人聊天室 台灣kiss倩色網 辣美眉173show影片 武則天影音視訊聊天室 名模辣妹聊天室 無碼av女優,av無碼,無碼dvd 女生自衛圖片 聊天交友sun city 成人免費視訊 完美女人 情色小站 18成人影城 美女交友semalaya 視訊聊天 ilover99 論壇 一對多美女視訊 sexy girl video movie免費色情 成人視訊qk176 女同志聊天室 性愛教學 av969-成人情色 85cc免費影片觀看 18-美女交友 情人視訊聊天 成人遊戲區 girl5320 線上 free 色性影片區

Anonymous said...

Thanks for answering my question..

How do you make use of this mining to detect connect heavy applications or periods (log on storms)?

I've read on one Oracle COE case study that the "Listener records the number of connect requests it can processes per second, not the number of connect requests it gets per second."

It also mentioned the following..
"the Listener will fork and execute a new process and then look into the protocol stack and pick up all the connect requests that are there. It only logs the connection request at some point during the processing of that request (it does not have the information it puts in the log yet), so it does not log every connection request as it pulls it from the protocol stack."

Here's the document..
Case Study Master [ID 342534.1] Configuration Solutions for a High Load Environment

lisaware said...

Great stuff! Thanks! I used this to track down why I suddenly got a max_processes exceeded. Used your tools to grab 15 minute increments before and after when the problem occured & found my culprit.

so, basicaly, a series of these:

col action for a10
col connect_string for a50
col service_name for a20
set lines 100
set pages 50
col cnt for 99,999
col pgm for a75

spool 1045to1100lis.log

select distinct parse_listener_log_line(connect_string, 'PROGRAM') pgm, count(*) cnt
from listener_log
where log_date > to_date('2011-11-18 10:45', 'YYYY-MM-DD HH:MI')
and log_date < to_date('2011-11-18 11:00', 'YYYY-MM-DD HH:MI')
group by parse_listener_log_line(connect_string, 'PROGRAM')
order by cnt;

spool off

Thank you very much!


Nicholas said...

I came after a long time,i glad to read such post you did a great work its impressing thanks i will keep visiting.

Jon Finke said...

Very handy stuff - I am doing a DB upgrade/migration, and this will help me clean up my firewall rules - if they haven't connected in a year, they are off the list!

Unknown said...

@Jon Thanks for the compliments. I am glad you could use it.