SQL> CREATE USER rhq IDENTIFIED BY rhq;
RHQ currently supports Oracle versions 10g or 11g (support for 10g is deprecated). Earlier versions are not supported.
To install Oracle, please consult the Oracle documentation.
When you have an Oracle server installed, continue to the next section, Oracle Preparation, to prepare your database.
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 Oracle Advanced Preparation for that information.
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.
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;
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;
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.
Due to licensing restrictions, RHQ does not ship with the Oracle JDBC driver. Thus, out of box, RHQ will not be able to connect to your Oracle database - you must download the Oracle JDBC driver yourself (accepting all licensing agreements required by Oracle) and install it in the appropriate location within the RHQ server installation.
You can download the Oracle JDBC driver from this Oracle URL: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html.
Once you obtain the Oracle JDBC driver jar file, you must copy it to the <rhq-server-install-dir>/modules/org/rhq/oracle/main directory. You also must make sure the name of the jar matches that in the module.xml file located in the same directory (see the <resource-root> element's "path" attribute). Do this before running the server or installer.
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_xa 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;
As per the Oracle documentation, the Oracle XA recover method requires SELECT privilege on DBA_PENDING_TRANSACTIONS and EXECUTE privilege on SYS.DBMS_XA as listed above. However, for database versions prior to Oracle Database 11g Release 1 (11.1), where an Oracle patch including a fix for bug 5945463 is not available or it is infeasible to apply the patch for the particular application scenario, the recover method further requires EXECUTE privilege on SYS.DBMS_SYSTEM. Thus an additional GRANT must be added to the list above:
GRANT EXECUTE ON sys.dbms_system TO rhq;
Additional information can be found in the JBoss Transaction Admin Guide.
The following recommendations were based on Oracle 10g - your mileage may vary. Do not take these recommendations as "set in stone" as you may find your environment will operate better with different numbers.
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.
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".
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;
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.
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.
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.