오라클 리스너(Listener) 로그 분석 Shell : 접속 IP목록

오라클 리스너 로그 파일을 분석하는 Shell을 만들어 사용중이다. Shell이라고 하기엔 너무 간단하다. awk를 이용한 한줄 명령어이다.

오라클 리스너의 로그 파일은 그냥 보면 복잡해 보이지만 다른 로그 파일과 마찬가지로 정형화된 포맷을 가지고 있다.
이에 대한 설명이 오라클 MOS문서 2322110.1에 잘 나와 있다.
정상적으로 connection이 이루어진 경우 오라클 로그에는 한줄이 기록되며 한줄의 내용은 6개의 항목으로 구성된다.

<날짜시간> * <data about incoming connection> * <data about outgoing connection> * <action> * <sid or service> * <Error number>

아래는 오라클 문서에 나와 있는 리스너 로그파일의 내용이다.
거의 대부분 리스너 로그 파일은 아래의 형태로 기록된다.
MOS문서에 따르면
It is not uncommon for PROGRAM or HOST in the incoming connection data to be blank, but we always log what HOST we provided the session in the outgoing connection data via an IP address.
내가 제대로 해석한 것이라면 outgoing connection data(3번째 항목이다)에 접속한 HOST를 항상(always) 기록한다고 나온다. 그러므로 3번째 항목을 이용하여 Connection을 맺은 Host를 확인 할 수 있다.

19-OCT-2017 20:31:34 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=foo12c_service.example.com)(CID=(PROGRAM=mycustomapp.exe)(HOST=appserver3.example.com)(USER=osuserfred))(INSTANCE_NAME=FOO12c)) * (ADDRESS=(PROTOCOL=tcp)(HOST=90.65.815.823)(PORT=42594)) * establish * foo12c_service.example.com * 0 19-OCT-2017 20:31:56 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=oracle))(SID=FOO12c)) * (ADDRESS=(PROTOCOL=tcp)(HOST=99.96.985.765)(PORT=38259)) * establish * FOO12c * 0 26-OCT-2017 07:23:56 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=foo12c_service.example.com)(CID=(PROGRAM=sqlplus)(HOST=appserver1.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=90.546.96.454)(PORT=29224)) * establish * foo12c_service.example.com * 0 26-OCT-2017 07:20:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=foo12c_service)(CID=(PROGRAM=sqlplus)(HOST=appserver2.example.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=90.546.96.451)(PORT=34886)) * establish * foo12c_service * 12514

이 내용을 이용하여 나의 DB에 접속한 Host를 식별할 수 있으며 어느 HOST에서 더 자주 접속하고 있는지 확인하고자 아래의 Shell을 만들었으며 수년째 잘 이용하고 있다.
실제 사이트에서는 하나의 DB에 수십개의 서버(100개가 넘는 경우도 많다)에서 DB에 접속하는 일이 아주 흔한 일이므로 아래의 Shell을 이용하여 각 DB별로 어느 서버에서 더 많이 Connection이 들어오는지 평소에 확인해 놓는 것이 아주 중요하다.

뿐만 아니라 차세대 사업등으로 DB이관을 할때에는 아래의 Shell은 유용한 정보를 제공해 줄 수 있었다.
차세대 사업이 일어나게 되면 AS-IS DB는 서비스를 오랜 기간 동안(보통 10년정도) 해오고 있는 것이므로 어떤 서비스가 있는지 담당 부서와 개발자들도 100% 파악하고 있지 못한 경우가 허다하다. 오랜기간 서비스하느라 담당자도 바뀌고 문서화도 완벽하게 현행화되어 있지 않은 것이다. 정상적인 서비스 중인데도 어디서 데이터들이 오고 가는지 모르는 경우를 숱하게 보아 왔다.
이런 상황에서는 DBA로서 리스너 로그를 이용하여 DB로 접속하고 있는 IP목록을 담당 부서에 제공해 줌으로써 이관에서 놓치는 경우를 많이 방지 할 수 있었다.

리스너 로그 파일은 보통 사이즈가 크기 때문에 원본 로그를 그대로 사용하면 오래 걸릴 수 있다. 그래도 결과는 정확하게 나온다. 이슈가 발생했거나 특정 날짜의 것만 분석하고자 한다면 리스너 로그 파일을 잘라서 파일을 생성하고 그 파일을 get_ip.sh로 분석하면 접속한 호스트를 효율적으로 분석할 수 있다.
물론 아래 쉘을 조금 수정하여 날짜를 파라미터로 받아 해볼수도 있겠다.^^

#!/bin/ksh
# get_ip.sh
#  
# This script print out which ip address connect to the database by analyzing listener log file
#
# HanGyo Jung 2019.01.09
# Usage : ./get_ip.sh <Listner Log File>
#
# -- Notice --
# This shell have to use only LISTENER LOG FILE
# if grep doesn't exist then use egrep
# egrep '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}' $1|awk -FHOST '{print $3}'|cut -d= -f2|cut -d\) -f1|sort -n -k1|uniq -c
# grep -E '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}' $1|awk -FHOST '{print $3}'|cut -d= -f2|cut -d\) -f1|sort -n -k1|uniq -c
#
# Lastest version (using awk)
# Output will be like below
#  265 HOST=XX.97.11.233
# 2135 HOST=XXX.16.162.20
# 2147 HOST=XXX.16.165.16
# 2926 HOST=XXX.16.164.16
# 6687 HOST=XXX.16.166.80
# 7772 HOST=XXX.16.72.4
# 9592 HOST=XXX.16.72.3
# 13274 HOST=XXX.16.160.141
# 65073 HOST=XXX.16.166.92
# 103666 HOST=XXX.16.71.4
# 208197 HOST=XXX.16.71.3
# 241216 HOST=XXX.16.72.1
# 337454 HOST=XXX.16.72.2
# 504773 HOST=XXX.16.71.2
# 890761 HOST=XXX.16.71.1
# 1043505 HOST=XXX.16.164.9


USAGE="
usage : ${0} <Listener Log File>
        ex) ${0} listener.log
"
ARG_COUNT=$#

if [ ${ARG_COUNT} -eq 0 ]
then
  echo "$USAGE"
  exit
fi

awk -F* '(NF==6) { print $3 }' $1|grep HOST|awk -F\( '{print $(NF-1)}'|cut -d\) -f1|sort |uniq -c|sort -n

참고 Oracle MOS Doc ID 2322110.1 (오라클 MOS 접속 계정 필요)
Interpretting the listener log entries for incoming connections

관련 글
2022.04.20 - [IT] - 리스너 로그에 기록되는 오류. * 12537 또는 12569