Many years ago, I found an article written by Arup Nanda, on mining the listener log file. If you have not read it, I encourage you to do it, you can find the article here.
After reading the article, I realized how useful the information could be, and the wheels in my brain started moving and thinking on how I could implement it in the environment I was working in. Since then I tried implementing it anywhere I go.
Today’s article will be focusing on why reviewing the listener.log file is important, and the next article will go into more detail about how I implemented it.
Have you ever looked at the listener.log file? Did you find it easy to navigate and extract information from it? Did you even know what to look for in the file?
Most DBAs don’t look regularly at the listener.log file, mostly because they are not aware of the value it provides. Another reason why they don’t look at it, is because time constraints.
If the process is not automated, then reading through thousands of lines is time consuming. DBAs get side tracked by other tasks and forget all about the listener.log file.
If you don’t read the log regularly, don’t sweat it, you are not the only one
.
Navigating and extracting information from the listener.log can be very boring, and tedious.
Overcoming these obstacles can be accomplished by automating the whole process, which will be the goal of the next articles.
Why is it important to review the listener.log?
- To identify who was trying to connect to the database. These attempts will be captured whether the connections succeed or not. This information is great when combined with the audit trail
- You can identify what service names are users using. Let’s say you want to retire the hrprd.dbaparadise service name, and you are not sure if users are actually using it. By monitoring the listener.log for a month, or a year, or as long as you feel comfortable doing so, you can identify if the service name is in use or not, and then take an informed decision on retiring it.
- You can identify what programs are accessing the database. This is great information to be aware of, especially during upgrades and migrations, when you need to know how when users are connecting to the database, what tools do they use? Are they using Excel, Web Logic, SQL Developer, perl, and so on. All this information is available in the listener.log file.
- You can identify what errors and the frequency of them are occurring during connections to the database. If a connection encounters an error, the error will be captured in the listener.log file.
- You can also identify the number of connections made through the listener during a day, to each database the listener is servicing.
The above five reasons should be enough in convincing you to want to implement a process to review the listener.log file.
Next week I’ll show you how I implemented this process.
