Antonio Musarra

Posted on Jan 9, 2022 • Originally published at dontesta.it on Jan 9, 2022

(English) How to use Oracle Database 19c Pre-Built Developer VM

#blog #database #oracle #rdbms

Ci spiace, ma questo articolo è disponibile soltanto in Inglese Americano. Per ragioni di convenienza del visitatore, il contenuto è mostrato sotto nella lingua alternativa. Puoi cliccare sul link per cambiare la lingua attiva.

How many times have we faced the arduous setup of an Oracle Database instance for our development environment and “spitting blood”?

I guess these times have been too many, here is a good reason to start using what Oracle makes available to us developers, that is, the Pre-Built Developer VMs for Oracle VM VirtualBox.

In the course of this article, we will see how to setup an Oracle Database 19c VM from the Database Virtual Box Appliance that Oracle provides.

1. Requirements

Before starting, it is necessary to verify that the following requirements are met.

Operating System that supports Virtual Box version 6.x
At least 2 GB of RAM
At least 15 GB of storage space
At least 2 GHz process
Administrator privileges

The reference Virtual Appliance is dated 20/06/2019 whose content is indicated below.

Oracle Linux 7
Oracle Database 19.3
Oracle SQL Developer 19.1
Oracle Application Express 19.1
Hands-On-Labs (accessible via the Toolbar Menu in Firefox)
- Oracle REST Data Services 19.1
- Oracle SQL Developer Data Modeler 19.1
- Oracle XML DB

Attention! This Virtual Appliance is for use in development/test environments only, it should not be used in a pre-production or production environment.

2. Virtual Appliance Setup

The setup procedure consists of some steps summarized below:

Download and install Oracle VM VirtualBox
Oracle DB Developer VM Download
Importing the Virtual Appliance on Oracle VM VirtualBox
Starting and testing the Virtual Appliance

2.1 Download and install Oracle VM VirtualBox

From the Oracle VM VirtualBox home page, you can download the binaries for your operating system. In my case, I proceeded to download the version for macOS.

The installation procedure is very simple and is not the subject of this article; in case of difficulty or further information, refer to the User Manual. The reference version for this article is 6.1.30. Figure 1 shows detailed information about the version installed on my MacBook Pro with macOS Monterey (version 12.1).

Figure 1 – Information on VirtualBox

2.2 Downloading Oracle DB Developer VM

From the Oracle DB Developer VM link you can access the DeveloperDaysVM2019-05-31_20.ova file which contains the Virtual Appliance. The OVA/OVF format is a standard for the packaging and distribution of Virtual Appliances.

Downloading the file requires the availability of an Oracle account (which you can create for free if necessary). The file size is over 7 GByte, so I recommend checking the file checksum at the end of the download. In Console 1, one of the ways for hash verification using the md5 and sha commands available on macOS, Unix / Linux and Windows 10 is identified.

MD5 (sum): 20b5a5bd91cfe9d4f5acf5128f06146e
SHA1: e0b9f8af8158664139b9ca970c3500752fc15fd2

# Verify the checksum MD5 and SHA1 on macOS
$ md5 DeveloperDaysVM2019-05-31_20.ova
$ shasum DeveloperDaysVM2019-05-31_20.ova

# Verify the checksum MD5 and SHA1 on Unix/Linux
$ md5sum DeveloperDaysVM2019-05-31_20.ova
$ sha1sum DeveloperDaysVM2019-05-31_20.ova

# Verify the checksum MD5 and SHA1 on Windows 10
> certutil -hashfile DeveloperDaysVM2019-05-31_20.ova MD5
> certutil -hashfile DeveloperDaysVM2019-05-31_20.ova SHA1

2.3 Importing the Virtual Appliance on Oracle VM VirtualBox

Once the ova file has been obtained, it is possible to proceed with importing it using the Import Appliance (or Import Virtual Application) function of Oracle VM VirtualBox which we can access from the File menu (see Figure 2).

Figure 2 – Access to the Import Appliance function (or Import Virtual Application)

After clicking on the menu item indicated above, you should see a dialog box like the one shown in Figure 3. The only information to specify is the location of the OVA file, after which it will be possible to continue by clicking on the Continue button.

Figure 3 – First Virtual Appliance import step: specify the location of the OVA file

After clicking on the Continue button, a subsequent dialog box will open showing the detailed information (such as CPU, RAM, age.) Of the Virtual Appliance (see Figure 4). It is possible to change some settings, but I recommend leaving those proposed; then continue by clicking on the Import button.

Figure 4 – Second step of Virtual Appliance import: summary of detailed information of the VA and start import

Figure 5 shows the progress of the import process. The duration of the import depends on the strength of your own and the availability of resources at the time of import. In my case, the import process took about 2 minutes.

Figure 5 – Virtual Appliance import process

Once the import process is finished, you should have a situation like the one shown in Figure 6, that is, a new Virtual Machine called Oracle DB Developer VM.

Figure 6 – Imported Oracle DB Developer VM virtual machine

Before you can start the virtual machine, you need to:

Check and/or create a new host network. This will allow access to the Oracle database services also from the host’s network, that is, from our PC
Add a new network adapter to the virtual machine and configure it to use the host’s network

For the first step, access the Host Network Manager window from the __File -> Host Network Manager menu, and you should see what is shown in Figure 7.

Figure 7 – Host network manager

In this case there is no host network interface present, we must therefore proceed with the creation, a step that can be done by clicking on the button that shows the Create label.

If one or more host network configurations are already present, it would be possible to use one of those available.

Figure 8 shows the new host network (vboxnet0) just created and the basic characteristics. After the creation of the network, we have to activate the DHCP Server option, leaving the default settings. This network will be the one that we will then specify on the configuration of the new network card of the virtual machine just imported.

*Important! * After setting the flag to enable the DHCP server, we need to close and reopen Oracle VM VirtualBox, this to make the DHCP server start correctly.

Figure 8 – New host network 192.168.56.1/24 with active DHCP

For the second step, a second network card must be added from the VM network settings by setting the type (“Connected to”) to host-only card, in this case specifying the name of the host network created in the previous step.

Figure 9 shows the configuration of the new network card configured to be connected to the host network (vboxnet0), while Figure 10 the summary of the VM settings after configuring the new network card.

Figure 9 – Configuration of the second network card connected to the host network

Figure 10 – Additional network card also visible on the VM summary

2.4 Starting and testing the Virtual Appliance

Once the step of importing and modifying the network settings has been completed, it is possible to proceed with starting the virtual machine through the start “green button”. Figure 11 shows the Grub boot menu highlighting the Oracle Linux operating system, which will be booted by default (without any action on our part).

Figure 11 – Starting the VM. Grub boot menu of the Oracle Linux operating system

After a few minutes, which can vary a lot depending on your hardware resources, you should find yourself in front of the GNOME desktop as shown in Figure 12 (even if the arrangement of the objects on the desktop may be different).

Figure 12 – GNOME desktop after starting the VM

Figure 12 highlights some important data that will be needed to connect to the Oracle database and machine via SSH. Below, I report these parameters.

Username/Password: oracle/oracle (also sudo user)
Oracle SID: orclcdb
Pluggable DB: orcl

At this point, we must verify that:

the Oracle database services in particular are in listen;
the connection to the database via SQL*Plus works correctly;
the VM is able to communicate with the public network (internet);
the new network card is seen correctly by the Oracle Linux operating system, and configure it via DHCP.

Console 2 shows the commands needed to obtain the above information.

# Verify that the SQLNet service is listening on standard TCP / IP port 1521
$ netstat -ltnp

# Verification of connection to the Oracle database via SQL*Plus with the user who has the SYSDBA role
$ sqlplus sys/oracle as sysdba

# Check connectivity to the public network (internet)
$ ping www.google.it

# Verify the configuration of the network card connected to the host network created earlier
$ ifconfig

# Configuration of the eth1 network card (see output of 
# ifconfig command) to get the IP address from the server 
# DHCP.
$ sudo dhclient eth1

Figures 13, 14 and 15 illustrate in the order indicated in Console 2 the output of the commands given directly on the machine. From Figure 13 it is possible to notice that the SQLNet service is correctly in LISTEN on TCP/IP port 1521 (all interfaces 0.0.0.0).

Figure 13 – Verify that Oracle’s SQLNet service is active on TCP/IP port 1521

From Figure 14 it is possible to notice how the connection to the Oracle database through SQL*Plus takes place in the correct way by showing the banner with the detailed information about the Oracle instance.

Figure 14 – Test connection to the SQLNet service via SQLPlus as a user with the role of SYSDBA

On the desktop (see Figure 12) there is a link to the SQL Developer tool that you could use as an interface to the Oracle database. The tool is already configured with a database connection as SYSDBA user. The version of SQL Developer installed on the VM is quite old, 19.1.

Figure 15 shows the correct configuration of the network card, which acquired the IP address 192.168.56.5 from the DHCP server.

To make the activation of the network card permanent via DHCP, it is possible to act on the Connect automatically flag and then to Apply on the configuration detail of the network card accessible through the Network Manager (see figures below).

Figure 15 – Verifying the correct configuration of the network card configured to use the host network

Figure 16 – Access to the configuration of the network cards

Figure 17 – List of network cards present on the system

Figure 18 – Permanent activation of the new eth1 network card connected to the host network

3. Connect to database from host machine

Once the setup of the Oracle Database VM is finished, we can interact with its services directly from our host machine, thanks to the fact that we are able to reach the SQLNet and SSH service.

# Connect to VM via SSH
# The specified IP address is the one obtained from the command
# ifconfig run on the VM. Address assigned by the server
# Host network DHCP. Remember that the default password
# of the oracle user is oracle
$ ssh oracle@192.168.56.5

To establish the connection to the database, you are obviously free to use any SQL client that supports Oracle. I prefer to stay around Oracle and for this I recommend installing and using SQL Developer whose latest version is 21.41.

Once SQL Developer is installed and started, we can add the database connection using the following parameters.

Username: sys
Password: oracle
Role: SYSDBA
Host Name: 192.168.56.5 (IP address of your VM which may be different from the one indicated here and in Figure 19)
Service Name: orcl

Figures 19 and 20 show the database connection setup and query execution.