Unable to connect to Oracle 11g server remotely using SQL developer ORA-12520

database-designlisteneroracleoracle-11g

Hello everyone I'm unable to connect to my Oracle Server remotely using SQL developer ….while I can connect from SQL developer when I'm in the server directly ….I get the following error when I try to test my TNS listener

ORA-12520: TNS:listener could not find available handler for requested type of server

I followed the Oracle help …and I found out that My Oracle service ORCL is registered

this is from Oracle support

ORA-12520: 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.

this is the output of lsnrctl services

` 

 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 15-
     SEP-2017 16:49
     :00
     Copyright (c) 1991, 2010, Oracle.  All rights reserved.

     Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: OSAMA-PC, pid: 3240>
         (ADDRESS=(PROTOCOL=tcp)(HOST=osama-PC)(PORT=49212))
    The command completed successfully

Lnsrctl status output
`

C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-SEP-2017 
18:2
:26

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - 
Prod
ction
Start Date                17-SEP-2017 17:39:16
Uptime                    0 days 0 hr. 48 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   
C:\app\MOD\product\11.2.0\dbhome_1\network\admin\list
ner.ora
Listener Log File         c:\app\mod\diag\tnslsnr\osama-
PC\listener\alert\log.x
l
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=osama-PC)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Windows\system32>

tnsping orcl result

C:\Windows\system32>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-
SEP-2
017 18:29:11

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\MOD\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = 
TCP)
(HOST = osama-PC)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (20 msec)

C:\Windows\system32>

Best Answer

A couple of things can trigger this error. My guess is this the first thing below.

Max Process Limit Reached

You may have have exceeded the maximum number of processes. (see here and here.)

This query will show you the current utilization, max utilization, initial allocation, and limit for Processes: select * from v$resource_limit where resource_name in ('processes','sessions');. You will need to run the query from the server, since you can't get in remotely. If Max Utilization is at or near your limit value, this may be the problem.

You can see what the process limit is by logging into Oracle as sysdba from the server. show parameter processes will display something that says processes and has an integer value. You can extend this value by issuing this command: alter system set processes=XXX scope=spfile; set XXX to a higher value. Oracle recommends 200 for a "small" system and 400 for a "medium" system. You will have to shutdown and restart Oracle to enact the change.

Oracle bug

Oracle mentions a Bug, bug number 13831723, in Oracle 11.1. Your information seems to indicate you're running 11.2, so this shouldn't apply. But if it does, then you'll need to install a patch from Oracle. Log into Oracle support and search for Oracle DOC ID 1566704.1. They say it is fixed in 11.1.1.7.0 and up.

Related Question