Introduction
Oracle Compute Infrastructure (OCI) is the next generation cloud platform run by Oracle. Like some of its competitors, Oracle offers an always free tier where you can access a range of cloud products and services without having to pay anything ever. And while you will need to provide credit card information to prove that you are not a bot, it will not going to be charged unless you explicitly upgrade to a paid account.
For us database folks OCI is mainly interesting because you can run a database in there, but OCI has much more stuff: storage, application server, Zeppellin notebooks etc. In this post I will describe how you can set up an R shiny server on an OCI compute instance and connect it to an autonomous database running in the same cloud.
High level overview
This guide assumes that you already have an account on OCI and have created an autonomous database. Both steps are extremely simple (creating a database in OCI hardly requires more than pressing a button on a web console), but if you need any help with that you can refer to OCI hands-on labs.
The high level outline of the remaining steps are as follows:
- Create a Virtual Cloud Network (VCN)
- Create a compute instance
- Install R
- Install R packages
- Configure firewall to open the port 3838 for R shiny
- Add ingress rule in the Security List for the same port on VCN
- Install Oracle client
- Install ROracle with paths pointing to Oracle client
- Set up Oracle Wallet to connect to your database from the Shiny server
While it’s not too complicated, I found that some of the dependencies may fail to install automatically — maybe because I didn’t add some yum repositories properly or something like that. So I just installed them manually.
All in all, the entire installation process should take about 40-45 minutes of your time.
Detailed instructions
1. Log in to your OCI account
2. Create VNC network for the shiny server
Main page, left-hand side menu:
Networking -> Virtual Cloud Networks -> Start VCN Wizard, accept all default options
3. Create a compute instance
Same menu:
Compute -> Instances -> Create Instance
You will need to provide a public ssh key to connect to the instance.
4. Note the public IP address for later
5. Log on to the instance via ssh
ssh -i opc@ip_address_from_prev_step
6. Install R
sudo yum install R
7. Install R shiny (not the server)
sudo su - -c "R -e \"install.packages('shiny',repos='https://cran.rstudio.com/')\""
8. Use the commands specified on rstudio.com:
wget https://download3.rstudio.org/centos6.3/x86_64/shiny-server-1.5.14.948-x86_64.rpm
9. and then install it
sudo rpm -i shiny-server-1.5.14.948-x86_64.rpm
10. Make the shiny server start automatically starting from the next reboot
sudo systemctl enable shiny-server
11. Start the shiny server manually
sudo systemctl start shiny-server
12. Open 3838 port in the firewall
sudo firewall-cmd --permanent --add-port=3838/tcp
sudo firewall-cmd --permanent --add-port=3838/udp
sudo firewall-cmd --reload
13. Add ingress rule for Security List for Rshiny-network with 0.0.0.0/0 as CIDR block, TCP protocol, port=3838
14. Install package cairo-devel which somehow fails to install automatically
sudo yum install cairo-devel
15. Install Cairo R package
sudo su - -c "R -e \"install.packages('Cairo', repos='https://cran.rstudio.com/')\""
16. Download Oracle instant client
wget https://download.oracle.com/otn_software/linux/instantclient/19600/oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/19600/oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/19600/oracle-instantclient19.6-devel-19.6.0.0.0-1.x86_64.rpm
17. Install the Oracle instant client rpms downloaded in the previous step
sudo rpm -i oracle-instantclient19.6-basic-19.6.0.0.0-1.x86_64.rpm
sudo rpm -i oracle-instantclient19.6-sqlplus-19.6.0.0.0-1.x86_64.rpm
sudo rpm -i oracle-instantclient19.6-devel-19.6.0.0.0-1.x86_64.rpm
18. Download ROracle package
wget https://cran.r-project.org/src/contrib/ROracle_1.3-1.tar.gz
19. Install DBI R package
sudo su - -c "R -e \"install.packages('DBI', repos='https://cran.rstudio.com/')\""
20. Install ROracle
sudo su - -c "R CMD INSTALL --configure-args='--with-oci-lib=/usr/lib/oracle/19.6/client64/lib --with-oci-inc=/usr/include/oracle/19.6/client64' path_to_the_file/ROracle_1.3-1.tar.gz"
where path_to_the_file should obviously be replaced with the actual path.
21. Download the wallet from the “DB connection” tab, unpack and edit sqlnet.ora to set DIRECTORY to the actual path where wallet contents are located
22. Create a wallet directory for the shiny user
sudo mkdir -p /etc/ORACLE/WALLETS/shiny/
and copy contents of the wallet zip archive there
That’s it. You can now create a simple database web application to make sure everything works together.
sudo mkdir -p /srv/shiny-server/sample-apps/dbhello chown shiny /srv/shiny-server/sample-apps/dbhello sudo su shiny - cd /srv/shiny-server/sample-apps/dbhello
And then create two files, ui.R:
library(shiny)
shinyUI(pageWithSidebar(
headerPanel("It's Alive!"),
sidebarPanel(
textInput("txtUselessInput",
"Enter useless text:"
)
),
mainPanel(
textOutput("mytext")
)
))
and server.R:
library(shiny)
library(ROracle)
shinyServer(function(input, output) {
output$mytext <- renderText({
con <- dbConnect(drv=dbDriver("Oracle"), user="username", pass="password", dbname='(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.uk-london-1.oraclecloud.com))(connect_data=(service_name=mvrzyguheaz9w3v_myatp_high.atp.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com,OU=Oracle BMCS FRANKFURT,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))')
dbGetQuery(con, "select * from global_name")$GLOBAL_NAME
})
})
with the actual username, password and database connect string (from tnsnames.ora in your wallet zip archive) instead of the placeholders.
At this point, your application should be accessible from the browser at your_public_ip:3838/sample-apps/dbhello.
Should anything go wrong, check the logs (/var/log/shiny-server) for clues. Have fun!
Oracle Diagnostician 