Oracle Asm Load Balancing_anthony Noriega 6gw55

Oracle ASM Load Balancing Balancing ASM Load for Successful Storage Management Session # S313474

ANTHONY D NORIEGA [email protected]

ADN R & D

Speaker Qualifications • Oracle Consultant, ADN R & D • Speaker at Collaborate, IOUG LIVE, Quest Conference and NYOUG meetings. • 25 years of IT experience • 20 years of Oracle experience, 14 as a DBA (v6 thru 11g) • RMAN experience with Oracle8i,9i, 10g, and 11g, since 1999.

• BS Systems Engineering, Universidad del Norte, 1987. • MS Computer Science, NJIT, 1992 • PhD CIS candidate, NJIT, 1997 • MBA MIS, Montclair State University, 2006 • College Math Professor and former HS Math Teacher Principal.

Objectives • Present an independent array of concise tips to successfully manage ASM load balancing. • Introduce and illustrate ASM rebalancing and performance tuning scenarios. • Understand ASM instance background processes and their role in load balancing. • Learn the fundamentals for ASM manageability with both GUI API and CLI interfaces. • Enhance a blueprint for clusterware, VLDBs, ACFS and ADVM.

ASM Instance Processes • An ASM instance has two background processes, namely: • RBAL, which coordinates rebalance activities for disk groups. • ARBx, x=0,1,2..., which actually performs the balance of extents movements.

Database Instance Processes There are 3 ASM-related database instance background processes, namely:

 RBAL, which performs global opens on all disks in the diskgroup.  ASMB, this process CSS using the group name and acquires the associated ASM connect string, subsequently used to connect to the ASM instance.  O00x, a group slave processes, with a numeric sequence starting at 000.

ASM Disk Striping Main purposes  To balance loads across all of the disks in a disk group  To reduce I/O latency

Types of Striping

 Coarse-grained striping Provides load balancing for disk groups  Fine-grained striping Reduces latency for certain file types by spreading the load more widely.

Allocation Unit and Extents The contents of Oracle ASM files are stored in a disk group as a collection of extents that are stored on individual disks within disk groups. Each extent resides on an individual disk. Extents consist of one or more allocation units (AU), and can use variable size to enable for larger Oracle ASM data files, reduce SGA memory requirements for very large databases, and improve performance for file creation and open operations.

Allocation Unit and Extents The initial extent size equals the disk group allocation unit size and it increases by a factor of 4 or 16 at predefined thresholds. The extent size of a file varies as follows: 

 

Extent size always equals the disk group AU size for the first 20000 extent sets (0 - 19999). Extent size equals 4*AU size for the next 20000 extent sets (20000 - 39999). Extent size equals 16*AU size for the next 20000 and higher extent sets (40000+).

Oracle ASM Redundancy Levels External redundancy ASM does not provide mirroring redundancy and relies on the storage system to provide RAID functionality. Any write error causes a forced dismount of the disk group. All disks must be located to successfully mount the disk group.

Normal redundancy ASM provides two-way mirroring by default, i.e., all files are mirrored so that there are two copies of every extent.

High redundancy

Oracle ASM provides triple mirroring by default. A loss of two ASM disks in different failure groups is tolerated.

ASM: Overall Architecture ARB0

RBAL

...

ARB1

...

ARBn

Group Services ... ...

ASMB

RBAL

FG

Oracle ASM Instance

Oracle Database Instance

Oracle Database Instance

Streams Pool

Shared Pool

keep recycle

recycle

PGA

32K

16k

Java Pool

4k

Server Process 2

2k

PGA

Default

Server Process 1

Large Pool

Background Processes

PGA

Redo Buffer

Flash Cache

Other Components O0001

RBAL

PGA

ASBM

PGA

O000

ASM Slave processes

ASM Instance Stack Application

Database File System ACFS Volume Manager

ASM

ADVM

Operating System

ASM and Oracle Clusterware To share a disk group among multiple nodes, the DBA must install Oracle Clusterware on all of the nodes (with or without RAC). ASM instances on separate nodes do not need to be part of an Oracle ASM cluster and normally they cannot communicate with each other. Likewise, multiple nodes which are not part of an Oracle ASM cluster are unable to share a disk group.

ASM and Oracle Clusterware Failure groups defined for a disk group at ASM disk group creation cannot be altered afterwards to change the redundancy level of the disk group.

ASM metadata resides within the disk group containing information used by ASM to control a disk group, including: o o o o o

The disks that belong to a disk group The filenames of the files in a disk group The location of disk group data file extents The amount of space that is available in a diskgroup A redo log recording information about atomically changing metadata blocks

Initialization Parameters ASM_DISKGROUPS ASM_DISKSTRING ASM_POWER_LIMIT ASM_PREFERRED_READ_FAILURE_GROUPS AU_SIZE COMPATIBLE.ADVM COMPATIBLE.ASM DB_CACHE_SIZE COMPATIBLE.RDBMS DIAGNOSTIC_DEST INSTANCE_TYPE LARGE_POOL_SIZE PROCESSES REMOTE__FILE SHARED_POOL_SIZE

Initialization Parameters ASM_DISKGROUPS A dynamic parameter specifies a list of the names of disk groups that the ASM instance mounts at startup. Example setting ASM_DISKGROUPS dynamically: SQL> ALTER SYSTEM SET ASM_DISKGROUPS = DATA1, ADNFRA;

ASM_DISKSTRING The ASM_DISKSTRING initialization parameter specifies the comma-delimited list of strings that limits the set of disks that an Oracle ASM instance discovers. It can include wildcard characters. /dev/sda*

Initialization Parameters

ASM_POWER_LIMIT

• Specifies the default power for disk rebalancing. • Default value is 1 • Valid Ranges are • [0,11] for versions earlier than 11.2 • [0,1024] for versions 11.2 or later. • A value of 0 disables rebalancing.

ASM_PREFERRED_READ_FAILURE_GROUPS Its value is a comma-delimited list of strings specifying the failure groups preferentially read by the instance.

REMOTE__FILE If set, it enables -based authentication for the ASM instance checking for a file.

Initialization Parameters DB_CACHE_SIZE

Determines the size of the buffer cache.

DIAGNOSTIC_DEST Specifies the directory for instance diagnostics It defaults to the $ORACLE_BASE directory (Oracle grid infrastructure installation.)

INSTANCE_TYPE It must be set to Oracle ASM for an ASM instance.

PROCESSES Its setting affects Oracle ASM, but the default is usually suitable. Adjust to PROCESSES = 50 + 50*N (N, number of database instances connected to the ASM instance.)

Initialization Parameters SHARED_POOL_SIZE

Determines the amount of memory required to manage the instance. No need to set with automatic memory management (AMM). SGA configuration guidelines are as follows:

PROCESSES

Add 16 to database instance value.

LARGE_POOL_SIZE

Add an additional 600K to the instance value.

SHARED_POOL_SIZE

Add up the values from the data, temp and logfiles in ASM, with consideration to the redundancy level used.

Initialization Parameters • With normal redundancy disk groups, every 50 GB requires 1 MB of extra shared pool plus 4 MB • With high redundancy disk groups, every 33 GB requires 1 MB of extra shared pool plus 6 MB • With external redundancy disk groups, every 100 GB of space requires about 1 MB of extra shared pool plus 2 MB. SHARED_POOL_SIZE ESTIMATION QUERIES

SELECT SUM(bytes)/(1024*1024*1024) TOT_DATA_BYTES FROM V$DATAFILE; SELECT SUM(bytes)/(1024*1024*1024) TOT_TEMP_BYTES FROM V$TEMPFILE WHERE status='ONLINE'; SELECT SUM(bytes)/(1024*1024*1024) AS TOT_LOG_BYTES FROM V$LOGFILE L1, V$LOG L2 WHERE L1.group#=L2.group#;

Initialization Parameters AU_SIZE

Determines the size of the allocation unit for the disk group. Listed in the ALLOCATION_UNIT_SIZE column of the V$ASM_DISKGROUP view.

COMPATIBLE.ASM

Specifies the minimum software version for any Oracle ASM instance that uses a disk group.

COMPATIBLE.RDBMS

Specifies the minimum software version for any database instance that uses a disk group.

COMPATIBLE.ADVM

Determines whether the disk group can contain Oracle ASM volumes.

Optimizing Disk Discovery Disk discovery is the mechanism used to find the operating system names for disks Oracle ASM can access, when the instance is started, by matching ASM_DISKSTRING. Disk discovery also takes place when: •Running the following SQL statements such as, namely: oMount a disk group with ALTER DISKGROUP ... MOUNT oAdd a disk with CREATE or ALTER DISKGROUP...ADD DISK oOnline a disk with ALTER DISKGROUP ... ONLINE DISK oResize a disk with ALTER DISKGROUP...RESIZE DISK oQuery V$ASM_DISKGROUP or V$ASM_DISK views •Running OEM or ASM Configuration Assistant (ASMCA) operations invoking the above SQL statements. •Running ASMCMD commands that perform the same operations as the SQL statements as listed above.

Important ASM Discovery Rules The following ASM rebalancing rules apply: Oracle ASM can discover up to 10,000 disks. Oracle ASM only discovers disk partitions, excluding partitions that include the partition table. From the installation perspective, candidate disks are those that have the CANDIDATE, PROVISIONED, or FORMER header status, and they can be added to disk groups without using the FORCE flag.

ASM Load Balancing Adding Disks to a Diskgroup ALTER DISKGROUP adndata1 ADD DISK '/dev/diskA3' NAME diskA3, '/dev/diskA4' NAME diskA4; ALTER DISKGROUP adndata1 ADD DISK '/dev/diskA*'; ALTER DISKGROUP adndata2 ADD DISK '/dev/diskD*' REBALANCE POWER 4 WAIT; An alter diskgroup statement can fail when either a disk is already part of another diskgroup or when the search string matches disks already found in other disk groups. ;

ASM Maintenance If /dev/diskC3 was previously a member of a disk group that no longer exists, then it is possible to use the FORCE option to add the disk as a member of another disk group. This scenario is illustrated below and adndata3 cannot be mounted for the statement to succeed. ALTER DISKGROUP adndata3 ADD DISK '/dev/diskC3' FORCE; The FORCE option must be used if Oracle ASM recognizes that the disk was managed by Oracle, listed as FOREIGN in the V$ASM_DISK view. Thus, the FORCE keyword is used to add the disk to a disk group.

ASM Maintenance With its DROP DISK clause the ALTER DISKGROUP statement handles the task of dropping disks from a disk group. To drop all of the disks in specified failure groups, use the DROP DISKS IN FAILGROUP clause. The ALTER DISKGROUP...DROP DISK SQL statement returns to SQL prompt before the drop and rebalance operations complete, so the DBA must wait until the HEADER_STATUS column for this disk in V$ASM_DISK changes to FORMER before reusing, removing or disconnecting the dropped disk. Querying V$ASM_OPERATION allows to determine the amount of time remaining for the rebalance operation to complete.

ASM Maintenance The following example drops diska5 from disk group adndata1, and also illustrates how multiple actions are possible with one ALTER DISKGROUP statement.

ALTER DISKGROUP adndata1 DROP DISK diskA5 ADD FAILGROUP failgroup1 DISK '/dev/diskA7' NAME diskA7 POWER 5;

Intelligent Data Placement IDP enables setting disk regions on Oracle ASM disks for optimal performance, such that frequently accessed data is placed on the outermost (hot) tracks which have greater speed and higher bandwidth while files with similar access patterns are set physically close in order to reduce latency. Intelligent Data Placement also enables the placement of primary and mirror extents into different hot or cold regions.

Intelligent Data Placement Intelligent Data Placement works best for:

 Databases with data files that are accessed at different rates.  Disk groups that are more than 25% full.  Disks that have better performance at the beginning of the media relative to the end. Because Intelligent Data Placement leverages the geometry of the disk, it is well suited to JBOD (just a bunch of disks). In contrast, a storage array with LUNs composed of concatenated volumes masks the geometry from Oracle ASM. Then the COMPATIBLE.ASM and COMPATIBLE.RDBMS disk group attributes must be set to 11.2 or higher to use IDP.

Intelligent Data Placement Intelligent Data Placement can be managed with the ALTER DISKGROUP ADD or MODIFY TEMPLATE SQL and the ALTER DISKGROUP MODIFY FILE SQL statement, which include a disk region clause for setting hot/mirrorhot or cold/mirrorcold regions in a template: ALTER DISKGROUP adndata1 ADD TEMPLATE datafile_hot ATTRIBUTE ( HOT MIRRORHOT);

The ALTER DISKGROUP ... MODIFY FILE SQL statement that sets disk region attributes for hot/mirrorhot or cold/mirrorcold regions: ALTER DISKGROUP adndata1 MODIFY FILE '+data/adn3/datafile/tools.255.765689507' ATTRIBUTE ( HOT MIRRORHOT);

Intelligent Data Placement

ASM Default Templates Settings

Resizing Disk Groups The RESIZE clause of ALTER DISKGROUP enables you to perform the following operations: •Resize all disks in the disk group •Resize specific disks The following SQL statement illustrates a sample scenario: ALTER DISKGROUP adndata1 RESIZE DISKS IN FAILGROUP failgroup1 SIZE 150G;

Rebalancing Disk Groups Manual rebalance of files in a disk group is possible using the REBALANCE clause of the ALTER DISKGROUP statement. The POWER clause of the ALTER DISKGROUP … REBALANCE statement determines the degree of parallelism, from 0 (disable) to 11 (maximum). Rebalance is asynchronous and takes place in the background. The DBA can specify the WAIT keyword to cause the ALTER DISKGROUP...REBALANCE command to wait until the rebalance operation is complete.

Rebalancing Rules for Disk Groups An ongoing rebalance command is restarted if the storage configuration changes either when you alter the configuration, or when the configuration changes due to a failure or an outage and manual rebalance is required. The ALTER DISKGROUP...REBALANCE statement runs on a single node even if you are using Oracle RAC.

Oracle ASM can perform one disk group rebalance at a time on a given instance. Thus, multiple rebalances on different disk groups are processed serially. However, the DBA can initiate rebalances on different disk groups on different nodes in parallel.

Rebalancing Rules for Disk Groups Rebalancing continues across a failure of the Oracle ASM instance performing the rebalance. The REBALANCE clause (with its associated POWER and WAIT/NOWAIT keywords) can also be used in ALTER DISKGROUP commands that add, drop, or resize disks. Oracle restarts the processing of an ongoing rebalance operation if the storage configuration changes. If the next rebalance operation fails because of a error, then a manual rebalance may be required. Example: ALTER DISKGROUP adndata2 REBALANCE POWER 6 WAIT;

Managing Capacity in Disk Groups For Normal redundancy disk group, it is best to have enough free space in the disk group to tolerate the loss of all disks in one failure group whose size could be equivalent to the size of the largest failure group. For High redundancy disk group, it is optimal to have enough free space to cope with the loss of all disks in two failure groups. The amount of free space should be equivalent to the sum of the sizes of the two largest failure groups.

Managing Capacity in Disk Groups The V$ASM_DISKGROUP view contains the following columns that contain information to help manage capacity: REQUIRED_MIRROR_FREE_MB indicates the amount of space that must be available in a disk group to restore full redundancy after the worst failure that can be tolerated by the disk group without adding additional storage. This requirement ensures that there are sufficient failure groups to restore redundancy. Also, this worst failure refers to a permanent failure where the disks must be dropped, not the scenario where the disks go offline and then back online.

Managing Capacity in Disk Groups The amount of space displayed in this column, takes mirroring into consideration and is computed as follows: •Normal redundancy disk group with more than two failure groups The value is the total raw space for all of the disks in the largest failure group. The largest failure group is the one with the largest total raw capacity. •High redundancy disk group with more than three failure groups The value is the total raw space for all of the disks in the two largest failure groups. USABLE_FILE_MB indicates the amount of free space, adjusted for mirroring, that is available for new files to restore redundancy after a disk failure.

ACFS Oracle Automatic Storage Management Cluster File System (Oracle ACFS) is a multi-platform, scalable file system, and storage management technology that extends Oracle Automatic Storage Management (Oracle ASM) functionality to customer files maintained outside of the Oracle Database. Oracle ACFS s many database and application files, including executables, database trace files, database alert logs, application reports, BFILEs, and configuration files. Other ed files are video, audio, text, images, engineering drawings, and other general-purpose application file data.

ASM Dynamic Volume Manager Limits of Oracle ADVM

The default configuration for an Oracle ADVM volume is four columns of 64 MB extents in length and a 128 KB stripe width. Oracle ADVM writes data as 128 KB stripe chunks in round robin fashion to each column and fills a stripe set of four 64 MB extents with 2000 stripe chunks before moving to a second stripe set of four 64 MB extents for volumes greater than 256 megabytes. Note that setting the number of columns on an Oracle ADVM dynamic volume to 1 effectively turns off striping for the Oracle ADVM volume.

ADVM On Linux Oracle ADVM volume devices are created as block devices regardless of the configuration of the underlying storage in the Oracle ASM disk group.

The Oracle ASM instance is started during the Grid Infrastructure installation process whenever the Oracle Clusterware Registry (OCR) and voting files are configured within an Oracle ASM disk group.

ADVM

Besides, the Oracle ASM instance can also be started using the Oracle ASM Configuration Assistant and the Oracle ACFS drivers are loaded based on that action. In steady state mode, the Oracle ACFS drivers are automatically loaded during Oracle Clusterware initialization when the Oracle High Availability Services Daemon (OHASD) calls the start action for the Oracle ASM instance resource that also results in loading the Oracle ACFS drivers due to the resource dependency relationship. The start action for the Oracle ACFS drivers’ resource attempts to load the Oracle ACFS, Oracle ADVM, and Oracle Kernel Services (OKS) drivers into the native operating system.

Managing Volumes in a Disk Group SQL> ALTER DISKGROUP adndata1 ADD VOLUME voladn1 SIZE 12G; Diskgroup altered. SQL> ALTER DISKGROUP adndata1 RESIZE VOLUME voladn1 SIZE 16G; Diskgroup altered. SQL> ALTER DISKGROUP adndata1 DISABLE VOLUME voladn1; Diskgroup altered. SQL> ALTER DISKGROUP adndata1 ENABLE VOLUME voladn1; Diskgroup altered. SQL> ALTER DISKGROUP ALL DISABLE VOLUME ALL; Diskgroup altered.

ASM Rebalancing Factors The following is list factors possibly affecting the impact of load balancing on ASM instances:

Factor 1: Redundancy Factor 2: Clusterware Factor 3: Allocation Unit Factor 4: Striping Types Factor 5: Extent design Factor 6: Compatibility Issues Factor 7: for ACFS Factor 8: for ADVM

Concluding Remarks ASM's rebalance process is very easy. ASM simply rebalances a diskgroup whenever a disk is added or dropped. Rather than restriping all the data, ASM only needs to move an amount of data by using index techniques to spread extents on the available disks.

Combining operations, such as ADD and DROP a DISK, at the same time, helps minimize the overhead on both the ASM and database instances, and consequently or normal production operations

Concluding Remarks Finally, adjusting the ASM_POWER_LIMIT can enable controlled rebalancing while operations requiring data movement, control of redundancy and other-relevant tasks are being performed. The above mentioned conclusions are valid for a native ASM environment, and one ing ACFS and ADVM. As ASM technology becomes a widely RAC infrastructure standard with 65% production deployment.

Thank you! Please complete your evaluation form! Speaker: Anthony Noriega Title: Oracle ASM Load Balancing. ORACLE ASM STORAGE FORUM SESSION ID: S313474 http://noriegaaoracleexpert.blogspot.com [email protected]