Oracle® Real User Experience Insight Installation Guide Release 6.5.2 for Linux x86-64 Part Number E20329-02 |
|
|
View PDF · Mobi |
This chapter describes how you can set up a alternative Oracle database instance for use by the Enriched data export facility. The use of this facility is fully described in the Oracle Real User Experience Insight User's Guide.
Note:
Before proceeding with the configuration of the alternative database, it is recommended that you make a backup of your configuration. Select Configuration, then System, then Maintenance, and then Backup and restore.By default, when using the Enriched data export facility, the data is exported to the same database instance as used by the Reporter. However, it is strongly recommended that you configure an alternative database instance for enriched data export. This is due to the following reasons:
The SQL queries used to access the exported data can place a significant performance overhead on the database. Be aware that if large amounts of data need to be handled, complex SQL queries need to be executed, or a number of queries need to be run against the exported data within a 5-minute period, the use of a separate database will provide a significant performance improvement.
The use of a separate export database instance will minimize the impact on your RUEI deployment, as well as provide for easier management of it. Particularly in the case of database sizing and backup.
If you intend to use an alternative export database, this must be an Oracle database version 11gR1 or 11gR2, and installation of the Oracle database software should have been completed before starting the setup procedure described in the rest of this chapter. Be aware that advanced knowledge of Oracle database administration is assumed.
The setup procedure described in this chapter refers to a number of settings (such as RUEI_DB_TNSNAME_BI). These are explained in Table 4-1.
Migration to an Alternative Enriched Data Export Database
Be aware that when migrating Enriched data export from one database to another, the export data currently stored in the previous database is not migrated to the new database. Because the defined data retention policy is no longer enforced on the previous database, any historical data will remain on the previous database. If required, the necessary tables can be manually purged from the previous database.
Access to the data in the export database is available via SQL. Be aware that the SQL queries used to access exported data can place a significant performance overhead on the export database. Therefore, it is recommended that you carefully review the design of your SQL queries to minimize their overhead. In particular, you should ensure that table columns not required for external analysis are dropped from the returned data. In addition, you should try to minimize the number of SQL queries run during a 5-minute period. In particular, try to avoid querying the same data more than once.
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.
Logon to the export database system as the root
user, and issue the following command:
dbca -silent -createDatabase -gdbName EXPORT_DATABASE_NAME \ -sid EXPORT_DATABASE_NAME -characterSet AL32UTF8 \ -templateName Data_Warehouse.dbc -databaseType DATA_WAREHOUSING \ -redoLogFileSize 500 -initParams recyclebin=off -initParams audit_trail=none
where:
EXPORT_DATABASE_NAME
specifies the export database instance name.
For performance reasons, it is recommended that the recyclebin
and audit_trail
features are disabled.
The character set instance should be specified as ALT32UTF8
.
For performance reasons, it is strongly recommended that you use compressed tablespaces.
Issue the following command to enable compression on the USERS tablespace:
alter tablespace USERS default compress;
By default, a single 32 GB datafile is created for the USERS tablespace. For most deployments, you will need to be increase this by using the following command:
alter tablespace USERS add datafile 'user02.dbf' size 5M autoextend on;
Note that in the command shown above, the default datafile location is specified. You are free to specify an alternative location.
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, and the scheduled processes reading the export database tables, you may need to reschedule these maintenance tasks to a period with low traffic/load levels (for example, 03:00). Information on how to reschedule planned maintenance tasks is provided in the Oracle Database Administrator's Guide available at following location:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/memory003.htm#ADMIN11200
Access to the alternative database requires the creation of an authorized user.
Issue the following commands to create the RUEI database user with the minimum required privileges:
create user RUEI_DB_USER_BI identified by "password" default tablespace USERS temporary tablespace TEMP profile DEFAULT quota 50G on USERS; alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited; grant create session, create table to RUEI_DB_USER_BI;
where:
RUEI_DB_USER_BI
specifies the export database user name.
password
specifies the required password variable.
After the alternative 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 data files directory (RUEI_DATA) of the RUEI home directory (RUEI_USER) on the Reporter system.
Ensure that the sqlnet.ora
file contains the following:
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.
Create the tnsnames.ora
file. It should contain the following:
RUEI_DB_TNSNAME_BI =(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain) (PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=RUEI_DB_INST_BI)(SERVER=POOLED)))
where:
RUEI_DB_TNSNAME_BI
specifies the export database connect string.
RUEI_DB_INST_BI
specifies the export database instance name.
Ensure that the HOST
setting specifies your database. If you use a host name, ensure that it is also specified in the /etc/hosts
setup. However, you can also specify an IP address.
For performance reasons, you may want to use a shared pool for all connections to the database.
Within Oracle Database Resident Connection Pooling (DRCP), use the following command to enable shared pooling:
exec dbms_connection_pool.start_pool; exec dbms_connection_pool.configure_pool(inactivity_timeout=>3600, max_think_time=>3600);
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. A wallet should already exist to connect to the Reporter database.
Use the following command to create a temporary copy of the ewallet.p12
and cwallet.sso
files in the RUEI_DATA directory, and add the new database credentials to the wallet:
mkstore -wrl /dev/shm -createCredential RUEI_DB_TNSNAME_BI RUEI_DB_USER_BI
where:
RUEI_DB_TNSNAME_BI
specifies the export database connect string.
RUEI_DB_INST_BI
specifies the export database instance name.
Note that you are prompted for the wallet password and the database password for RUEI_DB_USER_BI.
Move back the ewallet.p12
and cwallet.sso
files to the RUEI_DATA directory. Ensure that the permissions for these files are set correctly. Both files should have the ownership of RUEI_USER and RUEI_GROUP. The ewallet.p12
file only needs to be readable by the RUEI_USER, but both files need to be readable by RUEI_GROUP.
If the database instance has been set up correctly, it should now be possible to access the export 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_BI
If this step fails, you should carefully review the procedure described so far before proceeding.
Edit the /etc/ruei.conf
configuration file on the Reporter system from which you intend to export enriched data.
Use the RUEI_DB_TNSNAME_BI setting to specify the database connect string to be used by the Enriched data export facility.
Important:
Other than the modification described above, do not make any other changes to theruei.conf
file.Within the Reporter, select System, then Maintenance, then System reset, and click the Reapply latest configuration radio button. When ready, click Next. You are prompted to confirm the re-application.
This forces a reload of the ruei.conf
file, and the changes that you have made to it during the setup procedure to take effect. The tables described in Appendix R of the Oracle Real User Experience Insight User's Guide will now be created and populated in the alternative database.
Note that in period between setting RUEI_DB_TNSNAME_BI and the reloading of the ruei.conf
file, messages can appear in the Event log. If these messages do not persist after re-loading of the ruei.conf
file, they can be ignored.