|Oracle® Database High Availability Best Practices
11g Release 1 (11.1)
|PDF · Mobi · ePub|
This section describes best practices for configuring a fault-tolerant storage subsystem that protects data while providing manageability and performance. These practices apply to all Oracle Database high availability architectures described in Oracle Database High Availability Overview.
Characterize your database performance requirements using different application workloads. Extract statistics during your target workloads by gathering the beginning and ending statistical snapshots. Some examples of target workloads include:
You can gather the necessary statistics by using Automatic Workload Repository (AWR) reports or by querying the
GV$SYSSTAT view. Along with understanding the database performance requirements, you must evaluate the performance capabilities of a storage array. You can use the
DBMS_RESOURCE_MANAGER.CALIBRATE_IO PL/SQL procedure to determine the maximum capacity of your storage array.
When you understand the performance and capacity requirements, choose a storage platform to meet those requirements. One example solution is Oracle Exadata Storage Servers that offer excellent performance and availability characteristics. Each Exadata cell can be viewed as a unit of I/O performance and capacity. Therefore, the only decision that must be made is how many cells are required.
The Oracle Storage Grid is implemented using either Oracle Automatic Storage Management (ASM) and Oracle Exadata Storage Server Software or ASM and third-party storage. The Oracle Storage Grid with Exadata seamlessly supports MAA-related technology, improves performance, provides unlimited I/O scalability, is easy to use and manage, and delivers mission-critical availability and reliability to your enterprise. The following sections provide best practice recommendations for Oracle Storage Grid using Exadata.
Use the following list to protect storage against unplanned outages:
DB_BLOCK_CHECKSUM initialization parameter to
TYPICAL (default) or
Makes sure the checksum is stored in the database blocks so that when it is received by Exadata Cell the Hardware Assisted Resilient Data (HARD) check can be performed on the blocks.
Choose ASM redundancy type (
HIGH) based on your desired protection level and capacity requirements
NORMAL setting stores two copies of ASM extents, while the
HIGH setting stores three copies of ASM extents. Normal redundancy provides more usable capacity and high redundancy provides more protection.
Ensure the ASM default disk repair timer is set correctly
You can set a disk repair timer attribute on your disk group to specify how long disks remain offline before being dropped. The default disk repair time is 3.6 hours. The appropriate setting for your environment depends on how long you expect a typical transient type of failure to persist.
Monitor the ASM disk group balance to prevent allocation failures
You should monitor the disk group balance to avoid allocation failures. Allocation failures are possible if the disk group becomes out of balance. ASM generally ensures that the disk group stays balanced, but in some rare cases (such as a failed rebalance operation) the disk group can become imbalanced. Because disk group imbalance can cause performance or space exhaustion problems, it is an operational best practice to periodically check it.
Ensure I/O performance can be sustained after an outage
Ensure that you have enough Exadata cells to support your service-level agreement I/O requirements if a failure occurs. For example, a typical case for a Storage Grid with n cells would be to ensure that n-1 cells could support the application service levels (for example, to handle a cell failure).
Use the following list of best practices for planned maintenance
Size I/O for performance first, and then set it for capacity
When building your Oracle Storage Grid, make sure you have enough Exadata Cells to support I/O's per second and MB/second to meet your service-level requirements. Then, make sure you also have enough capacity. This order is important because you do not want to buy enough Exadata Cells to support capacity but then find the system cannot meet your performance requirements.
Add Exadata cells to scale the Oracle Storage Grid
When you scale out or add performance to your Exadata system, add Exadata Cells. Exadata scales linearly. When you understand the amount of I/O resource available to you in Exadata Cells, then you know how many Exadata cells are necessary.
Employ Exadata configuration automation
Take advantage of the following Oracle Exadata Storage Server tools and features to automate and simplify configuration tasks:
CREATE CELLDISK ALL—This CELLCLI command automatically creates celldisks on all available logical unit numbers (LUNs).
CREATE GRIDDISK ALL PREFIX=
prefix—This CELLCLI command automatically creates grid disks on all available Exadata cell disks.
Note that the name you provide for
prefix should be the same as the disk group name. For example, to create grid disks for your ASM disk group
DATA, use the
CREATE GRIDDISK ALL PREFIX='DATA' command. Because no size is specified in this example, the grid disk consumes the whole cell disk.
ASM automated failure group naming
When creating ASM failure groups on Oracle Exadata Storage Server, grid disks in the same Oracle Exadata Storage Server cell are automatically placed in the same ASM failure group. There is no need to specify the failure group during disk group creation, which simplifies the
CREATE DISKGROUP syntax.
Oracle Exadata Storage Server includes the DCLI utility on each cell. You can use the Dcli utility to execute commands or scripts in parallel across a defined set of cells. The Dcli tool simplifies any operations that must be run across a subset or all cells. Configuration of SSH user equivalency across all cells is an important prerequisite for optimizing the use of DCLI commands. DCLI provides the
-k option to automate the distribution of SSH private keys into the
/opt/oracle.cellos/db.iscsi.sh script is available on the database servers to completely automate the error prone process of iSCSI configuration, iSCSI device creation, and
udev configuration. You can run this script on any Oracle Database Machine during the initial configuration. When complete, the iSCSI devices are available in the
/dev/ocr* directory. (Note that these devices are used for the Oracle Clusterware OCR and voting device.)
Perform the following steps to use ASM disk resynchronization for Exadata Cell planned maintenance:
Take offline the failure group that corresponds with the Exadata cell.
This includes specifying the amount of offline time in the
OFFLINE clause if the default
DISK_REPAIR_TIME is not adequate. For example:
ALTER DISKGROUP diskgroup_name OFFLINE DISKS IN FAILGROUP failure group name DROP AFTER integer in hours or minutes
Perform the platform planned maintenance.
Place online the failure group that corresponds with the Exadata cell. For example:
ALTER DISKGROUP diskgroup_name ONLINE DISKS IN FAILGROUP failure group name
ASM tracks all of the changed extents while the failure group is offline and resynchronizes them when the failure group comes back online. Make sure that the time for planned maintenance operations does not exceed the disk repair time. Otherwise, the disks are dropped and must be re-added.
After performing planned maintenance (for example, to add or remove storage), it is necessary to subsequently perform a rebalance to spread data across all of the disks. There is power limit associated with the rebalance. You can set a power limit from 1 and 11 to specify how many processes perform the rebalance. If you do not want the rebalance to impact applications, then set the power limit low. However, if you want the rebalance to finish as quickly as possible, then set the power limit high. To determine the default power limit for rebalances, check the value of the
ASM_POWER_LIMIT initialization parameter in the ASM instance.
Oracle Exadata Best Practices in My Oracle Support (formerly OracleMetalink) note 757552.1
Oracle Exadata Storage Server Software User's Guide for instructions about setting up and configuring IORM
The MAA white paper "Best Practices for Migrating to Oracle Exadata Storage Server" at
ASM is a vertical integration of both the file system and the volume manager built specifically for Oracle database files. ASM extends the concept of stripe and mirror everything (SAME) to optimize performance, while removing the need for manual I/O tuning (distributing the data file layout to avoid hot spots). ASM helps manage a dynamic database environment by letting you grow the database size without shutting down the database to adjust the storage allocation. ASM also enables low-cost modular storage to deliver higher performance and greater availability by supporting mirroring and striping.
Moreover, ASM manages the storage in the Exadata cell. For Exadata cells, ASM provides data protection against drive and cell failures, the best possible performance, and extremely flexible configuration and reconfiguration options. ASM automatically distributes the data across the Exadata Storage servers, and transparently and dynamically redistributes data when Exadata Storage servers are added or removed from the storage grid.
The recommended practice is to use ASM to manage all of your database files. You can phase ASM into your environment by initially supporting only the flash recovery area.
Chapter 16 "Migrating Databases to and from ASM with Recovery Manager" in the Oracle Database Backup and Recovery User's Guide
The MAA white papers "Migration to Automatic Storage Management (ASM)" and "Best Practices for Creating a Low-Cost Storage Grid for Oracle Databases" at
Oracle Database Storage Administrator's Guide for more information about configuring ASM
Eliminates the need for every Oracle process to open a file descriptor for each ASM disk, thus improving system resource usage.
Simplifies the management of disk device names, makes the discovery process simpler, and removes the challenge of having disks added to one node and not be known to other nodes in the cluster.
Eliminates the impact when the mappings of disk device names change upon system reboot.
See Also:Oracle ASMLib Web site at
When using ASM for database storage, you should create at least two disk groups: one disk group for the database area and another disk group for the flash recovery area:
The database area contains active database files, such as data files, control files, online redo log files, standby redo log files, broker metadata files, and change tracking files used for RMAN incremental backups. For example:
CREATE DISKGROUP DATA DISK '/devices/lun01','/devices/lun02','/devices/lun03','/devices/lun04';
The flash recovery area contains recovery-related files, such as a copy of the current control file, a member of each online redo log file group, archived redo log files, RMAN backup sets, and flashback log files. For example:
CREATE DISKGROUP RECO DISK '/devices/lun05','/devices/lun06','/devices/lun07','/devices/lun08', '/devices/lun09','/devices/lun10','/devices/lun11','/devices/lun12';
If using ASMLib in a Linux environment, then create the disks using the
ORACLEASM CREATEDISK command:
/etc/init.d/oracleasm createdisk lun1 /devices/lun01
Then, create the disk groups. For example:
CREATE DISKGROUP DATA DISK 'ORCL:lun01','ORCL:lun02','ORCL:lun03','ORCL:lun04';
To simplify file management, use Oracle managed files to control file naming. Enable Oracle managed files by setting these initialization parameters:
Allocate entire disks to the database area and flash recovery area disk groups
Partition each disk into two partitions, one for the database area and another for the flash recovery area
Figure 2-1 illustrates allocating entire disks. The advantages of this option are:
Easier management of the disk partitions at the operating system level because each disk is partitioned as just one large partition.
The disadvantage of this option is:
Less I/O bandwidth, because each disk group is spread over only a subset of the available disks.
The second partitioning option is illustrated in Figure 2-2. It requires partitioning each disk into two partitions: a smaller partition on the faster outer portion of each drive for the database area, and a larger partition on the slower inner portion of each drive for the flash recovery area. The ratio for the size of the inner and outer partitions depends on the estimated size of the database area and the flash recovery area.
The advantage of this approach is:
Higher I/O bandwidth is available, because both disk groups are spread over all available spindles. This advantage is considerable for the database area disk group for I/O intensive applications.
There is no need to create a separate disk group with special, isolated storage for online redo logs or standby redo logs if you have sufficient I/O capacity.
The disadvantages are:
A double disk failure may result in the loss of both disk groups, requiring the use of a standby database or tape backups for recovery.
Higher initial administrative efforts are required to partition each disk properly.
Disk multipathing software aggregates multiple independent I/O paths into a single logical path. The path abstraction provides I/O load balancing across host bus adapters (HBA) and nondisruptive failovers when there is a failure in the I/O path. You should use disk multipathing software with ASM.
When specifying disk names during disk group creation in ASM, use the logical device representing the single logical path. For example, when using Device Mapper on Linux 2.6, a logical device path of
/dev/dm-0 may be the aggregation of physical disks
/dev/sdh. Within ASM, the
ASM_DISKSTRING parameter should contain
/dev/dm-* to discover the logical device
/dev/dm-0, and that logical device is necessary during disk group creation:
asm_diskstring='/dev/dm-*' CREATE DISKGROUP DATA DISK '/dev/dm-0','/dev/dm-1','/dev/dm-2','/dev/dm-3';
When setting up redundancy to protect from hardware failures, there are two options to consider:
Storage array based RAID
If you are using a high-end storage array that offers robust built-in RAID solutions, then Oracle recommends that you configure redundancy in the storage array by enabling RAID protection, such as RAID1 (mirroring) or RAID5 (striping plus parity). For example, to create an ASM disk group where redundancy is provided by the storage array, first create the RAID-protected logical unit numbers (LUNs) in the storage array, and then create the ASM disk group using the
CREATE DISKGROUP DATA EXTERNAL REDUNDANCY DISK '/devices/lun1','/devices/lun2','/devices/lun3','/devices/lun4';
If the storage array does not offer the desired level of redundancy, or if you must configure redundancy across multiple storage arrays, then use ASM redundancy. ASM provides redundancy with the use of failure groups, which are defined during disk group creation. ASM redundancy can be either normal redundancy, where extents are two-way mirrored, or high redundancy, where extents are three-way mirrored. After a disk group is created, the redundancy level cannot be changed.
Failure group definition is specific to each storage setup, but you should follow these guidelines:
If every disk is available through every I/O path, as would be the case if using disk multipathing software, then keep each disk in its own failure group. This is the default ASM behavior if creating a disk group without explicitly defining failure groups.
For an array with two controllers where every disk is seen through both controllers, create a disk group with each disk in its own failure group:
CREATE DISKGROUP DATA NORMAL REDUNDANCY DISK '/devices/diska1','/devices/diska2','/devices/diska3','/devices/diska4', '/devices/diskb1','/devices/diskb2','/devices/diskb3','/devices/diskb4';
For an array with two controllers where each controller sees only half the drives, create a disk group with two failure groups, one for each controller, to protect against controller failure:
CREATE DISKGROUP DATA NORMAL REDUNDANCY FAILGROUP controller1 DISK '/devices/diska1','/devices/diska2','/devices/diska3','/devices/diska4' FAILGROUP controller2 DISK '/devices/diskb1','/devices/diskb2','/devices/diskb3','/devices/diskb4';
CREATE DISKGROUP DATA NORMAL REDUNDANCY FAILGROUP array1 DISK '/devices/diska1','/devices/diska2','/devices/diska3','/devices/diska4' FAILGROUP array2 DISK '/devices/diskb1','/devices/diskb2','/devices/diskb3','/devices/diskb4';
When determining the proper size of a disk group that is protected with ASM redundancy, enough free space must exist in the disk group so that when a disk fails ASM can automatically reconstruct the contents of the failed drive to other drives in the disk group while the database remains online. The amount of space required to ensure ASM can restore redundancy following disk failure is in the column
REQUIRED_MIRROR_FREE_MB in the
V$ASM_DISKGROUP view. The amount of free space that you can use safely in a disk group (taking mirroring into account) and still be able to restore redundancy after a disk failure is in the
USABLE_FILE_MB column in the
V$ASM_DISKGROUP view. The value of the
USABLE_FILE_MB column should always be greater than zero. If
USABLE_FILE_MB falls below zero, then add more disks to the disk group.
You can use clustered ASM with both Oracle single-instance databases and Oracle Real Application Clusters (Oracle RAC). In an Oracle RAC environment, there is one ASM instance for each node, and the ASM instances communicate with each other on a peer-to-peer basis. Only one ASM instance is required and supported for each node regardless of the number of database instances on the node. Clustering ASM instances provides fault tolerance, flexibility, and scalability to your storage pool.
See Also:Oracle Database Storage Administrator's Guide for more information about clustered ASM
Installing ASM in its own home directory enables you to keep the ASM home separate from the database home directory. By using separate home directories, you can upgrade and patch ASM and the Oracle Database software independently, and you can deinstall Oracle Database software without affecting the ASM instance, thus increasing availability.
See Also:Oracle Database 2 Day + Real Application Clusters Guide for information about installing ASM in a home directory separate from the home directory used by Oracle Database
MEMORY_TARGET initialization parameter in the ASM instance to automate and simplify manageability of ASM process memory consumption. This is the only parameter that you must set for complete ASM memory management. Oracle recommends that you use automatic memory management for ASM.
Automatic memory management is enabled by default on an ASM instance, even when the
MEMORY_TARGET parameter is not explicitly set. The default value used for
MEMORY_TARGET is acceptable for most environments.
If you do not set a value for
MEMORY_TARGET, but you do set values for other memory related parameters, Oracle internally calculates the optimum value for
MEMORY_TARGET based on those memory parameter values. You can also increase
MEMORY_TARGET dynamically, up to the value of the
MEMORY_MAX_TARGET parameter, just as you can do for the database instance.
See Also:Oracle Database Storage Administrator's Guide for information about memory-related initialization parameters for ASM
Although ensuring that all disks in the same disk group have the same size and performance characteristics is not required, doing so provides more predictable overall performance and space utilization. When possible, present physical disks (spindles) to ASM as opposed to Logical Unit Numbers (LUNs) that create a layer of abstraction between the disks and ASM.
If the disks are the same size, then ASM spreads the files evenly across all of the disks in the disk group. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a disk group have the same I/O load. Because ASM load balances workload among all of the disks in a disk group, different ASM disks should not share the same physical drive.
See Also:Oracle Database Storage Administrator's Guide for complete information about administering ASM disk groups
The ASM instance is managed by a privileged role called
SYSASM, which grants full access to ASM disk groups. Using
SYSASM enables the separation of authentication for the storage administrator and the database administrator. By configuring a separate operating system group for ASM authentication, you can have users that have
SYSASM access to the ASM instances and do not have
SYSDBA access to the database instances.
See Also:Oracle Database Storage Administrator's Guide for information about authentication to access ASM instances
Mounting multiple disk groups in the same command ensures that disk discovery is only run once, thereby increasing performance. Disk groups that are specified in the
ASM_DISKGROUPS initialization parameter are mounted automatically at ASM instance startup. To mount disk groups manually, use the
ALTER DISKGROUP...MOUNT statement and specify the
ALTER DISKGROUP ALL MOUNT;
See Also:Oracle Database Storage Administrator's Guide for information about mounting and dismounting disk groups
ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk to a disk group, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. The process of redistributing data so that it is also spread across the newly added disks is known as rebalancing. By executing storage maintenance commands in the same command, you ensure that only one rebalance is required to incur minimal impact to database performance.
See Also:Oracle Database Storage Administrator's Guide for information about
Using failure groups to define a common failure component ensures continuous access to data when that component fails. For maximum protection, use at least three failure groups for normal redundancy and at least five failure groups for high redundancy. Doing so allows ASM to tolerate multiple failure group failures and avoids the confusing state of having ASM running without full redundancy.
Note:If you have purchased a high-end storage array that has redundancy features built in, then use those features from the vendor to perform the mirroring protection functions and set the ASM diskgroup to external redundancy. Along the same lines, use ASM normal or high redundancy with low-cost storage.
For large databases over 10 TB that run Oracle Database 10g, increase the ASM allocation unit to 16 MB and the stripe size to 1 MB. Doing this provides faster ASM file opens and efficiently supports the deployment of very large databases with sizes in the range from 10 TB to 1 PB. For more information about this procedure, see support note 368055.1 at
If you have advanced the
COMPATIBLE initialization parameter to Oracle Database 11g Release 1 (11.1), there is no need to increase the ASM allocation unit because Oracle Database 11g provides variable sized extents. Variable size extents enable support for larger ASM data files, reduce SGA memory requirements for very large databases, and improve performance for file
You should periodically check disk groups for imbalance. Occasionally, disk groups can become unbalanced if certain operations fail, such as a failed rebalance operation. Periodically checking the balance of disk groups and running a manual rebalance, if needed, ensures optimal ASM space utilization and performance.
Use the following methods to check for disk group imbalance:
To check for an imbalance on all mounted disk groups, run the script available in support note 367445.1 at
To check for an imbalance from an I/O perspective, query the statistics in the
V$ASM_DISK_IOSTAT view before and after running a large SQL*Plus statement. For example, if you run a large query that performs only read I/O, the
READ_BYTES columns should be approximately the same for all disks in the disk group.
Higher ASM rebalance power limits make a rebalance operation run faster but can also affect application service levels. Rebalancing takes longer with lower power values, but consumes fewer processing and I/O resources that are shared by other applications, such as the database.
Set the power limit to the highest value that does not affect the application service levels. If the
POWER clause is not specified in an
ALTER DISKGROUP statement, or when rebalance is run implicitly when you add or drop a disk, then the rebalance power defaults to the value of the
ASM_POWER_LIMIT initialization parameter. You can adjust the value of this parameter dynamically.
See Also:Oracle Database Storage Administrator's Guide for more information about rebalancing ASM disk groups
Use the ASMCMD utility to ease the manageability of day-to-day storage administration. The ASMCMD is a command-line utility that you can use to view and manipulate files and directories in ASM disk groups. ASMCMD can list the contents of disk groups, perform searches, create and remove directories and aliases, display space usage, and more. You cannot use ASMCMD to create or drop disk groups, or to add or drop disks in a disk group. You must use SQL*Plus commands to perform these operations.
Use Oracle Recovery Manager (RMAN) to migrate to ASM with very little downtime. You can also use Oracle Data Guard to migrate to ASM with even less downtime (occurs in approximately the same amount of time it takes to perform a switchover).
See Also:Oracle Database Backup and Recovery User's Guide for complete information about performing ASM data migration
DISK_REPAIR_TIME disk group attribute specifies how long a disk remains offline before ASM drops the disk. If a disk is made available before the
DISK_REPAIR_TIME parameter has expired, the storage administrator can issue the
ONLINE DISK command and ASM resynchronizes the stale data from the mirror side. In Oracle Database 11g, the online disk operation does not restart if there is a failure of the instance on which the disk is running. You must reissue the command manually to bring the disk online.
DISK_REPAIR_TIME disk group attribute to the maximum amount of time before a disk is definitely considered to be out of service.
See Also:Oracle Database Storage Administrator's Guide for information about restoring the redundancy of an ASM disk group after a transient disk path failure
You should proactively mine vendor logs for disk errors and have ASM move data off the bad disk spots. Disk vendors usually provide disk-scrubbing utilities that notify you if any part of the disk is experiencing problems, such as a media sense error. When a problem is found, use the ASMCMD
REMAP command to move ASM extents from the bad spot to a good spot.
Note that this is only applicable for data that is not accessed by the database or ASM instances, because in that case ASM automatically moves the extent experiencing the media sense error to a different location on the same disk. In other words, use the ASMCMD
REMAP command to proactively move data from a bad disk spot to a good disk spot before that data is accessed by the application.
See Also:Oracle Database Storage Administrator's Guide for information about the ASMCMD command-line utility