Monday, April 19, 2010

Tracing listener connections

We've hit an issue were clients are being disconnected from the database, but there are no error messages apart from ORA-03113 and ORA-03114 (which basically means "you have lost your connection to the database" - duh).
There are several odd things about these disconnects:

Idle connections aren't affected, so it's not a time-out issue.
Multiple PCs get disconnected at the same time, so it's not as if a session hits a profile limit and that gets killed.
Only one application is affected, no other uses have reported issues.
It's not limited to one geographical location.
It happens at (seemingly) random times.
It doesn't matter what process or screen the user is accessing.


I've set tracing on at the listener level to see if anything gets written there - there is nothing in the listener.log.
I originally set the level to ADMIN, but that generated 20Mb of trace in 20 minutes, so I dropped it to USER.

The line goes into the listener.ora file (find it by doing lsnrctl stat):

trace_level_listener=USER

The listener then needs to be reloaded to put it into effect:

lsnrctl reload

lsnrctl stat

will show the trace file location:

Listener Trace File       /u01/app/oracle/product/9.2.0/network/trace/listener.trc

Since this is going to get large, I have put in a cron job to cycle it every hour. The drop outs tend to occur overnight.

This is the script that compresses and cycles the log:

#!/bin/ksh
export tstamp=`date +%d_%m_%Y_%H%M`
     cd /u01/app/oracle/product/9.2.0/network/trace
     if [[ -f listener.trc ]] ; then
         # copy current file to time-stamped one
         cp listener.trc listener_${tstamp}.trc
         # Zero the trace file
         cat /dev/null > listener.trc
         # compress any  dated logs
         find . -name "listener_*.trc" -exec compress -f {} \;
     fi

No comments:

Post a Comment