DBA Sensation

September 24, 2008

ORA-12516 Error TNS:listener could not find available handler with matching protocol stack

Filed under: [client related] — zhefeng @ 3:01 pm

today when a user was doing heavy data injection against our 10g RAC database, got error “ORA-12516, TNS:listener could not find available handler with matching protocol stack”.

It looks like the databases was rejecting the connections. According to metalink, by increase the “processes” parameters should resolve this problem.

#Step1: take a look at the processes limition

select * from gv$resource_limit;

check the “MAX_UTILIZATION” (the peak value) and “INITIAL_ALLOCATION” (your current setting) for processes and sessions.

In my case, sometime the maximum of processes has been reached.

#Step2: increase the parameter from 150 (default) to 300

sql>alter system set processes=300 scope=spfile;

#Step3: reboot the database to let parameter taking effect.

SQL> show parameters processes

NAME                                 TYPE        VALUE
———————————— ———– ——————————

processes                            integer     300

After adjusted processes, sessions was automatically adjusted to 335 by system.

SQL> show parameters sessions

NAME                                 TYPE        VALUE
———————————— ———– ——————————

sessions                             integer     335

Problem resolved with no more complains.

P.S. some people is sugguesting to use this formula to decide the value of processes.

User connections = processes*10%*shared_server

1 Comment »

  1. I can’t even login to sqlplus with sys or any other user….

    Comment by aalap — April 4, 2012 @ 12:59 pm

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: