2.2. Setting up Oracle

Only two things are required to run JBoss ON on Oracle:
  • A database
  • A user with adequate permissions
Basic configuration follows the process of setting up the database and users. There is also an advanced configuration process that gives more control over the database settings, such as increased memory limits, that can improve performance for large JBoss ON deployments.

NOTE

Manual migration from Oracle 10g to Oracle 11g R2 for an existing JBoss ON server is not supported.

2.2.1. Prepping Oracle Settings

There are several settings in the Oracle configuration that can be tuned to provide better performance for JBoss ON.

2.2.1.1. Setting SGA and PGA Sizes

Oracle settings for SGA and PGA sizes are very important for JBoss ON performance. If these values are too small, the database will be very slow. There are two specific settings to adjust:
  • sga_target
  • pga_aggregate_target
Talk to the database administrator to verify the sizing requirements for Oracle's SGA and PGA settings.

2.2.1.2. Tuning Open Cursors

Run the following SQL command to check if the max_open_cur setting has a value lower than 300:
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;
If the value is lower then 300, then open more cursors:
ALTER SESSION SET OPEN_CURSORS = 300 SCOPE=BOTH;

2.2.1.3. Setting the Number of Processes and Sessions

The v$resource_limit limit sets the maximum number of Oracle processes and sessions which JBoss ON is allowed to have. The equation for this calculation has this general flow:
calculate the number of processes => add additional processes for Enterprise Manager => calculate the total number of sessions (final value)
There are two ways to calculate the number of processes (one using the number of agents and the other the number of servers). Use whichever method results in a higher number.

Table 2.1. Calculating Oracle Processes

Calculation Type Equation Example
Agents 1.5 * number_of_agents 1.5 * 100 agents = 150
Servers 60 * number_of_servers 60 * 2 servers = 120
with Oracle Enterprise Manager highest_number_of_processes + 40 1.5 * 100 agents + 40 = 190

As noted in Table 2.1, “Calculating Oracle Processes”, the calculation is slightly different for systems using Oracle Enterprise Manager. In that situation, first calculate the processes for agents and servers. Then, take whichever value is highest and add another 40, and that yields the number of processes to set.
After calculating the total number of processes, then take that number and multiply it by 1.1 to determine the total number of sessions (and the final value for v$resource_limit).

Example 2.1. Calculating Oracle Processes and Sessions for JBoss ON

Example Corp. is planning to deploy 175 agents and 3 servers. They will be using Oracle Enterprise Manager to manage their Oracle instance.
The first step is to calculate the number of processes based on agents and based on servers:
1.5 * 175 agents = 262.5 processes
60 * 3 servers = 180 process
So the method to use for processes is the agent's method, since that value is higher.
They add another 40 to the number of processes to accommodate the Oracle Enterprise Manager.
262.5 + 40 = 302.5
The total number of process is 302.5. From there, they calculate the number of sessions:
302.5 * 1.1 = 332.75
The final value for their Oracle v$resource_limit limit database setting is 333.

2.2.2. Configuring Oracle

A specific Oracle database and user need to be configured for JBoss ON to access to store its data.
  1. Create or determine an Oracle instance to be used for the JBoss ON database.
    Run the Oracle server for JBoss ON on its own hardware, and then create a database. The database can be named anything.
  2. Log into Oracle as the system user.
    CONNECT sys/your_sys_password AS sysdba;
  3. Create a user that JBoss ON will use to access Oracle. Create the user named rhqadmin with the password rhqadmin. For example:
    SQL> CREATE USER rhqadmin IDENTIFIED BY rhqadmin;
  4. Grant the required permissions to the Oracle user. At a minimum, this user must have the connect and resource roles. For example:
    SQL> GRANT connect, resource TO rhqadmin;
  5. Set additional permissions for the JBoss ON Oracle user that define parameters to handle database commits.
    JBoss ON uses internally two phase commit for some of database actions. To recover from two phase commit failures, the Oracle user has to has appropriate permissions, otherwise the database will return XAException.XAER_RMERR errors.
    Set these four privileges for the user:
    GRANT SELECT ON sys.dba_pending_transactions TO user;
    GRANT SELECT ON sys.pending_trans$ TO user;
    GRANT SELECT ON sys.dba_2pc_pending TO user;
    GRANT EXECUTE ON sys.dbms_xa TO user;
    The GRANT EXECUTE line assumes that the Oracle server is version 11g R1 or 10g R2. For an unpatched version of Oracle older than 11g R1, then use this line instead:
    GRANT EXECUTE ON sys.dbms_system TO user;
  6. Make sure that the db_block_size value is at least 8 KB.
    SQL> show parameter db_block_size;
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size                        integer     8192

2.2.3. Configuring Oracle (Advanced)

There are optional configurations that can help Oracle perform effectively with large JBoss ON environments, such as deployments with hundreds of JBoss ON agents. This configuration is not necessary for smaller environments.

TIP

For advanced configuration, install Oracle using the graphical wizard rather than SQL command-line tools.
  1. Create a new database.
    1. Open the Oracle Database Configuration Assistant.
    2. Select New Database.
    3. Set the Includes datafiles parameter to No.
    4. Decline to install the example schemas to save space.
    5. Select Typical Memory configuration, and then set the database sizing type to OLTP.
    6. Allocate the highest percentage of system resources that the system can afford. This should be between 70% and 90%, with the highest value preferred.

    WARNING

    Locally manage all tablespaces.
  2. Create the JBoss ON user.
    CREATE USER rhqadmin IDENTIFIED BY rhqadmin;
  3. Grant the required permissions to the new user.
    GRANT CONNECT, RESOURCE TO rhqadmin;
  4. Set additional permissions for the JBoss ON Oracle user that define parameters to handle database commits.
    JBoss ON uses internally two phase commit for some of database actions. To recover from two phase commit failures, the Oracle user has to has appropriate permissions, otherwise the database will return XAException.XAER_RMERR errors.
    Set these four privileges for the user:
    GRANT SELECT ON sys.dba_pending_transactions TO rhqadmin;
    GRANT SELECT ON sys.pending_trans$ TO rhqadmin;
    GRANT SELECT ON sys.dba_2pc_pending TO rhqadmin;
    GRANT EXECUTE ON sys.dbms_system TO rhqadmin;