1

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.

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.

  • 1
    You don't need both SID and SERVICE_NAME. This ought to work with just the service name. – Alex Poole Jul 8 '14 at 15:59
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.