Analyzing the Listener.log

A little bit more than a month ago I spoke at OSWOUG event (and wrote this post about it). In the event, Jared Still talked about free tools for Oracle database. He basically said that there is a lot of stuff out there on the internet, and if you can’t find what you need, then write it and publish. So I couldn’t find what I need, and I wrote it, and now I publish (thanks Jared).

When I checked the databases of one of my customers a while ago, I saw that their listener.log get large pretty quickly, so I wanted to investigate it. What I wanted to do is to analyze the log to see how many connection they have per day and from what application. I checked and couldn’t find such a tool (maybe there is, but I didn’t look hard enough). In any case, I decided to write something. I originally wrote something quick and dirty in bash (which I love), but after I wanted to change it I decided to write my first python code. It took me a few hours (as I had to lean python while writing), but here it is.

A few comments and limitations:

  1. As this is my first python code ever (and I’ve never been a real developer), if you know python, the code might seem like it was written by a grade 4 kid. Sorry about that
  2. It works for both windows and linux (yay!)
  3. It currently supports only the default listener (I use “lsnrctl status” to get the log file, without any listener name, and I have a couple of assumptions about the log name)
  4. It supports a non diag dest (the log file is *.log) and diag dest (if the log is alert/log.xml I replace it to trace/listener.log)
  5. If it can’t find a listener log line in the output of “lsnrctl status” you’ll get an error and the out put of “lsnrctl status”
  6. The script is probably far from being perfect, but it’s nice and easy. If you have any comments or additions, I’ll be happy to apply and publish them

What the script does and output:

  1. As I said, the script first find the listener log
  2. Then it analyzes the log and asks if you prefer a csv output or text in a table format
  3. It prints basic information (first and last dates in the log, and number of connections)
  4. The main part is a table of connections where the first column is the date, followed by a column for each application found in the log where the values in the table are the amount of connections. This is either formatted as a table (space padded) or as a csv to load to excel or whatever

Things that I wanted to do but didn’t (for no good reason other than time, I might do that someday):

  1. Write the output to a file instead of the screen
  2. Count the connection based on source host instead of program
  3. Support different aggregation (global, per week, per month…)
  4. Support any listener (when the listener name is provided) and/or simply provide a listener log file

[24-Oct-17 update]

I got a few comments about output examples. In fact, the original plan was to add a couple to the post, but for some reason I didn’t. So here are the examples, followed by the script:


C:\Work\Oracle\Scripts>python lsnr.py

Analyzing log: c:\oracle\diag\tnslsnr\liron-laptop\listener\trace\listener.log... done

Please choose from the following output options:
1. csv format
2. text table format
1

Connection Analysis
===================
First date in log: 2014-07-23
Last date in log: 2017-09-12
Number of connections in the log: 131

Connection distribution per day:
================================

Date ,JDBC Thin Client ,SQL Developer ,sqlplus.exe
2014-07-23 , ,6 ,
2014-07-31 , ,5 ,
2015-02-12 , ,3 ,
2015-10-16 , , ,4
2015-11-04 , , ,54
2015-11-06 , , ,16
2015-11-09 , , ,14
2015-11-10 , , ,8
2016-06-17 , ,1 ,
2016-06-24 ,5 , ,
2017-02-17 , ,5 ,
2017-04-28 , ,4 ,
2017-09-05 , ,4 ,
2017-09-10 , ,1 ,
2017-09-12 , ,1 ,


C:\Work\Oracle\Scripts>python lsnr.py

Analyzing log: c:\oracle\diag\tnslsnr\liron-laptop\listener\trace\listener.log... done

Please choose from the following output options:
1. csv format
2. text table format
2

Connection Analysis
===================
First date in log: 2014-07-23
Last date in log: 2017-09-12
Number of connections in the log: 131

Connection distribution per day:
================================

|========================== |========================== |========================== |========================== |
| Date                      | JDBC Thin Client          | SQL Developer             | sqlplus.exe               |
|========================== |========================== |========================== |========================== |
| 2014-07-23                |                           | 6                         |                           |
| 2014-07-31                |                           | 5                         |                           |
| 2015-02-12                |                           | 3                         |                           |
| 2015-10-16                |                           |                           | 4                         |
| 2015-11-04                |                           |                           | 54                        |
| 2015-11-06                |                           |                           | 16                        |
| 2015-11-09                |                           |                           | 14                        |
| 2015-11-10                |                           |                           | 8                         |
| 2016-06-17                |                           | 1                         |                           |
| 2016-06-24                | 5                         |                           |                           |
| 2017-02-17                |                           | 5                         |                           |
| 2017-04-28                |                           | 4                         |                           |
| 2017-09-05                |                           | 4                         |                           |
| 2017-09-10                |                           | 1                         |                           |
| 2017-09-12                |                           | 1                         |                           |
|========================== |========================== |========================== |========================== |

The code is on GitHub, you can find it here.

Advertisements

20 thoughts on “Analyzing the Listener.log

  1. Awesome … Very good script – The output which give per day number of connection established host,program etc.,

    Is there possible to get the count per minute in day ? can you please help us

    Like

    • Hi,
      If you’d like to have the aggregation per minute, you basically need to change the date key. It’s currently in the format DD-MM-YYYY, if you add hour and minute you’ll get what you want (you will need to adjust the printing as well).
      The date key format is in line 46, the printing in 91 and 110 (depends how you want to get the output, csv ot text table).
      I hope it helps.
      Liron

      Like

      • Thanks. I tried by assign format %d/%m/%Y/%H:%M:%S – Getting this error.

        Don’t know my OS is Red hat Linux. Is there anywhere can you help me?

        Traceback (most recent call last):
        File “./python1″, line 46, in
        line_datef = datetime.strptime(line_date,”%d/%m/%Y/%H:%M:%S”)
        File “/usr/lib64/python2.6/_strptime.py”, line 325, in _strptime
        (data_string, format))
        ValueError: time data ’12-SEP-2017′ does not match format ‘%d/%m/%Y/%H:%M:%S’

        Like

    • Hi Sarat,
      You’re right, I think I was wrong with the changes I gave you. I’ll try to find a few minutes to change the script and check it and I’ll send you an update.
      Liron

      Like

    • Hi,
      Here is the solution, perform the following changes:
      Change line 45 to:
      line_date = line.split(“*”)[0].rstrip()

      In line 46 the format should be: “%d-%b-%Y %H:%M:%S”
      In lines 77, 78, 91 and 110 change the format to: “%Y-%m-%d %H:%M”

      I hope it will work now.
      Liron

      Like

      • Still i am getting the same error.

        Traceback (most recent call last):
        File “listener_connect_check.py”, line 46, in ?
        line_datef = datetime.strptime(line_date,”%d-%b-%Y %H:%M:%S”)
        AttributeError: type object ‘datetime.datetime’ has no attribute ‘strptime’

        Like

  2. Hello,

    First, interesting script. Nice work. šŸ™‚
    I wanna ask how does script behave on some big log file (like 1 GB). How long does it takes to analyze some very big file?

    Like

  3. Very good script.
    But, you must configure your NLS_LANG variable to AMERICAN_AMERICA.
    The script does not run if you configure in FRENCH.

    Question : Is it possible to identify all running Listeners, and identify the Log for all of them ?

    Like

  4. Nice script. Any chance of you putting it into a regular GitHub repository, rather than a Gist? I’d like to enhance it just a bit to support non-default listener names, and would be happy to create a pull request for that purpose. I don’t believe GitHub exposes the ability to create PRs on Gists, however.

    I have, however, forked the Gist so that I can play with it a bit. šŸ™‚

    Like

    • Adric,
      Thanks a lot for offering that, it would be great.
      I don’t have GitHub user, so I need to find the time to create one and move the script. Will try to do that soon.

      Like

  5. @amitzil

    I’ve created a second Pull Request for your GitHub repository, to allow the script to gracefully handle the program name being unspecified. The original PR allows the logfile to be specified on the command line, making it possible to analyze the file on a different machine than the database server.

    Assuming that you’re happy with the changes, you should be able to approve them via the Pull Requests tab on GitHub (https://github.com/amitzilblog/Listener-analyzer/pulls). This will merge the changes into your repository, making them more easily available to any interested parties. This is completely up to you, naturally. šŸ™‚

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s