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

Today in a multiple instance server i configured a new instance on Windows platform and  i encounter next problem when i try connect from my laptop:

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 – Production on Sun Aug 26 09:21:56 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.

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

Cause: The process of handing off a client connection to another process failed.

Action: Turn on listener tracing and re-execute the operation. Verify that the listener
and database instance are properly configured for direct handoff. If problem persists, call Oracle Support

Checking alertlog:
ORA-00020: maximum number of processes (150) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process m000 submission failed with error = 20
Thu Apr 24 10:09:21 2014
Process m000 submission failed with error = 20
One solution is install more memory in server, another is tuning memory of other instances on the server, there multiple ways to solve this issue, depends why this ORA is caused,  a quick fix is increase the number of processes.

SQL> show parameter processes;
NAME TYPE VALUE
——– ——- ———
processes integer 150

SQL> alter system set processes=300 scope=spfile;
SQL> shutdown immediate;
SQL> startup;
SQL> show parameter processes;
NAME TYPE VALUE
——– ——- ———
processes integer 300

I found that some people cant fix this changing processes parameter, not all lost, workarround exist :-), configure in your listener.ora and restart listener:

set parameter to DIRECT_HANDOFF_TTC_LISTENER=OFF

That is because server may need of a swap memory to disk as it runs out of memory space.  Sometimes,  Oracle processes can take up alot of memory.

Some other fix for this problem,  depending shared/dedicated server enviroment:

In a shared server environment . 

Shutdown the dispatcher and add new dispatchers.
SQL> show parameter dispatchers
SQL> select name from v$dispatcher;
SQL> alter system shutdown immediate ‘D001’;
Add new dispatcher 
SQL> alter system set DISPATCHERS = ‘(protocol=tcp)(dispatchers=4)’;

In dedicated Server, PGA is fully allocated for all processes.Check PGA memory allocation.

SQL> SELECT * FROM V$PROCESS ORDER BY PGA_ALLOC_MEM DESC

Kill unnecessary Sessions for freed PGA memory 

 

 

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s