Can't connect to Oracle using tns

Question

I'm using an Oracle 11g R2 database. I use Oracle SQL Developer. If i create a new connection and check Basic Connection Type and fill the fields i can connect. If i select TNS and select the one i want in the dropdown menu, it says Failure E/S Exception: The Network Adapter could not establish the connection.

This is the tnsnames.ora. I use it to connect to 2 database, dblilly and astrea. I can connect correctly to astrea. Listener is on and the instances ready. Do you see something i miss? Do you need more info to help me?

# tnsnames.ora Network Configuration File: C:\OracleODAC\x32\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LIDA_ALC_DBLILLY=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.109.107)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl)
    )
  )

LIDA_INDY_DBLILLY=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.109.107)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl2)
    )
  )

HARVEST_DBLILLY=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.109.107)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl2)
    )
  )

DELTA_DBLILLY=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.109.107)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl2)
    )
  )

AUTOLAB_DBLILLY=
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=192.168.109.107)
        (PORT=1521)
      )
      (CONNECT_DATA=
        (SERVER=dedicated)
        (SERVICE_NAME=orcl2)
      )
    )
    (DESCRIPTION=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=192.168.109.107)
        (PORT=1521)
      )
      (CONNECT_DATA=
        (SERVER=dedicated)
        (SERVICE_NAME=orcl2)
      )
    )
    (DESCRIPTION=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=192.168.109.103)
        (PORT=1521)
      )
      (CONNECT_DATA=
        (SERVER=dedicated)
        (SERVICE_NAME=orcl.cartif.local)
      )
    )
  )

LIDA_INDY_ASTREA=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.109.103)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl.cartif.local)
    )
  )

HARVEST_ASTREA=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.109.103)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl.cartif.local)
    )
  )

DELTA_ASTREA=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.109.103)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=orcl.cartif.local)
    )
  )

AUTOLAB_ASTREA=
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=192.168.109.103)
        (PORT=1521)
      )
      (CONNECT_DATA=
        (SERVER=dedicated)
        (SERVICE_NAME=orcl.cartif.local)
      )
    )
    (DESCRIPTION=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=192.168.109.103)
        (PORT=1521)
      )
      (CONNECT_DATA=
        (SERVER=dedicated)
        (SERVICE_NAME=orcl.cartif.local)
      )
    )
  )

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl2
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:/u01/app/oracle/product/11.2.0/dbhome_1/bin/oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dblilly)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

lsnrctl status

oracle@dblilly:/root> lsnrctl status LISTENER                                   
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 24-AUG-2015 14:57:02

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                24-AUG-2015 12:49:04
Uptime                    0 days 2 hr. 7 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dblilly)(PORT=1521)))
Services Summary...
Service "orcl2" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1" has 1 instance(s).
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl2XDB" has 1 instance(s).
  Instance "orcl2", status READY, has 1 handler(s) for this service...
The command completed successfully

I can connect with this settings:

But can't with this ones

Can you test the connection information from SQL Developer, not using Tnsnames? — Nick
I didn't understand your last comment. You are able to successfully connect in SQL Developer, without using tnsnames ? — Nick
Yes, but i can't using tnsnames. I'm able to connect using a connectionstring not the tnsnames. — Oscar Vicente Perez

Nick Nick · Accepted Answer · 2015-08-24T12:25:37

In your listener.ora, you are missing a closing " and a closing ) around the ENVS parameter.

Your TNSNames file is setup to use a Service, however in SQL Developer you are connecting via the SID. You should also change your TNSNames to specify the SID instead of the service name.

SID vs Service Name

Can't connect to Oracle using tns

1 Answers