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

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 Received on Mon Mar 17 2008 - 10:47:21 CDT