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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[oracle@racdr1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@racdr1 ~]$ sqlplus / as sysasm
[oracle@racdr1 ~]$ . oraenv ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@racdr1 ~]$ sqlplus / as sysasm
[oracle@racdr1 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@racdr1 ~]$ sqlplus / as sysasm
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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.
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.
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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
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
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SQL> Create Tablespace tbs_test datafile '+DATA01' size 50M;
Tablespace created.
SQL> Create Tablespace tbs_test datafile '+DATA01' size 50M; Tablespace created.
SQL> Create Tablespace tbs_test datafile '+DATA01' size 50M;

Tablespace created.

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
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
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.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[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.
[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.
[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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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';
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';
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SQL> ALTER DISKGROUP DATA01 SET ATTRIBUTE 'content.type' = 'data';
Diskgroup altered.
SQL> ALTER DISKGROUP DATA01 SET ATTRIBUTE 'content.type' = 'data'; Diskgroup altered.
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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SQL> ALTER DISKGROUP DATA01 ADD DISK 'ORCL:DATADISK6' REBALANCE POWER 5;
Diskgroup altered.
SQL> ALTER DISKGROUP DATA01 ADD DISK 'ORCL:DATADISK6' REBALANCE POWER 5; Diskgroup altered.
SQL> ALTER DISKGROUP DATA01 ADD DISK 'ORCL:DATADISK6' REBALANCE POWER 5;

Diskgroup altered.

3.2. Drop Disk from a Diskgroup:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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"
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"
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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SQL> ALTER DISKGROUP DATA01 DROP DISK 'DATADISK6' REBALANCE POWER 5;
Diskgroup altered.
SQL> ALTER DISKGROUP DATA01 DROP DISK 'DATADISK6' REBALANCE POWER 5; Diskgroup altered.
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.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SQL> ALTER DISKGROUP DATA ADD DISK 'ORCL:DATADISK6' DROP DISK 'DATADISK7' REBALANCE POWER 11;
Diskgroup altered.
SQL> ALTER DISKGROUP DATA ADD DISK 'ORCL:DATADISK6' DROP DISK 'DATADISK7' REBALANCE POWER 11; Diskgroup altered.
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
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
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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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 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 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.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SQL> ALTER DISKGROUP DATA01 SET ATTRIBUTE 'failgroup_repair_time'='1H';
Diskgroup altered.
SQL> ALTER DISKGROUP DATA01 SET ATTRIBUTE 'failgroup_repair_time'='1H'; Diskgroup altered.
SQL> ALTER DISKGROUP DATA01 SET ATTRIBUTE 'failgroup_repair_time'='1H';

Diskgroup altered.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
ALTER DISKGROUP DATA01 REPLACE DISK DISK6 WITH 'ORCL:DATADISK5' POWER 3;
ALTER DISKGROUP DATA01 REPLACE DISK DISK6 WITH 'ORCL:DATADISK5' POWER 3;
ALTER DISKGROUP DATA01 REPLACE DISK DISK6 WITH 'ORCL:DATADISK5' POWER 3;

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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[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
[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
[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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
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
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.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[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 ~]# 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 ~]# 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
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[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
[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
[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

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[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 .... 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
....
kfdhdb.dskname:                   DISK7 ; 0x028: length=5
kfdhdb.grpname:                  DATA01 ; 0x048: length=6
kfdhdb.fgname:              CONTROLLER2 ; 0x068: length=11
....
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[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
[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
[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: