7 Administrative Tasks for Oracle Machine Learning for R
This chapter describes administrative tasks for maintaining and optimizing OML4R.
This chapter contains these topics:
- Install Oracle R Distribution on Linux in a Non-Default R_HOME
The Oracle R Distribution RPMs can be installed to a directory other than the default LinuxR_HOME
,/usr/lib64/R
. - Upgrade Oracle Machine Learning for R
You can upgrade OML4R to the current release from any previous release by reinstalling the product. - Migrate Oracle Machine Learning for R Data
OML4R Server includes migration scripts that you can run to migrate the RQSYS schema and OML4R user data from a source database to a target database - Migrate Oracle Machine Learning for R After a Database Upgrade
After upgrading your Oracle Database, you must migrate your OML4R Server components to the newORACLE_HOME
. - Uninstall Oracle Machine Learning for R
Instructions for uninstalling OML4R - Install Additional R Packages on Linux or UNIX
On Linux and UNIX platforms, the OML4R Server installation provides theORE
script, which you can run from the operating system prompt to install additional R packages. - Create a Database User for Oracle Machine Learning for R
In Database 18c or later, therquser.sql
script ships with Oracle Database 18c and later and resides in the$ORACLE_HOME/R/server
directory. The script installs creates a new OML4R user, and the scriptrqgrant.sql
in the same directory applies the required grants to the new user. With Oracle Database 12c and earlier, theserver
script installation process automatically creates or configures a user for OML4R if one does not already exist. - Create and Modify Environment Variables on Windows
If thePATH
,ORACLE_SID
, andORACLE_HOME
environment variables do not exist, you must create them. - Create an Oracle Wallet for an Oracle Machine Learning for R Connection
An Oracle wallet is a password-protected container for storing security credentials in Oracle Database. - Control Memory Used by Embedded R
How to control the memory used by embedded R execution.
7.1 Install Oracle R Distribution on Linux in a Non-Default R_HOME
The Oracle R Distribution RPMs can be installed to a directory other than
the default Linux R_HOME
, /usr/lib64/R
.
The procedure in the following example installs the Oracle R Distribution 3.6.1 RPMs to a non-default location and still allows the user to invoke the previously installed version, R-3.3.0.
The example installs the RPMs into the directory /opt/R361
. It
installs the following RPMs:
R-3.6.1-1.el7.x86_64.rpm
R-core-3.6.1-1.el7.x86_64.rpm
R-devel-3.6.1-1.el7.x86_64.rpm
libRmath-3.6.1-1.el7.x86_64.rpm
libRmath-devel-3.6.1-1.el7.x86_64.rpm
libRmath-static-3.6.1-1.el7.x86_64.rpm
-
From the directory that contains the RPMs, install the Oracle R Distribution 3.6.1 RPMs to a non-default location using the
--prefix
flag:# rpm -i *.rpm --prefix=/opt/R361
-
Set
R_HOME
to the R-3.6.1 location and add$R_HOME/bin
toPATH
:# export R_HOME=/opt/R361/lib64/R
# export PATH=$R_HOME/bin:$PATH
-
Invoke the newly installed R-3.6.1.
# R
Oracle Distribution of R version 3.6.1 (--) -- "Shake and Throw" Copyright (C) The R Foundation for Statistical Computing Platform: x86_64-pc-linux-gnu (64-bit)...
If you still want to use the previous version of R, rename the default R executable
/usr/bin/R
to the old R version; for
example, /usr/bin/R-3.3.0
:
# mv /usr/bin/R /usr/bin/R-3.3.0
Now you can invoke R 3.3.0:
$ R-3.3.0
Oracle Distribution of R version 3.3.0 (--) -- "Action of the Toes"
Copyright (C) The R Foundation for Statistical Computing
Platform: x86_64-unknown-linux-gnu (64-bit)...
Parent topic: Administrative Tasks for Oracle Machine Learning for R
7.2 Upgrade Oracle Machine Learning for R
You can upgrade OML4R to the current release from any previous release by reinstalling the product.
Note:
Upgrading from OML4R 1.1 is not supported on IBM AIX. To upgrade Oracle R Enterprise 1.1 on IBM AIX, first uninstall Oracle R Enterprise 1.1 (including R) and then download and install the later version.
To upgrade OML4R and migrate your data:
-
Ensure that you have the version of R that is required for the release of OML4R that you are installing.
See the table of configuration requirements and server support in Oracle Machine Learning for R System Requirements for On-Premises Database for the R requirement.
If you are don’t need to upgrade R, proceed to Step 2.
If you do need to upgrade R, do the following:
-
Back up your OML4R user schema, data store objects, R scripts, and the RQSYS schema.
-
Remove the Oracle R Distribution RPMs or open source R components.
-
Install the required R version, then proceed to Step 2.
-
-
To upgrade OML4R Server for Oracle Database Release 12c or earlier, run the
server.sh
orserver.bat
script to perform an installation.When the script detects an earlier version of OML4R Server, it asks if you want to upgrade. Type
Yes
to start the upgrade. (TypeNo
to abort the process.)See About the Server Script for details.
-
To upgrade OML4R Server for Oracle Database 18c or later, use the
rquncfg.sql
script corresponding to the source database to uninstall the OML4R server, andrqcfg.sql
to reinstall the OML4R server to the target database.See Install Oracle Machine Learning for R Server for Oracle Database 19c or later for details.
-
To upgrade OML4R Client, re-install the OML4R packages and supporting packages. You do not need to uninstall the current packages before installing the new packages.
See Install Oracle Machine Learning for R Client for instructions.
Parent topic: Administrative Tasks for Oracle Machine Learning for R
7.3 Migrate Oracle Machine Learning for R Data
OML4R Server includes migration scripts that you can run to migrate the RQSYS schema and OML4R user data from a source database to a target database
The source and target must have the same version of the Oracle Database and of OML4R Server.
To locate the scripts, navigate to the server
directory and change to the migration
subdirectory.
/oreserver_install_dir/server/migration
The migration
subdirectory contains a README and the following subdirectories:
-
exp
— contains the scriptore_srcexport.pl
for exporting the RQSYS schema and all OML4R user data to a dump file. -
imp
— contains the scriptore_destimport.pl
for importing the RQSYS schema and all OML4R user data from the dump file created byore_screxport.pl
. -
oreuser
— contains scripts for exporting and importing data for a specific OML4R user.
Instructions for running the migration scripts are provided in the README.
Parent topic: Administrative Tasks for Oracle Machine Learning for R
7.4 Migrate Oracle Machine Learning for R After a Database Upgrade
After upgrading your Oracle Database, you must migrate your OML4R Server components to the new ORACLE_HOME
.
ORACLE_HOME
, then running an R function using OML4R embedded R execution results in errors such as:ORA-28578: protocol error during callback from an external procedure
The components of OML4R Server are:
-
The Oracle Database schema RQSYS and schema-related objects
-
Oracle Database shared libraries for supporting OML4R clients
-
OML4R packages and supporting packages installed on the Oracle Database server
After a database upgrade, you must migrate the RQSYS schema and dependent database components to the new ORACLE_HOME
. The OML4R packages must also be installed to the new database location.
You can easily do this by running the OML4R Server installation script against the new ORACLE_HOME
. Doing so creates a new path to the ORACLE_HOME
in the OML4R metadata.
The following steps illustrate migrating OML4R 1.5.1 from an initial database installation on a Linux system to a new database after a database upgrade. Oracle Database was upgraded from Release 12.1.0.2 to Release 12.2.0.1. Oracle R Distribution and OML4R are not upgraded, only migrated to the new ORACLE_HOME.
WARNING:
When migrating to an Oracle Database Release 18c or later database instance, use the rqcfg.sql
script. Do not use the server.sh
or server.bat
script.
Parent topic: Administrative Tasks for Oracle Machine Learning for R
7.5 Uninstall Oracle Machine Learning for R
Instructions for uninstalling OML4R
This topic contains these sections:
- Uninstall OML4R Server from Oracle Database 18c or Later
How to uninstall OML4R from Oracle Database Release 18c or later. - Uninstall OML4R Server from Oracle Database 12c and Earlier
To uninstall OML4R Server, run theserver
script with the--uninstall
option. - Uninstall OML4R Client
Instructions for uninstalling OML4R Client.
Related Topics
Parent topic: Administrative Tasks for Oracle Machine Learning for R
7.5.1 Uninstall OML4R Server from Oracle Database 18c or Later
How to uninstall OML4R from Oracle Database Release 18c or later.
The rquncfg.sql
script uninstalls the database functions and procedures that are associated with OML4R. It does not remove the OML4R libraries in $ORACLE_HOME/lib
because these are shipped with Oracle Database. Also, it does not remove the OML4R packages in $ORACLE_HOME/R/library
.
To uninstall OML4R Server components, run the rquncfg.sql
script.
Parent topic: Uninstall Oracle Machine Learning for R
7.5.2 Uninstall OML4R Server from Oracle Database 12c and Earlier
To uninstall OML4R Server, run the server
script with the --uninstall
option.
You can perform either a full or a partial uninstall. A partial uninstall is performed by default.
WARNING:
Do not use theserver.sh
script to uninstall Oracle Machine Learning for R components from Oracle Database Release 18c or Release 19c. Doing so results in important files being deleted from the database. Instead, for an 18c or later database, use the rquncfg.sql
script.
This topic has the following sections:
- Performing a Partial Uninstall
A partial uninstall removes the RQSYS metadata and PL/SQL packages from the database but leaves the libraries and R packages that support OML4R Server in Oracle home. - Performing a Full Uninstall
A full uninstall removes the RQSYS schema metadata and PL/SQL code from the database and removes all OML4R Server libraries and R packages from Oracle home.
Related Topics
Parent topic: Uninstall Oracle Machine Learning for R
7.5.2.1 Performing a Partial Uninstall
A partial uninstall removes the RQSYS metadata and PL/SQL packages from the database but leaves the libraries and R packages that support OML4R Server in Oracle home.
If OML4R Server support is installed in more than one database instance in the same Oracle home, or if it is installed in a pluggable database (PDB), then a partial uninstall removes OML4R Server support from the specified database without affecting the other databases. The server
script performs a partial uninstall by default.
Note:
If you accidentally perform a full uninstall for one of the instances or PDBs that share support for OML4R Server, then the other shared instances or PDBs will no longer support OML4R Server. You can easily restore OML4R Server support in Oracle home by rerunning the server
script to perform an installation in one of the shared instances or PDBs.
If you run the server
script with the -u
option, then a partial uninstall is performed. You can specify the --keep
option to explicitly request a partial uninstall. The following commands all perform a partial uninstall of OML4R Server:
./server.sh --uninstall ./server.sh -u ./server.sh -u --keep ./server.sh --uninstall --keep
Related Topics
7.5.2.2 Performing a Full Uninstall
A full uninstall removes the RQSYS schema metadata and PL/SQL code from the database and removes all OML4R Server libraries and R packages from Oracle home.
The following commands each perform a full uninstall of OML4R Server:
./server.sh --uninstall --full ./server.sh -u -full
Note:
If you accidentally perform a full uninstall in a shared Oracle home, then rerun the server
script to reinstall OML4R Server support. See Performing a Partial Uninstall for details.
Related Topics
7.5.3 Uninstall OML4R Client
Instructions for uninstalling OML4R Client.
To uninstall the OML4R packages and supporting packages, start R and type the commands listed in the following example.
Example 7-1 R Commands for Uninstalling OML4R Packages
remove.packages("arules") remove.packages("DBI") remove.packages("Cairo") remove.packages("ORE") remove.packages("OREbase") remove.packages("OREcommon") remove.packages("OREdm") remove.packages("OREdplyr") remove.packages("OREds") remove.packages("OREeda") remove.packages("OREembed") remove.packages("OREgraphics") remove.packages("OREmodels") remove.packages("OREpredict") remove.packages("OREstats") remove.packages("ORExml") remove.packages("png") remove.packages("randomForest") remove.packages("ROracle") remove.packages("statmod")
Parent topic: Uninstall Oracle Machine Learning for R
7.6 Install Additional R Packages on Linux or UNIX
On Linux and UNIX platforms, the OML4R Server installation provides the ORE
script, which you can run from the operating system prompt to install additional R packages.
The ORE
script is a wrapper for the R installation command: R CMD INSTALL
.
By default, R packages are installed in /usr/lib64/R/library
. The OML4R Server installation provides the ORE script, which is executed from the operating system shell to install R packages and to start R. The ORE script is a wrapper for the default R script, a shell wrapper for the R executable. It can be used to start R, run batch scripts, and build or install R packages. Unlike the default R script, the ORE script installs packages to a location writable by the oracle user and accessible by all OML4R users: $ORACLE_HOME/R/library
. All R packages installed with the ORE script are installed to this location.
To execute the script:
ORE CMD INSTALL R_package_name
Parent topic: Administrative Tasks for Oracle Machine Learning for R
7.7 Create a Database User for Oracle Machine Learning for R
In Database 18c or later, the rquser.sql
script ships with Oracle Database 18c and later and resides in the $ORACLE_HOME/R/server
directory. The script installs creates a new OML4R user, and the script rqgrant.sql
in the same directory applies the required grants to the new user. With Oracle Database 12c and earlier, the server
script installation process automatically creates or configures a user for OML4R if one does not already exist.
Example 7-2 Creating an OML4R User
./server.sh . . Choosing ORE user ORE user to use [list]:
Press Enter to display a list of available users.
BI
HR
IX
OE
SCOTT
SH
ORE user to use [list]: ruser2
.
.
If you choose a user that exists, the script configures the user to support OML4R. If you specify a user that does not already exist, the script creates the user.
Example 7-3 Creating an OML4R User in SQL*Plus
You can create an OML4R user in SQL*Plus by following these steps:
-
Log in with system privileges:
SQLPLUS / AS SYSDBA
-
Execute a statement like the following to create the user:
CREATE USER oml_username IDENTIFIED BY password DEFAULT TABLESPACE default_tablepace_name TEMPORARY TABLESPACE temp_tablespace_name QUOTA UNLIMITED ON default_tablespace_name;
See Also:
Oracle Database SQL Language Reference for details about creating a user
-
Grant the required privileges:
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE MINING MODEL TO oml_username;
See Also:
Oracle Database SQL Language Reference for details about granting privileges to a user
- About the RQADMIN Role
Theserver
script installation process creates a database role called RQADMIN.
Parent topic: Administrative Tasks for Oracle Machine Learning for R
7.7.1 About the RQADMIN Role
The server
script installation process creates a database role called RQADMIN.
When the RQADMIN role is granted to an OML4R user, the user can create and drop R scripts for embedded R execution. By default, the server
script does not grant the RQADMIN role to the OML4R user.
Note:
Any OML4R user can execute embedded R, but only OML4R users with the RQADMIN role can create and drop the R scripts.
If you choose to grant the RQADMIN role in SQL*Plus, then log in with system privileges and execute a statement like the following:
SQLPLUS / AS SYSDBA
GRANT RQADMIN TO oml_username;
Caution:
Use caution when granting the RQADMIN role. Only users that require OML4R administrative privileges should have this role.
Related Topics
Parent topic: Create a Database User for Oracle Machine Learning for R
7.8 Create and Modify Environment Variables on Windows
If the PATH
, ORACLE_SID
, and
ORACLE_HOME
environment variables do not exist, you must create
them.
Assign the values specified in Figure 7-2. On Windows systems, you must be an administrator to create or modify environment variables.
To create or modify environment variables on Windows 10:
-
-
On the Windows taskbar, right-click the Windows icon and select System.
-
In the Settings window, under Related Settings, click Advanced system settings.
Figure 7-1 Advanced System Settings in Windows
Description of "Figure 7-1 Advanced System Settings in Windows" -
On the Advanced tab, click Environment Variables.
Figure 7-2 Environment Variables Dialog in Windows
Description of "Figure 7-2 Environment Variables Dialog in Windows" -
Click New to create a new environment variable. Click Edit to modify an existing environment variable.
Note:
Click New to modify either the user variables or system variables. If the users accessing the Windows server use the same version of R and/or Oracle Database, modify system variables. If the users accessing the Windows server use different versions of R and/or Oracle Database, modify the user variables. - After creating or modifying the environment variable, click Apply and then OK to have the change take effect.
-
Note:
The graphical user interface for creating environment variables may vary slightly, depending on your version of Windows.
Parent topic: Administrative Tasks for Oracle Machine Learning for R
7.9 Create an Oracle Wallet for an Oracle Machine Learning for R Connection
An Oracle wallet is a password-protected container for storing security credentials in Oracle Database.
Wallets provide a secure mechanism for specifying connection details in embedded R scripts.
To create a wallet for an OML4R connection:
-
Start Oracle Wallet Manager:
-
(Linux and UNIX) At the command line, enter
owm
. -
(Windows) Select Start, Programs, Oracle-HOME_NAME, Integrated Management Tools, Wallet Manager.
-
-
To create the wallet, follow the instructions in the Oracle Database documentation for your supported platform:
- For Oracle Database 12c and later, go to the Oracle Database Documentation page in Oracle Help Center.
- Select your version of Oracle Database.
- In the Topics section, select Security.
- In the Centralized User Management section, select Oracle Database Enterprise User Security Administrator's Guide.
- See the chapter Using Oracle Wallet Manager.
For Oracle Database 11c, Release 11.2.0.4, see
Using Oracle Wallet Manager
in Oracle Database Advanced Security Guide. -
Locate the connection string for the OML4R database in
tnsnames.ora
. For example:mydb_test = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = myserver) (PORT = 1521) ) (CONNECT_DATA = (sid=ORCL)) )
-
Specify the connection information in the wallet. Follow the instructions in the Oracle Database security documentation referenced in Step 2.
-
After you configure the wallet, you can connect to the OML4R server database by simply specifying the connection identifier. For example:
ore.connect(conn_string = "mydb_test", all = TRUE)
To learn more about
ore.connect
, use the R help command:help(ore.connect)
To Configure an Oracle Wallet for Use with External Procedures
- Create a wallet
store.
$ mkstore -create -wrl /home/oracle/wallet
When prompted to do so, assign a username and password. This example uses the database user OML_USER with the password
apassword
and the PDB ORCL. - Assign wallet
credentials.
$ mkstore -wrl /home/oracle/wallet -createCredential ORCL oml_user apassword
- In SQL*Plus, log in as OML_USER using the
wallet.
$ sqlplus /@ORCL
- Show the
user.
SQL> show user; USER is "OML_USER"
Example 7-4 Testing the Wallet Connection
This example tests using embedded R execution in the wallet connection in an
OML4R session. The example uses the iris
data set that is in the datasets
package that is included in an R
distribution.
ore.doEval(function(){print("TEST")})
TEST_WALLET_DF
function() {
return(as.data.frame(length(iris)))
}
ore.scriptLoad("TEST_WALLET_DF")
ore.doEval(FUN.NAME="TEST_WALLET_DF")
length(iris)
Listing for This Example
> ore.doEval(function(){print("TEST")})
[1] "TEST"
>
> TEST_WALLET_DF
function() {
return(as.data.frame(length(iris)))
}
>
> ore.scriptLoad("TEST_WALLET_DF")
>
> ore.doEval(FUN.NAME="TEST_WALLET_DF")
> length(iris)
1 5
Note:
In embedded R execution, an R function that creates a database connection will fail because Oracle Database does not support recursive external procedures. To connect an embedded R execution function to a database, use theore.connect
special control argument.
Parent topic: Administrative Tasks for Oracle Machine Learning for R
7.10 Control Memory Used by Embedded R
How to control the memory used by embedded R execution.
You can control the memory used by embedded R execution by limiting the heap memory (vector and cons in R terminology) that is automatically managed by the R gc
mechanism. To limit the size of heap memory in the database, use the sys.rqconfigset
utility. The keyword arguments for sys.rqconfigset
are described in the following table.
Table 7-1 SYS.RQCONFIGSET Keyword Arguments
Keyword | Default | Description |
---|---|---|
|
|
Minimum R vector heap memory |
|
|
Maximum R vector heap memory |
|
|
Minimum number of R cons cells |
|
|
Maximum number of R cons cells |
Example 7-5 SQL Commands for Controlling Memory Used by Embedded R
-- Set the minimum R vector heap memory to 20M EXEC sys.rqconfigset('MIN_VSIZE', '20M'); -- Set the maximum R vector heap memory to 100M EXEC sys.rqconfigset('MAX_VSIZE', '100M') -- Set the minimum number of R cons cells to 500x1024 EXEC sys.rqconfigset('MIN_NSIZE', '500K'); -- Set the maximum number of R cons cells to 10x10x1024 EXEC sys.rqconfigset('MAX_NSIZE', '10M'); -- Set maximum vector heap memory and maximum cons cells to unlimited EXEC sys.rqconfigset('MAX_VSIZE', NULL); EXEC sys.rqconfigset('MAX_NSIZE', NULL);
Note:
The sys.rqconfigset
procedure does not control the C type memory that may be allocated by Calloc, Realloc, calloc, or malloc. Such C type memory is mainly created to hold temporary values used by R functions that are implemented in C. Under normal circumstances, C type memory is limited in size and does not significantly affect the memory usage of R.
The sys.rqconfigset
procedure edits settings in a configuration table called sys.rq_config
. You can view the contents of this table to verify various environment settings for OML4R. Among the settings stored in sys.rq_config
are the memory limits for embedded R. If necessary, you can modify these memory limits, however in most cases you should not modify the values in sys.rq_config
.
The following query shows sample values stored in sys.rq_config
.
SQL> SELECT * FROM sys.rq_config; NAME VALUE ------------------------- ----------------------------------------------------- R_HOME /usr/lib64/R R_LIBS_USER /dbhome_1/R/library VERSION 1.5.1 MIN_VSIZE 32M MAX_VSIZE 4G MIN_NSIZE 2M MAX_NSIZE 20M
Parent topic: Administrative Tasks for Oracle Machine Learning for R