Home » RDBMS Server » Performance Tuning » ORA-12520: TNS:listener could not find available handler for requested type of server
ORA-12520: TNS:listener could not find available handler for requested type of server [message #161939] Tue, 07 March 2006 23:46 Go to next message
rvnair123
Messages: 26
Registered: January 2006
Junior Member
Hello,
After we were simulating our server with 50 users in Test Env, we ran different queries.After which we kept getting error
ORA-12520: TNS:listener could not find available handler for
requested type of server

How do i handle this error,coz it really stops everything.
Is there solution to this?

Thanks,
Rekha
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #162063 is a reply to message #161939] Wed, 08 March 2006 09:29 Go to previous messageGo to next message
aciolac
Messages: 242
Registered: February 2006
Senior Member
> oerr ora 12520
12520, 00000, "TNS:listener could not find available handler for requested type of server"
// *Cause: None of the known and available service handlers for requested type
// of server (dedicated or shared) are appropriate for the client connection.
// *Action: Run "lsnrctl services" to ensure that the instance(s) have
// registered with the listener and that the appropriate handlers are
// accepting connections.


Write what services have Your listener, and output of command
>lsnrcts status
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #162134 is a reply to message #162063] Wed, 08 March 2006 22:24 Go to previous messageGo to next message
rvnair123
Messages: 26
Registered: January 2006
Junior Member
Hello,
Well i wasnt expecting this answer coz when many users connect & query is run ,then i get this error else the connections are fine.Most of the queries run fine for large number of users connected.
I wanted to know why is this error raised when i run some 1 or 2 query which does not have large amount of data but cause this error.How can i solve this?

Thanks,
Rekha
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #162179 is a reply to message #162134] Thu, 09 March 2006 01:28 Go to previous messageGo to next message
aciolac
Messages: 242
Registered: February 2006
Senior Member
Listeners are necessary only to open connexion with database. After you are connected, you can work with database without any listener started. So, you error are very strange. I am find on th Metalink that increasing of the value for PROCESSES parameter in init.ora can help. Also try to change your listener.ora file. For example my listener.ora(doesn't give no one error):
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SDU=8192)
(TDU=8192)
(GLOBAL_DBNAME = dbmic.prom)
(SID_NAME = DBMIC)
(ORACLE_HOME = /software/oracle/product/8.1.7)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prom)(PORT = 1521))
)
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #202160 is a reply to message #162179] Wed, 08 November 2006 07:42 Go to previous messageGo to next message
eb1421
Messages: 3
Registered: November 2006
Junior Member
Hello,

I have exactly the same problem.
I am a complete Oracle beginner and I am working with Oracle 10g Express Edition.
My configuration is the one automatically set during installation.

I have 10 users sending a series of queries simultaneously (for benchmark purpose).
If I have 1 user, everything is OK.
When I have 10 users, for most of the queries I get the following error:
ORA-12520: TNS:listener could not find available handler for
requested type of server
I think it is a problem of number of simultaneous connections.

I have tried to :
- increase PROCESSED and PARALLEL_MAX_SERVERS in init.ora
- set QUEUE_SIZE in listener.ora
- set RECV_BUF_SIZE and SEND_BUF_SIZE in listener.ora and sqlnet.ora
...
and it does not help

Besides, the result of lsnrctl services seems OK.

Can someone help me with this problem ?
Thank you very much

Eve


Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #203631 is a reply to message #202160] Wed, 15 November 2006 21:15 Go to previous messageGo to next message
aladdha
Messages: 4
Registered: October 2006
Location: Mumbai
Junior Member

Hi,

Rekha...is the Database is on Windows Server ?? if so then try to reconfigure your listener.

Thanks
Ashish.
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #219051 is a reply to message #202160] Mon, 12 February 2007 13:04 Go to previous messageGo to next message
mycole
Messages: 3
Registered: February 2007
Location: Berkeley
Junior Member
Did anyone figure out how to resolve this issue???

I'm using Oracle 10g and having the same issue when I open multiple connections in my DAL layer. ORA-12520: TNS:listener could not find available handler for requested type of server

I'm confused if I should try and reuse the same connection for all of my queries and updates... I could make it static. Anyone have any suggestions?
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #219052 is a reply to message #161939] Mon, 12 February 2007 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Error: ORA-12520 (ORA-12520)
Text: TNS:listener could not find available handler for requested type of server
---------------------------------------------------------------------------
Cause: None of the known and available service handlers for requested type of server (dedicated or shared) are appropriate for the client connection.
Action: Run "lsnrctl services" to ensure that the instance(s) have
registered with the listener and that the appropriate handlers are accepting connections.
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #219059 is a reply to message #219052] Mon, 12 February 2007 14:19 Go to previous messageGo to next message
mycole
Messages: 3
Registered: February 2007
Location: Berkeley
Junior Member
Hello anacedent,

I ran my unit tests, which execute approximately 300 calls to the database. They're not simultanous calls, but do occur within seconds of each other. 15 out of 20 tests failed due to the TNS Error. I then ran lsnrctl services and got the output below. The first 5 or so tests make the connection and execute successfully.

Shouldn't I be seeing some number next to refused for some of these handlers?

Mycole

.....

C:\Documents and Settings\mycole>lsnrctl services

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-FEB-2007 12:11
:27

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "XE_XPT" has 1 instance(s).
Instance "xe", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:144 refused:0 state:ready
LOCAL SERVER
"D000" established:33 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: BRAZIL, pid: 2964>
(ADDRESS=(PROTOCOL=tcp)(HOST=brazil.OUARS.Berkeley.edu)(PORT=1079))
Service "xe" has 1 instance(s).
Instance "xe", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:144 refused:0 state:ready
LOCAL SERVER
"D000" established:33 refused:0 current:0 max:1002 state:ready
DISPATCHER <machine: BRAZIL, pid: 2964>
(ADDRESS=(PROTOCOL=tcp)(HOST=brazil.OUARS.Berkeley.edu)(PORT=1079))
The command completed successfully
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #219076 is a reply to message #161939] Mon, 12 February 2007 18:30 Go to previous messageGo to next message
mycole
Messages: 3
Registered: February 2007
Location: Berkeley
Junior Member
UPDATE: I setup a test where I run 1000 inserts, 1000 selects and a 1000 deletes on a table. I'm able to do this in about 20 seconds.

When I try to increase to 2000, I start getting "ORA-12520: TNS:listener could not find available handler for requested type of server" around 1600 in the loop. The point at which it starts failing isn't consistent.

Could this just be a limitation of Oracle? Any ideas?
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #219077 is a reply to message #161939] Mon, 12 February 2007 18:42 Go to previous message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Oracle changed the listener with the V10.2 release.
This is a serious attempt to avoid the error you are seeing.
Double the value of PROCESSES & bounce the DB server.
The run your test again. I doubt you'll get the errors again.
Previous Topic: 9i to 10g
Next Topic: Load on Oracle server
Goto Forum:
  


Current Time: Wed Dec 07 03:17:59 CST 2016

Total time taken to generate the page: 0.11198 seconds