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