Find Client IP from listener log

Some time we need to know who has made a connection attempt (successful/ unsuccessful ) because listener log is capturing all the connection attempts.

cd $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/<listener name>/trace

ls -ltr 

-rw-rw----. 1 oracle oracle 3733 May 29 10:22 listener.log

cat listener.log | grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}' | sort | uniq -c | sort -n

  

if you want to save the screen out to a file:

grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}' listener.log | sort | uniq -c | sort -n > listener_unique_ip.txt

Unique Attempt Counts	IP
1	10.1.**.39
4	10.1.**.77
33	10.1.**.76

Based on above result you can implement ACL in sqlnet.ora by tcp.invited_nodes.