intelligent data placing

Managing ASM Disk Groups

May 27, 2013 Bharat Leave a comment

View Mounted Disks

SQL> SELECT group_number, disk_number, mount_status, header_status,
mode_status, state, redundancy, name, failgroup, path
FROM v$asm_disk
WHERE mode_status = 'ONLINE';
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE 
REDUNDA NAME FAILGROUP PATH
------------ ----------- ------- ------------ ------- ------ ------- ----- --------- --------
1 0 CACHED MEMBER ONLINE NORMAL UNKNOWN DATA1 DATA1 ORCL:DATA1

HEADER_STATUS column indicates the use of the disk and may contain one of the following values:

MEMBER – This disk is assigned to a disk group.
PROVISIONED – The disk has been stamped with ASM data thereby prepared for assigned to a disk group and use by ASM. At present though, the disk is not being used.
CANDIDATE – The disk has been discovered and is potentially usable by ASM, but otherwise no actions have been taken to utilize the device.

Create Disk Groups

SQL> CREATE DISKGROUP sidDG1 NORMAL REDUNDANCY
       FAILGROUP sidDB1FGa DISK
         '/dev/data2',
         '/dev/disk3'
       FAILGROUP sidDB1FGb DISK
         '/dev/data2a',
         '/dev/data3a'
Diskgroup created.

Drop Disk Group

SQL> DROP DISKGROUP sidDG1 INCLUDING CONTENTS;
Diskgroup dropped.

Adding a disk

SQL> ALTER DISKGROUP sidDG1 ADD
FAILGROUP sidDB1FGa
DISK '/dev/data4'
FAILGROUP sidDB1FGb
DISK '/dev/data4a';
Diskgroup altered.

Dropping a Disk

SQL> ALTER DISKGROUP sidDG1 DROP DISK '/dev/data4';
Diskgroup altered.

Alter rebalance power

Override the default rebalancing power limit defined in the ASM_POWER_LIMIT attribute for any disk group

SQL> ALTER DISKGROUP sidDG1 REBALANCE POWER 5;
Diskgroup altered.

Mount/Dismount Disk Groups

SQL> ALTER DISKGROUP sidDG1 DISMOUNT;
Diskgroup altered.

SQL> ALTER DISKGROUP sidDG1 MOUNT;
Diskgroup altered.

View basic metadata

SQL> SELECT name, allocation_unit_size, state, type, offline_disks
     FROM v$asm_diskgroup;

NAME           ALLOCATION_UNIT_SIZE  STATE       TYPE   OFFLINE_DISKS
--------------- -------------------- ----------- ------ -------------
sidDG1                       1048576 MOUNTED     EXTERN             0
sidDG2                       1048576 CONNECTED   EXTERN             0

SQL> SELECT group_number, disk_number, mount_status, state, redundancy
     FROM v$asm_disk;

GROUP_NUMBER DISK_NUMBER MOUNT_S STATE    REDUNDA
------------ ----------- ------- -------- -------
           1           0 CACHED  NORMAL   UNKNOWN
           1           1 CACHED  NORMAL   UNKNOWN
           2           0 CACHED  NORMAL   UNKNOWN
           2           1 CACHED  NORMAL   UNKNOWN

Internal Metadata Consistency Check

There may be inconsistencies between the internal metadata maintained for disk group files and the actual storage of those files. This can be checked through

SQL> ALTER DISKGROUP data CHECK REPAIR;
Diskgroup altered.

Resize Diskgroup

The ALTER DISKGROUP … RESIZE operation will allow one to modify the size of storage and to perform a rebalance operation as well.

SQL> ALTER DISKGROUP data
       RESIZE DISK data1
       SIZE 300 G
       REBALANCE POWER 2;
Diskgroup altered.

If the new size is greater than disk capacity, the statement will fail.

Disk Group Version Compatibility

Compatible attribute settings at the disk group level can allow older relational databases to coexist with newer ones in the same ASM environment. This attribute also determines whether or not newer ASM features are enabled.

Setting	Description
COMPATIBLE.ASM	Determines the compatibility level of the ASM instance.
COMPATIBLE.RDBMS	Determines the compatibility level of all RDBMS instances using the ASM disk group.

Suppose that an Oracle 11g R2 ASM instance needed to support Oracle 10g R1 database instances, and that one wanted to ensure that no ASM features beyond Oracle 10g were used within the disk groups. One would set the compatibility attributes for both properties to 10.1. This is the case in the disk group listed next within the V$ASM_DISKGROUP view.

SQL> SELECT name, compatibility, database_compatibility
     FROM v$asm_diskgroup;

NAME       COMPATIBIL DATABASE_C
---------- ---------- ----------
DATA       10.1.0.0.0 10.1.0.0.0

Modifying Disk Group Compatibility

If we want to use this same ASM instance to take advantage of the new features within Oracle 11g create a different disk group named “sidDG1” using the same ASM instance. Next, within this new disk group one might enable the new features by a higher setting for COMPATIBLE.ASM.

SQL> ALTER DISKGROUP sidDG1
       SET ATTRIBUTE 'compatible.asm' = '11.2';
Diskgroup altered.

If we want to restrict the disk group to be compatible to database instances

SQL> ALTER DISKGROUP sidDG1
       SET ATTRIBUTE 'compatible.rdbms' = '11.2';
Diskgroup altered.

Disk Group Access Control

We can isolate access to ASM data for one instance from another database instance which may have distinct security authorizations. Otherwise, any user with SYSDBA rights would be able to access any ASM disk group or file for any instance using the ASM installation.

With access control, a user with SYSDBA rights is selectively granted SYSASM rights on the disk group or file to which access control has been granted.

Note: This feature is available starting with Oracle 11g R2.

Access Control Privileges

The following privileges may be assigned to a disk group or to an individual file within a disk group.

Privilege	Description
owner	This is the user which created the disk group or file for which access control privileges are assigned.
group	This is a list of users assembled into an ASM group and for which access control privileges will be assigned.
other	These are the access control privileges for any SYSDBA user who is not the owner or an authorized group.

Implementing Access Control

In order to implement access control, each database installation must be owned by a distinct system account. Access control will be implemented as below

One connects to the database instance using SYSDBA rights.
A new disk group is created for that database instance or an existing disk group is modified. Default access control privileges are assigned to the disk group at this same time and all subsequent files created within the disk group will inherit these privilege settings.
Individual files whose default access control privileges were inherited from the disk group may be overridden with file-specific privileges.

Access Control Details

The specific access control privileges for a disk group are assigned in this manner:

One enables access control for the disk group.
One specifies the rights for the owner, access control user group and other users in the form of a 3-digit user mask, with each digit indicating the rights for the respective user accounts.
One adds system accounts as authorized users for the disk group.
One adds users from the list of authorized users to the list of user groups for the disk group.

The 3-digit mask may be understood as follows, with each of the 3 positions in the mask indicating the privilege assigned to the owner, user group and other users respectively.

Privilege	Description
0	Read-write privileges
2	Read-only privileges
6	No privileges

Mask	Description
000	Read-write privilege for the owner, user group and other users.
026	Read-write privilege for the owner, read-only privilege for the user group, no privileges for other users.
066	This is the default setting and indicates read-write privilege for the owner, no privileges for the user group, no privileges for other users.

Default Access Control for a Disk Group

SQL> ALTER DISKGROUP data
        SET ATTRIBUTE 'access_control.enabled' = 'true';
Diskgroup altered.

SQL> ALTER DISKGROUP data
        SET ATTRIBUTE 'access_control.umask' = '002';
Diskgroup altered.

Managing Disk Group Access Control Groups

With the access control privileges in effect for a disk group, one can then define which system accounts belong to the user group(s) associated with the disk group. Firstwe need to define one or more system accounts as users for the disk group. The owner is always considered to have access to the disk group.

SQL> ALTER DISKGROUP data
        ADD USER 'oracle1', 'administrator';
Diskgroup altered.

Next, these users may be associated with an access control group by means of the ALTER DISK GROUP … ADD USERGROUP statement. These users will now obtain the user group privileges assigned to the disk group earlier in this discussion.

SQL> ALTER DISKGROUP data
         ADD USERGROUP 'test4x_storageadmin'
         WITH MEMBER 'oracle1', 'administrator';
Diskgroup altered.

Now list the system accounts associated with the disk group user groups by querying V$ASM_USER.

SQL> SELECT group_number, user_number, os_name
     FROM v$asm_user;

GROUP_NUMBER USER_NUMBER OS_NAME
------------ ----------- --------------------
           1           1 oracle
           1           2 administrator
           1           3 oracle1

A new member to the user group may be added.

SQL> ALTER DISKGROUP data
         MODIFY USERGROUP 'test4x_storageadmin'
         ADD MEMBER 'oracle2';
Diskgroup altered.

Or a member may be dropped from the user group, thereafter obtaining its privileges from the other users position in the mask.

SQL> ALTER DISKGROUP data
         MODIFY USERGROUP 'test4x_storageadmin'
         DROP MEMBER 'oracle2';
Diskgroup altered.

Finally a user group may be dropped altogether.

SQL> ALTER DISKGROUP data
         DROP USERGROUP 'test4x_storageadmin';
Diskgroup altered.

Additional Disk Group Attributes

DISK_REPAIR_TME

Individual disks within a group for which redundancy is enabled can fail while the others remain operational. Until such time as the failed device is repaired and brought back online, extents updated on the other online disks of the group become stale and must be eventually copied to the failed disk once it is back online. As long as the failed disk is part of the group and the stale extents are tracked, the disk group may be repaired once the device is online again.

The disk group attribute DISK_REPAIR_TIME indicates the time period for which stale extent tracking should occur. If the offline disk is brought online during this period, then it will be resynchronized using fast mirror resync. If the offline disk is not brought online during this time period, then it will be dropped from the disk group permanently. At a later point in time a device can be added to the group and it may be either resynchronized or rebalanced with the other disk group members as needed.

The default value for DISK_REPAIR_TIME is 3.6 hours, but it may be modified using the following command.

SQL> ALTER DISKGROUP sidDG1
       SET ATTRIBUTE 'disk_repair_time' = '4h';
Diskgroup altered.

Fast Mirror Resync

We can quickly bring a disk within a disk group back online after a crash by means of the fast mirror resync capability.

Consider the following scenario: an individual disk within a disk group crashes and for a period of two hours is offline from its disk group. Each update to a different extent creates a stale extent, although these are relatively few since updates during this period are concentrated on a few specific extents.

Once the disk comes online, re-synchronizing it with the other members of the disk group can be very time consuming. For this reason one can bring the disk online individually with the following command.

SQL> ALTER DISKGROUP sidDG1 ONLINE DISK d1_0001;
Diskgroup altered.

This immediately allows new writes to be included on this disk, avoiding any further backlog of stale extents. Then, as processing permits, extents which became stale during the disk outage are copied to the device. This enhancement provides a dramatic reduction in disk group failure repair time.

AU_SIZE

By default an allocation unit is 1 MB in size. However, if one knows in advance that very large files will be used in a disk group, then one can modify the allocation unit size at the time the disk group is created using the AU_SIZE attribute. The larger sizes permitted are 2, 4, 8, 16, 32 and 64 MB. With variable size extents the same formula in obtaining allocation units is used, but the size of the extents is much larger based upon the larger size allocation unit.

In this example a 2 MB allocation unit is specified for the disk group.

SQL> CREATE DISKGROUP sidDG1 NORMAL REDUNDANCY 
           FAILGROUP sidDB1FGa DISK '/dev/data2', '/dev/disk3' 
           FAILGROUP sidDB1FGb DISK '/dev/data2a', '/dev/data3a' 
                                        ATTRIBUTE 'au_size' = '2M'; 
Diskgroup created.

Disk Group File Attributes

One may also manage individual files within the disk groups on a limited basis.

File Permissions

All files created within the disk group will inherit the access control privileges associated with the disk group. However, each file may have its own unique set of access control privileges by means of the ALTER DISKGROUP … SET PERMISSION statement.

SQL> ALTER DISKGROUP data
      SET PERMISSION OWNER=Read write, GROUP=Read write, OTHER=Read only
      FOR FILE '+DATA/TEST4G/DATAFILE/SIDERISTS1.256.723751977';
Diskgroup altered.

Note: This feature is available starting with Oracle 11g R2.

File Name Aliases

When managing disk group files, it may be advantageous to assign an alias name. One may thereafter reference the file by its alias name rather than its system-assigned name.

SQL> ALTER DISKGROUP data
         ADD ALIAS '+DATA/TEST4G/DATAFILE/SiderisIndex' FOR
                   '+DATA/TEST4G/DATAFILE/SIDERISIDX1.263.723912065';
Diskgroup altered.

Obtaining File Metadata

Useful metadata for the ASM files is available from the V$ASM_FILE view.

SQL> SELECT group_number, file_number, bytes, type, permissions
     FROM v$asm_file;

GROUP_NUMBER FILE_NUMBER      BYTES TYPE                 PERMISSIONS
------------ ----------- ---------- -------------------- -----------------
           1         253       1536 ASMPARAMETERFILE     rw-rw-rw-
           1         256     114688 DATAFILE             rw-rw-rw-
           1         257    9748480 CONTROLFILE          rw-rw-rw-
           1         258    9748480 CONTROLFILE          rw-rw-rw-
           1         259       3584 PARAMETERFILE        rw-rw-rw-
           1         260    1056768 DATAFILE             rw-rw-rw-
           1         261   52429312 ONLINELOG            rw-rw-rw-
           1         262   52429312 ONLINELOG            rw-rw-rw-
           1         263    1056768 DATAFILE             rw-rw-rw-
           1         264     212992 DATAFILE             rw-rw-r--

Disk Group Templates

A disk group template is a set of attributes which define such things as redundancy mirroring and striping characteristics, intelligent data placement and access control settings for files stored within the disk group.

A system-defined set of templates exists for the different categories of files or file types managed by ASM. By means of such templates, files created within the disk group inherit a consistent set of storage attributes appropriate for their file type.

Each disk group may have its own templates with their own unique attributes appropriate for that disk group.

Examining Template Attributes

The V$ASM_TEMPLATE view will indicate the current set of templates associated with a disk group when the disk group is created. The view will also indicate the default attribute settings for the templates.

SQL> SELECT * FROM v$asm_template;

GROUP_NUMBER ENTRY_NUMBER REDUND STRIPE S NAME              PRIM MIRR
------------ ------------ ------ ------ - ------------------ ---- ----
           1           60 UNPROT COARSE Y PARAMETERFILE      COLD COLD
           1           61 UNPROT COARSE Y ASMPARAMETERFILE   COLD COLD
           1           63 UNPROT COARSE Y DUMPSET            COLD COLD
           1           64 UNPROT FINE   Y CONTROLFILE        COLD COLD
           1           65 UNPROT COARSE Y FLASHFILE          COLD COLD
           1           66 UNPROT COARSE Y ARCHIVELOG         COLD COLD
           1           67 UNPROT COARSE Y ONLINELOG          COLD COLD
           1           68 UNPROT COARSE Y DATAFILE           COLD COLD
           1           69 UNPROT COARSE Y TEMPFILE           COLD COLD
           1          170 UNPROT COARSE Y BACKUPSET          COLD COLD
           1          171 UNPROT COARSE Y AUTOBACKUP         COLD COLD
           1          172 UNPROT COARSE Y XTRANSPORT         COLD COLD
           1          173 UNPROT COARSE Y CHANGETRACKING     COLD COLD
           1          174 UNPROT COARSE Y FLASHBACK          COLD COLD
           1          175 UNPROT COARSE Y DATAGUARDCONFIG    COLD COLD
           1          176 UNPROT COARSE Y OCRFILE            COLD COLD
           1          177 UNPROT COARSE Y OCRBACKUP          COLD COLD
           1          178 UNPROT COARSE Y ASM_STALE          COLD COLD

One will be able to modify these attributes, as well as the templates themselves, for each disk group.

Using a Template

By default if you want to place all data files within the hot region of the disk for a particular disk group modify the attributes of the system-supplied template named DATAFILE in this manner.

SQL> ALTER DISKGROUP data
         ALTER TEMPLATE "DATAFILE"
         ATTRIBUTES (UNPROTECTED COARSE HOT);
Diskgroup altered.

Thereafter, if one wanted to create a new data file using this template reference both the disk group and the template using the syntax ‘+DiskGroup(TemplateName)’.

SQL> ALTER TABLESPACE SiderisTS2
        ADD DATAFILE '+DATA(DATAFILE)' SIZE 100M;
Tablespace altered.

ASM Disk Group Sector Size

Disk groups include an attribute known as SECTOR_SIZE. A substantial degradation in performance will occur if the SECTOR_SIZE setting is 512 while 4K sector disk devices are used. So we can use this setting to ensure that the operation of the disk group is consistent with the disk devices contained within the group.

SQL> CREATE DISKGROUP sidDG1 NORMAL REDUNDANCY
       FAILGROUP sidDB1FGa DISK
         '/dev/data2',
         '/dev/disk3'
       FAILGROUP sidDB1FGb DISK
         '/dev/data2a',
         '/dev/data3a'
       ATTRIBUTE 'sector_size' = '4096';
Diskgroup created.

SQL> SELECT group_number, sector_size
     FROM v$asm_diskgroup;

GROUP_NUMBER SECTOR_SIZE
------------ -----------
           1        4096

All devices within a disk group must have the same SECTOR_SIZE setting.

ASM Intelligent Data Placement

One would implement appropriate data placement of the data files using a few basic steps. First, one must ensure that the compatible.rdbms disk group attribute is set to the appropriate release.

SQL> ALTER DISKGROUP data
       SET ATTRIBUTE 'compatible.rdbms' = '11.2';
Diskgroup altered.

Next, using the ALTER DISKGROUP…MODIFY FILE command within the ASM instance, one can move the data files to the appropriate region of the disk.

SQL> ALTER DISKGROUP data
       MODIFY FILE
       '+DATA/test4g/datafile/siderists1.256.723751977'
       ATTRIBUTE (COLD);
Diskgroup altered.

SQL> ALTER DISKGROUP data
       MODIFY FILE
       '+DATA/test4g/datafile/siderisidx1.263.723912065'
       ATTRIBUTE (HOT);
Diskgroup altered.

Note: Initially this operation will only affect the placement of new file extensions. When rebalance operation happens, then all file extents will be placed according to the instructions given here.

In addition to the primary region of a file, one could also specify the placement of mirror regions using the MIRRORHOT and MIRRORCOLD attributes as well. Of course, the disk group must support mirroring in order for this option to succeed.

SQL> ALTER DISKGROUP data
       MODIFY FILE
       '+DATA/test4g/datafile/siderisidx1.263.723912065'
       ATTRIBUTE (HOT
                  MIRRORCOLD);
Diskgroup altered.