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 10.2.0.1.0 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
setenv ORACLE_SID XE

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).

6 comments:

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 ?