Problem with listener ora-12514: TNS: listener does not currently know of service requested in connect discriptor

when you try to login with sql plus, what are you doing ?


post text with cut-and-paste rather than screen captures - easier for you, easier for us, less data faster downloads, allows for cut-n-paste for the output back into answers as needed

win-win-win-win

I figured it out.  What I had to do was run the lsnrctl services command and get the exact name of the services registered with the listener.  Then make sure that name was plugged into the tnsnames.ora file as the service name for the particular alias.

Here's what happens.  The database runs fine at first.  But after running multiple reports etc, the listener stops receiving messages.  Actually it blocks the instance I'm trying to connect to.  When I run lsnrctl services I get the following:


Service "CLRExtProc" has 1 instances(s).
   Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service ...
       Handlers(s):
          "DEDICATED" established:346 refused 0
               LOCAL SERVER
Service "orcl.Falcon-Domain.Local" has 1 instances(s).
   Instance "orcl", status READY, has 1 handler(s) for this service ...
       Handlers(s):
          "DEDICATED" established:346 refused 0 state:blocked
               LOCAL SERVER
Service "orclXDB.Falcon-Domain.local" has 1 instances(s).
   Instance "orcl", status READY, has 1 handler(s) for this service ...
       Handlers(s):
          "D000" established:0 refused:0 current:0 max:1022 state:ready
               DISPATCHER <machine: WINSVR-FALCON, pid 9300>
               (ADDRESS=(PROTOCOL=tcp)(HOST=WINSVR-FALCON.Falcon-Domain.local)(PORT=1689))


When I try to connect in sql plus to any user using an alias I get

ORA-12516: TNS:listener could not find available handler with matching protocol stack.

When I try to connect / as sysdba i get

ERROR: ORA-00020: maximum number of processes (300) exceeded.

Here is the contents of my init.ora

##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
 
###########################################
# Cache and I/O
###########################################
db_block_size=8192
 
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
 
###########################################
# Database Identification
###########################################
db_domain=Falcon-Domain.Local
db_name=orcl
 
###########################################
# File Configuration
###########################################
control_files=("D:\app\Administrator\oradata\orcl\control01.ctl", "D:\app\Administrator\flash_recovery_area\orcl\control02.ctl")
db_recovery_file_dest=D:\app\Administrator\flash_recovery_area
db_recovery_file_dest_size=4039114752
 
###########################################
# Miscellaneous
###########################################
compatible=11.2.0.0.0
diagnostic_dest=D:\app\Administrator
memory_target=1288699904
 
###########################################
# Processes and Sessions
###########################################
processes=300
 
###########################################
# Security and Auditing
###########################################
audit_file_dest=D:\app\Administrator\admin\orcl\adump
audit_trail=db
remote_login_passwordfile=shared
 
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"
 
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS1
 
Here is the contents of my listener.ora

# listener.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = WINSVR-FALCON.Falcon-Domain.Local)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\Administrator


Here is the contents of my tnsnames.ora

# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLE1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.Falcon-Domain.Local)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = WINSVR-FALCON.Falcon-Domain.Local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.Falcon-Domain.Local)
    )
  )

If the database is blocked somehow (maximum processes exceeded), then I believe it "blocks" itself in the listener to stop more incoming connections... as shown here "state:blocked":

Service "orcl.Falcon-Domain.Local" has 1 instances(s).
   Instance "orcl", status READY, has 1 handler(s) for this service ...
       Handlers(s):
          "DEDICATED" established:346 refused 0 state:blocked
               LOCAL SERVER

Select allOpen in new window

You should increase the number of processes and restart your database... or a probably better solution is to find out who is using so many connections:

select username, machine, count(*)
  from v$session
  group by username, machine
  order by username, machine;

Select allOpen in new window

If you had your database hard-wired into the listener.ora via SID_LIST, then you won't get the listener error... but you still won't be able to connect to the database because of the sessions issue.  The simple solution is to increase the processes and restart the database (Taking a backup of the spfile in text format before doing so... just in case you need it):

create pfile='/tmp/initSID.ora' from spfile;
alter database set processes=600 scope=spfile;
shutdown immediate
startup

Select allOpen in new window

if you're running out of processes, it could be because the client code is connecting multiple times but is not disconnecting thus creating orphan sesssions.  if you're sure every client is cleaning up after itself then increase the processes/sessions parameters as noted above.

Ok, what is happening here is that there is a user named SYSMAN that is spawning sessions.  The only thing running is the database.  I'm not launching my application at all.  If I run SELECT COUNT(*), USERNAME FROM V$SESSION GROUP BY USERNAME;  I see that I have 4 lines.  

COUNT(*) USERNAME
---------------  ------------------------------------------------------------------
21        
55             SYSMAN
1               DBSNMP
1               SYS

The SYSMAN user will continue to increase until it goes over what ever I've set as the maximum processes (currently 300).  I've installed 11G on a few other systems and haven't had this problem so I'm confused as to what may be causing it here.

that's your enterprise manager agent.  It shouldn't continue to grow like that.  You may need to resintall it or you've encountered a bug.

as a workaround you could create a profile with SESSIONS_PER_USER set to some limit and then assign SYSMAN to that profile and simply let it fail when it tries to exceed that limit

I'm paranoid by nature, so can we rule out something else connecting as SYSMAN?  It should only be Enterprise Manager... but just in case:

select username, machine, program, count(*)
  from v$session
  where username is not NULL
  group by username, machine, program
  order by 1,2,3;

Select allOpen in new window

Thanks, before you responded I shut down a service which seems to have fixed the problem.  But I'm not sure what they do and if they are critical to me.    The service I shut down was oracledbconsole<sid>.  Is this a service I need and what does it do?

It's the enterprise manager console to administrate/maintain the local database.

https://www.experts-exchange.com/questions/23656620/What-does-the-OracleDBConsole-sid-service-do.html

I presume you want Enterprise Manager to start/stop, manage your database?  If not, then turn it off... but it does sound there is a bug or misconfiguration somewhere in there.

I think there is too.  I appreciate the help.  I'm still trying to find out what the root problem is and when I do I'll close this questions.  But the queries you provided really helped me to find out what was spawning the processes.

Ok, here is what I did to fix this and I'm not sure what the problem was, but when the database was created, the domain name was being attached to every reference to the sid "orcl.Falcon-Domain.Local".  The sid name was orcl.  So I uninstalled and reinstalled but this time forced the sid name to orcl only and after that had no problems.   I still would love to know what the problem is.