B Setting up an Alternative Enriched Data Export Database Instance

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.

Introduction

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.

Accessing the Export Data

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.

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.

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

Using Compressed Tablespaces

For performance reasons, it is strongly recommended that you use compressed tablespaces.

1. Issue the following command to enable compression on the USERS tablespace:

   alter tablespace USERS default compress;
   

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

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

Creating the RUEI Database User

Access to the alternative database requires the creation of an authorized user.

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

Setting up the Connection Data

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.

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

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

Setting up DRCP Connection Pooling

For performance reasons, you may want to use a shared pool for all connections to the database.

1. 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);
   

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. A wallet should already exist to connect to the Reporter database.

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

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

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

Editing the RUEI Configuration File

Edit the /etc/ruei.conf configuration file on the Reporter system from which you intend to export enriched data.

1. 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 the ruei.conf file.

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