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
