Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What error do you get when trying to connect? –  Balazs Papp Jul 9 at 12:33
    
I have edited my question to include the return message –  pipo_dev Jul 9 at 19:28

1 Answer 1

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.

share|improve this answer
    
I think that might be the case, when I run telnet oraclehost 1521 I get the following output: Trying 192.168.33.1... telnet: connect to address 192.168.33.1: Connection refused telnet: Unable to connect to remote host. I have disabled /etc/init.d/iptables, is there another firewall I should turn off? –  pipo_dev Jul 9 at 19:24
    
are you sure your listener is on 192.168.33.1 and did you use port 1521? Normally a fw causes: no reaction at all. –  ik_zelf Jul 9 at 20:23
    
I'm sure about the port, as you may notice from the output of the lsnrctl status command. However I'm not sure about 192.168.33.1. How can I check this? –  pipo_dev Jul 9 at 22:17
    
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 :) –  pipo_dev Jul 9 at 23:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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