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 DBAZine.com on how to create an external table to read the listener logs using simple SQL; but unfortunately DBAZine.com 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
8 comments:
Good Stuff :) but I also wonder how you could make use of the listener logs to diagnose a network performance issue..
@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.
Great job Arup, in the last month i'm very busy on a trouble DB-client connection, your advise are always amazing.
bye
Alberto
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
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!
-Lisa
I came after a long time,i glad to read such post you did a great work its impressing thanks i will keep visiting.
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!
@Jon Thanks for the compliments. I am glad you could use it.
Post a Comment