ORE: Oracle R Entreprise installation – Part I

R is a very powerful programming language for statical computing and graphics, it is almost a standard software for data analysis. Oracle recognized the importance of R in data visualization and created Oracle R Entreprise.

What is?

Oracle R Entreprise is a layer. That layer allows the direct mapping of R data types to Oracle database objects and generate SQL using R expressions. That means you can interact with your database directly using R language.
Oracle R Entreprise is also a statistics engine with several functions and procedures executed in Oracle database.
The architecture behind Oracle R Entreprise is a client/server model, where the client contains a collection of R packages to connect and interact directly with database and the server a collection of PL/SQL procedures and functions to support clients requests.

Install on OEL6 – Prerequisites

I’m going to use OEL 6.4 to install server and client software of ORE (Oracle R Entreprise). Besides check if it is a supported operating system, you will need to install R first. The installation of R is required on both server and clients. Installing it on OEL 6 is very simple if you can use yum to install it for you (make sure that oel6_addons and oel6_latest are enabled in public-yum-ol6.repo file).


[ol6_latest]
name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

[ol6_addons]
name=Oracle Linux $releasever Add ons ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/addons/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

After this, you just need to fire up yum and wait (the install size for me of R and all dependencies was 270MB, so be patient)

[root@phoenix ~]# yum install R.x86_64

To verify R installation, just run R in command line. I will use oracle user for testing purposes:

[oracle@phoenix ~]$  R

Oracle Distribution of R version 2.15.2  (--) -- "Trick or Treat"
Copyright (C)  The R Foundation for Statistical Computing
ISBN 3-900051-07-0
Platform: x86_64-unknown-linux-gnu (64-bit)
...
> demo()
> q()
Save workspace image? [y/n/c]: n

After installing R (or before if you wish) you need to verify Oracle Database compatibility. It requires a Oracle Entreprise Edition 11.2 (11.2.0.3 or you will need to install the patch for bug 11678127. Check MOS). Apart from requiring a patched 11.2.0.1/2 database or 11.2.0.3 you also need to configure extproc which is needed for ORE to call external procedures written in another language, R in this particular case.
On newer versions of Oracle, you don’t have to edit listener.ora or tnsnames.ora, only define the environment variables to be used by external procedures in extproc.ora located in $ORACLE_HOME/hs/admin.

Install on OEL6 – ORE Server Installation

First you need to download client and server here: http://www.oracle.com/technetwork/database/options/advanced-analytics/r-enterprise/ore-downloads-1502823.html
Set your environment variables adding $ORACLE_HOME/lib to LD_LIBRARY_PATH and $ORACLE_HOME/bin to PATH. Make sure also that ORACLE_SID is pointing to correct instance.

[oracle@phoenix ~]$  unzip ore-server-linux-x86-64-1.3.1.zip
[oracle@phoenix ~]$  cd server/
[oracle@phoenix server]$  ./install.sh

Oracle R Enterprise 1.3.1 Server Installation.

Copyright (c) 2012, 2013 Oracle and/or its affiliates. All rights reserved.

Checking R ................... Pass
Checking R libraries ......... Pass
Checking ORACLE_HOME ......... Pass
Checking ORACLE_SID .......... Pass
Checking sqlplus ............. Pass
Checking ORACLE instance ..... Pass
Checking ORE ................. Pass

Current configuration
  R_HOME               = /usr/lib64/R
  R_LIBS_USER          = /u01/app/oracle/product/11.2.0/dbhome_1/R/library
  ORACLE_HOME          = /u01/app/oracle/product/11.2.0/dbhome_1
  ORACLE_SID           = fenix

Do you wish to install ORE? [yes] yes

Choosing RQSYS tablespaces
  PERMANENT tablespace to use for RQSYS [SYSAUX]:
  TEMPORARY tablespace to use for RQSYS [TEMP]:

Tablespaces summary
  PERMANENT tablespace = SYSAUX
  TEMPORARY tablespace = TEMP

Installing libraries ......... Pass
Installing RQSYS data ........ Pass
Installing RQSYS code ........ Pass
Installing ORE packages ...... Pass
Creating ORE script .......... Pass

NOTE: ORE has been enabled for all database users. Next, install the
      supporting packages.

Now you have a RQSYS schema with all necessary objects. RQSYS is a locked account.
Next step is to install supporting packages:

[oracle@phoenix ~]$  unzip ore-supporting-linux-x86-64-1.3.1.zip
[oracle@phoenix ~]$  cd supporting/
[oracle@phoenix supporting]$  ORE CMD INSTALL DBI_0.2-5_R_x86_64-unknown-linux-gnu.tar.gz
* installing to library ‘/u01/app/oracle/product/11.2.0/dbhome_1/R/library’
* installing *binary* package ‘DBI’ ...
* DONE (DBI)
[oracle@phoenix supporting]$  ORE CMD INSTALL ROracle_1.1-9_R_x86_64-unknown-linux-gnu.tar.gz
* installing to library ‘/u01/app/oracle/product/11.2.0/dbhome_1/R/library’
* installing *binary* package ‘ROracle’ ...
* DONE (ROracle)
[oracle@phoenix supporting]$  ORE CMD INSTALL png_0.1-4_R_x86_64-unknown-linux-gnu.tar.gz
* installing to library ‘/u01/app/oracle/product/11.2.0/dbhome_1/R/library’
* installing *binary* package ‘png’ ...
* DONE (png)

Now to finish server installation we need to create and GRANT a user to use in the future. Inside your server directory run the following demo_user.sh.It will create a RQUSER that you can use when requesting calls to R and a grant (RQADMIN) that allows users to create or drop R scripts that use the database embedded R engine.

[oracle@phoenix server]$  ./demo_user.sh

Oracle R Enterprise 1.3.1 Server User Creation.

Copyright (c) 2012, 2013, Oracle and/or its affiliates. All rights reserved.

Checking ORACLE_HOME ......... Pass
Checking ORACLE_SID .......... Pass
Checking sqlplus ............. Pass
Checking ORACLE instance ..... Pass
Checking ORE ................. Pass

Current configuration
  ORACLE_HOME          = /u01/app/oracle/product/11.2.0/dbhome_1
  ORACLE_SID           = fenix

Do you wish to create an ORE user? [yes]

Choosing tablespaces
  PERMANENT tablespace to use [USERS]:
  TEMPORARY tablespace to use [TEMP]:

Choosing user
  ORE user to use [rquser]:
  Password to use for user rquser:

Tablespaces and user summary
  PERMANENT tablespace = USERS
  TEMPORARY tablespace = TEMP
  ORE user             = rquser

Creating ORE user ............ Pass
Done
[oracle@phoenix server]$  sqlplus / as SYSDBA

SQL> GRANT RQADMIN to RQUSER;
Grant succeeded.

Last and important step on server installation is verify if everything is working as expected. Fire up R and set .libPaths() to specify the library trees that R knows about and, hence, uses when looking for packages. If no error returs everything is fine with server. .libPaths() is expecting $ORACLE_HOME/R/library

[oracle@phoenix server]$  R
\
Oracle Distribution of R version 2.15.2  (--) -- "Trick or Treat"
Copyright (C)  The R Foundation for Statistical Computing
ISBN 3-900051-07-0
Platform: x86_64-unknown-linux-gnu (64-bit)
...
> .libPaths('/u01/app/oracle/product/11.2.0/dbhome_1/R/library')
> q()
Save workspace image? [y/n/c]: n

Done with the server part. Let’s take the client side.

Install on OEL6 – ORE Client Installation

Things are a little bit easy with the client. Assuming that you have downloaded the client file, just unzip it and install using R CMD INSTALL as follows:

[oracle@phoenix ~]$  unzip ore-client-linux-x86-64-1.3.1.zip

[root@phoenix client]# R CMD INSTALL ORE_1.3.1_R_x86_64-unknown-linux-gnu.tar.gz
* installing to library ‘/usr/lib64/R/library’
* installing *binary* package ‘ORE’ ...

* DONE (ORE)
Making packages.html  ... done
[root@phoenix client]# R CMD INSTALL OREbase_1.3.1_R_x86_64-unknown-linux-gnu.tar.gz
[root@phoenix client]# R CMD INSTALL OREeda_1.3.1_R_x86_64-unknown-linux-gnu.tar.gz
[root@phoenix client]# R CMD INSTALL OREdm_1.3.1_R_x86_64-unknown-linux-gnu.tar.gz
[root@phoenix client]# R CMD INSTALL OREgraphics_1.3.1_R_x86_64-unknown-linux-gnu.tar.gz
[root@phoenix client]# R CMD INSTALL OREpredict_1.3.1_R_x86_64-unknown-linux-gnu.tar.gz
[root@phoenix client]# R CMD INSTALL OREstats_1.3.1_R_x86_64-unknown-linux-gnu.tar.gz
[root@phoenix client]# R CMD INSTALL ORExml_1.3.1_R_x86_64-unknown-linux-gnu.tar.gz

Please be aware that i’m installing client a server on same machine. If you are going to install on different machines make sure that you install Oracle Instant Client (http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html) before you run CMD INSTALL on R.

Install on OEL6 – ORE Post Installation

On server:

[oracle@phoenix server]$  R
> .libPaths('/u01/app/oracle/product/11.2.0/dbhome_1/R/library')

On client. I will connect to local database with lcmarques username. Will also run a test on R layer and embedded R using demos already created by Oracle. Please note that your embedded test will only work if your user has the RQADMIN role.

[oracle@phoenix client]$  R
> library(ORE)
Loading required package: OREbase

Attaching package: ‘OREbase’

The following object(s) are masked from ‘package:base’:

    cbind, data.frame, eval, interaction, order, paste, pmax, pmin,
    rbind, table

Loading required package: OREstats
Loading required package: MASS
Loading required package: OREgraphics
Loading required package: OREeda
Loading required package: OREdm
Loading required package: lattice
Loading required package: OREpredict
Loading required package: ORExml

> ore.connect("lcmarques", password="lcmarques", conn_string="", all=TRUE)
Loading required package: ROracle
Loading required package: DBI

> demo("aggregate", package = "ORE")
	demo(aggregate)
	---- ~~~~~~~~~
> #
> #     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

> demo("row_apply", package = "ORE")
	demo(row_apply)
	---- ~~~~~~~~~

That is it. I will now investigate ORE to see what it is capable 🙂 Feel free to comment.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s