关于Listener Log的信息的深度挖掘

1. 创建一个目录,以创建外部表,目录指向Listener_Log的目录

create directory LISTENER_LOG_DIR 
as 'D:\oracle\ora92\network\log' 
/

2. 创建一个完全读取Listener Log的外部表

create table full_listener_log
(
  line varchar2(4000)
)
organization external (
  type oracle_loader  
  default directory LISTENER_LOG_DIR  
  access parameters (
     records delimited by newline     
     nobadfile      
     nologfile      
     nodiscardfile  
  )  
  location ('listener.log')
)
reject limit unlimited
/

3. 查询该外部表,直接读取Listener Log信息

SQL> select * from full_listener_log;

LINE
--------------------------------------------------------------------------------
TNSLSNR for 64-bit Windows: Version 9.2.0.2.0 - Production on 26-SEP-2007 14:26:
Copyright (c) 1991
System parameter file is d:\oracle\ora92\network\admin\listener.ora
Log messages written to d:\oracle\ora92\network\log\listener.log
Trace information written to d:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0
Started with pid=1904
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MSLIATest.msl.cn)(PORT=1
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
26-SEP-2007 14:26:18 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=cas))(COMMAND=s
TNSLSNR for 64-bit Windows: Version 9.2.0.8.0 - Production on 26-SEP-2007 15:44:
Copyright (c) 1991
System parameter file is d:\oracle\ora92\network\admin\listener.ora
Log messages written to d:\oracle\ora92\network\log\listener.log
Trace information written to d:\oracle\ora92\network\trace\listener.trc
Trace level is currently 0
Started with pid=4032
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MSLIATest.msl.cn)(PORT=1

4. 也可以进行条件查询

SQL> select * from full_listener_log
  2  where line like 'Log messages written%'
  3  /

LINE
--------------------------------------------------------------------------------
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log
Log messages written to d:\oracle\ora92\network\log\listener.log

5. 创建另一个外部表,以得到Listener Log的分类明显信息

create table listener_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 LISTENER_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 ('listener.log')
)
reject limit unlimited
/

6. 创建信息挖掘函数

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; 
--You search for the presence of this parameter, note the position of the parentheses and the equality sign, and determine the position of the value of the paramet
    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;
/

7. 挖掘连接用户信息

SQL> select parse_listener_log_line(connect_string,'USER')
  2  from listener_log;

PARSE_LISTENER_LOG_LINE(CONNEC
--------------------------------------------------------------------------------
acas
acas
acas
acas
Alex
backup
backup
......
Alex
Alex
Alex

8. 挖掘明细分类信息

SQL> col host format a20
SQL> col l_user format a20
SQL> col service format a15
SQL> col logdate format a20
SQL> select to_char(log_date,'mm/dd/yy hh24:mi:ss') logdate,
  2  parse_listener_log_line(connect_string,'HOST') host,
  3         parse_listener_log_line(connect_string,'USER') l_user,
  4         parse_listener_log_line(connect_string,'SERVICE') service
  5  from listener_log
  6  where parse_listener_log_line(connect_string, 'COMMAND') = 'stop';

LOGDATE              HOST                 L_USER               SERVICE
-------------------- -------------------- -------------------- ---------------

9. 挖掘连接程序统计信息

SQL> col program format a70
SQL> col cmt format 999,999
SQL> select parse_listener_log_line(connect_string,'PROGRAM') program,
  2         count(1) cnt
  3  from listener_log
  4  group by parse_listener_log_line(connect_string,'PROGRAM');

PROGRAM                                                                       CNT
---------------------------------------------------------------------- ----------
C:\Dev6i\BIN\IFDBG60.exe                                                       30
C:\Dev6i\BIN\PLUS80W.EXE                                                        1
C:\Dev6i\BIN\RWBLD60.EXE                                                       13
C:\Dev6i\BIN\RWRBE60.exe                                                       14
C:\Dev6i\BIN\RWRUN60.EXE                                                       11
C:\ORA6I\BIN\IFDBG60.exe                                                       31
C:\ORA6I\BIN\PLUS80W.EXE                                                        2
C:\ORA6I\BIN\RWBLD60.EXE                                                       20
C:\ORA6I\BIN\RWRBE60.exe                                                       44
.......
C:\ORA6I\BIN\RWRUN60.EXE                                                        8
C:\ORA6I\BIN\ifbld60.EXE                                                       15
C:\ORA6I\BIN\ifrun60.EXE                                                      304
C:\ORA6I\bin\IFDBG60.exe                                                       90
d:\ORANT\BIN\ifrun60.EXE                                                       34
d:\oracle\ora92\bin\IMP.EXE                                                    25
d:\oracle\ora92\bin\ORACLE.EXE                                                 13
d:\oracle\ora92\bin\nid.exe                                                     6
d:\oracle\ora92\bin\sqlplus.exe                                                32
d:\oracle\ora92\bin\sqlplusw.exe                                                7
                                                                            20367

10. Service Name使用情况

col sid format a15
select parse_listener_log_line(connect_string,'SID') sid, count(1) cnt
from listener_log
group by parse_listener_log_line(connect_string,'SID');

11. Service Names的使用数量

select name from v$services
minus
select distinct parse_listener_log_line(connect_string,'SERVICE_NAME')from listener_log;

select distinct parse_listener_log_line(connect_string,'SERVICE_NAME')  
from listener_log;

12. 跟踪Client机

col host format a40
select parse_listener_log_line(connect_string,'HOST') host, count(1) cnt
from listener_log
group by parse_listener_log_line(connect_string,'HOST');

select
   parse_listener_log_line(connect_string,'SERVICE_NAME') SN,   
   parse_listener_log_line(protocol_info,'HOST') host,   
   count(1) cnt
from listener_log
where
   parse_listener_log_line(connect_string,'HOST') = '__jdbc__'
group by
   parse_listener_log_line(connect_string,'SERVICE_NAME'),   
   parse_listener_log_line(protocol_info,'HOST');

13. 跟踪Service Names

col sn format a15
col host format a45
col cnt format 999,999
select   parse_listener_log_line(connect_string,'SERVICE_NAME') SN,
   parse_listener_log_line(connect_string,'HOST') host,
   count(1) cnt
from listener_log
group by
   parse_listener_log_line(connect_string,'SERVICE_NAME'),   
   parse_listener_log_line(connect_string,'HOST');

14. DBA Service Names

col l_user format a15
col l_host format a30
col cnt format 999,999
select
   parse_listener_log_line(connect_string,'USER') l_user,   
   decode (
         parse_listener_log_line(connect_string,'HOST'),      
         '__jdbc__',      
         parse_listener_log_line(protocol_info,'HOST'),      
         parse_listener_log_line(connect_string,'HOST')   
   ) l_host,   
   count(1) cnt
from listener_log
where
   parse_listener_log_line(connect_string,'SERVICE_NAME') = 'DBA'
group by
   parse_listener_log_line(connect_string,'USER'),   
   decode (
         parse_listener_log_line(connect_string,'HOST'),      
         '__jdbc__',      
         parse_listener_log_line(protocol_info,'HOST'),      
         parse_listener_log_line(connect_string,'HOST')   
   )
order by 1,2,3;
Trackback

no comment untill now

Add your comment now

切换到手机版