Listener Log Oracle 11g R2

listenerlogsoracle-11g-r2

I'm using Oracle 11gR2 in RHEL5. Now a days my application, during run time, is showing exception as below.

  Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

and 

 Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

It happens only during the peak time.

Where can I find the reason? Somewhere it might be logging all. Where is it? I checked the alert_db.log as well as log.xml inside the tnslsnr, but couldn't find anything relating this.

Best Answer

The documentation explains how to go about configuring listener tracing, but in summary:

Add:

TRACE_FILE_<LISTENERNAME>=listener.log
TRACE_DIRECTORY_<LISTERNAME>=/path_to_your_log_directory
TRACE_LEVEL_<LISTENERNAME>=user

You can up the TRACE_LEVEL to admin or support if you need more information, but be aware that they generate increasing amounts of output.

Your issue could be due to either a DB not being able to service a request due to the maximum number of processes being reached (check the alert log), a lack of resources on the box (are kernel parameters set correctly?) or an outright DB crash.

Related Question