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
