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.
Same ORA-12516 error.
Running 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.
Same 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;
If an 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 sqlplus thusly:
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…