How to Make an In-Database listener.log File

Posted in: Technical Track

Ever wished the listener.log file was a table in the database? Wish no more! About three years ago, I sent this recipe in an email to my co-workers. Just recently, Shakir re-sent it after using the method in an emergency. Since it seems to have proved its value, I now offer it to our readers.

Using Oracle’s external tables, we can “query” the listener.log file:

Step 1: Create an “oracle directory” of where your file is:

create directory TNSLOG as '$ORACLE_HOME/network/log';

Step 2: Create an external table definition. Note that no data is loaded, just the method reading the file:

CREATE TABLE listener_log (
timestamp date,
connect_data VARCHAR2(2000),
protocol_info VARCHAR2(80),
EVENT VARCHAR2(200),
SID VARCHAR2(200),
RETURN_CODE number
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY TNSLOG
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
NOBADFILE NODISCARDFILE NOLOGFILE
FIELDS TERMINATED BY "*" LRTRIM (timestamp char date_format DATE mask "DD-MON-YYYY hh24:mi:ss", connect_data, protocol_info, event,sid,return_code)
)
LOCATION ('listener.log')
)
REJECT LIMIT UNLIMITED;

Step 3: Query the table directly or load it into an Oracle table for better performance and consistency. You can limit your date range here to load only the period you need:

create table listener_log2 as 
select TIMESTAMP,connect_data, event, sid, return_code,
substr(connect_data, instr(connect_data,'HOST=')+5, instr(connect_data,')', instr(connect_data,'HOST='))-instr(connect_data,'HOST=')-5) 
as host from listener_log where timestamp >= sysdate - interval '3' day;

Note that I extract the “host” to see where connections are coming from.

Step 4: Query grouping by hour. If you need say by minute, replace hh with mi.

select host,trunc(timestamp,'hh'),count(*)-count(nullif(return_code,0)) as success, count(nullif(return_code,0)) as failure,count(*) as total
from listener_log2 group by host,trunc(timestamp,'hh') order by 2,1;

This a quick and dirty way of doing it, but it serves its purpose. Feel free to improve on this (perhaps have it extract all the connect data, including “service updates” etc.) and post the improved version in a comment.

Interested in working with Christo? Schedule a tech call.

About the Author

An Oracle ACE with a deep understanding of databases, application memory, and input/output interactions, Christo is an expert at optimizing the performance of the most complex infrastructures. Methodical and efficiency-oriented, he equates the role of an ATCG Principal Consultant in many ways to that of a data analyst: both require a rigorous sifting-through of information to identify solutions to often large and complex problems. A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

6 Comments. Leave new

Arup Nanda posted a three-part series about mining the listener log here: http://www.dbazine.com/oracle/or-articles/nanda14/

Reply

Fabulos !!! Thank you very much for sharing with us this info.

Many thanks!!!!!!

Reply
deni yulianti
July 1, 2008 4:38 am

hello everybody……..

i’m deni yulianti…

i need your help to make the log file in mrtg in linux fedora core 5……

please help me…..
this’s my email: dencar_girl87@yahoo.com…..

thank you..

sincerely your,,,

deni yulianti

Reply

Dear Deni,

try to read this text first, I’m sure it will save you lot of time in future

http://www.catb.org/~esr/faqs/smart-questions.html

Reply
Andrey Goryunov
February 22, 2011 5:43 pm

Christo,

there “MISSING FIELD VALUES ARE NULL”
clause is missing :) which will help
to catch entries with not all fields filled.

Thanks,
Andrey

Reply
Inbraak detecteren in de Oracle listener log « Transfer Solutions
April 28, 2011 2:33 am

[…] om de listener log als external table in de database leesbaar te maken, zoals beschreven in dit artikel op de Pythian blog. Persoonlijk ben ik daar niet zo’n voorstander van, want als de database bereikt kan worden […]

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *