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...

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.