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.
