Tuesday, November 4, 2008

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

We use Oracle Database 10g Express Edition Release for our RoR development and were hitting the TNS:listener error all the time. This made concurrent development a real pain and we couldn't leave staging servers and sqldeveloper sessions up.

Google searches turned up really scant information - mainly advices to increase the PROECESSES limit. I am not a DBA, so wasn't sure what needed to be done.
In any case, the following solution worked like a charm and I hope others who have this issue find this post useful.

source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.csh

And then execute the following steps to increase the number processes and sessions:
1) Connect to the database using the sys or system user:
sqlplus SYSTEM/<syspwd>@//<dbhost>/xe
2) To confirm the PROCESSES and SESSIONS values, run the following script:
sql> col name format A30
Sql> col value format A30
sql> select name, value from v$parameter where name in ('processes','sessions');
3) SQL> alter system set processes=300 scope=spfile;
4) SQL> alter system set sessions=300 scope=spfile;
5) Bounce the database to reflect the changes
6) To confirm new values run the script (2).


Idris said...

Thanks for posting this info. It was really helpful

Maurizio said...

Vey very useful!

biswa said...

it is working ........
thnk u , very very useful

Madhur Tewani said...

sorry but its not working for me...

Anonymous said...

@madhur - user error? it worked for me

Anonymous said...

are these command need to be commit ?