The need to check for Oracle Net connection errors is a routine check that the database administrator must do in case of possible database connection errors:
you may be sometimes frequently asked: "my application cannot (or could not) connect to the database ..." but the application is actually hiding the Oracle error message
(I found this is especially the case with Java code using JDBC).

listener log structure

The listener has its own log file that is very useful to check connexions errors as documented in Database Net Services Administrator's Guide.

The general syntax of the listener log entry is:

Timestamp * Connect Data [* Protocol Info] * Event [* SID | Service] * Return Code

The return code is:

  • 0 if the operation is succesfull
  • different from 0 is the operation has failed.

    • This simply means that this return code is the Oracle error message number returned by the listener to the client.

    For example following entry means that listener has returned ORA-12514: TNS:listener does not currently know of service requested in connect to client:

    26-AUG-2016 19:04:18 
* (CONNECT_DATA=(SERVICE_NAME=KO)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) 
* (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.20)(PORT=41291)) 
* establish 
* KO 
* 12514

    NB: to make listener log entries more readable in this article I have edited all entries so that each field is displayed on a separate line.

    However things can get a little bit more difficult if:

  • databases are consolidated on a single machine and use the same listener
  • applications connect (very) frequently to database instance(s)
  • listener log is not rotated for various reasons.

    • In above cases the listener log has a lot of entries and it can take some time to find manually connection errors.

    grep command

    You can use grep with an regular expression to:

    - filter connection requests with * e character string that can only match * establish expression

    - filter the return code that must have a least 2 digits (because 0 return code is always a single digit in listener log) at line end.

    Here is the grep command:

    grep -E '\* e.*[0-9]{2}

    Example:
    

    $ grep -E '\* e.*[0-9]{2}

    
This should work for Oracle 12.1, Oracle 11.2 and even Oracle 10.2 on all Linux platforms and also other Unix platforms.
    

    I wonder if there is any shorter command (excluding aliases) ?
    

    
If you have one please let me know by adding a comment.
    


    

    Example:
    

    

    
This should work for Oracle 12.1, Oracle 11.2 and even Oracle 10.2 on all Linux platforms and also other Unix platforms.
    

    I wonder if there is any shorter command (excluding aliases) ?
    

    
If you have one please let me know by adding a comment.
    
 /u01/app/oracle/diag/tnslsnr/hol/listener/trace/listener.log
26-AUG-2016 19:04:18 
* (CONNECT_DATA=(SERVICE_NAME=KO)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) 
* (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.20)(PORT=41291)) 
* establish 
* KO 
* 12514
26-AUG-2016 19:15:50 
* (CONNECT_DATA=(SERVICE_NAME=FTEX)(CID=(PROGRAM=sqlplus)(HOST=hol.localdomain)(USER=oracle))) 
* (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.20)(PORT=41327)) 
* establish 
* FTEX 
* 12514

    

    
This should work for Oracle 12.1, Oracle 11.2 and even Oracle 10.2 on all Linux platforms and also other Unix platforms.
    

    I wonder if there is any shorter command (excluding aliases) ?
    

    
If you have one please let me know by adding a comment.
    



    Example:
    

    

    
This should work for Oracle 12.1, Oracle 11.2 and even Oracle 10.2 on all Linux platforms and also other Unix platforms.
    

    I wonder if there is any shorter command (excluding aliases) ?
    

    
If you have one please let me know by adding a comment.