Set up Oracle database

The following information provides configuration guidance to use Oracle databases with B2Bi, including:

About the Oracle database

When you use OCI drivers, you must supply the specific driver version that matches your Oracle database version.

After the B2Bi installation, you can find Oracle database drivers at <B2Bi_install_directory>\Interchange\corelib.

Important: You must create the database before you start B2Bi for the first time.

Install and tune Oracle

When you install an Oracle database, use the following parameters for compatibility with B2Bi:

Parameter Recommendation
Listener Port The trading engine by default uses port 1521 (can be configured)
Block Size 8 kilobytes
Undo tablespace 4 gigabytes or greater
Undo management Auto
PGA_AGGREGATE_TARGET
  • 300 megabytes if not using Message Tracker
  • 500 megabytes if using Message Tracker

If you do not use automatic PGA memory, see SORT_AREA_SIZE.

SORT_AREA_SIZE

If you do not use automatic PGA memory:

  • 65 kilobytes if not using Message Tracker
  • 256 kilobytes if using Message Tracker
SHARED_POOL 300 megabytes or greater
DB_CACHE_SIZE

1 gigabyte minimum

If there is available memory, set the buffer as high as possible to enhance performance.

PROCESSES 1000 or greater (see Calculating process requirements)
OPEN_CURSORS 1000
Server Sessions Dedicated (do not use MTS)
Tablespace Extent Management LMT (Locally Managed Tablespace) and AUTOALLOCATE enabled
Tablespace Segment Management ASSM (Automatic Segment Space Management)
Tablespace Default Logging Mode Logging
Redo Groups 3 or greater
Redo File Size

The sizing of redo log files is a based on load and speed of the system, which is a tradeoff between performance (log switching has a high processing cost) and recovery time (a large redo log size may drive the MTTR to a high value). Use the following configurations as a starting point, and adjust as needed:

  • For small load configurations: 250-500 MB
  • For mid-size configurations: 500 MB or greater
  • For high load configurations: 2 GB
Tip   The optimium sizing provides log switching every 15 minutes.
Gathering Oracle Statistics Weekly or with any 10 percent change in record count

Calculate process requirements

The minimum recommended PROCESSES value is 1000. However, additional processes must be allowed if you run multiple nodes.

The total number of required Oracle processes is dependent on:

  • The total number of CN (Control Node) and TE (Trading Engine) nodes for each Interchange cluster that is serviced by the database.
  • The parameter value MaxConnectionsPerPartition in <B2Bi_install_directory>/interchange/conf/datastoreconfig.xml.
Note   MaxConnectionsPerPartition is dependent on the following two parameter values if you have set custom values in <B2Bi_install_directory>/interchange/conf/tuning.properties:
    • systemThrottle.maximumTaskQueueSize
    • MaxConnectionsPerPartition

To calculate the Oracle PROCESSES requirement:

  1. Compute the required Oracle processes separately for each cluster of each environment that the database services.
  2. Add the separate number of required processes together to determine the grand total of processes needed in the instance of Oracle.

Generic formula to compute the PROCESSES value for a single environment

Use the following generic formula to compute the PROCESSES value for a single environment:

[(Number of CN nodes) * (Value of MaxConnectionsPerPartition) = total CN connections]

+

[(Number of TE nodes) * (Value of MaxConnectionsPerPartition) = total TE node connections]

+

1000 minimum processes required

= Total number of processes required in Oracle for a single environment

Example calculation

If you calculate the number of processes for a three machine cluster installation of B2Bi, by default, datastoreconfig.xml specifies a maximum of 100 connections per node (partition), for example:

<Property key="openjpa.ConnectionProperties" value="DisableConnectionTracking=true,ReleaseHelperThreads=0,MinConnectionsPerPartition=50,MaxConnectionsPerPartition=100,ConnectionTimeoutInMs=30000, IdleMaxAgeInMinutes=10,ConnectionTestStatement=&quot;SELECT SYSDATE FROM DUAL&quot;"/>

If tuning.properties is not customized, you can use the default MaxConnectionsPerPartition=100 value in datastoreconfig.xml.

Each machine in the cluster has one control node. If you use the default datastoreconfig.xml value it would take at least n * 100 processes in Oracle to account for the control nodes alone (where n is the number of machines in the cluster). Additionally, you must add 100 for each trading engine node.

For example, when you have with a cluster of three machines with:

  • 2 trading engine nodes on machine A
  • 2 trading engine nodes on machine B
  • 3 trading engine nodes on machine C

The following processes are required:

3 * 100 = 300 control node connections

+

(2 + 2 + 3) * 100 = 700 trading engine node connections

Total of 300 + 700 + the 1000 minimum = 2000 processes required in Oracle for this example.

Note   You must allow a minimum value of 800 processes.

Oracle's Dead Connection Detection (DCD)

Oracle's Dead Connection Detection (DCD) feature enables you to recover the resources allocated to database connections that are no longer in use.

  • If you enable DCD, there is a negative result of higher network traffic overhead, but a positive result of limiting the amount of time a severed connection hangs on to locks.
  • If you do not enable DCD, the locks will eventaully release; however, in specific cases it may take 10 to 15 minutes for the release to occur.
Tip   If you enable DCD, we recommend setting SQLNET.EXPIRE_TIME = 3 in the database sqlnet.ora file. This is generally a safe value based on the maximum number of connections a database may have with many trading engine nodes.

Oracle Real Application Clusters (RAC)

Note   The trading engine can be safely deployed on Oracle Real Application Clusters (RAC); although, it is not a viable scalability solution due to the way B2Bi uses and connects to the database. The load balancing of message traffic on more than one RAC node degrades overall throughput.

Oracle presents a single hostname for B2Bi to use for database connectivity. Failover performance is handled by Oracle.

Note   B2Bi is tested with Oracle Dataguard. This provides unlimited time for the standby to connect. When the primary goes down,B2Bi will try and reconnect, fail, and the application will automatically recycle. It will repeat this cycle until connection is successful. Connect descriptors for both primary and standby must be in the datastoreconfig.xml connection string.

The following example shows typical settings in the following file:

<B2Bi_install_directory>/Interchange/conf/datastoreconfig.xml

Where:

  • <Host> is the name of the clustered database machine Single Client Access Name (SCAN)
  • <DatabaseName> is the service name used for the database node cluster
  • <Connection URL> parameter is "service_name" (instead of "SID")
Note   Oracle database clustering requires the <ConnectionURL> descriptor in the file to use "service_name". You must manually enter this value as it is not set during installation.

Other Oracle settings

The SGA (memory) and other settings may need to be adjusted to account for the number of processes and cursors. Refer to Oracle's documentation for assistance.

Operating system settings

If you specify a large number of processes in Oracle, you may need to adjust certain operating system settings. For example, under UNIX you might need to adjust message queue, semaphore, and IPC settings. Refer to Oracle's documentation and your operating system documentation for assistance.

User configuration

CREATE SEQUENCE permission

To support clustered configurations with an Oracle database, you must configure your Oracle user with the CREATE SEQUENCE permission before you start the server. To do this, execute a command similar to:

GRANT CREATE SEQUENCE TO <my_oracle_b2biUser>

User name guidelines

When using an Oracle database, the user name that B2Bi uses to connect to the database must not have permissions to access any schema other than the B2Bi schema. Unless the user is restricted to only this schema, the application may try to update tables in other schemas with names similar to tables in the trading engine schema.

The following is an example of a message when B2Bi tries to update a table in another schema:

To resolve this error, use Oracle Enterprise Manager Console and select Security > Users > your_user > Roles Granted.

Remove any roles that allow B2Bi to access schemas owned by other users.

For example, if one of the roles is DBA, the user can see all schemas, including those owned by other users.

Oracle database setup procedure

  1. Configure the tablespace.
  2. Administrators of large enterprise systems should consider using custom tablespaces. If custom tablespaces are not used, all tables and indexes are created in the user default tablespace. Set the default tablespace to a minimum of 1 gigabyte and enable the autoextend option.
  3. Create a schema and password.
  4. Run the following statements:
    • grant create procedure to username
    • grant create session to username
    • grant create table to username
    • grant create view to username
    • grant create sequence to username
    • alter user [user] quota unlimited on [tablespace]
    • Substitute real values for the variables enclosed by brackets. This statement must be run for each tablespace a user is using. If you use the default tablespace, you only run this statement once.
  5. Install B2Bi (if it is not already installed).
  6. Check the database connection settings before starting the servers.
  7. See Check database connections.
  8. To make database connection changes, run the B2Bi installer in configure mode and make the required changes. See Server installer configure mode.
  9. Start the servers. When the servers start, they connect to the database and generate the required tables.

Oracle custom tablespaces option

About custom tablespaces

Users of Oracle have an option to use custom tablespaces to permit advanced schema management. Under this option, data are organized in different tablespaces rather than the default of a single tablespace. This reduces fragmentation of tablespaces and results in improved performance.

This option is intended for large enterprise systems.

You must configure custom tablespaces using a new database, before you start the trading engine server the first time. Oracle users who want to change to custom tablespaces must manually move tables and indexes.

When custom tablespaces are in place, users can upgrade later to newer versions of B2Bi without additional configuration, provided the same database is used. This is possible because the settings in the B2Bi datastoreconfig.xml file carry forward to the new datastoreconfig.xml file in the upgraded application.

Implementation procedure

  1. Install the trading engine and set up an Oracle database.
  2. Do not start B2Bi.
  3. Edit a sql script to create the custom tablespaces in Oracle.
  4. A script, oracle_create_tableSpaces.sql, is provided in <B2Bi_install_directory>\Interchange\tools.
  5. To use this script, first configure it for your environment. You must set correct values for the following two properties:
    • define ORASID=orasid
    • define DATA_FILE_PATH=/oradata/&ORASID/data
  6. In the section of the file for tablespace names, you can use the names as provided. If you change the names, you also must edit the datastoreconfig.xml file to reflect the changed names. You can create more or fewer tablespaces in your own script, but you must change datastoreconfig.xml accordingly. Check each tablespace data file number and size to make sure your database has sufficient capacity.
  7. Save and close the file.
  8. Open for editing the datastoreconfig.xml file in <B2Bi_install_directory>\Interchange\conf.
  9. Change the value of the following element to true:
  10. <TableSpaces enabled="False">
  11. Change the following only if you used your own sql script and not the sample script, or if you changed the tablespace names in the sample script.
    • <TableSpace type="Large_Table" name="CYCLONE_LARGE_TABLES"/>
    • <TableSpace type="Medium_Table" name="CYCLONE_MEDIUM_TABLES"/>
    • <TableSpace type="Small_Table" name="CYCLONE_SMALL_TABLES"/>
    • <TableSpace type="Large_Index" name="CYCLONE_LARGE_INDEXES"/>
    • <TableSpace type="Medium_Index" name="CYCLONE_MEDIUM_INDEXES"/>
    • <TableSpace type="Small_Index" name="CYCLONE_SMALL_INDEXES"/>
  12. Each TableSpace entry must correspond to the tablespace name in the sql script. To group two together (for instance, Medium_Index and Small_Index) into the same tablespace, set the name of the two entries to the same value.
  1. Save and close the file.
  2. Run the SQL script in Oracle.
  3. Start the server. This creates the application’s database schema according to the settings in the datastoreconfig.xml file.

Related Links