A Generic Database Instance Setup
Note:
While RUEI is supported on Oracle Database releases 11gR2 and later, the best performance for RUEI 13.3.1.0 is achieved with Oracle Database 12c Release 1.
You can download Oracle Database (12c Release 1 or 11g Release 2) Standard Edition, Standard Edition One, or Enterprise Edition from the Oracle database home page at the following location:
http://www.oracle.com/technetwork/database/enterprise-edition/downloads
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 of Database Setup
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). For more information, see Check The RUEI Configuration File. -
The instance should be based on the
Data_Warehouse.dbc
template. -
The character set of the instance should be set to
AL32UTF8
. -
The
recyclebin
andaudit_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. 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/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.
Using the ruei_database.dbt
template (32K blocksize) which can be found in the /root/ruei/db_templates/
directory, they should be consistent with the following:
dbca -silent -createDatabase -gdbNameRUEI_DB_INST
-sidRUEI_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.
Creating Tablespaces
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. 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 on default compress;
The following command line instruction can be used to enable compression on the TSDEFAULT
tablespace:
alter tablespace TSDEFAULT
default compress;
Select Configuration> General>Advanced settings, and then Reporter data retention policy to create the table space. The size of the required database instance is 500 GB (or larger). The required disk space depends on the specified Reporter data retention policy.
For most RUEI deployments, you will require more than a single datafile in the TSDEFAULT
tablespace.The default datafiles location is used, and you may want to specify a different location for the datafiles. Run 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 system:
-
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.
The names of these two tablespaces are fixed and not configurable. The required tablespaces can be created running the following commands:
create tablespaceRUEI_DB_TSCONF
datafile 'uxconf01.dbf' size 5M reuse autoextend on default compress; create tablespaceRUEI_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. The script requires three input variables to be set, one for each configurable table space name.
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, see the Oracle Database Administrator's Guide.
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 running 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 userRUEI_DB_USER
identified by PASSWORD default tablespace TSDEFAULT temporary tablespace TEMP profile DEFAULT quota 500G on TSDEFAULT; alter userRUEI_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 toRUEI_DB_USER
; grant execute on ux_dbms_lock toRUEI_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. The script requires three input variables to be set, one for each configurable table space name.
Creating Database Triggers
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 running the prepdb_triggers.sql
scripts located in the sql_scripts
directory.
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)))
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.
Run the following command to create the Oracle wallet on the database system:
mkstore -wrl /tmp -create
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 running the following command:
mkstore -wrl /tmp -createCredentialRUEI_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
.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 running the following commands:
chownRUEI_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.