May 02 2020

Automatic Storage Management (ASM)

In this topic, I will discuss how to manage ASM in Oracle including following items.

  • Important queries to manage or monitor ASM.
  • Create ASM DISKGROUP
  • Manage Existing Diskgroup (add / drop disk)
  • Tuning I/O Performance in Oracle ASM
  • Mapping ASM Disk with Physical DISK

ASM REDUNDANCY Level:

  • NORMAL REDUNDANCY – Two-way mirroring, requiring two failure groups.
  • HIGH REDUNDANCY – Three-way mirroring, requiring three failure groups.
  • EXTERNAL REDUNDANCY – In this configuration there are only primary extents and no mirrored extents.

1. Important queries to manager or monitor ASM.

Use the following query to check the list of ASM DISKs are available in Oracle ASM Instance:

[oracle@racdr1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@racdr1 ~]$ sqlplus / as sysasm
SQL> set linesize 1000
SQL> col PATH for A30
SQL> Select dg.Name DISKGROUP_NAME, d.NAME DISK_NAME, d.PATH, d.TOTAL_MB, d.FREE_MB, d.MOUNT_STATUS, d.HEADER_STATUS,d.MODE_STATUS,d.STATE
FROM V$ASM_DISK d, V$ASM_DISKGROUP dg
WHERE dg.GROUP_NUMBER = d.GROUP_NUMBER
ORDER BY dg.GROUP_NUMBER, d.NAME;

DISKGROUP_NAME                 DISK_NAME                      PATH                             TOTAL_MB    FREE_MB MOUNT_S HEADER_STATU MODE_ST STATE
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ------- ------------ ------- --------
FRA                            DATADISK1                      ORCL:DATADISK1                       5114       5064 CACHED  MEMBER       ONLINE  NORMAL
FRA                            DATADISK2                      ORCL:DATADISK2                       5114       5065 CACHED  MEMBER       ONLINE  NORMAL
DATA                           DATADISK3                      ORCL:DATADISK3                       5114       2130 CACHED  MEMBER       ONLINE  NORMAL
DATA                           DATADISK4                      ORCL:DATADISK4                       5114       2130 CACHED  MEMBER       ONLINE  NORMAL
DATA01                         DATADISK8                      ORCL:DATADISK8                       5114       4807 CACHED  MEMBER       ONLINE  NORMAL
OCR                            OCRDISK1                       ORCL:OCRDISK1                        3067       1552 CACHED  MEMBER       ONLINE  NORMAL
OCR                            OCRDISK2                       ORCL:OCRDISK2                        3067       1585 CACHED  MEMBER       ONLINE  NORMAL
OCR                            OCRDISK3                       ORCL:OCRDISK3                        3067       1576 CACHED  MEMBER       ONLINE  NORMAL

10 rows selected.

Use following query to show the List of ASM DISKGroup are associated with a Database:

SQL> SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance, SUBSTR(c.db_name,1,12) AS dbname
FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c 
WHERE dg.group_number = c.group_number;

DISKGROUP                      INSTANCE     DBNAME
------------------------------ ------------ --------
DATA                           ORCL1        ORCL
OCR                            +ASM1        +ASM
OCR                            -MGMTDB      _mgmtdb

Create a Tablespcae with new ASM DISKGroup

SQL> Create Tablespace tbs_test datafile '+DATA01' size 50M;

Tablespace created.

Again run the query to check DISKGROUP List for ORCL database:

SQL> SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance, SUBSTR(c.db_name,1,12) AS dbname
FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c
WHERE dg.group_number = c.group_number;

DISKGROUP                      INSTANCE     DBNAME
------------------------------ ------------ --------
DATA                           ORCL1        ORCL
DATA01                         ORCL1        ORCL
OCR                            -MGMTDB      _mgmtdb
OCR                            +ASM1        +ASM

2. Create ASM DISKGROUP

ASM supported version: (Doc ID 337737.1)

ClusterwareASMDBCertified
19c19c19c, 18c, 12.2, 12.1, 11.2Y
18c18c18c, 12.2, 12.1, 11,2 Y
12.212.212.2, 12.1, 11.2Y
12.112.112.1, 11.2, 11.2, 10.2Y
11.211.211.2, 11.1, 10.2Y

Below attributes value may set during ASM Diskgroup creation.

  • AU_SIZE: Specifies the size of the allocation unit for the disk group.You can view the value of the AU_SIZE disk group attribute in the ALLOCATION_UNIT_SIZE column of the V$ASM_DISKGROUP view.
  • COMPATIBLE.ASM: Determines the minimum software version for any Oracle ASM instance that uses a disk group.
  • COMPATIBLE.RDBMS: Determines the minimum software version for any database instance that uses a disk group.
  • COMPATIBLE.ADVM: Determines whether the disk group can contain Oracle ADVM volumes.
[oracle@racdr1 ~]$ echo $ORACLE_HOME
/u01/app/12.1.0/grid
[oracle@racdr1 ~]$ echo $ORACLE_SID
+ASM1
[oracle@racdr1 ~]$ sqlplus / as sysasm

SQL> CREATE DISKGROUP DATA01 NORMAL REDUNDANCY
      FAILGROUP controller1 DISK
        'ORCL:DATADISK6' NAME DISK6
      FAILGROUP controller2 DISK
        'ORCL:DATADISK7' NAME DISK7
      ATTRIBUTE 'au_size'='4M', 'compatible.asm' = '12.1.0.0.0', 'compatible.rdbms' = '11.2',        'compatible.advm' = '11.2';

Diskgroup created.

If disk path is using full path like below then create Diskgroup with below command. Get path information from V$ASM_DISK

CREATE DISKGROUP data NORMAL REDUNDANCY
  FAILGROUP controller1 DISK
    '/devices/diska1' NAME diska1,  '/devices/diska2' NAME diska2
  FAILGROUP controller2 DISK
    '/devices/diskb1' NAME diskb1, '/devices/diskb2' NAME diskb2
  ATTRIBUTE 'au_size'='4M', 'compatible.asm' = '11.2',  'compatible.rdbms' = '11.2',   'compatible.advm' = '11.2';

Set Attributes:

Attributes can be set or changed with ALTER DISKGROUP or asmcmd command. Get Attributes list with the V$ASM_ATTRIBUTE view or ASMCMD -> lsattr

SQL> ALTER DISKGROUP DATA01 SET ATTRIBUTE 'content.type' = 'data';

Diskgroup altered.

3. Manage Existing Diskgroup (add / drop disk):

3.1. Add Disk into a DiskGroup:

SQL> ALTER DISKGROUP DATA01 ADD DISK 'ORCL:DATADISK6' REBALANCE POWER 5;

Diskgroup altered.

3.2. Drop Disk from a Diskgroup:

SQL> ALTER DISKGROUP DATA01
DROP DISK 'ORCL:DATADISK6'
REBALANCE POWER 5;
  2    3  ALTER DISKGROUP DATA01
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15054: disk "ORCL:DATADISK6" does not exist in diskgroup "DATA01"

When drop a disk from Diskgroup, don’t use full path of DISK. Correct Syntax is:

SQL> ALTER DISKGROUP DATA01 DROP DISK 'DATADISK6' REBALANCE POWER 5;

Diskgroup altered.

3.3. Adding and Dropping disk with Single Command: Replace is better option but applicable for Normal or High Redundancy.

Note::: If disks don’t configure properly or multi-path option is missing then after adding disk into disk group, both instance will not run parallel. So for any kind of disk issues (if arise after adding into a Diskgroup) then add and drop in single command will be usefully. 

SQL> ALTER DISKGROUP DATA ADD DISK 'ORCL:DATADISK6' DROP DISK 'DATADISK7' REBALANCE POWER 11;

Diskgroup altered.

3.4. Replacing Disks in Disk Groups

A disk or multiple disks in a disk group can be replaced, rather than dropped and added back.

The single replace operation is more efficient than dropping and adding disks. This operation is especially useful when disks are missing or damaged.

For External Redundancy: Data Diskgroup is External Redundancy

SQL> ALTER DISKGROUP DATA REPLACE DISK DATADISK5 WITH 'ORCL:DATADISK8' POWER 3;
ALTER DISKGROUP DATA REPLACE DISK DATADISK5 WITH 'ORCL:DATADISK8' POWER 3
*
ERROR at line 1:
ORA-15067: command or option incompatible with diskgroup redundancy

For Normal or High Redundancy: DATA01 is Normal Redundancy

SQL> ALTER DISKGROUP DATA01 REPLACE DISK DISK6 WITH 'ORCL:DATADISK8' POWER 3;
ALTER DISKGROUP DATA01 REPLACE DISK DISK6 WITH 'ORCL:DATADISK8' POWER 3
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15145: ASM disk 'DISK6' is online and cannot be replaced.
SQL> ALTER DISKGROUP DATA01 SET ATTRIBUTE 'failgroup_repair_time'='1H';

Diskgroup altered.
ALTER DISKGROUP DATA01 REPLACE DISK DISK6 WITH 'ORCL:DATADISK5' POWER 3;

3.5. The re-balanced activity can be monitored using below query: 

[oracle@racdr1 ~]$ echo $ORACLE_HOME
/u01/app/12.1.0/grid
[oracle@racdr1 ~]$ echo $ORACLE_SID
+ASM1
[oracle@racdr1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 2 11:45:24 2020
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> SELECT GROUP_NUMBER, PASS, STATE FROM V$ASM_OPERATION;

GROUP_NUMBER PASS      STAT
------------ --------- ----
           2 REBALANCE RUN
           2 COMPACT   WAIT

SQL> SELECT GROUP_NUMBER, PASS, STATE FROM V$ASM_OPERATION;

GROUP_NUMBER PASS      STAT
------------ --------- ----
           2 REBALANCE DONE
           2 COMPACT   RUN

SQL> SELECT GROUP_NUMBER, PASS, STATE FROM V$ASM_OPERATION;

GROUP_NUMBER PASS      STAT
------------ --------- ----
           2 REBALANCE DONE
           2 COMPACT   REAP

SQL> SELECT GROUP_NUMBER, PASS, STATE FROM V$ASM_OPERATION;

no rows selected

4. Tuning I/O Performance in Oracle ASM

Tuning I/O Performance in Oracle ASM:

Important Articles:

To identify unbalanced I/O Operations in Oracle ASM DISKGROUP below script can use

SQL> SELECT dg.group_number "GROUP#", dg.name, DECODE (total_dg.total_io, 0, 100, 100 * (DECODE (SIGN (1 - df.sum_io / total_dg.total_io), -1, 0, (1 - df.sum_io / total_dg.total_io)))) "IO_BALANCED"
    FROM (SELECT d.group_number group_number, SUM (ABS ((d.reads + d.writes) - tot.avg_io)) sum_io 
	FROM v$asm_disk_stat d, (SELECT group_number, SUM (reads) + SUM (writes), DECODE (COUNT (*), 0, 0, (SUM (reads) + SUM (writes)) / COUNT (*)) avg_io FROM v$asm_disk_stat 
		WHERE header_status = 'MEMBER' 
		GROUP BY group_number) tot
    WHERE header_status = 'MEMBER' AND tot.group_number = d.group_number
    GROUP BY d.group_number) df,
    (SELECT group_number, SUM (reads) + SUM (writes) total_io 
			FROM v$asm_disk_stat 
			WHERE header_status = 'MEMBER'
			GROUP BY group_number) total_dg,
			V$ASM_DISKGROUP dg
    WHERE df.group_number = total_dg.group_number AND df.group_number = dg.group_number;

    GROUP# NAME                           IO_BALANCED
---------- ------------------------------ -----------
         1 FRA                             2.16670915
         2 DATA                            37.2085975
         4 OCR                             63.7633002
         3 DATA01                          99.8717949

5. Mapping Details between ASM and Physical DISK

Sometimes it is required to find the Physical disk with corresponding ASM DISK to drop or modify.

Below script can used to identify the Physical disk mapping with ASM Disk. 

[root@racdr1 ~]# cat asm_disk_mapping.sh
#!/bin/bash
# ASM_DISK_PATH  -- disk name in ASMLIB
# ASM_DISK -- disk name in ASM
# DEVICE -- physical disk name
GRID_HOME=`cat /etc/oratab  | grep ^+ASM | awk -F":" '{print $2}'`
for ASM_DISK_PATH in `ls /dev/oracleasm/disks/*`
 do
    ASM_DISK=`$GRID_HOME/bin/kfed read $ASM_DISK_PATH | grep dskname | tr -s ' '| cut -f2 -d' '`
    major_minor=`ls -l $ASM_DISK_PATH | tr -s ' ' | cut -f5,6 -d' '`
    device=`ls -l /dev/ | tr -s ' ' | grep -w "$major_minor" | cut -f10 -d' '`
    echo "ASM LIB Disk Name : $ASM_DISK_PATH"
    echo "ASM DISK name : $ASM_DISK"
    echo "Physical disk device : /dev/$device"
    echo "---------------------------------------------------------------"
done
[root@racdr1 ~]# ./asm_disk_mapping.sh

ASMLIB disk name : /dev/oracleasm/disks/DATADISK1
ASM_DISK name : DATADISK1
Physical disk device : /dev/sde1
ASMLIB disk name : /dev/oracleasm/disks/DATADISK2
ASM_DISK name : DATADISK2
Physical disk device : /dev/sdf1
ASMLIB disk name : /dev/oracleasm/disks/DATADISK3
ASM_DISK name : DATADISK3
Physical disk device : /dev/sdg1
ASMLIB disk name : /dev/oracleasm/disks/DATADISK4
ASM_DISK name : DATADISK4
Physical disk device : /dev/sdh1
ASMLIB disk name : /dev/oracleasm/disks/OCRDISK1
ASM_DISK name : OCRDISK1
Physical disk device : /dev/sdb1
ASMLIB disk name : /dev/oracleasm/disks/OCRDISK2
ASM_DISK name : OCRDISK2
Physical disk device : /dev/sdc1
ASMLIB disk name : /dev/oracleasm/disks/OCRDISK3
ASM_DISK name : OCRDISK3
Physical disk device : /dev/sdd1

We can also use the kfed utility, to read the disk headers and get the disk name that is being used in the ASM disk group. Check these information – kfdhdb.dskname, kfdhdb.grpname and kfdhdb.fgname

[root@racdr1 ~]# echo $ORACLE_HOME
/u01/app/12.1.0/grid
[root@racdr1 ~]# $ORACLE_HOME/bin/kfed read /dev/sdl1
....
kfdhdb.dskname:                   DISK7 ; 0x028: length=5
kfdhdb.grpname:                  DATA01 ; 0x048: length=6
kfdhdb.fgname:              CONTROLLER2 ; 0x068: length=11
....
[root@racdr1 ~]# echo $ORACLE_HOME
/u01/app/12.1.0/grid
[root@racdr1 ~]# $ORACLE_HOME/bin/kfed read /dev/sdl1
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          2 ; 0x003: 0x02
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483649 ; 0x008: disk=1
kfbh.check:                  1922227825 ; 0x00c: 0x7292de71
kfbh.fcn.base:                       70 ; 0x010: 0x00000046
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:ORCLDISKDATADISK7 ; 0x000: length=17
kfdhdb.driver.reserved[0]:   1096040772 ; 0x008: 0x41544144
kfdhdb.driver.reserved[1]:   1263749444 ; 0x00c: 0x4b534944
kfdhdb.driver.reserved[2]:           55 ; 0x010: 0x00000037
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                202375168 ; 0x020: 0x0c100000
kfdhdb.dsknum:                        1 ; 0x024: 0x0001
kfdhdb.grptyp:                        2 ; 0x026: KFDGTP_NORMAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:                   DISK7 ; 0x028: length=5
kfdhdb.grpname:                  DATA01 ; 0x048: length=6
kfdhdb.fgname:              CONTROLLER2 ; 0x068: length=11

Important Links: