This appendix describes how you can set up an 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 particular 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, 11gR2 or 12c Release1, and installation of the Oracle database software should have been completed before starting the setup procedure described in the rest of this appendix. Be aware that advanced knowledge of Oracle database administration is assumed.
The setup procedure described in this appendix refers to a number of settings (such as RUEI_DB_TNSNAME_BI). These are explained in Table 2-3.
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 automatically 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 particular period. In particular, try to avoid querying the same data more than once.
This section describes the procedure that must be followed in order to setup the database instance on the alternative database server.
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. Do the following:
Logon to the alternative database system as the oracle 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 literal 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. Do the following:
Issue the following SQL command as the System Administrator on the alternative database server 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 add additional table space by using the following SQL 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://docs.oracle.com/cd/E11882_01/server.112/e25494/tasks.htm#ADMIN0235
Access to the alternative database requires the creation of an authorized user. Do the following:
Issue the following commands on the alternative database server 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.
This section describes the procedure that must be followed in order for the Reporter and Processing Engine systems to connect to the alternative database server. Note that this procedure must be followed on the Reporter and each Processing Engine system.
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 directory (RUEI_DATA) on the Reporter and Processing Engine systems. Do the following:
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=RUEI_DATA)))
DIAG_SIGHANDLER_ENABLED = FALSE
Ensure that the DIRECTORY setting points to the directory for RUEI data (RUEI_DATA) specified in the /etc/ruei.conf file.
Edit the tnsnames.ora files on the Reporter and Processing Engine systems. You should add the following:
RUEI_DB_TNSNAME_BI =(DESCRIPTION= (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=BI_database_server) (PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=RUEI_DB_INST_BI)))
where:
BI_database_server specifies the network address (hostname or IP address) of the alternative Enriched data export database server.
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.
The Reporter and Processing Engines require non-interactive access to the alternative Enriched data export 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. Do the following:
Use the following command to add the new credentials to the existing wallet files ewallet.p12 and cwallet.sso:
mkstore -wrl RUEI_DATA -createCredential RUEI_DB_TNSNAME_BI RUEI_DB_USER_BI
where:
RUEI_DB_TNSNAME_BI specifies the export database connect string.
RUEI_DB_USER_BI specifies the user of the remote database.
Note that you are prompted for the wallet password and the database password for RUEI_DB_USER_BI.
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 and each Processing Engine system from which you intend to export enriched data. Use the RUEI_DB_TNSNAME_BI setting to specify the export database connect string. For more information, see Section 2.4.1, "The RUEI Configuration File".
Important:
Other than the modification described above, do not make any other changes to theruei.conf file.Logout and logon again as the moniforce user.
Restart processing on the Reporter system by issuing the following command:
project -restart