OBIE 12c integration with Oracle R Enterprise
Created: Sep 27, 2016
- 1. Pre-requisites
- 1.1. OS requirements
Make sure that the following version of OS has been installed:
64-bit Oracle Solaris 10 update 10 through
Oracle Solaris 11 for both SPARC and x86-64
(Intel) platforms
Check database server for RT400:
cat /etc/release
Oracle Solaris 10 1/13 s10s_u11wos_24a SPARC
- 1.2. Oracle R Enterprise Server Support Matrix
Oracle R Enterprise | Open source R or Oracle R Distribution | Oracle Database (see Note) |
- 1.0
- 2.13.2
- 11.2.0.3, 11.2.0.4, 12.1
- 1.1
- 2.13.2
- 11.2.0.3, 11.2.0.4, 12.1
- 1.2
- 2.15.1
- 11.2.0.3, 11.2.0.4, 12.1
- 1.3
- 2.15.1
- 11.2.0.3, 11.2.0.4, 12.1
- 1.3.1
- 2.15.1, 2.15.2, 2.15.3
- 11.2.0.3, 11.2.0.4, 12.1
- 1.4
- 3.0.1, 3.1.1
- 11.2.0.3, 11.2.0.4, 12.1.0.1
- 1.4.1
- 3.0.1, 3.1.1
- 11.2.0.3, 11.2.0.4, 12.1.0.1
- 1.5
- 3.2.x
- 11.2.0.4, 12.1.0.1, 12.1.0.2
|
We will use version combination 1.4.1/3.1.1 with 11.2.0.4 database.
- 2. Install Oracle R Distribution software for Oracle Solaris
- 2.1. Download Oracle R Distribution software for Oracle Solaris
Go the Oracle Open Source Software Download page for Oracle R Distribution:
https://oss.oracle.com/ORD/
Software location: /export/media/software/OBIEE/ord311
Use obieeapp account.
cd /export/media/software/OBIEE/ord311
ls -l
-rw-r--r-- 1 dpopovic dba 41363374 Sep 27 14:02 ord-3.1.1-sol10-sparc-64-sunstudio12u3.tar.gz
-rw-r--r-- 1 dpopovic dba 42078748 Sep 27 2016 ord-3.1.1-supporting-sol10-sparc-64-sunstudio12u3.tar.gz
- 2.2. Uncompress the first file (ord-3.1.1-sol10-sparc-64-sunstudio12u3.tar.gz)
tar -xvzf ord-3.1.1-sol10-sparc-64-sunstudio12u3.tar.gz
x ord-3.1.1-sol10-sparc64.pkg, 70257664 bytes, 137222 tape blocks
x install.sh, 322 bytes, 1 tape blocks
x uninstall.sh, 116 bytes, 1 tape blocks
- 2.3. Install ORD
Run install.sh to install Solaris package.
- install.sh (root privilege is required)
Victor has installed it on unhs42 (all zone should be able to see it)
Verify if the package has been installed:
pkginfo -l ORD
PKGINST: ORD
NAME: Oracle R Distribution
CATEGORY: application
ARCH: sparc
VERSION: 3.1.1
BASEDIR: /usr/lib/64
VENDOR: Oracle
PSTAMP: 5thDec12
INSTDATE: Sep 27 2016 15:05
STATUS: completely installed
FILES: 4270 installed pathnames
395 directories
427 executables
136552 blocks used (approx)
- 2.4. Uncompress the second file (ord-3.1.1-supporting-sol10-sparc-64-sunstudio12u3.tar.gz)
As oracle database software owner (data source):
cd $ORACLE_BASE (/db/rpt/rt400/db)
mkdir ordlib
cd ordlib
gunzip -c /export/media/software/OBIEE/ord311/ord-3.1.1-supporting-sol10-sparc-64-sunstudio12u3.tar.gz | tar -xvf -
or on newer version of OS (option z)
tar -xvzf /export/media/software/OBIEE/ord311/ord-3.1.1-supporting-sol10-sparc-64-sunstudio12u3.tar.gz
x libcairo.so, 2937864 bytes, 5739 tape blocks
x libcairo.so.2, 2937864 bytes, 5739 tape blocks
x libdpstrf.a, 30384 bytes, 60 tape blocks
x libfai.so.3, 1602848 bytes, 3131 tape blocks
x libfai2.so.3, 2328696 bytes, 4549 tape blocks
x libfai2_isa.so.3, 2474064 bytes, 4833 tape blocks
x libfmaxlai.so.1, 1366280 bytes, 2669 tape blocks
x libfmaxvai.so.1, 1346840 bytes, 2631 tape blocks
x libfminlai.so.1, 1368072 bytes, 2673 tape blocks
x libfminvai.so.1, 1350680 bytes, 2639 tape blocks
x libfprodai.so.1, 615392 bytes, 1202 tape blocks
x libfsu.so.1, 2759448 bytes, 5390 tape blocks
x libfsumai.so.1, 585248 bytes, 1144 tape blocks
x libfui.so.2, 213952 bytes, 418 tape blocks
x libiconv.so.2, 1086944 bytes, 2123 tape blocks
x libncurses.so.5, 818040 bytes, 1598 tape blocks
x libreadline.so.6, 449104 bytes, 878 tape blocks
x libsunmath.so.1, 642920 bytes, 1256 tape blocks
x libsunperf.so.8, 64806576 bytes, 126576 tape blocks
ls -l
total 175376
-r-xr-xr-x 1 rt400ora dba 2937864 Jan 8 2015 libcairo.so
-r-xr-xr-x 1 rt400ora dba 2937864 Jan 8 2015 libcairo.so.2
-r-xr-xr-x 1 rt400ora dba 30384 Jan 8 2015 libdpstrf.a
-rwxr-xr-x 1 rt400ora dba 1602848 Jan 8 2015 libfai.so.3
-rwxr-xr-x 1 rt400ora dba 2474064 Jan 8 2015 libfai2_isa.so.3
-rwxr-xr-x 1 rt400ora dba 2328696 Jan 8 2015 libfai2.so.3
-rwxr-xr-x 1 rt400ora dba 1366280 Jan 8 2015 libfmaxlai.so.1
-rwxr-xr-x 1 rt400ora dba 1346840 Jan 8 2015 libfmaxvai.so.1
-rwxr-xr-x 1 rt400ora dba 1368072 Jan 8 2015 libfminlai.so.1
-rwxr-xr-x 1 rt400ora dba 1350680 Jan 8 2015 libfminvai.so.1
-rwxr-xr-x 1 rt400ora dba 615392 Jan 8 2015 libfprodai.so.1
-rwxr-xr-x 1 rt400ora dba 2759448 Jan 8 2015 libfsu.so.1
-rwxr-xr-x 1 rt400ora dba 585248 Jan 8 2015 libfsumai.so.1
-rwxr-xr-x 1 rt400ora dba 213952 Jan 8 2015 libfui.so.2
-r-xr-xr-x 1 rt400ora dba 1086944 Jan 8 2015 libiconv.so.2
-r-xr-xr-x 1 rt400ora dba 818040 Jan 8 2015 libncurses.so.5
-r-xr-xr-x 1 rt400ora dba 449104 Jan 8 2015 libreadline.so.6
-rwxr-xr-x 1 rt400ora dba 642920 Jan 8 2015 libsunmath.so.1
-rwxr-xr-x 1 rt400ora dba 64806576 Jan 8 2015 libsunperf.so.8
These tar files contain the shared libraries for libR.so:
• libiconv.so.2
• libncurses.so.5
• libreadline.so.6
• libsunperf.so
- libsunperf.so, Sun Performance Library, and its dependent shared libraries
are included in Oracle Solaris Studio.
- 2.5. Add local directory with supporting libraries to LD_LIBRARY_PATH
There is an issue due to two LD_LIBRARY environment variables
LD_LIBRARY_PATH_64
LD_LIBRARY_PATH
I made changes in .profile to support only one variable
unset LD_LIBRARY_PATH_64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_BASE/ordlib:/usr/sfw/lib/sparcv9
Additionally, add path /usr/sfw/lib/sparcv9. It is required for R when loading Cairo package.
- 2.6. Verify libR.so dependency on supporting libraries
Run the following command to verify that libR.so is picking up its shared
library dependencies correctly from the local directory.
ldd -r /usr/lib/64/R/lib/libR.so
libsunmath.so.1 => /db/rpt/rt400/db/ordlib/libsunmath.so.1
libfai.so.3 => /db/rpt/rt400/db/ordlib/libfai.so.3
libfai2.so.3 => /db/rpt/rt400/db/ordlib/libfai2.so.3
libfsumai.so.1 => /db/rpt/rt400/db/ordlib/libfsumai.so.1
libfprodai.so.1 => /db/rpt/rt400/db/ordlib/libfprodai.so.1
libfminlai.so.1 => /db/rpt/rt400/db/ordlib/libfminlai.so.1
libfmaxlai.so.1 => /db/rpt/rt400/db/ordlib/libfmaxlai.so.1
libfminvai.so.1 => /db/rpt/rt400/db/ordlib/libfminvai.so.1
libfmaxvai.so.1 => /db/rpt/rt400/db/ordlib/libfmaxvai.so.1
libfui.so.2 => /db/rpt/rt400/db/ordlib/libfui.so.2
libfsu.so.1 => /db/rpt/rt400/db/ordlib/libfsu.so.1
libmtsk.so.1 => /lib/64/libmtsk.so.1
libm.so.2 => /lib/64/libm.so.2
libreadline.so.6 => /db/rpt/rt400/db/ordlib/libreadline.so.6
libncurses.so.5 => /db/rpt/rt400/db/ordlib/libncurses.so.5
libnsl.so.1 => /lib/64/libnsl.so.1
libsocket.so.1 => /lib/64/libsocket.so.1
librt.so.1 => /lib/64/librt.so.1
libdl.so.1 => /lib/64/libdl.so.1
libiconv.so.2 => /db/rpt/rt400/db/ordlib/libiconv.so.2
libsunperf.so.8 => /db/rpt/rt400/db/ordlib/libsunperf.so.8
libm.so.1 => /lib/64/libm.so.1
libc.so.1 => /lib/64/libc.so.1
libthread.so.1 => /lib/64/libthread.so.1
libkstat.so.1 => /lib/64/libkstat.so.1
libpthread.so.1 => /lib/64/libpthread.so.1
libmp.so.2 => /lib/64/libmp.so.2
libmd.so.1 => /lib/64/libmd.so.1
libscf.so.1 => /lib/64/libscf.so.1
libaio.so.1 => /lib/64/libaio.so.1
libpicl.so.1 => /usr/lib/64/libpicl.so.1
libdoor.so.1 => /lib/64/libdoor.so.1
libuutil.so.1 => /lib/64/libuutil.so.1
libgen.so.1 => /lib/64/libgen.so.1
/platform/SUNW,SPARC-Enterprise/lib/sparcv9/libc_psr.so.1
/lib/sparcv9/../libm/sparcv9/libm_hwcap1.so.2
Looks good.
- 2.7. Start R
Start R by typing R at the command prompt
R
Oracle Distribution of R version 3.1.1 (2014-07-10) -- "Sock it to Me"
Copyright (C) 2014 The R Foundation for Statistical Computing
Platform: sparc-sun-solaris2.10 (64-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
You are using Oracle's distribution of R. Please contact
Oracle Support for any problems you encounter with this
- distribution.
>
- 3. Install Oracle R Enterprise Server for Oracle Solaris
- 3.1. Download Oracle R Enterprise Server software for Oracle Solaris
http://www.oracle.com/technetwork/database/database-technologies/r/r-enterprise/downloads/index.html
cd /export/media/software/OBIEE/ore141
ls -l
total 27168
-rw-r--r-- 1 dpopovic dba 4904153 Sep 27 2016 ore-client-solaris-sparc-64-1.4.1.zip
-rw-r--r-- 1 dpopovic dba 5292703 Sep 27 2016 ore-server-solaris-sparc-64-1.4.1.zip
-rw-r--r-- 1 dpopovic dba 3672125 Sep 27 2016 ore-supporting-solaris-sparc-64-1.4.1.zip
- 3.2. Unzip the files
As oracle database software owner:
cp /export/media/software/OBIEE/ore141/* $ORACLE_BASE/ore
cd $ORACLE_BASE/ore
unzip ore-server-solaris-sparc-64-1.4.1.zip
unzip ore-supporting-solaris-sparc-64-1.4.1.zip
ls -l
total 27264
-rw-r--r-- 1 rt400ora dba 4904153 Sep 27 15:37 ore-client-solaris-sparc-64-1.4.1.zip
-rw-r--r-- 1 rt400ora dba 5292703 Sep 27 15:37 ore-server-solaris-sparc-64-1.4.1.zip
-rw-r--r-- 1 rt400ora dba 3672125 Sep 27 15:37 ore-supporting-solaris-sparc-64-1.4.1.zip
drwxr-xr-x 3 rt400ora dba 8192 Sep 27 15:40 server
-r-xr-xr-x 1 rt400ora dba 54467 Aug 6 2014 server.sh
drwxr-xr-x 2 rt400ora dba 8192 Sep 27 15:40 supporting
- 3.3. Run server.sh script
./server.sh -i
Oracle R Enterprise 1.4.1 Server.
Copyright (c) 2012, 2014 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 ................... Pass
Checking ORACLE instance ........... Pass
Checking CDB/PDB ................... Pass
Checking ORE ....................... Pass
Choosing RQSYS tablespaces
PERMANENT tablespace to use for RQSYS [list]: SYSAUX
TEMPORARY tablespace to use for RQSYS [list]: TEMP
Choosing RQSYS password
Password to use for RQSYS:
Choosing ORE user
ORE user to use [list]: RQUSER
Choosing RQUSER tablespaces
PERMANENT tablespace to use for RQUSER [list]: USERS
TEMPORARY tablespace to use for RQUSER [list]: TEMP
Choosing RQUSER password
Password to use for RQUSER:
Current configuration
R Version ........................ Oracle Distribution of R version 3.1.1 (2014-07-10)
R_HOME ........................... /usr/lib/64/R
R_LIBS_USER ...................... /db/rpt/rt400/db/11.2.0/R/library
ORACLE_HOME ...................... /db/rpt/rt400/db/11.2.0
ORACLE_SID ....................... rt400
Existing R Version ............... None
Existing R_HOME .................. None
Existing ORE data ................ None
Existing ORE code ................ None
Existing ORE libraries ........... None
RQSYS PERMANENT tablespace ....... SYSAUX
RQSYS TEMPORARY tablespace ....... TEMP
ORE user type .................... New
ORE user name .................... RQUSER
ORE user PERMANENT tablespace .... USERS
ORE user TEMPORARY tablespace .... TEMP
Grant RQADMIN role ............... No
Operation ........................ Install/Upgrade/Setup
Proceed? [yes] y
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
- 3.4. Verify Oracle R Enterprise Server installation
cd /db/rpt/rt400/db/ore
ls -l *log
-rw-r--r-- 1 rt400ora dba 58 Sep 27 15:44 outcdb.log
-rw-r--r-- 1 rt400ora dba 581 Sep 27 15:47 rqconfig.log
-rw-r--r-- 1 rt400ora dba 526 Sep 27 15:48 rqgrant.log
-rw-r--r-- 1 rt400ora dba 2254 Sep 27 15:47 rqinst.log
-rw-r--r-- 1 rt400ora dba 7206 Sep 27 15:47 rqproc.log
-rw-r--r-- 1 rt400ora dba 777 Sep 27 15:48 rquser.log
Examine log files to verify the success of the installation process.
- 3.5. Validate Oracle R Enterprise functionality
Add RQADMIN role to RQUSER.
sqlplus system/<passwd>
SQL>grant rqadmin to rquser;
To connect Oracle R Enterprise Client to Oracle R Enterprise Server, start R using the ORE script:
ORE
Oracle Distribution of R version 3.1.1 (2014-07-10) -- "Sock it to Me"
Copyright (C) 2014 The R Foundation for Statistical Computing
Platform: sparc-sun-solaris2.10 (64-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
You are using Oracle's distribution of R. Please contact
Oracle Support for any problems you encounter with this
- distribution.
> library(ORE)
Loading required package: OREbase
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
Loading required package: OREpredict
Loading required package: ORExml
> ore.connect("RQUSER", password="rquser10", conn_string="", all=TRUE)
Loading required package: ROracle
Loading required package: DBI
> ore.is.connected()
[1] TRUE
> ore.ls()
character(0)
> CARS <- ore.push(cars)
> head(CARS)
speed dist
1 4 2
2 4 10
3 7 4
4 7 22
5 8 16
6 9 10
> ore.doEval(function() { 123 })
[1] 123
>
- 3.6. Run Oracle R Enterprise example scripts
You can further verify the success of the installation by running the Oracle R Enterprise demo scripts. If a script runs to completion without errors, then the example is successful.
The example scripts are located in $ORACLE_HOME/R/library/ORE/demo.
This R command provides a list of available examples:
demo(package="ORE")
> demo(package="ORE")
Demos in package 'ORE':
aggregate Aggregation
analysis Basic analysis & data processing operations
basic Basic connectivity to database
binning Binning logic
columnfns Column functions
cor Correlation matrix
crosstab Frequency cross tabulations
datastore DataStore operations
datetime Date/Time operations
derived Handling of derived columns
distributions Distribution, density, and quantile functions
do_eval Embedded R processing
esm Exponential smoothing method
freqanalysis Frequency cross tabulations
glm Generalized Linear Models
graphics Demonstrates visual analysis
group_apply Embedded R processing by group
hypothesis Hyphothesis testing functions
matrix Matrix related operations
nulls Handling of NULL in SQL vs. NA in R
odm_ai Oracle Data Mining: attribute importance
odm_ar Oracle Data Mining: association rules
odm_dt Oracle Data Mining: decision trees
odm_glm Oracle Data Mining: generalized linear models
odm_kmeans Oracle Data Mining: enhanced k-means clustering
odm_nb Oracle Data Mining: naive Bayes classification
odm_nmf Oracle Data Mining: non-negative matrix
factorization
odm_oc Oracle Data Mining: o-cluster
odm_svm Oracle Data Mining: support vector machines
push_pull RDBMS <-> R data transfer
rank Attributed-based ranking of observations
reg Ordinary least squares linear regression
row_apply Embedded R processing by row chunks
sampling Random row sampling and partitioning of an
ore.frame
sql_like Mapping of R to SQL commands
stepwise Stepwise OLS linear regression
summary Summary functionality
table_apply Embedded R processing of entire table
>
These commands run two of the examples. The aggregate script tests the use of an R function on data that is resident in database memory; the row_apply script tests embedded R execution.
demo("aggregate", package="ORE")
demo("row_apply", package="ORE")
====================================================
demo("aggregate", package="ORE")
demo(aggregate)
---- ~~~~~~~~~
Type <Return> to start :
> #
> # O R A C L E R E N T E R P R I S E S A M P L E L I B R A R Y
> #
> # Name: aggregate.R
> # Description: Demonstrates aggregations
> # See also summary.R