| ORA-12520: TNS:listener could not find available handler for requested type of server [message #204892] |
Wed, 22 November 2006 09:25  |
alexandrend
Messages: 5
Registered: November 2006
Location: Geneva
|
Junior Member |
|
|
Hi guys
I'm a completelly newbie and I need you help to figure out what is happening with my Oracle Express 10.2 instance.
Whenever I try to connect to the database using sqlplus I receive the following error message:
sqlplus user/password@XE
ORA-12520: TNS:listener could not find available handler for requested type of server
I read all the previous topics here about this problem but I couldn't find a way to fix my instance.
Another strange thing is that when I execute sqlplus with no additional parameters I got a different error message.
>sqlplus
Enter user-name: user
Enter password: xxx
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
The ORACLE_SID variable is defined as XE.
Here is the output of the "lsnrctl services" command:
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-NOV-2006 16:26:21
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
Services Summary...
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" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:blocked
LOCAL SERVER
Service "XEXDB" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:972 state:ready
DISPATCHER <machine: <MY HOSTNAME>, pid: 3150>
(ADDRESS=(PROTOCOL=tcp)(HOST=<MY HOSTNAME>)(PORT=32779))
Service "XE_XPT" has 1 instance(s).
Instance "XE", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:blocked
LOCAL SERVER
The command completed successfully
Note that the database is running correctly since I can connect using the web interface and issue SQL commands.
Thanks for your help.
|
|
|
|
| Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #204895 is a reply to message #204892] |
Wed, 22 November 2006 09:34  |
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
|
Senior Member |
|
|
Hi,
ORA-12520: TNS:listener could not find available handler for requested type of server
ORA-00020: maximum number of processes (%s) exceeded
One possible clause is above error.
so you should increase processes parameter.
start>run>cmd
cmd>set oracle_sid="put_ur_db_name"
cmd>sqlplus "sys as sysdba"
SQL>show parameter processes
SQL>--- increase value ( Eg : suppose current value is 150)
SQL>alter system set processes = 200 scope=spfile;
SQL>-- You have to bounce ur database for effect of above parameter bcoz processes is static parameter.
SQL>shutdown immediate;
SQL>
SQL>startup
SQL>show parameter processes.
check you listener problem is solve.
reply plz.
hope this helps
Taj
[Updated on: Wed, 22 November 2006 09:35] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #204911 is a reply to message #204892] |
Wed, 22 November 2006 10:04 |
alexandrend
Messages: 5
Registered: November 2006
Location: Geneva
|
Junior Member |
|
|
Thank you guys.
I think that my database was somehow stuck.
I was shutting it down using the "/etc/init.d/oracle stop" command but I figured out that some Xe processes were still alive after that.
By killing manually the Xe processes and following the user52 suggestion I can finally connect to my database using sqlplus.
Just one final question.
I defined the ORACLE_SID to XE.
If I execute sqlplus user/password@XE I get the following error message:
ORA-12541: TNS:no listener
but if I execute just sqlplus and after enter the user and password I can connect.
Do you have any clue why this is happening?
Thank you again.
|
|
|
|
|
|
| Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #204916 is a reply to message #204911] |
Wed, 22 November 2006 10:09 |
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
|
Senior Member |
|
|
Hi,
when you use @tns_entry then Oracle connect with tns_entry through listener.
or wher you issue only usrname and pwd then tns_entry is not using.
right now ur listener status is stop so start first.
************************
C:\>lsnrctl start
***********************
then try to connect what happen reply...
C:\>lsnrctl stop
LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 22-NOV-2006 20:05
:36
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
32-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ALwarid-Taj.fakhruddin.l
ocal)(PORT=1521)))
The command completed successfully
C:\>sqlplus "hr/hr@orcl"
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Nov 22 20:05:46 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
ERROR:
ORA-12541: TNS:no listener
Enter user-name: hr
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
