Author Archives: SandeepSingh

Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

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;