Check for non-successful connection attempts in listener.log

This could become a regular question from your security team: can you find out if someone tried to mess with the listener when trying to connect? Often you see hackers target port 1521 and sending random data and/or garbage over the wire. The listener initially accepts the connection but closes it when it doesn’t receive data it expects.

Let’s assume you need to find if there were any unsuccessful connection entries in the listener.log for a given day. First of all-how do they have to look if they are successful? Typical entries are as follows:

1 08-FEB-2010 04:49:54 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=testserv) *
   (ADDRESS=(PROTOCOL=tcp)(HOST=oracleserver)(PORT=4307)) * establish * testserv * 0
2 08-FEB-2010 04:49:55 * service_update * dev1 * 0
3 08-FEB-2010 04:49:57 * service_update * dev3 * 0

The important bit is at the end-the “0” means “normal, successful completion”. If there is a problem, you would therefore assume there is an Oracle error number from the TNS range (>12000).

Awk is the swiss army knife to find such results, and this is how you could use it (apologies in advance for my poor command of awk-if you know a better way please let me know!)

[oracle@server1 ~]$ grep "08-FEB" listener.log | awk  '{ if ( $NF != 0 ) print $0 }'

The built-in variable NF is the last of all the fields which are enumerated from $1. So in summary, this little snippet does the following:

  1. Find all lines for a given day (here: February 8th) in the $ORACLE_HOME/network/log/listener.log file
  2. Print the lines where the last field is not equal to 0

This can easily be wrapped up into a nagios check to be executed by NRPE-if in case of doubt: simplify (to quote Piet de Visser).

So next time you get output such as:

1 09-FEB-2010 16:25:56 *  * (ADDRESS=(PROTOCOL=tcp)(HOST=
   (PORT=3929)) * establish *  * 12525

… something might be wrong. The “oerr” command provides more information about a particular error code.

By the way this is gawk-3.1.5-14.el5 on RHEL 5.3.


5 thoughts on “Check for non-successful connection attempts in listener.log

  1. Don Seiler

    Great article! One small correction:

    Find all lines for a given day (here: February 8th) in the $ORACLE_HOME/network/log/listener.ora file

    I think you mean listener.log, not listener.ora. ;)

    1. Martin Post author

      Hi Don!

      Thanks for letting me know, you are of course right-I changed the line.



  2. Pingback: Blogroll Report 05/02/2009 – 12/02/2010 « Coskan’s Approach to Oracle

Comments are closed.