Oracle Preparation

The following steps need to be done to Oracle for use with RHQ. More preparation is necessary if the advanced Oracle setup will be used.

1. Create or determine an Oracle instance to be used for the RHQ database.
   It is recommended that the Oracle server to be used by RHQ be running on its own hardware. Install Oracle on the machine to be used, and create a database (you can name the database whatever you want). The Oracle instance will be ready for the next step.
2. Create the user RHQ will use to access Oracle.
   There are several ways to create a user in Oracle. One way is with SQL*Plus. First, log into the Oracle instance as the system user with SQL*Plus, then issue the CREATE USER command:

   SQL> CREATE USER rhq IDENTIFIED BY rhq;

   The above command would create a user named 'rhq' with a password of 'rhq'.

3. Grant the required permissions to the Oracle user.
   The Oracle user must possess the connect and resource roles. This can be easily done in SQL*Plus with the GRANT command:

   SQL> GRANT connect, resource TO rhq;

Oracle is now ready to accept a RHQ installation.

Advanced Oracle Configuration

This is optional configuration that can help Oracle perform very well with very large RHQ environments. This configuration is not necessary for small to medium environments. An example of an environment where this type of configuration would help performance is an environment with hundreds of RHQ Agents.

1. Create a new database using Oracle Database Configuration Assistant. Select New Database (Includes datafiles = No). Decline to install the Example Schemas to save space.
2. If this advanced configuration is being used, Oracle should be installed on a dedicated host. So select Typical Memory configuration. Select OLTP as the type of database sizing to use. Allocate as high a percentage of system resources as you can afford. This should be 70-90%, ideally in the higher range.

   Locally Managed Tablespaces All tablespaces should be locally managed.

   Redo Logging should be turned OFF for all tablespaces, by specifying the NOLOGGING clause when creating the tablespaces. In fact, Redo Logging should be turned off for ALL the tablespaces in the same database. This is a major bottleneck for the database, and, in our scenario, we need high throughput, which comes at the expense of recoverability. In other words, export data and back it up when you want to recover.

3. Create the RHQ user.

   CREATE USER rhq IDENTIFIED BY rhq;

4. Grant permissions to the new user:

   GRANT CONNECT, RESOURCE TO rhq;

Number of Sessions Allowed

Under heavy load, the RHQ Server is able to make many database requests concurrently. If your Oracle is not configured to handle the amount of requests the RHQ Server makes, you may see errors in the RHQ Server log that mention "ORA-00018: maximum number of sessions exceeded". If you expect to run an RHQ Server with a lot of agents talking to it (say, 100 or more), consider setting your Oracle SESSIONS configuration parameter to something like 250. See RHQ-46.