2

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.

3

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.

  • Ok. I was finally able to stablish a connection. I'm using Vagrant to start up the VM hosting Oracle. So I just included the following configuration in the Vagrantfile: config.vm.network :forwarded_port, guest: 1521, host: 1521 // config.vm.network :forwarded_port, guest: 1158, host: 1158 I don't know what that did exactly, but I'm glad it worked :) – felipe_gdr Jul 9 '14 at 23:42

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.