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

Blog at WordPress.com.