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

On Mar 17, 11:47 am, bdbafh <bdb..._at_gmail.com> wrote:
> On Mar 16, 8:06 pm, "www.douglassdavis.com"
>
>
>
> <douglass_da..._at_earthlink.net> 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
>
> > I am using MDB2 database abstraction library.
>
> > 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.
>
> > Does this mean that it is not dropping the connection once the page is
> > finished loading? How can this be fixed?
>
> > Thanks.
>
> connect via bequeath (avoid the tns listener)
>
> C:\> set ORACLE_SID=xe
>
> C:\> sqlplus / as sysdba
>
> in sqlplus, type the following:
>
> SQL> show parameter processes
>
> SQL> select count(1) from v$session;
>
> SQL> select count(1) from v$process where addr>'00';
>
> If all of the process slots are exhausted, you won't have been able to
> connect at all, in which case killing off user sessions would be the
> way to go to free up a slot.
>
> If you are able to connect and it is in fact a problem with the number
> of process slots allocated, use the following:
>
> SQL> show parameter spfile
>
> If an spfile is in fact in use, type:
>
> SQL> alter system set processes=150 scope=spfile;
>
> If no spfile is in use, edit the init.ora file instead.
>
> Cycling the database instance (or stopping and restarting the OS
> service) will make the change take effect.
>
> 85 dedicated server sessions on a laptop?
> Yeah, you *can* do it ... and you say its development.
> Does the laptop have a solid state hard drive?
>
> -bdbafh

I guess it was cumulative number of sessions I was looking at before...

Apparently I had 20 processes running on that port in an ESTABLISHED state according to netstat. They take a while to go away. So, I increased it to 150, that should work.

But, thanks. And thanks Frank. Received on Mon Mar 17 2008 - 22:50:51 CDT