Unsupported content This version of the documentation is no longer supported. However, the documentation is available for your convenience. You will not be able to leave comments.

Recommendations for an Oracle database server

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

The macro unmigrated-inline-wiki-markup from Confluence is no longer available.

servers.

The reports data warehouse schema can become very large based on the number of 

The macro unmigrated-inline-wiki-markup from Confluence is no longer available.

servers in your 

The macro unmigrated-inline-wiki-markup from Confluence is no longer available.

installation and the number of jobs that you run. The following recommendations are for an environment that includes a large warehouse database that is tens of gigabytes or higher in size, which typically happens with thousands of managed servers. The recommendations take future growth into consideration, with potential increase in the number of servers and jobs that need to be executed.

These recommendations are not intended as the "requirements" for all installations. Before installing the product, BMC strongly recommends that you complete a full sizing exercise for the warehouse database with assistance from BMC to determine specific installation requirements.

CPU

Due to the performance characteristics of data warehouses in general, such as long running queries, high volume inserts, and parallelization, 4 to 8 CPUs (at 2 GHZ per CPU) per server are recommended.

I/O bandwidth

Deploy all 

The macro unmigrated-inline-wiki-markup from Confluence is no longer available.

databases on a Storage Area Network (SAN) to ensure the I/O bandwidth 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 critical components to deploy are:

  • 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 multipathing. 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 chucks and allows for the full I/O bandwidth of your storage infrastructure.

Memory

A minimum of 32 GB of memory on database servers that are used as reports data warehouses is recommended. This does not mean that BMC BladeLogic Decision Support for Server Automation requires 32 GB of dedicated memory, but that the database server on which the reports data warehouse is installed should have 32 GB of memory in total.

Reports data warehouse architecture

Use the following recommendations for the 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-database.

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 failover (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 warehouse architecture due to the increase in Oracle block size (16, 32).
  • Set the Oracle parameter, parallel_execution_message_size to at least 8K.
  • Segment the workload via 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 multipathing 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.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*