Oracle R Enterprise
Oracle R Enterprise (ORE) makes open source R ready for enterprise. R commands and scripts can run on data stored in Oracle Database. ORE runs as an embedded component of Oracle Database.
Architecture
As shown in below figure, ORE has a client/server architecture.
ORE packages on the client side enables database tables to appear as native R data frames. On the server side R commands can be executed within SQL statements and PL/SQL blocks.
Installation Steps
On server side
- Oracle database Enterprise Edition
- Oracle R distribution (recommended, open source R distro is also supported but have to build from source)
- Oracle R enterprise server
- Oracle R supporting packages
On Client side
- Oracle Database Client
- Oracle R distribution
- Oracle R supporting packages
- Oracle R enterprise supporting packages
On server side
Need to make sure you are using the right combination of Oracle Database EE, Oracle R distribution and Oracle R enterprise. I am installing below combination. Refer
support matrix for details. Note that Oracle R enterprise runs on 64-bit platforms only.
Install Oracle database Enterprise Edition
I am not listing the steps here. Below section explains steps to set environment variables and verify database is up and running.
Set environment variables
After installing oracle database, set below environment variables
Refer Installation and Administration
Guide.
$ bash
ORACLE_HOME=/scratch/rdbms12.1.0.2/app/oracle/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=orcl12c ; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH;export PATH
NLS_LANG=AMERICAN_AMERICA.AL32UTF8;export NLS_LANG
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH;export LD_LIBRARY_PATH
echo $ORACLE_HOME
echo $ORACLE_SID
echo $PATH
echo $NLS_LANG
echo $LD_LIBRARY_PATH
Verify database is up and running.
$ sqlplus / as sysdba
exit
Install Oracle R distribution
I have to update my yum configuration to get this working. Oracle R distribution is part of addon channel. Addon repository url for Oracle Enterprise Linux 6 (64 bit) is give below.
http://public-yum.oracle.com/repo/OracleLinux/OL6/addons/x86_64/
$
I have added below section to my yum repo. Refer my previous
blog entry for more details on configuring yum.
$ sudo su
$ cd /etc/yum.repos.d
$vi /etc/yum.repos.d
Add below section for addons channel. This was missing in my public-yum-ol6.repo.
[oel6_addons]
name=Oracle Linux $releasever Add ons ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/addons/x86_64/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1
Now run yum repolist. Here is a sample output.
Also add proxy and timeout params to /etc/yum.conf if you are behind a proxy server.
proxy=
timeout=300
Without above parameters, got below error while running 'yum repolist'
$yum repolist
Loaded plugins: aliases, changelog, downloadonly, kabi, presto, refresh-packagekit, security, tmprepo, verify, versionlock
Loading support for kernel ABI
http://public-yum.oracle.com/repo/OracleLinux/OL6/UEK/latest/x86_64/repodata/repomd.xml: [Errno 14] PYCURL ERROR 7 - "couldn't connect to host"
Trying other mirror.
repo id repo name status
ol6_UEK_latest Latest Unbreakable Enterprise Kernel for Oracle Linux 6Server (x86_64) 0
ol6_latest Oracle Linux 6Server Latest (x86_64) 0
repolist: 0
After adding proxy and timeout, try yum repolist again
$ yum repolist
Loaded plugins: aliases, changelog, downloadonly, kabi, presto, refresh-packagekit, security, tmprepo, verify, versionlock
Loading support for kernel ABI
....
Now yum is configured properly. Proceed to install R distribution.
# yum install R-3.2.0
sample output below (truncated)
Loaded plugins: aliases, changelog, downloadonly, kabi, presto, refresh-packagekit, security, tmprepo, verify, versionlock
Loading support for kernel ABI
Setting up Install Process
Resolving Dependencies
--> Running transaction check..
.....truncated....
Installed:
R.x86_64 0:3.2.0-2.el6
Dependency Installed:
R-core.x86_64 0:3.2.0-2.el6 R-devel.x86_64 0:3.2.0-2.el6 libRmath.x86_64 0:3.2.0-2.el6 libRmath-devel.x86_64 0:3.2.0-2.el6
Complete!
Download Oracle R Packages
Got to
download link.
Accept license and download client, supporting and server packages(ORE 1.5).
$ cd /scratch/softwares/R/
$ ls
ore-server-linux-x86-64-1.5.zip
ore-supporting-linux-x86-64-1.5.zip
$ unzip ore-server-linux-x86-64-1.5.zip
$ unzip ore-supporting-linux-x86-64-1.5.zip
$ ./server.sh -i
This failed with below error
./server.sh -i --sys Chef1420
Oracle R Enterprise 1.5 Server.
Copyright (c) 2012, 2015 Oracle and/or its affiliates. All rights reserved.
Checking platform .................. Pass
Checking R ......................... Pass
Checking R libraries ............... Pass
Checking ORACLE_HOME ............... Pass
Checking ORACLE_SID ................ Pass
Checking sqlplus ................... Fail
ERROR: invalid password
I have to pass @ to get it working.
eg: ./server.sh -i --sys mysyspassword@orcl12c
Refer this
section for other inputs required to complete server.sh
Sample output below:
... truncated.......
RQSYS PERMANENT tablespace ....... SYSAUX
RQSYS TEMPORARY tablespace ....... TEMP
ORE user type .................... New
ORE user name .................... RUSER2
ORE user PERMANENT tablespace .... USERS
ORE user TEMPORARY tablespace .... TEMP
Grant RQADMIN role ............... No
Operation ........................ Install/Upgrade/Setup
Proceed? [yes]
Removing R libraries ............... Pass
Installing R libraries ............. Pass
Installing ORE libraries ........... Pass
Installing RQSYS data .............. Pass
Configuring ORE .................... Pass
Installing RQSYS code .............. Pass
Installing ORE packages ............ Pass
Creating ORE script ................ Pass
Installing migration scripts ....... Pass
Installing supporting packages ..... Pass
Creating ORE user .................. Pass
Granting ORE privileges ............ Pass
Done
On Client Side
Install R Distribution on Linux
Refer similar step "On Server side" section to configure yum. And run below command.
$ yum install R-3.2.0
Install Oracle Database instant client
Go to database instant client download
link.
accept license and download below zip files (basic and sdk zip files)
1) instantclient-basic-linux.x64-12.1.0.2.0.zip
2) instantclient-sdk-linux.x64-12.1.0.2.0.zip
save above file to /scratch/R/client/
Unzip above files
unzip instantclient-basic-linux.x64-12.1.0.2.0.zip
unzip instantclient-sdk-linux.x64-12.1.0.2.0.zip
Need to create symlink to .so files. Refer installation of zip file section for
details.
cd /scratch/softwares/R/db_instant_client/instantclient_12_1
$ ln -s libclntsh.so.12.1 libclntsh.so
$ ln -s libocci.so.12.1 libocci.so
Need to set below env variables after installing ORE client packages.
$ bash
$ OCI_LIB64=/scratch/softwares/R/db_instant_client/instantclient_12_1;export OCI_LIB64
$ PATH=/scratch/softwares/R/db_instant_client/instantclient_12_1:$PATH;export PATH
$ LD_LIBRARY_PATH=/scratch/softwares/R/db_instant_client/instantclient_12_1;export LD_LIBRARY_PATH
Install R client packages
Accept license and download ora client platform package.
ore-client-linux-x86-64-1.5.zip
unzip client zip
$ cd client
$ sudo su
And run below commands. Basically need to replace three thing in the template commands specified
here. (_version_ replaced to _1.5_ , arch replaced to x86_64, platform replaced to linux)
R CMD INSTALL ORE_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL OREbase_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL OREcommon_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL OREdm_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL OREeda_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL OREembed_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL OREgraphics_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL OREmodels_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL OREpredict_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL OREstats_1.5_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL ORExml_1.5_R_x86_64-unknown-linux-gnu.tar.gz
Follow similar steps to install supporting packages also.
$ exit
$ cd ..
$ unzip ore-supporting-linux-x86-64-1.5.zip
$ cd supporting
replace arch -> x86_64
replace platform -> linux-gnu
R CMD INSTALL ROracle_1.2-1_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL DBI_0.3.1_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL png_0.1-7_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL Cairo_1.5-8_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL arules_1.1-9_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL statmod_1.4.21_R_x86_64-unknown-linux-gnu.tar.gz
R CMD INSTALL randomForest_4.6-10_R_x86_64-unknown-linux-gnu.tar.gz
Basically need to replace arch and platform in the template commands specified
here.
Connecting Oracle R Enterprise Client to Oracle R Enterprise Server
$ cd /usr/lib64/R/bin
$
$ ./R
I got below error first time, while loading ORE package.
> library(ORE)
Loading required package: OREbase
Loading required package: OREcommon
Attaching package: 'OREbase'
The following objects are masked from 'package:base':
cbind, data.frame, eval, interaction, order, paste, pmax, pmin,
rbind, table
Loading required package: OREembed
Loading required package: OREstats
Loading required package: MASS
Loading required package: OREgraphics
Loading required package: OREeda
Loading required package: OREmodels
Loading required package: OREdm
Loading required package: lattice
Error in dyn.load(file, DLLpath = DLLpath, ...) :
unable to load shared object '/usr/lib64/R/library/ROracle/libs/ROracle.so':
libclntsh.so.11.1: cannot open shared object file: No such file or directory
Error: package 'OREdm' could not be loaded
Looks like the shared object ROracle.so is referring to libclntsh.so.11.1 instead of libclntsh.so.12.1
bash-4.1$ pwd
/usr/lib64/R/library
bash-4.1$ grep -irsh "libclntsh.so.11.1" *
Binary file ROracle/libs/ROracle.so matches
As a work around, I have created a symlink, so that libclntsh.so.12.1 can be referred as libclntsh.so.11.1.
$ cd /scratch/softwares/R/db_instant_client/instantclient_12_1
ln -s libclntsh.so.12.1 libclntsh.so.11.1
$ cd /usr/lib64/R/bin
$ ./R
$ library(ORE)
Now libraries loaded successfully.
Connect to remote database
> ore.connect(user="ruser2", sid="orcl12c", host="myhostnamehere", password="ruser2", port=1521, all=TRUE)
> ore.is.connected()
[1] TRUE
> ore.disconnect()
Posts
