Fairly clumsy title but nevertheless a cool solution.

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!)

$> 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:

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 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.36.135)
   (PORT=3929)) * establish * <unknown sid> * 12525

... something fishy might be going on. oerr ora <number> gives you more information about what happened.

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