Sandpit shenanigans with the Oracle Database Virtual Box Appliance

The Oracle Database Virtual Box Appliance (ODVBA) is a great way to gain hands-on experience with APEX, Oracle Database, and REST data services.

Effectively the ODVBA is used for self-education purposes.

The ODVBA is delivered as a virtual machine image for ease of use, and it features the following technologies:

• Oracle Linux 7
• Oracle Database 19.3 Linux x86–64
• Oracle SQL Developer 19.1
• Oracle Application Express 19.1
• Hands-On-Labs (Oracle REST Data Services 19.1, Oracle SQL Developer Data Modeler 19.1, and Oracle XML DB)

The ODVBA is an integrated environment, with all the required tools preinstalled. eg Oracle SQL Developer.

However for greater ease of use, and perhaps also for a performance benefit, it may be better to operate the ODVBA tools from the host (ie your workstation) instead of within the virtual machine.

This is especially convenient if your workstation already has the equivalent tools ready to go.

For example, the SQL Developer tool could be operated within the ODVBA.

SQL Developer from within the Oracle Database Virtual Box Appliance

Or, the SQL Developer tool could be operated from the host to access the ODVBA.

SQL Developer from within the host used to access the Oracle Database Virtual Box Appliance

So to operate the ODVBA from the host, the Oracle Virtual Box network needs to be configured.

Oracle Virtual Box networking

Oracle Virtual Box allows the configuration of many different types of networks for each virtual machine, and they are:

• Internal — The virtual machine can only communicate to any other virtual machines on the same host.
• Host-only — The virtual machine can only communicate with the host and any other virtual machines.
• NAT (Network Address Translation) — Generally allows only outbound connections to the internet for the virtual machine, but not to any other virtual machines nor to the host.
• NAT Service — Generally allows outbound connections to the internet for the virtual machine, and communication to any other virtual machine.
• Bridged — Allows inbound and outbound connections for the virtual machine, and communication to the host and any other virtual machines.

For our purposes, we are only interested in communication between the virtual machine and the host.

It’s possible to use a bridged network to communicate to the host, but for self-education purposes we are not interested in the virtual machine becoming a server for inbound connections.

Instead, a host-only network will be enough to allow communication between the virtual machine and the host.

Create a host-only network on the host

Firstly create the host-only network from the Virtual Box directory on the host.

> cd C:\Program Files\Oracle\VirtualBox
> VBoxManage hostonlyif create
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Interface 'VirtualBox Host-Only Ethernet Adapter #2' was successfully created
>

The next step is to configure the host-only network that we just created.

The IP address we specify in the configuration will be IP address of the host for this network. Let’s use a network ID of 192.168.0.0, so the host IP address will become 192.168.0.1.

> VBoxManage hostonlyif ipconfig "VirtualBox Host-Only Ethernet Adapter #2" --ip 192.168.0.1 --netmask 255.255.255.0
>

So our host-only network has been configured to allow virtual machine IP addresses ranging from 192.168.0.2 to 192.168.0.254.

The intent is for our ODVBA to use an IP address of 192.168.0.2.

Install and setup of the Oracle Database Virtual Box Appliance

Download the virtual machine image for the ODVBA from here:

oracle.com/database/technologies/databaseappdev-vm.html

The installation is as for any other virtual machine.

Also other prebuilt virtual machines from Oracle are here:

https://www.oracle.com/downloads/developer-vm/community-downloads.html

Setup the virtual machine

Adapter 1 will be for the NAT (Network Address Translation). This will be required in the event that we need to access the internet for any reason.

There is the option of disabling it, but we won’t bother as it may be helpful in future.

Now to allow the virtual machine of the ODVBA to recognise the host-only network, it needs to be configured in the virtual machine.

So simply go to the network settings of the virtual machine, and setup adapter 2 as follows.

Now when the virtual machine is started, just make sure the network adapters are enabled.

Finally we need to setup the IP address of the virtual machine.

The network ID was set up previously as 192.168.0.0, so the IP address of the virtual machine will be allocated as 192.168.0.2.

Setup the virtual machine as follows.

Ethernet (eth1) configuration

Now we are ready to connect to the ODVBA from the tools on the host.

Connect from the host tools

Sqlplus — If your host has an Oracle home, or even just an Oracle client installed, then you can connect as follows:

> sqlplus system/oracle@192.168.0.2:1521/orclcdb
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 23 16:55:27 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Wed Nov 04 2020 15:19:56 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>

SQL Developer — If your host has the SQL Developer client installed, then you can connect as below with the same login credentials as for Sqlplus.

To login as system, the connection type is basic and the role is the default.

SQLcl — For the SQLcl interface, the login is similar for Sqlplus.

> sql system/oracle@192.168.0.2:1521/orclcdb
This application requires a Java Runtime Environment 1.8.0_150
>

SQLcl requires Java, so the browser will automatically open at the Java download page.

Once Java is installed then the login will work.

> sql system/oracle@192.168.0.2:1521/orclcdb
SQLcl: Release 18.2 Production on Tue Nov 24 18:45:01 2020
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Last Successful login time: Tue Nov 24 2020 18:44:44 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>

Web browser — The ODVBA has Firefox installed by default.

However on your host may have Chrome (or Chromium) installed instead, so for APEX simply use the URL with the IP address of 192.168.0.2:

http://192.168.0.2:8080/ords/f?p=4550:1:3724779937959:::::

Workspace: Enter obe
Username: Enter obe
Password: Enter oracle

To use the REST demo, again use the URL with the IP address of 192.168.0.2:

http://192.168.0.2:8081/XFILES/Applications/RESTDEMO/JSONREST.html

That’s it!!!

Happy self-education!!!

Paul Guerin is an international consultant that specialises in Oracle database. Paul is based from a global delivery center in South East Asia, but has clients from Australia, Europe, Asia, and North America. Moreover, he has presented at some of the world’s leading Oracle conferences, including Oracle Open World 2013. Since 2015, his work has been featured in the IOUG Best Practices Tip Booklet, and in publications from AUSOUG, Oracle Technology Network, Quest, and Oracle Developers (Medium). In 2019, he was awarded as a most valued contributor for the My Oracle Support Community. He is a DBA OCP, and continues to be a participant of the Oracle ACE program.