Titus Maccius Plautus, a roman poet and philosopher who lived from 254 BC to 184 BC, said: “Wisdom is not attained by years, but by ability”. And also the famous quote in the title above.
What do I have in mind? Even midsize companies have nowadays dozens, if not hundreds, of database instances. Corporate IT departments would not surprise anyone if they support a couple of thousand databases.
The consolidation of these databases can be achieved in several ways. But here are some general guidelines on how to accomplish this task by using Exadata in the most optimal way in order to maximize cost reduction, high availability, secure separation of administrative duties and ease of performance tuning, management and monitoring.
It is really surprising how slowly companies adopt database consolidation granted the pressure IT management has in every possible direction. We can speculate for hours why so, but what I will concentrate on, are the technical aspects of Exadata consolidation.
Oracle recommends the creation of Exadata Hardware Pools. Hardware Pool is a machine or group of machines used as the target consolidation platform.
According to Oracle’s white paper “Best Practices For Database Consolidation On Exadata Database Machine“, an enterprise might create multiple Hardware Pools to make each consolidation target platform more manageable. The recommended minimum Hardware Pool size Oracle Maximum Availability Architecture Exadata Consolidation Best Practices is Exadata X2-2 Half Rack and the maximum recommended Hardware Pool size is two Exadata Database Machines Full Racks (plus additional Exadata storage expansion racks if required). Hardware Pools that fall within this range are the most common Exadata configurations for consolidation and provide sufficient capacity to efficiently achieve objectives for database consolidation.
The recommended storage configuration is one shared Exadata storage grid for each Hardware Pool. This storage grid contains all Exadata cells and Exadata disks, and is configured with either ASM high or normal redundancy. The recommended setup for Oracle Grid Infrastructure (which includes Oracle Clusterware and Oracle ASM) is to use one cluster per Hardware Pool.
Oracle has recommended several parameter settings for Exadata database consolidation:
If PageTables in /proc/meminfo is set to more than 2% of the physical memory size, then set the operating system parameter HugePages to the sum of all shared memory segments. Starting in 11.2.0.2, setting the database initialization parameter USE_LARGE_PAGES=ONLY on all instances prevents any instance from starting unless sufficient HugePages are available. Hugepages can only be used for SGA, so do not over-allocate. Also, the database parameters MEMORY_MAX_TARGET and MEMORY_TARGET are not compatible when HugePages are enabled. This is only for Linux. On Solaris, HugePages are automatically configured and used via intimate shared memory (ISM).
Operating system setting:
Set the number of shared memory segments (kernel.shmmni) greater than the number of databases.
Set the maximum shared memory segment size (kernel.shmmax) to 85% of physical memory size, which is the default.
Set the maximum total number of system semaphores (SEMMNS) greater than the sum of all database processes.
Set the maximum number of semaphores in a semaphore set (SEMMSL) greater than the largest number of processes in any single database.
Exadata Memory:
1. Exadata X2-2 based on the Sun Fire X4170 Oracle Database Servers (also known as V2) has 72 GB per database server.
2. Exadata X2-2 has 96 gigabytes (GB) of memory in the default configuration, with an option to expand to 144 GB of memory (with the Exadata memory expansion kit).
3. Exadata X2-8 has 1 terabyte (TB) (with the X4800) or 2 TB (with the X4800M2) per database server.
An important rule:
OLTP applications: SUM of databases (SGA_TARGET + PGA_AGGREGATE_TARGET) + 4 MB * (Maximum PROCESSES) < Physical Memory per Database Node
DW/BI applications: SUM of databases (SGA_TARGET + 3 * PGA_AGGREGATE_TARGET) < Physical Memory per Database Node
Remember to enable instance caging!
For the Oracle ASM instance, set PROCESSES= 50 * MIN ( # database instances on db node+ 1, 11) + 10 * MAX (# database instances on db node – 10, 0).
Limit PARALLEL_MAX_SERVERS:
1. X2-2: sum(PARALLEL_MAX_SERVERS) for all instances <= 240
2- X2-8: sum(PARALLEL_MAX_SERVERS) for all instances RECOVER MANAGED STANDBY DATABASE … PARALLEL 16.
Limit the number of processes and connections to the database servers:
–
For Exadata running Exadata Storage Server Software 11.2.3.1 or higher, configure a maximum of 60,000 processes per Hardware Pool. Upper limit target is 7,500 processes per node for X2-2. Upper limit target is 30,000 processes per node for X2-8. For Exadata running Exadata Storage Server Software 11.2.2.4.2 or less, configure a maximum of 20,000 processes per Hardware Pool. Upper limit target is 2,500 processes per node for X2-2. Upper limit target is 10,000 processes per node for X2-8.
The temporary tablespace should be:
BigFile Tablespace,
Located in DATA or RECO, whichever one is not HIGH redundancy,
Sized 32GB initially,
Configured with AutoExtend on at 4GB,
Configured with a MaxSize defined to limit out of control growth.
Answers to several additional Exadata related questions can be found in the following blogs/articles:
Who Manages the Exadata Machine? by Arup Nanda
Upgrade Exadata to 11.2.0.3 by Gleb Otochkin from Pythian
Best Practices For Database Consolidation On Exadata by Javier Puerta
Consolidation Strategies for Oracle Exadata and Oracle Database Machine by Dan Norris, X Team, Oracle Exadata Development
Expert Oracle Exadata by Kerry Osborne, Randy Johnson, Tanel Põder
Oracle Exadata – A platform for consolidation by Umesh Tanna
An excellent reference is the Consolidation Parameters Reference Table available at MOS: “Oracle Sun Database Machine Setup/Configuration Best Practices” [ID 1274318.1]
Back to the saying of Plautus: one does not have to wait for years in order to start implementing an Exadata consolidation. The product has the perfect ability to serve its many purposes: this is software, not wine!
Julian Dontcheff's Database Blog 