ORA : 12519 No appropriate Service Handler

Question

Sorry if my question has already been asked many times but I spent two days trying to solve it.

I have a machine in which I had an Oracle database. Everything was working propertly until I added two other databases.

Now, I can connect only to the last database created.

The three databases have the same connection settings (except the SID) :

• Host : localhost
• Port : 1521
• SID : Database1, Database2, Database3 (the last one created)

My tnsnames.ora file is as follows :

DATABASE3 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = DATABASE3)
    )
  )

DATABASE2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = DATABASE2)
    )
  )

DATABASE1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = DATABASE1)
    )
  )

LISTENER =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

My Listener.ora file is as follows :

SID_LIST_LISTENER =
  (SID_LIST =    
    (SID_DESC =
      (SID_NAME = DATABASE1)
      (ORACLE_HOME = E:\app\user\product\11.2.0\dbhome_1)      
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = DATABASE2)
      (ORACLE_HOME = E:\app\user\product\11.2.0\dbhome_1)      
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (SID_NAME = DATABASE3)
      (ORACLE_HOME = E:\app\user\product\11.2.0\dbhome_1)      
      (ENVS = "EXTPROC_DLLS=ONLY:E:\app\user\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))      
    )
  )

ADR_BASE_LISTENER = E:\app\user

When connecting to the databases :

• Database3 : Works fine !
• Database2 : ORA 12505, TNS:listener does not currently know of SID given in connect descriptor.
• Database1 : ORA 12519, TNS:no appropriate service handler found

I restarted the listener many times.

The command lsnrctl status returns (sorry it's in french)

Connection to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS du PROCESSUS D'ECOUTE
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                07-JUIL.-2014 09:24:42
Activity                  0 jours 12 heures 45 min. 0 sec
Niveau de trace           off
Sécurité                  ON: Local OS Authentication
SNMP                      OFF
Fichier de paramétres du processus d'écoute      E:\app\user\product\11.2.0\dbhome_1\network\admin\listener.ora
Fichier journal du processus d'écoute           e:\app\user\diag\tnslsnr\MyComputer\listener\alert\log.xml
RÚcapitulatif d'Úcoute des points d'extrÚmitÚ...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MyComputer)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
RÚcapitulatif services...
Le service "CLRExtProc" comporte 1 instance(s).
  L'instance "CLRExtProc", statut UNKNOWN, comporte 2 gestionnaire(s) pour ce service...
Le service "Database1" comporte 1 instance(s).
  L'instance "Database1", statut UNKNOWN, comporte 1 gestionnaire(s) pour ce service...
Le service "Database3XDB" comporte 1 instance(s).
  L'instance "Database3", statut READY, comporte 1 gestionnaire(s) pour ce service...
Le service "Database3" comporte 1 instance(s).
  L'instance "Database3", statut READY, comporte 1 gestionnaire(s) pour ce service...
La commande a réussi

I read in a forum that this might be a matter of connections maximum reached, I'm sure it's not the problem because no one is connected to my machine.

If any one can provide some help, I will be grateful.

Thanks.

Answer 1

http://docs.oracle.com/cd/E18283_01/network.112/e10836/concepts.htm

sales=
 (DESCRIPTION= 
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)) 
  (CONNECT_DATA= 
     (SID=sales)
     (SERVICE_NAME=sales.us.example.com)
     (INSTANCE_NAME=sales)))

It looks like you haven't put the SID in your TNSNAMES.ORA.

Answer 2

There is an 8 character limit on some things in Oracle, try shorting database1, database2 and database3 to db1, db2 and db3 also the characters _ and # are allowed if I remember correctly.

From Setting up the database

When creating the database, ensure that the length of the Oracle System ID (SID) does not exceed eight (8) characters.

For example:

• SID: abcd12345 is invalid

• SID: abcd123 is valid