A Quickie on Connecting to Oracle Database VM From A Host

by Ian Hellström (26 April 2015)

Although the pre-built Oracle Database 12c VMs come with Oracle SQL Developer and APEX, you may not want to leave the host environment and develop in the virtual machine (guest). Sure, you can set up a shared folder and enable bi-directional copy-paste functionality thanks to the so-called Guest Additions, but it’s not the same as working in your own host OS.

In this post I describe how you can connect from the host to the guest on which the VM resides with a few simple tweaks. I have also included a simple installation overview of SQL Developer for Ubuntu.

Oracle SQL Developer

First off, let’s install SQL Developer. As of this writing, the latest stable release is 4.0.3. There is also an early-adopter of 4.1 but it has some rather annoying bugs, so I do not recommend it, at least not as your go-to IDE. You can download the software from the OTN as usual.

In case you are on Ubuntu — I’m running 14.04 LTS — you can easily install SQL Developer. Just download the RPM and execute the following commands from the terminal:

cd $HOME/Downloads
sudo alien sqldeveloper-*.rpm
sudo dpkg -i sqldeveloper_*.deb

My browser automatically drops downloads into the Downloads folder of my $HOME, so you may have to change the first line yourself.

The alien command converts the RPM to a deb file, which can easily be installed with dpkg, the Debian package manager.

Before we run SQL Developer for the first time, we have to figure out the path to JDK. Mine is /usr/lib/jvm/java-7-oracle/. It usually is usr/lib/jvm/* where the wild card * can be something like java-1.7.0-openjdk-amd64 or java-7-openjdk-amd64. Anyway, make a note of the path, or best yet, copy it to the clipboard.

Now run SQL Developer from the command line:

sqldeveloper

It will now ask for the JDK path, so enter it and hit enter. Oracle SQL Developer should now become visible. After that, you can close the application for now, although leaving it open does not hurt.

Unfortunately, Unity-based Ubuntu distributions do not create entries for Dash, so you have to create one manually. That’s not too complicated though:

cd /usr/share/applications/
sudo touch sqldeveloper.desktop
sudo gedit sqldeveloper.desktop

Copy-paste the following, after which you need to save the file:

[Desktop Entry]
Exec=sqldeveloper
Terminal=false
StartupNotify=true
Categories=Oracle;
Type=Application
Icon=/opt/sqldeveloper/icon.png
Name=SQL Developer

Finally, run sudo update-desktop-database. You should now be able to see Oracle SQL Developer (and its icon) in Dash.

VirtualBox

Below is a description based on the advice of Joris Visscher and the adventures of Jeff Smith, the product manager of Oracle SQL Developer.

Once you have installed the VM with Oracle Database 12c, you should not launch it immediately. Instead, go to SettingsNetwork, select NAT from the drop-down for ‘Attached to’, expand Advanced and click on ‘Port forwarding’. Enter a name (any name will do but mine is called VM12c), pick TCP as the protocol and fill in ‘1512’ (no quotes) in both the host and guest ports.

Start the VM. When it has loaded, enter the password, which — unless you have already changed it — is likely just oracle. From the System menu go to Administration and choose Firewall.

Click on ‘Other Ports’ on the left, click ‘Add’ and pick ‘User Defined’ (near the bottom of the dialog). As the port enter ‘1521’ (without quotes obviously) and select TCP as the protocol. Apply these settings and reload.

You have now set up port forwarding and allowed access through the firewall of Oracle Enterprise Linux.

Back in SQL Developer

Leave the VM running and go to Oracle SQL Developer on the host. Add a new connection as shown in the screenshot below:

Oracle SQL Developer: connection settings on the host

The user pmuser and service name PDB1 are the standard ones for the VMs. Hopefully, you’re in and can enjoy the best of both worlds: a VM with the latest database that you can access from your favourite operating system, without the hassle of having to install the not-quite-latest express edition.