Oracle Database Listener.log Analize

Hello,

We are creating a new listener log file from listener.log file.

ErmanTESTDB:/oracle/grid/diag/tnslsnr/ErmanTESTDB/listener/trace#cp listener.log a_listener.log

 

1. create a new directory for listener.log file.

create directory erman_log_dir as '/oracle/grid/diag/tnslsnr/alioth/listener/trace';

2. create a new table for listener.log file
create table erman_log
(
log_date date,
connect_string varchar2(300),
protocol_info varchar2(300),
action varchar2(15),
service_name varchar2(15),
return_code number(10)
)
organization external (
type oracle_loader
default directory erman_log_dir
access parameters
(
records delimited by newline
nobadfile
nologfile
nodiscardfile
fields terminated by "*" lrtrim
missing field values are null
(
log_date char(30) date_format
date mask "DD-MON-YYYY HH24:MI:SS",
connect_string,
protocol_info,
action,
service_name,
return_code
)
)
location (erman_log_dir :'a_listener.log')
)
reject limit unlimited
/

3. Create a new function for parse_listener_log_line.
create or replace function parse_listener_log_line
(
p_in varchar2,
p_param in varchar2
)
return varchar2
as
l_begin number(3);
l_end number(3);
 l_val varchar2(2000);
 begin
 if p_param not in (
 'SID',
 'SERVICE_NAME',
 'PROGRAM',
 'SERVICE',
 'HOST',
 'USER',
 'PROTOCOL',
 'TYPE',
 'METHOD',
 'RETRIES',
 'DELAY',
 'PORT',
 'COMMAND'
 ) then
 raise_application_error (-20001,'Invalid Parameter Value'||p_param);
 end if;
 l_begin := instr (upper(p_in), '('||p_param||'=');
 l_begin := instr (upper(p_in), '=', l_begin);
 l_end := instr (upper(p_in), ')', l_begin);
 l_val := substr (p_in, l_begin+1, l_end - l_begin - 1);
 return l_val;
 end;

Now you can see the oracle database and all the host computers connected to it

select parse_listener_log_line(connect_string,'HOST') host, count(1) cnt
from erman_log
group by parse_listener_log_line(connect_string,'HOST');

Output :
blogddd.PNG

Hits: 12

Leave a Reply

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