7 Administrative Tasks for Oracle Machine Learning for R

This chapter describes administrative tasks for maintaining and optimizing OML4R.

This chapter contains these topics:

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
  1. 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

  2. Set R_HOME to the R-3.6.1 location and add $R_HOME/bin to PATH:

    # export R_HOME=/opt/R361/lib64/R

    # export PATH=$R_HOME/bin:$PATH

  3. 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)...

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:

  1. 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:

    1. Back up your OML4R user schema, data store objects, R scripts, and the RQSYS schema.

    2. Remove the Oracle R Distribution RPMs or open source R components.

    3. Install the required R version, then proceed to Step 2.

  2. To upgrade OML4R Server for Oracle Database Release 12c or earlier, run the server.sh or server.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. (Type No to abort the process.)

    See About the Server Script for details.

  3. 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, and rqcfg.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.

  4. 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.

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 script ore_srcexport.pl for exporting the RQSYS schema and all OML4R user data to a dump file.

  • imp — contains the script ore_destimport.pl for importing the RQSYS schema and all OML4R user data from the dump file created by ore_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.

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.

If you do not migrate the OML4R Server components to the new 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.
  1. Before migrating the OML4R components, back up the RQSYS schema and OML4R user schema.
  2. Run the OML4R Server installation script against the new ORACLE_HOME.
    $ ./server.sh
  3. As the sysdba user, verify that the OML4R configuration script is pointing to the new ORACLE_HOME:
    SQL> SELECT * FROM sys.rq_config;
    NAME                                                       VALUE
    ——————-----  ————————————————————————--------------------------- 
    R_HOME       /usr/lib64/R  
    R_LIBS_USER  /u01/app/oracle/product/12.2.0.1/dbhome_1/R/library
    VERSION      1.5.1
    ...
  4. As the sysdba user, verify that the OML4R dependent libraries ore.so and librqe.so are in the new ORACLE_HOME:
    SQL> SELECT library_name, file_spec FROM all_libraries WHERE owner = 'RQSYS';
    LIBRARY_NAME                                                FILE_SPEC
    ------------  -------------------------------------------------------
    RQ$LIB        /u01/app/oracle/product/12.2.0.1/dbhome_1/lib/ore.so
    RQELIB        /u01/app/oracle/product/12.2.0.1/dbhome_1/lib/librqe.so
  5. Finally, test the OML4R installation against the upgraded ORACLE_HOME by connecting your OML4R client to the OML4R server and running OML4R examples, such as those in the Correlating Data and Using the ore.tableApply Function topics in Oracle Machine Learning for R User’s Guide. These examples use the iris data set in the datasets package that is included in an R distribution.

7.5 Uninstall Oracle Machine Learning for R

Instructions for uninstalling OML4R

This topic contains these sections:

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.

  1. Change directories to $ORACLE_HOME/R/server.
    $ cd $ORACLE_HOME/R/server
  2. If you are using a PDB, connect to it.
    $ ALTER SESSION SET CONTAINER = pdbname;
  3. In SQL, run the uninstall script. The script takes a single input, which is the $ORACLE_HOME location. In the following example, the value of the ORACLE_HOME environment variable is /u01/app/oracle/product/18.0.0/dbhome_1.
    SQL> @rquncfg.sql
    Session altered.
    Enter value for 1: /u01/app/oracle/product/18.0.0/dbhome_1

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 the server.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:

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
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.

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")

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

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:

  1. Log in with system privileges:

    SQLPLUS / AS SYSDBA
    
  2. 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

  3. 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

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.

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:

    1. On the Windows taskbar, right-click the Windows icon and select System.

    2. In the Settings window, under Related Settings, click Advanced system settings.

      Figure 7-1 Advanced System Settings in Windows

      Description of Figure 7-1 follows
      Description of "Figure 7-1 Advanced System Settings in Windows"
    3. On the Advanced tab, click Environment Variables.

      Figure 7-2 Environment Variables Dialog in Windows

      Description of Figure 7-2 follows
      Description of "Figure 7-2 Environment Variables Dialog in Windows"
    4. 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.
    5. 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.

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:

  1. 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.

  2. To create the wallet, follow the instructions in the Oracle Database documentation for your supported platform:

    1. For Oracle Database 12c and later, go to the Oracle Database Documentation page in Oracle Help Center.
    2. Select your version of Oracle Database.
    3. In the Topics section, select Security.
    4. In the Centralized User Management section, select Oracle Database Enterprise User Security Administrator's Guide.
    5. 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.

  3. 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))
          )
    
  4. Specify the connection information in the wallet. Follow the instructions in the Oracle Database security documentation referenced in Step 2.

  5. 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

  1. 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.

  2. Assign wallet credentials.
    $ mkstore -wrl /home/oracle/wallet -createCredential ORCL oml_user
     apassword
  3. In SQL*Plus, log in as OML_USER using the wallet.
    $ sqlplus /@ORCL
  4. 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 the ore.connect special control argument.

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

MIN_VSIZE

32M

Minimum R vector heap memory

MAX_VSIZE

4G

Maximum R vector heap memory

MIN_NSIZE

1M

Minimum number of R cons cells

MAX_NSIZE

20M

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