分析ORACLE侦听日志

要分析一个数据库的故障，想了解侦听日志中的连接情况。

 

思路：
  把侦听日志文件加载到表中，用sql语句提取日志中信息。加载的方法可以使用sql*loader 或外部表

 

先把日志截取一部分
tail -100000 alert*.log > /tmp/lis.log
 
在一个临时库中创建表
-- drop table listener_log;

 

create table listener_log
(
  RQ        VARCHAR2(30),
  conn      VARCHAR2(400),
  address   VARCHAR2(400),
  action    VARCHAR2(20),
  service   VARCHAR2(20),
  retu_code VARCHAR2(10),
  host      VARCHAR2(50)
);
 
用 sql loader 加载侦听日志到表中

控制文件c.txt 内容：

LOAD DATA
infile 'd:\lis.log'
truncate
 INTO TABLE listener_log
when (conn <> ' service_update ') 
FIELDS TERMINATED BY "*"
trailing nullcols
(rq,conn,address,action,service,retu_code,host)

 

加载命令：
sqlldr scott/tiger control=d:\c.txt direct=true

 

--查询某天每小时连接数：
select substr(rq,-9,2) hh,count(0) cnt
from listener_log
where rq like '22-MAR-2011%'
group by substr(rq,-9,2)
order by hh;

 

用PL/SQL Developer可以生成柱状图，比较直观:

 

--查询每天的连接情况：
select substr(rq,1,6) dd,count(0) cnt
from listener_log
group by substr(rq,1,6)
order by dd;

 

--查看各 program 连接数：
select program, count(0)
  from (SELECT SUBSTR(conn,
        INSTR(conn, 'PROGRAM') + 8,
        INSTR(SUBSTR(conn, INSTR(conn, 'PROGRAM') + 8), ')') - 1) PROGRAM
          FROM listener_log
         WHERE conn LIKE '%PROGRAM%')
 group by program;

 

--查看各 host 的连接情况：

select host, count(0)
  from (SELECT SUBSTR(address,
        INSTR(address, 'HOST') + 5,
        INSTR(SUBSTR(address, INSTR(address, 'HOST') + 5), ')') - 1) host
          FROM listener_log
         WHERE conn LIKE '%HOST%')
 group by host;

 

 

结束。

 

 

 

 

 

以下内容无用：

--加载完后，可以将不要的数据进行剔除
select * from listener_log;

delete from listener_log

  where conn=' ping '

   or address=' status '

   or address =' services ';

 

commit;

 

--整理数据，将 host 列出来：
update listener_log set host=
 substr(address, instr(address, 'HOST=') + LENGTH('HOST='),
 instr(address, '(PORT=') - INSTR(address, 'HOST=')-LENGTH('(PORT=')) ;

 

commit;

 

--查看各 IP 连接情况：
select host, count(0) cnt
  from listener_log
 where rq like '24-MAR-2011%'
 group by host;