Re: Apache/PHP/Oracle XE TNS:listener could not find available handler

On Sun, 16 Mar 2008 17:06:46 -0700, www.douglassdavis.com wrote:

> I have an Apache server, PHP 5.2.5 and Oracle 10g Express Edition for
> development purposes on my laptop running Windows XP.
>
> I am using OCI to connect to Oracle from PHP

OCI8, I hope?

>
> I am using MDB2 database abstraction library.

Why? ADOdb and Creole are much more popular. Creole is gaining popularity because of Symfony and Propel.

>
>
> Occasionally when I load a page I will get this error: ORA-12520:
> TNS:listener could not find available handler for requested type of
> server
> MDB2 Error: connect failed
>
> When I try to connect from Oracle SQL developer: ORA-12519: TNS: no
> appropriate service handler found
>
> When I run lsnrctl services:
>
> Service "xe" has 1 instance(s).
> Instance "xe", status READY, has 1 handler(s) for this service...
> Handler(s):
> "DEDICATED" established:85 refused:0 state:blocked
> LOCAL SERVER
>
> It appears that the "established" number is not going down, only going
> up.

Of course it is going up. It counts the number of established connections since the listener was started. The only way decrease it is to reverse time. In order to do that, you would have to reverse entropy. Now, that is the last question and there is yet insufficient data for a meaningful answer.

>
> Does this mean that it is not dropping the connection once the page is
> finished loading? How can this be fixed?

That means that you are using persistent connections. What you have to do is to limit the number of persistent connections by appropriately adjusting the value of oci8.max_persistent and oci8.persistent_timeout in your php.ini. These parameters are described here: http://www.php.net/manual/en/ref.oci8.php

-- 
Mladen Gogala
http://mgogala.freehostia.com