msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

How to Connect to Oracle Database on a Guest VM from the Host

Here is the set up: The host is a Windows 7 machine, 64 bit with Oracle client. the guest is a Windows XP Virtualbox VM running Oracle Database 11.2.0.2. All default installations and configurations.

You want to be able to connect to the Oracle database (SQL*Plus, SQL Developer, TOAD…) from the host.

Here is what I did to enable Oracle TNS connection between the host and the guest.

Note: As mentioned in the comments, there are two other ways to accomplish connectivity between the host and the guest VM: NAT with port forwarding and host-only adapter. This article assumes that networking between the host and the guest is done via a bridged adapter.

Bridged Adapter

First, enable bridged networking in Virtualbox. To do so, open the Settings dialog of your virtual machine, go to the “Network” page and select “Bridged Adapter” in the drop down list for the “Attached to” field. Click OK. Start the VM.

Static IP

Second, make sure the guest, the Windows XP VM, is assigned a static IP address. Open a DOS command prompt and type ipconfig /all

Guest Dynamic IP

If Dhcp Enabled is set to No, that means you have a static IP address. Otherwise, you have a dynamic IP address.

If your current IP address is static then make note of it, you will need it later. If it is dynamic, then you need to assign a static IP address. Here is how:

Login to your router and gather the following information:

  • Router (Gateway) IP address
  • Subnet Mask
  • IP addresses of your DNS servers
  • The range of IP addresses that can be assigned as static IPs

For example, here is the information from my router:

Now, do the following:

  • Open Windows Start menu
  • Open Control Panel
  • Open Network Connections
  • Right-click on your connected LAN or Internet connection
  • Click Properties. This opens the Local Area Connection Properties window
  • In the General tab, click the Internet Protocol (TCP/IP) item, and click Properties

This opens the Internet Protocol (TCP/IP) Properties window.

  • Click Use the following IP address, and enter:
  • The static IP address you want to assign to this VM
  • The Subnet mask used by your router
  • The IP address of your router as the default gateway

In Use the following DNS server addresses, enter the IP addresses for the DNS servers your router uses.

Click OK and close all windows. Shutdown and restart the VM.

After you restart the VM, double check that you have a static IP address:

TNSPING

Next, if you try tnsping from the host machine you may get “TNS:operation timed out”. This is usually an indication that Windows firewall is enabled on the guest VM and is blocking port 1521 (assuming you’re using the default TNS port).

You need to make sure that port 1521 is open. Here is how:

  • On the guest VM, open Windows Start menu
  • Open Control Panel
  • Open Windows Firewall
  • Click on Exceptions tab
  • Click Add Port
  • Enter a name and the port number of the listener
  • Click OK to save and exit the settings windows

Now, try tnsping from the host again. You may get “TNS:no listener”.

Edit the listener.ora file in ORACLE_HOME\network\admin on the guest VM (You may also use Net Manager GUI for that). Replace localhost with the static IP address of the VM.

Restart the listener: From a DOS prompt on the VM, type lsnrctl. From the LSNRCTL> prompt type stop then start.

Try tnsping from the host again. It should now be successful.

SQL*Plus

Connect to the database using SQL*Plus from the host machine. you may get “TNS:listener does not currently know of service requested in connect descriptor”.

Add a service entry in your listener.ora file on the guest VM (You may also use Net Manager GUI for that).

Connect again and it should work.

Enjoy!


Filed in Oracle on 21 Feb 12 | Tags: ,


Reader's Comments

  1. |

    This will only work if you start the guest in the same physical network, otherwise you would get different IP numbers back pending the configuration of the DHCP server in the network you are connected to. If you are not connected to a network, you would not even be able to use it.

    Why don’t you use NAT with port forwarding on port 1521? That would always work, independent of the network you are in.

    • |

      Thanks for your comment and clarifications Erik. I understand there are multiple ways to achieve this. What I described in this article worked for me based on my setup which, as you indicated, included both host and guest on the same physical machine and network. Maybe I’ll put together a new post about how to set up port forwarding. Cheers!

  2. |

    If you don’t need to access the internet(or other external networks) from the guest, you can also select the ‘Host-only’ option. This is a simpler configuration and allows the host to connect to the guest without having to depend on the host’s network connectivity.

    • |

      Thanks Sujoy. I have updated the article and included a note about NAT with port forwarding as well as host-only configurations.Cheers!

  3. |

    [...] you are wondering about as how to Connect to Oracle Database on a Guest VM from the Host, then Awad has a blog about [...]

  4. |

    Hi,Eddie Can u please give me steps to connect oracle database 10g on Linux in VmWARE guest from host windows 7 64 bit OS.

    thanks

    • |

      Hi Goutham, I do not currently have vmware installed on my machine, so I cannot give the steps with any degree of accuracy. Conceptually they should be the same as other VMs. Maybe the vmware forums could help. Cheers!