Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

ORA-12518: TNS:listener could not hand off client connection

Leave a reply

Error:

ORA-12518: TNS:listener could not hand off client connection

An error occurs when the Oracle listener fails to send a client connection request due to process limits, full connection pools, or configuration issues.

Causes:

The main cause of this problem is that process limit exhaustion, which occurs when the number of concurrently running processes exceeds the predefined maximum limit set by the database parameter processes.

Solution: Increase Process Limits

Check the number of process and session 

SELECT resource_name, current_utilization, max_utilization,
       initial_allocation, limit_value
FROM v$resource_limit
WHERE resource_name IN ('processes', 'sessions');
-- Current utilization means currently utilized processes
-- Max utilization show data how much maximum utilized from last start of database
-- Limit the value set for the parameter after this value Oracle start giving error

Increase the value of process parameter 

show paremeter processes
show paramter sessions
-- If you increase the value of processes Oracle automatically calculate the value for sessions
-- and increase it.
-- Calculate recommended values
-- SESSIONS = PROCESSES * 1.1 + 5 (minimum)
-- Example: If PROCESSES = 1500, then SESSIONS = 1655
ALTER SYSTEM SET processes = 2000 SCOPE=SPFILE;
-- Restart required for these changes
SHUTDOWN IMMEDIATE;
STARTUP;
-- Verify changes
SHOW PARAMETER processes;
SHOW PARAMETER sessions;