I like to experiment with a lot of different things and of course, a significant number of my projects require an Oracle database and Oracle Rest Data Services (ORDS).
I often use a VirtualBox VM for my project in order to keep it’s environment “clean”.
This guide will walk through the steps to set up a VM with Oracle Xe and ORDS installed and ready to go.
Vagrant
Vagrant automates the creation of a new VM from scratch. I can use a Vagrantfile and some shell scripts to spin up a VM quickly, saving me time and hard drive space compared to keeping a bunch of VMs laying around that I would need to maintain. You can learn more on the Introduction to Vagrant page.
Prerequisites
Before we get started, install VirtualBox and Vagrant. After you’ve finished, download the following.
Downloads
- Oracle Database 18c Express Edition for Linux x64
- Java JRE. Get the 64bit download.
- Oracle REST Data Services (ORDS)
- Goto https://github.com/oracle/vagrant-boxes and either git clone the repository or download it as a zip file and extract it.
Versions
As of the date that I’m writing this post, I’m using the following versions of the above downloads. This walkthrough should work if you use these versions, if you use different versions you may have to make adjustments.
- Oracle Database 18c Express Edition 18.4.0.0.0 (18c)
- Java JRE – jre-8u201-linux-x64.rpm
- ORDS – ords-18.4.0.354.1002.zip
- https://github.com/oracle/vagrant-boxes – Latest commit eb4983d on Dec 7, 2018
Working in the host and virtual machine environments
Some of the following steps will be run inside the VM others in the Host environment. Check the title of the code blocks to make sure you’re in the correct environment.
To open an SSH connection to your new VM run the following command in the project directory created in the next step.
|
1 |
vagrant ssh |
Create the Toolbox
Make a copy of the 18.4.0-XE directory.
|
1 2 |
cp -r vagrant-boxes/OracleDatabase/18.4.0-XE dbDevToolbox cd dbDevToolbox |
When you are working in the virtual machine, the /vagrant/ directory will be mapped to the dbDevToolbox directory on the host machine.
Edit dbDevToolbox/Vagrantfile
Change the name value
|
19 20 |
# define hostname NAME = "db-dev-toolbox" |
Open port 8080 for ORDS to use.
|
53 54 55 56 |
# Oracle port forwarding config.vm.network "forwarded_port", guest: 1521, host: 1521 config.vm.network "forwarded_port", guest: 5500, host: 5500 config.vm.network "forwarded_port", guest: 8080, host: 8080 |
Create the VM and install Oracle Database XE
Run the following command to start the vagrant build process.
|
1 |
vagrant up |
This may take a few minutes to run. When it’s finished it will generate and display the system password, you’ll want to remember it.
At this point you have a new virtual machine with Oracle Xe installed and running.
Using your preferred SQL tool, test a database connection. (localhost:1521/xepdb1)
Optional: If you would prefer to set your own password, enter the following commands in a shell. Replace ‘newPassword’ with the password you want to use.
|
1 2 3 |
sudo su - oracle /home/oracle/setPassword.sh newPassword exit |
Install the Jave Runtime Environment (JRE)
- Switch to the root user.
- Use the .rpm file to install java.
- Check the java version.
- Exit the root user.
|
1 2 3 4 |
sudo su - rpm -ivh /vagrant/jre-8u201-linux-x64.rpm java -version exit |
You should see the following output.
java version “1.8.0_201”
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
Create the file dbDevToolbox/ords_params.properties
This will be used to configure ORDS. Feel free to modify the settings for your environment. If you change the port, remember to also change it in the Vagrantfile in the above step.
For an explanation of these and other settings, you can find the documentation here.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
bequeath.connect=true db.hostname=localhost db.port=1521 db.servicename=xepdb1 rest.services.ords.add=true db.username=ORDS_PUBLIC_USER user.public.password=oracle18 user.tablespace.default=SYSAUX user.tablespace.temp=TEMP schema.tablespace.default=SYSAUX schema.tablespace.temp=TEMP plsql.gateway.add=false standalone.http.port=8080 standalone.mode=false standalone.static.images=/opt/oracle/product/ords/images standalone.use.https=false |
Install ORDS
- Switch to the oracle user.
- Unzip ORDS into the Oracle product directory.
- Copy the above properties file into the ORDS directory.
- Set the LD_LIBRARY_PATH environment variable. This is needed in order to install ORDS with a bequeath connection.
- Run a silent installation of ORDS using the above parameter file.
- Exit the oracle user.
|
1 2 3 4 5 6 7 8 9 10 |
sudo su - oracle unzip -q /vagrant/ords-18.4.0.354.1002.zip -d /opt/oracle/product/ords/ cp /vagrant/ords_params.properties /opt/oracle/product/ords/ echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib' >> /home/oracle/.bashrc && source /home/oracle/.bashrc java -Dconfig.dir=/opt/oracle/product/ords/config -jar /opt/oracle/product/ords/ords.war install simple --parameterFile /opt/oracle/product/ords/ords_params.properties exit |
Autorun ORDS on boot
Create file dbDevToolbox/oracle-ords-18-4 using the following example.
If you decide to write your own, make sure ORDS does not attempt to start until after the database is fully running. For example, in the below file I included ‘# Required-Start: oracle-xe-18c’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
#!/bin/bash # # chkconfig: 2345 80 05 # description: This script is responsible for the ORDS service. # # Required-Start: oracle-xe-18c # processname: oracle-ords-18-4 # Red Hat or SuSE config: /etc/sysconfig/oracle-ords-18-4 # Set path if path not set case $PATH in "") PATH=/bin:/usr/bin:/sbin:/etc export PATH ;; esac # Check if the root user is running this script if [ $(id -u) != "0" ] then echo "You must be root user to run the configure script. Login as root user and then run the configure script." exit 1 fi NAME="Oracle REST Data Services" JAVA=`which java` ORDSWAR="/opt/oracle/product/ords/ords.war" CONFIGDIR="/opt/oracle/product/ords/config" LOGFILE=/tmp/ords_listener.log #start stop 'this' ORDS only picked out by war file. PID=`ps -ef | grep "$ORDSWAR" | grep -v grep |grep java| cut -c9-15` # To start ORDS standalone start() { $JAVA -Dconfig.dir=$CONFIGDIR -Xmx1024m -Xms256m -jar $ORDSWAR standalone 2>&1 > $LOGFILE & RETVAL=$! echo Started PID: $RETVAL echo } # To stop ORDS stop() { if [[ "X" != "${PID}X" ]]; then echo -n "Shutting down $NAME " kill $PID else echo $NAME not running fi echo return 0 } restart() { if [ "X" = "${PID}X" ]; then start else stop start fi } case "$1" in start) start ;; stop) stop ;; restart) restart ;; *) echo $"Usage: $0 {start|stop|restart}" exit 1 ;; esac exit 0 |
- Switch to root user.
- Copy the above file to /etc/init.d/
- Set the file permissions.
- Add the configuration.
- Set the configuration run levels.
- Start ORDS.
- Exit the root user.
|
1 2 3 4 5 6 7 |
sudo su - cp /vagrant/oracle-ords-18-4 /etc/init.d/ chmod 775 /etc/init.d/oracle-ords-18-4 chkconfig --add oracle-ords-18-4 chkconfig --level 2345 oracle-ords-18-4 on /etc/init.d/oracle-ords-18-4 start exit |
Test
At this point, your VM should have both Oracle Database XE and ORDS installed and running. If you’re already familiar with ORDS you should create a test module to make sure everything is working.
If you’re new to ORDS you can create the file dbDevToolbox/setupHr.sql. This script will enable the HR user and create an ORDS module.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password; connect hr/password@localhost:1521/xepdb1 BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'test_mod', p_base_path => '/test_mod/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'test_mod', p_pattern => 'test_pat', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'test_mod', p_pattern => 'test_pat', p_method => 'GET', p_source_type => 'json/query', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'select ''test'' from dual' ); COMMIT; END; / exit |
- Switch to the oracle user.
- Use SQL*Plus to run the above script. Replace <YourPassword> with the sys password from the beginning of the post.
- Exit the oracle user.
- Use curl to test the REST service.
|
1 2 3 4 |
sudo su - oracle /opt/oracle/product/18c/dbhomeXE/bin/sqlplus sys/<YourPassword>@localhost:1521/xepdb1 as sysdba @/vagrant/setupHr.sql exit curl http://localhost:8080/ords/hr/test_mod/test_pat |
You should see the following output.
|
1 |
{"items":[{"'test'":"test"}],"first":{"$ref":"http://localhost:8080/ords/hr/test_mod/test_pat"}} |
Automate
Creating a new VM with Oracle XE installed is already fully automated. Now, I’m going to show you how to roll the ORDS steps into the Vagrantfile.
The below file collects all of the above steps into a single shell script.
Create the file dbDevToolbox/scripts/installOrds.sh with the following.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
#!/bin/bash echo 'installOrds.sh: Started up' echo 'Install Jave jre' rpm -ivh /vagrant/jre-8u201-linux-x64.rpm echo 'Extract ORDS' unzip -q /vagrant/ords-18.4.0.354.1002.zip -d /opt/oracle/product/ords/ echo 'Copy ORDS properties file to ORDS directory' cp /vagrant/ords_params.properties /opt/oracle/product/ords/ echo 'Make sure the oracle user owns ORDS' chown -R oracle:oinstall /opt/oracle/product/ords/ echo 'Set the LD_LIBRARY_PATH environment variable' echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib' >> /home/oracle/.bashrc echo -n "Waiting for the database to start" while [ $(systemctl is-active oracle-xe-18c) != "active" ]; do echo -n ".." sleep 1 done echo "" echo 'Install ORDS' su -l oracle -c 'java -Dconfig.dir=/opt/oracle/product/ords/config -jar /opt/oracle/product/ords/ords.war install simple --parameterFile /opt/oracle/product/ords/ords_params.properties' echo 'Set up ORDS to run on boot' cp /vagrant/oracle-ords-18-4 /etc/init.d/ chmod 775 /etc/init.d/oracle-ords-18-4 chkconfig --add oracle-ords-18-4 chkconfig --level 2345 oracle-ords-18-4 on echo 'Start ORDS' su -l -c '/etc/init.d/oracle-ords-18-4 start' echo 'installOrds.sh: Complete' |
Edit dbDevToolbox/Vagrantfile
Provision the new script
|
56 57 58 59 60 61 62 63 |
# Provision everything on the first run config.vm.provision "shell", path: "scripts/install.sh", env: { "ORACLE_CHARACTERSET" => "AL32UTF8", "SYSTEM_TIMEZONE" => SYSTEM_TIMEZONE } config.vm.provision "shell", path: "scripts/installOrds.sh", env: {} |
Destroy the current VM and create a new one.
WARNING: This will completely destroy the VM we created above and create a new one from scratch. If you have made any changes you’d like to keep, make sure to back them up first.
- Destroy the VM.
- Confirm that you want to destroy the VM.
- Create a new VM.
|
1 2 3 |
vagrant destroy y vagrant up |
Future project virtual machines
For future projects you’ll only need to:
- Make a copy of the dbDevToolbox directory with a new name.
- Edit the Vagrantfile and modify the NAME entry.
- Run the vagrant up command.
When you’re finished with the project use vagrant destroy to clean up.
It’s rare to find such clear and complete description for this scenario.
Thank you very much, Blaine. This is a big help.