(English) How to use Oracle Database 19c Pre-Built Developer VM
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.
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).
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).
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.
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 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.
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.
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.
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.
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.
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).
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 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
# Configuration of the eth1 network card (see output of
# ifconfig command) to get the IP address from the server
$ 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).
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.
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).
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 firstname.lastname@example.org
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.
At this point, we have our own Oracle development VM that we can use to host the databases of the projects we work on.
4. Useful resources
- How to setup Docker container Oracle Database 19c for Liferay Development Environment
- How to build a Docker Liferay 7.2 image with the Oracle Database support
- Developer Day – Hands-on Database Application Development
- SQL Developer 21.4.1 Downloads
- Docker Images from Oracle
- [PDF] – Liferay Portal CE 7.4: Come configurare Oracle Database 19c