Oracle listener state blocked

Question

I have a webpp which works fine when under a light load. However, when we run a lot of threads each with their own database connection, then we start getting the error

ORA-12519: TNS:no appropriate service handler found

After looking online I found that running lsnrctl services was a good diagnostic step, so I did that. The result for our service was

Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:130 refused:0 state:blocked
         LOCAL SERVER

The number of established connections is consistent with the number of threads. However, the state:blocked seems like a cause and/or symptom of this problem.

So what's my next step? The max number of open sessions is 1024, which is more than enough, and there's no limit to the number of sessions per user. I ran this test after a reboot of the machine, and no other programs were connected. I'm really not sure what to try next, so any help will be greatly appreciated.

EDIT: Upping the processes and sessions parameters seemed to do the trick. In addition to finding Matthew's suggestion helpful, this email described my problem perfectly.

Answer 1

Have your checked your alert log it should tell you what is going wrong if oracle is running out of resources? Sounds like you may be out of processes,

Run in sqlplus,

SQL> show parameter processes

it will show you how many processes oracle will allow. You may need to increase this a bit.

If you have a metalink account, then check article 240710.1 for more details.