通过 shell 查找 Oracle 监听日志中所有 IPv4 地址

数据运维 2024-04-03 Escape 手机阅读

由于需要点对点访问数据库，那么数据库服务器则需要开启防火墙白名单策略。我们可以通过 iptables --list 命令查看操作系统防火墙白名单。那么有哪些访问数据库的 IPv4 地址呢？这个会记录在 Oracle 的监听日志 listener.log 中。

————————————————————————————
微信公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————

那么我们可以通过编写 shell 脚本通过正则表达式将监听日志中的 IPv4 都全部取出来。因为监听日志中有多种不同的连接类型，我这里是一测试环境只有三类，第一类是通过 JDBC 访问，还有一类是通过客户端访问的，另一类是 Oracle 自身通过 SERVER=DEDICATED 访问的。我这里只有 Oracle 19c 的版本，不知道 Oracle 版本不同是不是也不一样，这个大家自行查看测试。

22-MAR-2024 09:50:47 * (CONNECT_DATA=(CID=(PROGRAM=newdt)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=jiekexustb)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.75.22)(PORT=49126)) * establish * jiekexustb * 0 2024-03-22T09:51:10.343176+08:00 22-MAR-2024 09:51:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=jiekexustb)(CID=(PROGRAM=oracle)(HOST=t4-rac19c-72)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.75.23)(PORT=44953)) * establish * jiekexustb * 0 31-MAR-2024 23:46:55 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=tableau))(SERVER=DEDICATED)(SERVICE_NAME=jiekexustb)(CONNECTION_ID=yqYBVtTjS+elFTg4vJlLkQ==)) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.75.155)(PORT=37702)) * establish * jiekexustb * 0

单机环境

--查看监听日志位置 su - oracle lsnrctl status

cat $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener/trace/listener.log |sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p'|awk '{print $10}'|awk -F "=" '{print $4}'|awk -F")" '{print $1}'|grep -v ^$ |sort -u > iptables_rule_ip.txt cat $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener/trace/listener.log |sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p'|awk '{print $6}' |awk -F "=" '{print $4}'|awk -F")" '{print $1}'|grep -v ^$ |sort -u >> iptables_rule_ip.txt if [ $? = 0 ];then awk '{count[$1]++;} END {for (i in count) {print i}}' iptables_rule_ip.txt|sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p'|while read line do echo "firewall-cmd --permanent --add-rich-rule='rule family=\"ipv4\" source address=\"$line\" port protocol="tcp" port="11521" accept'" done fi cat iptables_rule_ip.txt

cat $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener/trace/listener.log |sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p'|awk '{print $10}'|awk -F "=" '{print $4}'|awk -F")" '{print $1}'|grep -v ^$ |sort -u

这段 Shell 命令链主要用于从 Oracle 监听器的日志文件中提取并过滤出唯一的 IPv4 地址。命令逐级解析和操作流程如下：

| cat $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener/trace/listener.log:


    使用 cat 命令读取 Oracle 监听器日志文件内容。
| sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p':

    通过管道 (|) 将日志内容传递给sed命令，并启用静默模式 -n，只打印匹配IPv4地址格式的行。
| awk '{print $10}':

    对匹配到的行进一步处理，仅打印每行的第 10 个字段（按照空格或制表符分隔）。
| awk -F "=" '{print $4}':

    再次使用 awk，但这次以等号(=)作为字段分隔符，打印出第 4 个字段的内容。
| awk -F ")" '{print $1}':

    继续使用 awk，这次以右括号)作为分隔符，打印出第一个字段。
| grep -v ^$:

    使用 grep 命令排除所有空行（即不包含任何字符的行）。

| sort -u: 最后，通过 sort 命令对输出进行排序，并使用 -u 选项去除重复项，从而得到唯一列表的IPv4地址。

sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p'

这是一个在 Unix 或 Linux 环境下使用的命令，使用了 sed（流编辑器）对输入的文本进行处理。具体解释如下：

sed -n：sed 命令默认会打印每一行处理后的结果，这里的 -n 参数表示静默模式，即只打印经过命令匹配且要求打印的行。

/pattern/p：这是 sed 的命令格式，其中 pattern 是正则表达式，p 表示打印。当某一行与给定的正则表达式 pattern 匹配时，这一行会被打印出来。

/[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}/ 是一个正则表达式，它用于匹配符合 IPv4 地址格式的字符串，也就是类似于 “xxx.xxx.xxx.xxx” 的形式，其中每个 “xxx” 都代表 0 到 255 之间的数字。

因此，整个命令的作用是：对于输入的文本，只打印出其中的 IPv4 地址。

如果监听日志格式不一样，有可能就会漏掉一部分 IP 地址，早上在我的微信公众号上发布的这个内容，有个朋友 @rs6k.xy 说匹配的不精确。嗯是的，确实是不精确，因为我打出来的结果里面还有个别用户名，但是没想到会漏掉。所以，他提供了另一个 shell 脚本，更简单高效。

egrep -i "CONNECT_DATA.*establish.* 0$" $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener/trace/listener.log | sed -r 's/.*HOST=([[:digit:]\.]+).*/\1/' | sort -uV

这个脚本的大概意思为：

使用增强型grep（egrep）命令搜索Oracle监听器日志文件，-i 参数表示不区分大小写。


    正则表达式 "CONNECT_DATA.*establish.* 0$" 会匹配任何包含 "CONNECT_DATA" 且后面紧接着任何字符（. *），接着是 "establish" 和任意字符，最后以数字“0”结尾的行，这通常对应于监听器日志中关于客户端连接建立的条目。
    sed -r：使用扩展正则表达式，这样可以在正则表达式中使用更简洁的元字符和量词。
    s：这是sed中的替换命令，其基本格式为s/old_pattern/new_pattern/，在这里用于查找并替换匹配的文本。
    .*HOST=：这部分匹配任何字符（.）任意次数（*），直到遇到 "HOST="。
    ([[:digit:]\.]+)：这部分是一个捕获组（由圆括号包围），匹配一个或多个数字（[[:digit:]]）和点（\.），这实际上是为了匹配IPv4地址。捕获组的内容会被记住，后续可以通过\1引用。
    .*：再次匹配任何字符任意次数，直到行尾，这表示在 "HOST=" 后面可以有任意其他内容。
    /\1/：这是替换的内容，\1 引用了前面捕获的第一个括号内的内容，也就是IPv4地址。这意味着只要找到 "HOST=" 后面跟着的IPv4地址，就会用这个IPv4地址替换掉整行内容。
    sort 命令对输出进行排序，-u 参数表示去除重复行，确保输出值唯一，-V 参数（如果支持的话）按照版本号排序，对于 IP 地址而言，这通常是按照数字顺序排序。

综上所述，这个 shell 命令会从每一行中提取出以 "HOST=" 开始的 IPv4 地址，并把整行内容替换为这个 IPv4 地址。在处理 Oracle 监听器文件或其他配置文件时，这个命令可以用来快速提取主机地址并去重排序这些 IP 地址。

如下是一个例子：

jiekexuadg:/u01/app/oracle/diag/tnslsnr/jiekexuadg/listener/trace(jiekeadg)$ egrep -i "CONNECT_DATA.*establish.* 0$" $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener/trace/listener.log | sed -r 's/.*HOST=([[:digit:]\.]+).*/\1/' | sort -uV > iptables_rule_ip.txt jiekexuadg:/u01/app/oracle/diag/tnslsnr/jiekexuadg/listener/trace(jiekeadg)$ if [ $? = 0 ];then awk '{count[$1]++;} END {for (i in count) {print i}}' ![20230831151f2d4bba0447a59a865a8b44fbc41e.png](https://oss-emcsprod-public.modb.pro/image/editor/20240403-86cb279f-7c3f-4489-9880-34d1458d2c5c.png)iptables_rule_ip.txt|sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p'|while read line do echo "firewall-cmd --permanent --add-rich-rule='rule family=\"ipv4\" source address=\"$line\" port protocol="tcp" port="11523" accept'" done fi firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.75.36" port protocol=tcp port=11523 accept' firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.75.155" port protocol=tcp port=11523 accept' firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.75.241" port protocol=tcp port=11523 accept' firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.75.225" port protocol=tcp port=11523 accept' firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.75.41" port protocol=tcp port=11523 accept' firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.75.43" port protocol=tcp port=11523 accept' firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.75.135" port protocol=tcp port=11523 accept' firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.75.181" port protocol=tcp port=11523 accept' firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.75.136" port protocol=tcp port=11523 accept' jiekexuadg:/u01/app/oracle/diag/tnslsnr/jiekexuadg/listener/trace(jiekeadg)$ cat iptables_rule_ip.txt 192.168.75.36 192.168.75.41 192.168.75.43 192.168.75.135 192.168.75.136 192.168.75.155 192.168.75.181 192.168.75.225 192.168.75.241

RAC 环境

从 RAC 主库 LISTENER_SCAN1 监听查看连接 IP

查看 scan 监听位置

su - grid


$lsnrctl status LISTENER_SCAN1

$cd $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener_scan1/trace

因为 Oracle 19c 对于监听日志过大的问题进行了优化，当监听日志达到一定大小后就会被切割，生成最新的 listener_scan1.log，历史的日志则会被切分到 listener_scan1_1.log，依次类推则日志中会出现 listener_scan1_22.log，所以查找 IP 时还需要遍历这些日志。

egrep -i "CONNECT_DATA.*establish.* 0$" $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener_scan1/trace/listener_scan1.log | sed -r 's/.*HOST=([[:digit:]\.]+).*/\1/' | sort -uV > iptables_rule_ip2.txt


egrep -i "CONNECT_DATA.*establish.* 0$" $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener_scan1/trace/listener_scan1_[1-22].log | sed -r 's/.*HOST=([[:digit:]\.]+).*/\1/' | sort -uV >>  iptables_rule_ip2.txt
--生成添加防火墙白名单命令

if [ $? = 0 ];then

    awk '{count[$1]++;} END {for (i in count) {print i}}' iptables_rule_ip2.txt|sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p'|while read line

    do

    echo "firewall-cmd --permanent --add-rich-rule='rule family=\"ipv4\" source address=\"$line\" port protocol="tcp" port="11521" accept'"

    done

fi

cat iptables_rule_ip2.txt

另外，LISTENER_SCAN 有时可能运行在另一个节点，所以以上 shell 需要在节点 2 的 grid 用户下也要运行一次，将生成的 all_ip2.txt 和节点 1 的合并后去重，生成最终需要的 IP 地址信息。如果 RAC 有三、五个 LISTENER_SCAN 则需要去各个目录下查找，相对而言还是比较麻烦的。

cat iptables_rule_ip2.txt >> iptables_rule_ip1.txt cat iptables_rule_ip1.txt | sort -u > iptables_rule_ip.txt

如果还有部分应用或者客户端直接连接 vip 而不是 scan ip 则还需要当成单机环境去本地监听日志里查找，最终合并到 iptables_rule_ip.txt 文件。

$ lsnrctl status | grep "Listener Log File" Listener Log File /u01/app/19.0.0/grid/network/log/listener.log

cat /u01/app/19.0.0/grid/network/log/listener.log |sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p'|awk '{print $10}'|awk -F "=" '{print $4}'|awk -F")" '{print $1}'|grep -v ^$ |sort -u > iptables_rule_ip0.txt cat /u01/app/19.0.0/grid/network/log/listener.log |sed -n '/[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}/p'|awk '{print $6}' |awk -F "=" '{print $4}'|awk -F")" '{print $1}'|grep -v ^$ |sort -u >> iptables_rule_ip0.txt

将上面 echo 出来的信息添加到防火墙白名单中即可，也可查看 iptables_rule_ip.txt 文件中的 IPv4 地址系统工程师自行编辑防火墙白名单。

其他相关命令

Linux 操作系统下查看历史来自某 IP 的连接时间和连接次数

cd $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener/trace grep "HOST=.*establish.*\* 0" listener.log | awk -F'*' '{match($3,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/); ip = substr($3,RSTART,RLENGTH);cnt[ip]+=1;last[ip]=$1;}END {for (i in cnt) printf "%-16s %9s %19s\n",i,cnt[i],last[i];}' | sort -k 1|grep 192.168.127.121 192.168.127.121 7 23-MAR-2024 00:59:58

Linux 下查看数据库有哪些 IP 连接进来

netstat -anop | grep ESTABLISHED | awk '$4 ~/:1521/'

全天每小时每个 IP 请求数

fgrep "02-APR-2024" listener.log|fgrep "establish"|awk -F* '{print $1 " "$3}'|awk -F= '{ print $1 " " $4}'|sed -e 's/¡$//g'| awk '{print $1 " " $2 " " $4}'|cut -b-14,21- |sort |uniq -c

某个时间段 IP 连接次数

for((i=1;i