Cannot access Win7 Oracle Database from Ubuntu VM

Question

My main (physical) machine is Windows 7. I have installed a "homestead" Virtual Machine using VirtualBox so that I can develop a Laravel application in the VM. Homestead (the VM) runs Ubuntu 14.10 as its OS.

Also on my Win 7 machine, I have installed an Oracle database. The database is running fine, and I can access it from an Sql*Plus session running on the Windows machine itself. However, I wish to access this same Oracle Database from the Homestead machine (ultimately so that I can access the database through my web application). As a first step to achieving this goal, I have tried to install the Oracle Instant Client on Homestead (11.2), and configured it to the best of my knowledge as follows:

Some environment variables that are set on the Ubuntu/Homestead machine are:

export ORACLE_HOME=/usr/lib/ora_home_directory
export LD_LIBRARY_PATH=$ORACLE_HOME
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$ORACLE_HOME

I can ping the Win7 machine from Homestead:

# ping -c 4 cbh01
PING cbh01 (192.168.1.9) 56(84) bytes of data.
64 bytes from 192.168.1.9: icmp_seq=1 ttl=127 time=2.96 ms
64 bytes from 192.168.1.9: icmp_seq=2 ttl=127 time=1.04 ms
64 bytes from 192.168.1.9: icmp_seq=3 ttl=127 time=1.21 ms
64 bytes from 192.168.1.9: icmp_seq=4 ttl=127 time=1.45 ms

I can launch Sql*Plus from the SSH session which I open to Homestead:

# sqlplus /nolog
SQL>

tnsnames.ora on the Homestead machine contains the following entry:

ORACBH =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = cbh01)(PORT = 1521))
      (CONNECT_DATA = (SERVICE_NAME = ORACBH))
  )

listener.ora on the Win7 machine looks like this:

SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = C:\oracle\product\10.2.0\db_2)
            (PROGRAM = extproc)
        )
        (SID_DESC =
            (GLOBAL_DBNAME = ORACBH)
            (ORACLE_HOME = C:\oracle\product\10.2.0\db_2)
            (SID_NAME = ORACBH)
        )
    )

LISTENER =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)

When I do a connect:

SQL> conn usr/pass@oracbh

It returns:

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

It seems to me that it's not taking notice of the tnsnames.ora file. This is a problem in itself, but no matter, perhaps I can work around the problem by connecting with the connect string directly.

SQL> conn usr/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=CBH01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORACBH)))

But this is simply met with the following:

ERROR:
ORA-12541: TNS:no listener

I have switched off all firewalls, so I don't think this is the problem. Otherwise I'm running out of ideas. :-/

Listener seems to be running fine.

C:\Users\Bob>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 05-JUN-2015 08:07:44

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.4.0 - Production
Start Date                05-JUN-2015 06:49:27
Uptime                    0 days 1 hr. 18 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product\10.2.0\db_2\network\admin\listener.ora
Listener Log File         C:\oracle\product\10.2.0\db_2\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "ORACBH" has 1 instance(s).
  Instance "ORACBH", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

I tend to think that the problem is a combination of how the VM accesses the network. I don't think it is an Oracle issue. My knowledge of networks and the like is limited so feel free to enlighten me on this front!! Similar issues are reported on some Oralce and VM support pages, e.g.: https://community.oracle.com/thread/2439416

Trying to debug connection across the network, I've used Telnet as follows:

vagrant@homestead:~$ telnet 192.168.1.9 1521
Trying 192.168.1.9...
telnet: Unable to connect to remote host: Connection refused

But as previously stated, I've already disabled all firewall software, which would eliminate a port-blocking at this level.

Answer 1

Follow these steps which will allow you to use Ubuntu as if it is connected physically to the host OS.

Use Bridged Adapter:

1. Choose your host network interface in the drop down.

Disable DHCP and use a static IP:

1- From the top of the screen select the network icon, next to the clock and volume, then click Edit Connections.

2- From the window that opens, go to Wired tab, select your connection (there should be only one connection, if you didn't touch anything). Then click Edit.

3- From the IPv4 Settings tab change Method from Automatic (DHCP) to Manual.

4- Under Addresses field, click on Add.

5- Enter your desired IP address and subnet mask and click Save, you can also enter an optional DNS server here.

Answer 2

I've been playing around with a number of configurations, so it's not easy to say that there was a specific solution to this problem. The bottom line is that if configurations are correct, there seems to be no inherent limitation in the VM framework being able to connect to the physical server's database (contrary to some comments that I have found on other forums).

Firstly, ensure that you know whether or not you're using TNSNAMES to resolve your connect string or not. In the sqlnet.ora file, you can choose to add the NAMES.DIRECTORY_PATH(TNSNAMES) parameter. If you do add this, then it seems that you are obliged to use a name resolution in your tnsnames.ora file (which surprised me). As a result, you cannot use '//servername.com:1521/dbsid' as your DSN connect string. As it happens, I need for the web server to be able to connect without using the TNS entry, so I removed the TNSNAMES option from the sqlnet.ora file.

Secondly, ensure that you do not have firewalls blocking your ports (specifically, the 1521 port). I thought that I had allowed an exception to my 1521 port in the Windows Firewall, but as I see it now, it seems that the changes I made did not take effect until I'd rebooted the Windows machine(!?!?).

You can verify that the port is open and un-firewalled as per comment from @BjarteBrandt (using nmap tool), to ensure that the port is really open. Run the following from the VM:

vagrant@homestead:~$ nmap -p 1521 cbh01

Starting Nmap 6.46 ( http://nmap.org ) at 2015-06-07 16:37 UTC
Nmap scan report for cbh01 (192.168.10.1)
Host is up (0.00046s latency).
Other addresses for cbh01 (not scanned): 192.168.1.14 192.168.56.1
PORT     STATE SERVICE
1521/tcp open  oracle

Nmap done: 1 IP address (1 host up) scanned in 0.08 seconds

Then when I test the SQL connection again...

vagrant@homestead:~$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 7 16:42:48 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn usr/pass@//192.168.1.14/oracbh
Connected.

It requires a reboot of the VM (or of the web server, at least), in order that this change in OCI configuration be propagated to the web server and web page.