This appendix describes how you can manually set up an Oracle database instance for use by the RUEI Reporter. RUEI supports Oracle database version 11gR2 and 12c Release 1.
Note:While RUEI is supported on Oracle Database releases 11gR2 and later, the best performance for RUEI 126.96.36.199 is achieved with Oracle Database 12c Release 1.
Note that the approach taken in this appendix is to describe the requirements for a generic database instance, rather than a detailed procedural description. Therefore, a sound working knowledge of Oracle database administration is required.
While a wide range of platforms are supported for deployment of a remote database, high performance platforms designed for large queries by comparatively few users offer the best deployment solutions.
Upon completion, the following parameters and settings should be specified for the new Oracle database instance:
RUEI_DB_INST: the name of the new database instance (as specified in the
/etc/ruei.conf file). See Section 2.4.1, "Check The RUEI Configuration File" for more information.
The instance should be based on the
The character set of the instance should be set to
audit_trail features should be disabled for performance reasons.
redolog file size, and adjust the size if necessary.
Each of these requirements is discussed in more detail in the following sections. Note that you are required to have
The SQL scripts referred to as alternatives to the procedures described in the rest of this appendix can found in the
/root/RUEI/extra/sql_scripts/ directory after extraction of the RUEI distribution zip.
The following discussion assumes that the Oracle database instance is created on the command line. However, you are free to use any suitable utility to specify the required parameters.
ruei_database.dbt template (32K blocksize) which can be found in the
/root/RUEI/131/db_templates/ directory, they should be consistent with the following:
dbca -silent -createDatabase -gdbName RUEI_DB_INST -sid RUEI_DB_INST \ -characterSet AL32UTF8 -templateName ruei_database.dbt -databaseType DATA_WAREHOUSING \ -redoLogFileSize 500 -initParams recyclebin=off -initParams audit_trail=none
Alternatively, on Linux platforms, the
ruei-prepare-db.sh script can also be run (as the Oracle user) to create the Oracle database instance as follows:
In addition to the
TSDEFAULT tablespace, two additional tablespaces must be created for the RUEI Reporter system.
Before continuing make sure you have chosen names for the default tablespace (named
TSDEFAULT below for reference), the configuration tablespace (default is
UXCONF) and the statistics tablespace (default is
UXSTAT). The latter two names should also be set in the
/etc/ruei.conf file using the
RUEI_DB_TSSTAT variables respectively. Note that the same tablespace names must be used for all components in your RUEI environment, such as the remote database and Processors.
For performance reasons, it is strongly recommended that you use compressed tablespaces. The following command can be used to create the TSDEFAULT tablespace. Note that the default datafiles location is used, and you may want to specify a different location for the datafiles:
create tablespace TSDEFAULT datafile 'uxdefault01.dbf' size 5M reuse autoextend ondefault compress;
The following command line instruction can be used to enable compression on the
alter tablespace TSDEFAULT default compress;
The size of the required database instance is 500 GB (or larger). The required disk space depends on the specified Reporter data retention policy (select Configuration, then General, then Advanced settings, and then Reporter data retention policy).
For most RUEI deployments, you will require more than a single datafile in the
TSDEFAULT tablespace. Note that the default datafiles location is used, and you may want to specify a different location for the datafiles. Use the following command to add additional datafiles:
alter tablespace TSDEFAULT add datafile 'user02.dbf' size 5M autoextend on;
In addition to the
TSDEFAULT tablespace, two additional tablespaces must be created for the Reporter and Processing Engine systems:
RUEI_DB_TSCONF: contains RUEI configuration information. Typically, less than 1 GB in size.
RUEI_DB_TSSTAT: contains RUEI statistics information used for internal purposes. Typically, only a few GB in size.
Note that the names of these two tablespaces are fixed and not configurable. The required tablespaces can be created using the following commands:
create tablespace RUEI_DB_TSCONF datafile 'uxconf01.dbf' size 5M reuse autoextend on default compress; create tablespace RUEI_DB_TSSTAT datafile 'uxstat01.dbf' size 5M reuse autoextend on default compress;
Alternatively, instead of using the commands described in this section, the table set up can be performed by running the
prepdb_tablespaces.sql SQL script. Note that the script requires three input variables to be set, one for each configurable table space name.
By default, Oracle database maintenance tasks are schedule to run at 22:00. These can have a significant impact on the overall database performance. Therefore, depending on traffic levels within the monitored environment, you may need to reschedule these maintenance tasks to a period with low traffic/load levels (for example, 03:00). For information on how to reschedule planned maintenance tasks, refer to the Oracle Database Administrator's Guide available at the following location:
The documented procedure can also be performed by running the
prepdb_maintenance_schedule.sql SQL script.
RUEI requires additional packages to be installed. These can be installed by issuing the following command:
Alternatively, you can install the packages manually with the
ux_dbms_session.sql scripts in the
This section explains the creation of the RUEI database user, and the permissions it must be assigned.The RUEI database user is specified in the
RUEI_DB_USER setting (in the
/etc/ruei.conf file). It receives the minimum required permissions. However, note that the
dbms_crypto permission is required for encryption of the SSL private keys that a Collector is using. In addition, because RUEI typically operates in an unattended 7x24 environment, the
PASSWORD_LIFE_TIME permission should be set to unlimited.The following examples show how the RUEI database user can be created with the minimum required permissions.
create user RUEI_DB_USER identified by PASSWORD default tablespace TSDEFAULT temporary tablespace TEMP profile DEFAULT quota 500G on TSDEFAULT; alter user RUEI_DB_USER quota unlimited on RUEI_DB_TSCONF quota unlimited on RUEI_DB_TSSTAT; alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited; grant create session, create sequence, create table, create trigger, create view, create synonym, create database link, create procedure, create materialized view, create type to RUEI_DB_USER; grant execute on dbms_crypto to RUEI_DB_USER; grant execute on ux_dbms_lock to RUEI_DB_USER; grant execute on ux_dbms_session to RUEI_DB_USER;
Alternatively, instead of using the commands described in this section, the RUEI database user configuration can be performed by running the
prepdb_user.sql SQL script. Note that the script requires three input variables to be set, one for each configurable table space name.
RUEI requires additional database triggers to be created. Create these triggers using the following command:
Alternatively, you can create the triggers manually using the
prepdb_triggers.sql scripts located in the
After the Oracle database instance has been defined, the connection data needs to be set up. This requires two files,
tnsnames.ora, in the RUEI home directory (
The following is an example of the contents of the
NAMES.DIRECTORY_PATH = (TNSNAMES) SQLNET.WALLET_OVERRIDE = TRUE WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/var/opt/ruei))) DIAG_SIGHANDLER_ENABLED = FALSE
Ensure that the
DIRECTORY setting points to the directory for RUEI data files (
RUEI_DATA) specified in the
The following is an example of the contents of the
uxinsight=(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=ruei)))
In the example above,
uxinsight is the database alias (
RUEI_DB_TNSNAME) specified in the
/etc/ruei.conf file. Ensure that the
HOST setting specifies your database. If you specify a host name, ensure that it is also specified in the
/etc/hosts setup. However, you can also specify an IP address.
If you have installed a processing engine as described in Chapter 8, "Installing Processing Engines" you require an additional entry for the processor engine to connect to a central reporter database. Add an alias similar to the following in your tnsnames.ora on the processing engine host:
config=(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=reporter-hostname)(PORT=1521))) (CONNECT_DATA=(SID=ruei)))
config is the alias which has been specified in RUEI_DB_TNSNAME_CFG in the
/etc/ruei.conf configuration file on the processing engine host.
The processing part of RUEI requires non-interactive access to the Oracle database. In order to achieve this, the Oracle
autologin wallet is used to store passwords securely.
Use the following command to create the Oracle wallet on the database system:
mkstore -wrl /tmp -create
Note that you are prompted for the wallet password.
After the (empty) wallet has been created, you must add the credentials of
RUEI_DB_USER to the Oracle wallet using the following command:
mkstore -wrl /tmp -createCredential RUEI_DB_TNSNAME RUEI_DB_USER
Two wallet files,
cwallet.sso, must be moved to the
RUEI_DATA directory on the Reporter system. Both files should have the ownership of
RUEI_GROUP. Note that
ewallet.p12 only needs to be readable by
cwallet.sso needs to be readable by both
RUEI_GROUP. On Linux, this can be accomplished by issuing the following commands:
chown RUEI_USER:RUEI_GROUP *wallet* chmod 600 ewallet.p12 chmod 640 cwallet.sso
If the Oracle database instance has been set up correctly, it should now be possible to enter the database without being prompted for the password. The
RUEI_USER on the Reporter system can access the database instance as follows:
If this last step fails, you should carefully review the information in this appendix before proceeding with your RUEI deployment.
If you have installed a processing engine as described in Chapter 8, "Installing Processing Engines" the processor needs an additional entry in the wallet, so the processor can authenticate against the reporter database.
To add the credentials to the oracle wallet, use the following command:
mkstore -wrl /RUEI_DATA -createCredential RUEI_DB_TNSNAME_CFG RUEI_DB_USER