Listern Log Mining

create or replace directory listener_log_dir 
as '/opt/oracle/product/10.2.0/db_2/network/log';


drop table listener_log;
create table listener_log
(
   log_date      date,
   connect_data  varchar2(300),
   protocol_data varchar2(300),
   command       varchar2(20),
   service_name  varchar2(19),
   return_code   number(10)
)
organization external (
   type oracle_loader
   default directory DATA_PUMP_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_data,
          protocol_data,
          command,
          service_name,
          return_code
      )
   )
   location ('listener.log.crash.20100311')
)
reject limit unlimited
/





create or replace function extract_value
-- Used for Listener Log Monitoring
(
    p_in varchar2,
    p_param in varchar2
)
return varchar2
as
    l_begin     number(3);
    l_end       number(3);
    l_val       varchar2(2000);
begin
    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;
/

Count per hour

select
to_char(log_date,'mm/dd/yy hh24')||':00' logdate,
count(*)
from listener_log
-- where extract_value (connect_data, 'PROGRAM') like ('%ECOMETRY%')
group by to_char(log_date,'mm/dd/yy hh24')
order by 1 asc
/

Count per minute

select
to_char(log_date,'mm/dd/yy hh24:mi') logdate,
count(*)
from listener_log
where extract_value (connect_data, 'PROGRAM') like ('%ECOMETRY%')
group by to_char(log_date,'mm/dd/yy hh24:mi')
order by 1 asc
/

Gather all info from listener log

col host_ip for a15
col l_user format a20
col service format a30
col program for a43
col instance for a8
col logdate format a20
col host format a20
col RC format 99999
col service_name for a19
col port_num for a10
col host for a18
select to_char(log_date,'mm/dd/yy hh24:mi:ss')  logdate,
extract_value (connect_data,'HOST')             host,
extract_value (connect_data,'USER')             l_user,
extract_value (connect_data,'PROGRAM')          program,
extract_value (connect_data,'SERVICE_NAME')     service_name,
extract_value (connect_data,'INSTANCE_NAME')    instance,
extract_value (protocol_data,'HOST')            host_ip,
extract_value (protocol_data,'PORT')            port_num,
return_code                                     RC
from listener_log
where return_code <> 0
and to_char(log_date,'mm/dd/yy hh24:mi:ss') < '03/09/10 05:00:00'--where extract_value (protocol_data,'HOST') = '172.16.5.190
--and extract_value (connect_data,'INSTANCE_NAME') like '%xxx%'
--or extract_value (connect_data,'SERVICE_NAME') like '%xxx%'
--where extract_value (connect_data,'HOST') is not null
--and extract_value (connect_data,'USER') not in ('oracle')
--where extract_value (connect_data,'USER') like 'rfhandheld%'
--where extract_value (connect_data, 'PROGRAM') like ('%ECOMETRY%') or extract_value (connect_data, 'PROGRAM') like ('%w3wp.exe%')
--and  extract_value (connect_data,'HOST') = 'xxxx-45'
order by 1 asc
/