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 13.1.2.1 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 Data_Warehouse.dbc template.
The character set of the instance should be set to AL32UTF8.
The recyclebin and audit_trail features should be disabled for performance reasons.
Monitor the 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 sysdba authorization.
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.
Using the 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:
./ruei-prepare-db.sh create_database
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_TSCONF and 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 TSDEFAULT tablespace:
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:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/toc.htm
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:
./ruei-prepare-db.sh sql_packages
Alternatively, you can install the packages manually with the ux_dbms_lock.sql and ux_dbms_session.sql scripts in the sql_scripts directory.
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:
./ruei-prepare-db.sh create_triggers
Alternatively, you can create the triggers manually using the prepdb_triggers.sql scripts located in the sql_scripts directory.
After the Oracle database instance has been defined, the connection data needs to be set up. This requires two files, sqlnet.ora and tnsnames.ora, in the RUEI home directory (RUEI_DATA).
The following is an example of the contents of the sqlnet.ora file:
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 /etc/ruei.conf file.
The following is an example of the contents of the tnsnames.ora file:
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)))
where 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_TNSNAME and RUEI_DB_USER to the Oracle wallet using the following command:
mkstore -wrl /tmp -createCredential RUEI_DB_TNSNAME RUEI_DB_USER
Two wallet files, ewallet.p12 and cwallet.sso, must be moved to the RUEI_DATA directory on the Reporter system. Both files should have the ownership of RUEI_USER and RUEI_GROUP. Note that ewallet.p12 only needs to be readable by RUEI_USER, while cwallet.sso needs to be readable by both RUEI_USER and 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:
sqlplus /@$RUEI_DB_TNSNAME
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