Oracle

Oracle version RHQ currently supports Oracle versions 10g or 11g. Earlier versions are not supported.

Oracle Installation

To install Oracle, please consult the Oracle documentation.

When you have an Oracle server installed, continue to the Oracle preparation section to prepare your database.

Oracle Preparation

To use Oracle with RHQ, you will need to follow the following steps. More preparation is required if the advanced Oracle setup will be used - please refer to Advanced Oracle Configuration for that information.

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 run on its own hardware. Install Oracle on the machine to be used, and create a database. You can choose any name for the database. The Oracle instance is now ready for the next step.
2. Create a user that 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 via as the system user with SQL*Plus, then issue the CREATE USER command:

   SQL> CREATE USER rhq IDENTIFIED BY rhq;

   That creates a user named rhq with a password of rhq. If you already have a user with that name and you want to delete it first and start clean, you can drop it via DROP USER rhq CASCADE;

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;

4. Make sure DB_BLOCK_SIZE is at least 8k:

   SQL> show parameter db_block_size;
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   db_block_size                        integer     8192

Oracle is now ready to accept a RHQ installation.

RHQ user requirements for XA

RHQ, internally, uses XA for some of its datasources. To be able to recover from transaction failures, it is required to grant special privileges to the 'rhq' datasource user you just created in the previous section. If you do not do this, XAException.XAER_RMERR errors will occur. The privileges you need to grant include:

GRANT SELECT ON sys.dba_pending_transactions TO rhq;
GRANT SELECT ON sys.pending_trans$ TO rhq;
GRANT SELECT ON sys.dba_2pc_pending TO rhq;
GRANT EXECUTE ON sys.dbms_system TO rhq;

In order to execute the above GRANT statements, you will need to be logged in as SYS. To login as SYS, use: CONNECT sys/your_sys_password AS sysdba;

Sessions and Processes

Currently the recommended algorithm for determining the maximum number of Oracle processes (as kept in v$resource_limit) which RHQ should use is the following:

Take the maximum of:

• 1.5 * total number of RHQ Agents in the environment, and;
• 60 * total number of RHQ Servers in the environment

then add 40 if you are also using Oracle Enterprise Manager (EM). For example if you had 100 RHQ Agents and 2 RHQ Servers, and you were using Oracle EM, you would have:

• 1.5 * 100 = 150
• 60 * 2 = 120

So the maximum of the two is 150; add 40 to support Oracle EM and that gives 190 processes.

The number of sessions (as kept in v$resource_limit) should be:

• 1.1 * number of processes

So, in this example, a maximum of 209 sessions should be sufficient. These settings should prevent you from seeing errors such as ORA-00018: maximum number of sessions exceeded.

SGA and PGA Sizes

Your Oracle settings for SGA and PGA sizes are very important in the performance of RHQ. Too small and your database will perform very slow, affecting RHQ in a very negative way. Talk to your DBA for proper sizing of your Oracle's SGA and PGA requirements. The settings you need to make sure you tune are "sga_target" and "pga_aggregate_target".

Tuning Open Cursors

If the following sql:

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

returns a max_open_cur value of less than 300 then execute:

ALTER SESSION SET OPEN_CURSORS = 300 SCOPE=BOTH;

Oracle Advanced Preparation

There are optional configurations that can help Oracle perform effectively with 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 the Oracle Database Configuration Assistant. Select New Database (Includes datafiles = No). Decline to install the Example Schemas to save space.
2. If an 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 the highest percentage of system resources that you can afford. This should be set between 70-90%, ideally in the higher range.

   Locally manage all tablespaces.