Cannot remotely connect to oracle express

oracleoracle-11g-r2

I have installed Oracle Express in a oracle-6.5-x64 virtual machine (let's called it host). The installation went fine, I am able to connect and run commands using SQLPlus from the host.

Now I'm trying to use SQLPlus from my Mac notebook (let's call it client) to access that remote database. I have installed the client packages and set the proper environment variables. I have tried connecting using EZConnect:

sqlplus system/<password>@<host-ip>[:1521]/XE

and also with TNS:

sqlplus system/<password>@XE

Both commands give me the following message:

ERROR:
ORA-12541: TNS:no listener

For the last option I have created a tnsnames.ora file in the client and exported a TNS_ADMIN variable pointing to the folder containing that file. The contents of tnsnames.ora are:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

I have tried using the host's IP instead of the hostname:

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.33.1)(PORT = 1521))

But that failed as well.

The lsnrctl status command on the host returns the following:

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 09-JUL-2014 11:39:22

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

Estabelecendo conexão com (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS do LISTENER
------------------------
Apelido                   LISTENER
Versão                    TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Data Inicial              09-JUL-2014 11:31:24
Funcionamento         0 dias 0 hr. 7 min. 58 seg
Nível de Análise     off
Segurança              ON: Local OS Authentication
SNMP                      OFF
Serviço Default           XE
Arq. Parâm. Listn.    /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Arq. Log Listener    /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/oraclehost/listener/alert/log.xml
Resumo de Atendimento...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclehost)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Resumo de Serviços...
O serviço "PLSExtProc" tem 1 instância(s).
  Instância "PLSExtProc", status UNKNOWN, tem 1 handler(s) para este serviço...
O serviço "XE" tem 1 instância(s).
  Instância "XE", status READY, tem 1 handler(s) para este serviço...
O serviço "XEXDB" tem 1 instância(s).
  Instância "XE", status READY, tem 1 handler(s) para este serviço...
O comando foi executado com êxito

As a last resource I have changed added oraclehost to the client's /etc/hosts file:

192.168.33.1    oraclehost

So I am able to successfully ping the host machine from the cliente by IP or hostname.

Best Answer

More often than not, a firewall on the database vm is the culprit. You can test this with

telnet oraclehost 1521

from your mac. This should give a quick response. If it does not give a quick response, like not response at all, a firewall is blocking the request. In that case allow traffic on port 1521, or disable the firewall.

Related Question