Connexion fails with the error : TNS-12518: TNS:listener could not hand off client connection
One of the following errors could also appear :
ORA-12547 / TNS-12547: lost contact
ORA-12537 / TNS-12537: connection closed
ORA-3135 / TNS-3135 : connection lost contact
ORA-3113 / TNS-3113 : end-of-file on communication channel
ORA-3106 / TNS-3106 : fatal two-task communication protocol error
ORA -3136 / TNS-3136 : WARNING inbound connection timed out
ORA-12535 / TNS-12535: TNS:operation timed out
ORA-12170 / TNS-12170: Connect timeout occurred
ORA-12637 / TNS-12637: Text: Packet receive failed
memory consumption and/or high load
In general ORA-12518 occurs if there is a resource problem , may be on the listener side or on the Database server.
It indicates a condition where the process is not able to make any more memory allocations either due to non-availability of resource or the process has reached the 32 bit (VM Size) limit
1- Timer issues causing abnormal threads :
Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application.
From Oracle base version 10.2.0.1 onwards the default value of INBOUND_CONNECT_TIMEOUT_<listenername> is 60 seconds. For previous releases it is zero by default.
a) Please add following parameter in listener.ora
INBOUND_CONNECT_TIMEOUT_<listener_name> = 0
For example, if the listener name is listenerTest, then:
INBOUND_CONNECT_TIMEOUT_listenerTest = 0
b) Add the following parameter to the sqlnet.ora file on the Database server
SQLNET.INBOUND_CONNECT_TIMEOUT = 0
The listener and database will need to be restarted after these changes.
These parameters are set on the SERVER side:
Note: This timeout restriction was introduced to combat Denial of Service (DoS) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.
Fore more info/setting on inbound connection time out, check the following note :
Note 465043.1 - Troubleshooting ORA - 3136 WARNING Inbound Connection Timed Out
2 - Lack of available memory :
Increase memory and swap in the system
3 - Less of processes in the database,:
Increase processes parameter if using dedicated connexion (init.ora / spfile file)
In unix, adjust resource limits to increase the number of max processes allowed.
4 - Set /3GB option :
Switch in the BOOT.INI file to enable the 4GB feature.
This allows a process to address 3GB and reserving 1GB for the kernel.
5- Tune SGA / shared_pool_size :
Reduce the SGA size to a reasonable figure which allows the user process to have enough memory to run.
Decrease the size of the database SGA by reducing the PGA_AGGREGATE_TARGET or SGA_MAX_SIZE or both.
The amount of memory for a session depends upon PGA_AGGREGATE_TARGET.
If PGA_AGGREGATE_TARGET is set to a very small value ,Oracle session may exceed this value.
6 - Set or Tune MTS :
Configure MTS or increase the number of DISPATCHERS (if Shared Server is being used).
7- If the listener crash with a core dump and the ora-12518 appears in the log, then you are probably hitting Bug 6139856 due to heavy load or out of memory.
For more detail and solutions, see :
Note 549932.1 - TNS Listener Crash with Core dump