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.
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.
Second, make sure the guest, the Windows XP VM, is assigned a static IP address. Open a DOS command prompt and type ipconfig /all
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:
For example, here is the information from my router:
Now, do the following:
This opens the Internet Protocol (TCP/IP) Properties window.
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:
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:
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.
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.