I recently ran into an issue where I would intermittently get this Oracle error:
ORA-12516, TNS:listener could not find available handler with matching protocol stack
Nearly all of the search results for that error detailed the need to have the Oracle’s listener set up correctly. However, in my case the problem was not that the listener wasn’t set up correctly, but that the “processes” database variable needed to be increased. Note: no, it wasn’t an issue with the server code not releasing connections or anything. The problem crept up even when running SQL scripts from the command line :\ Unfortunately, the error message was very misleading.
So, as all of the Oracle user forum entries instructed me to do, I logged into the Oracle box as the
oracle user, edited
$ORACLE_HOME/dbs/init.ora to update the
processes value from 50 to 100, and restarted Oracle.
lsnrctl service SERVICENAME showed that the connection turned to the blocked state (from a ready state) at around 50 processes. “That’s strange,” I thought, so I double-checked
init.ora, bounced the server, and ran the test.
ORA-12516 error. You’ve heard of the ‘definition of insanity,’ right? 😉
OK, long story short, not being a DBA, I didn’t realize that–in some cases–the init.ora file doesn’t appear to be used. To find out if that’s the case, run
sqlplus / as sysdba, then type the following:
SQL> show parameter spfile;
spfile is in fact in use, the full path will be shown in the
VALUE column. If a valid file path is shown, simply update your processes in
SQL> alter system set processes=100 scope=spfile;
You’ll need to restart the server for everything to take effect.
I then re-ran the tests and everything worked perfectly.
I love happy endings…