오라클 19c 리스너 로그 집계 스크립트

OS 환경 : Oracle Linux 8.7 (64bit)

 

DB 환경 : Oracle Database 19.27.0.0

 

방법 : 오라클 19c 리스너 로그 집계 스크립트

오라클에서 리스너 로그는 너무 많이 쌓이기 때문에 이 로그를 vi로만 봐서는 정확하게 보기 어려움
아래 스크립트를 이용해 집계를 내면 한눈에 볼수 있음

 

 

get_listen_stats.sh 스크립트

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

#!/bin/bash   # ----------------------------------------------------------------------------- # 스크립트: Oracle Listener 로그 집계 스크립트 (날짜 형식 yyyy-mm-dd) # 사용법: sh get_listen_stats.sh [로그 파일 경로] # 예시: sh get_listen_stats.sh /app/oracle/diag/tnslsnr/ora19adg1/listener/trace/listener.log # -----------------------------------------------------------------------------   # --- 입력값 검증 --- if [ -z "$1" ]; then     echo "오류: 분석할 로그 파일 경로를 입력하세요." >&2     echo "사용법: $0 /경로/listener.log" >&2     exit 1 fi   LOG_FILE=$1   # --- 헤더 출력 --- echo -e "접속_횟수\t호스트명\t접속서버_IP_주소\t최초_접속_일시\t최종_접속_일시"   # --- AWK 메인 로직 --- awk ' # 스크립트 시작 전 한 번만 실행 BEGIN {      # 출력 필드 구분자는 탭(Tab)     OFS="\t"           # 월 이름(MMM)을 숫자(mm)로 변환하기 위한 매핑 배열 생성     months = "JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC"     n = split(months, m_arr, "|")     for (i = 1; i <= n; i++) {         # m_arr[1]="JAN", m_arr[2]="FEB", ...         # month_map["JAN"] = "01", month_map["FEB"] = "02", ...         month_map[m_arr[i]] = sprintf("%02d", i)     } }   # "CONNECT_DATA"와 "establish"가 포함된 라인만 처리 /CONNECT_DATA/ && /establish/ {     # --- 타임스탬프 변환 로직 (YYYY-MM-DD HH:MI:SS) ---     # $1 = 28-JUN-2010, $2 = 01:27:38          # $1을 "-" 기준으로 분리 (date_parts[1]=28, date_parts[2]=JUN, date_parts[3]=2010)     split($1, date_parts, "-")          day = sprintf("%02d", date_parts[1]) # "28" -> "28", "1" -> "01"     month_name = toupper(date_parts[2])  # "JUN" (혹시 소문자일까봐 대문자로)     year = date_parts[3]                 # "2010"          # 위에서 만든 month_map을 사용해 월 이름을 숫자로 변환     month_num = month_map[month_name]    # "JUN" -> "06"          # yyyy-mm-dd hh:mi:ss 형식으로 재조립     ts = year "-" month_num "-" day "_" $2     # --- 타임스탬프 변환 끝 ---       # 정규표현식으로 호스트명(CID의 HOST) 추출     match($0, /\(HOST=([^)]+)\)/, m_host)     hname = m_host[1]          # 정규표현식으로 IP(ADDRESS의 HOST) 추출     match($0, /\(ADDRESS=.*\(HOST=([^)]+)\)/, m_ip)     ip = m_ip[1]          # "호스트명||IP"를 고유 키로 사용     key = hname "||" ip          # 집계: 카운트 증가 및 정보 저장     count[key]++     hostname[key] = hname     ip_addr[key] = ip          # 최초 접속 일시 저장 (이미 저장된 값이 없으면)     if (first_ts[key] == "") {         first_ts[key] = ts     }     # 최종 접속 일시는 항상 덮어쓰기     last_ts[key] = ts }   # 모든 라인 처리가 끝난 후 실행 END {     # 저장된 모든 키에 대해 반복     for (key in count) {         # 집계된 결과 출력         print count[key], hostname[key], ip_addr[key], first_ts[key], last_ts[key]     } }' "$LOG_FILE" | sort -nr -k1

 

 

실행 권한 부여

1	$ chmod u+x get_listen_stats.sh

 

 

리스너 파일 복제(선택)

1	$ cp /app/oracle/diag/tnslsnr/ora19adg1/listener/trace/listener.log /home/oracle/listener.log_bak

 

 

실제 수행

1 2 3 4 5 6 7 8 9 10 11 12 13 14

$ sh get_listen_stats.sh /home/oracle/listener.log_bak 접속_횟수       호스트명        접속서버_IP_주소        최초_접속_일시  최종_접속_일시 910535  __jdbc__        192.168.1.113   2025-03-23_13:48:44     2025-04-10_13:26:39 377400  WINDOW5 192.168.1.174   2025-03-23_13:50:01     2025-04-10_06:31:16 1232    __jdbc__        192.168.1.91    2025-03-24_00:00:01     2025-04-10_13:00:04 755     __jdbc__        192.168.1.143   2025-03-28_22:49:25     2025-04-10_10:10:32 489     newdb1  192.168.1.171   2025-03-23_16:25:20     2025-04-10_13:25:12 84      VDI099  192.178.1.144   2025-03-27_16:13:16     2025-04-10_12:40:19 4       __jdbc__        192.168.1.114   2025-03-29_18:01:02     2025-03-29_18:32:22 4       8911-4557-Y054  192.168.64.28   2025-03-25_22:44:04     2025-04-06_22:42:07 3       NGDIC   192.168.1.215   2025-03-25_14:42:43     2025-04-04_01:19:09 1       VDI007  192.178.1.145   2025-03-23_14:13:06     2025-03-23_14:13:06 1       WOOR_ENV        192.168.128.19  2025-04-05_21:33:36     2025-04-05_21:33:36 1       devdb   192.168.1.234   2025-04-09_11:40:41     2025-04-09_11:40:41

잘 수행됨

 

 

위 내용 복사 후 엑셀에 붙여넣기도 가능함, 텍스트 나누기 후 탭+공백 선택 후 마침

 

 

참고1. 테스트시 2코어 환경에서 267mb 크기의 리스너로그를 집계할때 약 33초 정도 소요됨

 

 

참고2. 리스너로그 샘플

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

$ tail -300f listener.log Fri Mar 23 13:48:44 2025 23-MAR-2025 13:48:44 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34683)) * establish * NEWORA1 * 0 23-MAR-2025 13:48:46 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34702)) * establish * NEWORA1 * 0 23-MAR-2025 13:48:46 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34704)) * establish * NEWORA1 * 0 23-MAR-2025 13:48:46 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34707)) * establish * NEWORA1 * 0 23-MAR-2025 13:48:48 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34714)) * establish * NEWORA1 * 0 23-MAR-2025 13:48:48 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34715)) * establish * NEWORA1 * 0 23-MAR-2025 13:48:48 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34717)) * establish * NEWORA1 * 0 23-MAR-2025 13:48:52 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34750)) * establish * NEWORA1 * 0 Fri Mar 23 13:48:55 2025 23-MAR-2025 13:48:55 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34769)) * establish * NEWORA1 * 0 23-MAR-2025 13:48:59 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34803)) * establish * NEWORA1 * 0 23-MAR-2025 13:48:59 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=newdb3)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) * status * 0 23-MAR-2025 13:49:01 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34817)) * establish * NEWORA1 * 0 23-MAR-2025 13:49:01 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34820)) * establish * NEWORA1 * 0 23-MAR-2025 13:49:02 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34822)) * establish * NEWORA1 * 0 23-MAR-2025 13:49:03 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34829)) * establish * NEWORA1 * 0 23-MAR-2025 13:49:03 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34830)) * establish * NEWORA1 * 0 23-MAR-2025 13:49:03 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34833)) * establish * NEWORA1 * 0 23-MAR-2025 13:49:04 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=newerp))(SID=NEWORA1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.113)(PORT=34834)) * establish * NEWORA1 * 0

 

 

참조 :