B Generic Database Instance Setup

This appendix describes how you can set up an Oracle database instance for use by the RUEI Reporter that is running on a platform other than Oracle Linux 5.x or RedHat Enterprise Linux 5.x. RUEI supports Oracle database version 11gR1 and 11gR2.

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.

Platform Support

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.

Overview

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

Location of SQL Scripts

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.

Creating the Database Instance

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. They should be consistent with the following:

dbca -silent -createDatabase -gdbName RUEI_DB_INST -sid RUEI_DB_INST \
-characterSet AL32UTF8 -templateName Data_Warehouse.dbc -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 USERS tablespace, three additional tablespaces must be created for the RUEI Reporter system:

Creating Tablespaces

For performance reasons, it is strongly recommended that you use compressed tablespaces. The following command line instruction can be used to enable compression on the USERS tablespace:

alter tablespace USERS default compress;

The size of the required database instance is 200 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 USERS 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 USERS add datafile 'user02.dbf' size 5M autoextend on;

In addition to the USERS tablespace, three additional tablespaces must be created for the Reporter and Processing Engine systems:

UXCONF: contains RUEI configuration information. Typically, less than 1 GB in size. UXSTAT: contains RUEI statistics information used for internal purposes. Typically, only a few GB in size. UXTEMP: contains RUEI temporary tables. Typically, several GB in size.

Note that the names of these three tablespaces are fixed and not configurable. The required tablespaces can be created using the following commands:

create tablespace UXCONF datafile 'uxconf01.dbf' size 5M reuse autoextend on default compress;
create tablespace UXSTAT datafile 'uxstat01.dbf' size 5M reuse autoextend on default compress;
create tablespace UXTEMP datafile 'uxtemp01.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.

DRCP Connection Pooling

For performance reasons, it is strongly recommended that you use a shared pool for all connection to the database. The following is an example of how to activate a shared pool for the database:

exec dbms_connection_pool.start_pool;
exec dbms_connection_pool.configure_pool(inactivity_timeout=>3600, max_think_time=>3600);

Alternatively, instead of using the commands described in this section, connection pooling can also be enabled by running the prepdb_drcp.sql SQL script.

Rescheduling Oracle Database Maintenance

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.

Installing SQL Packages

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.

Creating the RUEI Database User

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 USERS
         temporary tablespace TEMP
         profile DEFAULT
         quota 200G on USERS;

alter user RUEI_DB_USER
         quota unlimited on UXCONF
         quota unlimited on UXSTAT
         quota unlimited on UXTEMP;

alter profile DEFAULT
         limit PASSWORD_LIFE_TIME unlimited;

grant    create session,
         create sequence,
         create table,
         create trigger,
         create view,
         create synonym,
         create type,
         create procedure,
         create materialized view
         to RUEI_DB_USER;

grant execute on dbms_crypto to RUEI_DB_USER;
grant execute on ux_dbms_lock 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.

Setting up the Connection Data

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)(SERVER=POOLED)))

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.

Setting up the Oracle Wallet

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.