Archive
Managing ASM Disk Groups
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.
Bharat Damarla
Never Say Die
Blogs I Follow
StartupForce 