Home » RDBMS Server » Server Administration » 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 #204892] Wed, 22 November 2006 09:25 Go to next message
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 Go to previous messageGo to next message
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

icon12.gif  Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #204898 is a reply to message #204895] Wed, 22 November 2006 09:39 Go to previous messageGo to next message
alexandrend
Messages: 5
Registered: November 2006
Location: Geneva
Junior Member
Hi, thank you for the hint but no luck so far. Sad

-bash-3.00$ echo $ORACLE_SID
XE
-bash-3.00$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 22 16:43:58 2006

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

SQL> connect as sysdba
Enter user-name: system
Enter password:
Connected to an idle instance.
SQL>

Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #204901 is a reply to message #204898] Wed, 22 November 2006 09:43 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi ,

your database currently is shutdown ...

go below procedure.

SQL>STARTUP
after ur database is open then follow my previous instruction.

hope this helps
Taj.

[Updated on: Wed, 22 November 2006 09:44]

Report message to a moderator

Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #204903 is a reply to message #204892] Wed, 22 November 2006 09:46 Go to previous messageGo to next message
alexandrend
Messages: 5
Registered: November 2006
Location: Geneva
Junior Member
The things are becomming even stranger

-bash-3.00$ echo $ORACLE_SID
XE
-bash-3.00$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 22 16:43:58 2006

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

SQL> connect as sysdba
Enter user-name: system
Enter password:
Connected to an idle instance.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>
Re: ORA-12520: TNS:listener could not find available handler for requested type of server [message #204905 is a reply to message #204903] Wed, 22 November 2006 09:51 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi




SQL>ALTER DATABASE MOUNT;
SQL>ALTER DATABASE OPEN;

SQL>SHOW PARAMETER PROCESSES
SQL>CHANGE UR PROCESS PARAMETER VALUE.

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP

SQL>SHOW PARAMETER PROCESSES

HOPE THIS HELPS
Taj
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 Go to previous messageGo to next message
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 #204914 is a reply to message #204892] Wed, 22 November 2006 10:07 Go to previous messageGo to next message
alexandrend
Messages: 5
Registered: November 2006
Location: Geneva
Junior Member
Forget about that.
I just run /etc/init.d/oracle start and now everything is working.

Thank you.
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 Go to previous message
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>


Previous Topic: Archive log in Replication Server
Next Topic: MAXEXTENTS UNLIMITED IN TEMP SEGMENT
Goto Forum:
  


Current Time: Fri Dec 09 19:41:46 CST 2016

Total time taken to generate the page: 0.10294 seconds