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.
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 system set open_cursors=300 scope=spfile;
Note
This query applies only to the existing session. When the session is disconnected, the setting is returned to its previous value.
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.
| 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 (to accommodate the Oracle Enterprise Manager), and that yields the number of processes to set.
After calculating the total number of processes, add another 10% (take the 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.
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
Important
The following configuration is provided as an example of configuring this server quickly for a JBoss ON testing environment. Suggested values in these procedures should not be used in production environments. The procedure should not be used as a supported way of configuring a production server. Always follow the database provider configuration instructions carefully when configuring a production environment.
A specific Oracle database and user need to be configured for JBoss ON to access to store its data.
- Create a dedicated Oracle instance to be used for JBoss ON. This process is described in the Oracle documentation.
- Log into Oracle as the system user.
[jsmith@server ~]$ sqlplus SQL> CONNECT sys/your_sys_password AS sysdba;
- Create a database for JBoss ON. In this example, the database is named rhq. This process is described in more detail in the Oracle documentation.
SQL> CREATE DATABASE rhq; SQL> @?/rdbms/admin/catalog.sql SQL> @?/rdbms/admin/catproc.sql
- 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;
- Grant the required permissions to the Oracle user. At a minimum, this user must have the
connectandresourceroles. For example:SQL> GRANT connect, resource TO rhqadmin;
ImportantWhen configuring Oracle 12c as a backend for JBoss ON, note that theRESOURCErole no longer grantsUNLIMITED TABLESPACEto therhqadminuser by default. Granting this system privilege to a user must be done manually.To accomplish this, therhqadminuser must have adequateQUOTAfor the necessary tablespaces.If granting unlimited tablespace is acceptable, then this can be done explicitly:GRANT UNLIMITED TABLESPACE TO rhqadmin;
Otherwise, specific quota limits must be set. For example:ALTER USER $username QUOTA 100G ON $tablespace_name;
Or set it to unlimited:ALTER USER $username QUOTA -1 ON $tablespace_name;
- 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_RMERRerrors.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. For an unpatched version of Oracle older than 11g R1, then use this line instead:GRANT EXECUTE ON sys.dbms_system TO user;
- 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
