Check for non-successful connection attempts in listener.log

Sometimes you might want to know if any unauthorized person or hackers trying to connect to the database. Often you see hackers target port 1521 and sending random data garbage through the wire, listener initially accept the connection but closes the connection if it received incorrect data.

This is the example of successful connection:

[oracle@db]# grep "24-MAR" /opt/oracle/diag/tnslsnr/stagedevdb2/listener/trace/listener.log | awk '{ if ( $NF != 0 ) print $0 }'

24-MAR-2016 12:13:31 * (CONNECT_DATA=(SID=TESTON)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wildfly))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.76.108)(PORT=55345)) * establish * TESTON * 0

24-MAR-2016 12:13:32 * (CONNECT_DATA=(SID=TESTON)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wildfly))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.76.108)(PORT=55346)) * establish * TESTON * 0

24-MAR-2016 12:13:32 * service_update * ipxtest * 0

24-MAR-2016 12:13:32 * (CONNECT_DATA=(SID=TESTON)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=wildfly))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.76.108)(PORT=55347)) * establish * TESTON * 0

If you have successful connection then end of the line it will show “0” which means successful completion. If you see any other number (Oracle error number from the TNS range >12000) that means the connection was not successful and you have to investigate then.

Using the following command you can see those line that does not end with “0”

[oracle@db]# grep "24-MAR" /opt/oracle/diag/tnslsnr/stagedevdb2/listener/trace/listener.log | awk '{ if ( $NF != 0 ) print $0 }'

24-MAR-2016 11:30:27 * (CONNECT_DATA=(SERVICE_NAME=TESTONN)(CID=(PROGRAM=sqlplus)(HOST=stagedevdb2.ipx.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.76.50)(PORT=59227)) * establish * TESTONN * 12514

After filtering unsuccessful connection you can investigate the reason based on the ORA error number for example in the above example ORA error is ORA-12514: TNS:listener does not currently know of service requested in connect descriptor