Oracle database recommendations

This topic provides recommendations for setting up an Oracle database server to store the data warehouse schema for a large installation that can manage thousands of TrueSight Server Automation servers.

The data warehouse schema can become very large based on the number of TrueSight Server Automation servers in your TrueSight Server Automation - Data Warehouse installation and the number of jobs that you run.

Before installing the product, BMC strongly recommends that you complete a full sizing exercise, as described in Sizing tools for TrueSight Server Automation - Data Warehouse to determine your installation specific requirements.

I/O bandwidth

Deploy all TrueSight Server Automation - Data Warehouse databases on a Storage Area Network (SAN) that is required for the ETL activities. When deploying a SAN, you must deploy each component on the I/O path to guarantee the required throughput.

The following critical components are deployed:

Disk controllers
Disks
Host Bus Adapters (HBA)
Fiber channel connections
Fiber channel switches

In a typical two HBA configuration, each HBA can push 200 MB/s or 400 MB/s when multi-pathing. If you use faster HBAs, you can recalculate with projections from the vendor to obtain the maximum MB/s. If you deploy Oracle Real Application Clusters (RAC), this calculation increases by 50% in a two-node configuration due to the extra I/O path. As servers are added, additional I/O paths are created.

Automated Storage Management

Oracle Automated Storage Management (ASM) should be running on the server. ASM provides the speed of raw devices without the management overhead. Oracle stripes all data across each ASM device in 1 MB chunks and allows for the full I/O bandwidth of your storage infrastructure.

Reports data warehouse architecture

Use the following recommendations for the reports data warehouse architecture:

Redo: Separate redo logs onto separate file systems or ASM disk groups and deploy on RAID 1,0.
Data: Separate data from the redo activity in separate file systems or ASM disk groups and deploy data areas on RAID 1,0 or RAID 5. For information about the layout of data storage tablespaces, see Setting up the databases for TrueSight Server Automation - Data Warehouse, depending upon the database type.

Database memory parameters

The following memory parameters are recommended:

SGA: 12 GB
PGA: 6 GB

Oracle RAC

BMC recommends the following configuration for Oracle RAC:

Set up the public network, as follows:
- Deploy a fully redundant public network with fail over (the redundancy technology depends on the operating system and vendor).
- Leverage GigE on redundant switches with default Maximum Transmission Unit (MTU).
Set up the private network, as follows:
- Deploy a fully redundant private network with failover (the redundancy technology depends on the operating system and vendor).
- Leverage jumbo frames with GigE on redundant, isolated switches with an MTU of 9000 (actual MTU varies by vendor). This setting provides better improvements in the reports warehouse architecture due to the increase in Oracle block size (16, 32).
Set the Oracle parameter, parallel_execution_message_size to at least 8 K.
Segment the workload through Oracle 10g services on different nodes as needed (ETL, reports, RMAN).
Deploy ASM, as recommended earlier in this topic, to increase online redo log and cache fusion performance.
Deploy multiple HBAs with multi-pathing on a SAN.

Database parameter configuration

Use the following database parameter configuration:

The db_block_size parameter should be set to 16384 (16 K).
You need to configure this parameter when you create the database. Do not change the parameter after you create the database.
The db_file_multiblock_read_count parameter should be set to 32.
You can change this parameter value at run time.

Database log file sizing

BMC recommends the following database log file and group sizes. To improve database performance, create new log files that meet the following recommendations and then delete the old log files:

The log file size should be 3221225472 (3 GB).
The number of log groups should be 10.

You can make these changes at run time by using database log file and group creation commands.

Oracle processes parameter

BMC recommends that you set the Oracle processes parameter to a minimum value of 800 for both the TrueSight Server Automation database and the Oracle reports data warehouse database.

The ETL execution spawns multiple Oracle processes and makes multiple connections to the reports data warehouse. The actual number of processes that are needed is based on the number of domains , such as Inventory and Audit, for which ETL is being run in parallel.