LATEST TOPICS

Interpreting Free Space in ASM Diskgroup

The Perception:

I had the perception that the FREE_MB returned from querying the V$ASM_DISKGROUP is the actual amount of free space available under a particular ASM diskgroup.

Till date, I was using the following query to get space utilization for ASM diskgroups.

SQL>  select name,total_mb,free_mb, (free_mb/total_mb)*100 "%Free" from v$asm_diskgroup;

NAME                             TOTAL_MB    FREE_MB      %Free
------------------------------ ---------- ---------- ----------
DATA01                              10228      10120 98.9440751
DATA02                              15342      15180 98.9440751
FRA                                 10236       9752 95.2715905
DATA                                30708      27086  88.205028

However, I was completely wrong in my calculation about the free space in ASM diskgroups. For the matter of fact, I was also wrong in calculating the TOTAL_MB.

Reality Check:

The space allocation, utilization as well as the space availability in a ASM diskgroup is completely dependent on the type of redundancy defined for a ASM diskgroup.

We are all aware of the fact that, we can define 3 (three) type of disk redundancy while creating a ASM diskgroup as listed below.


1. Normal Redundancy
2. High Redundancy
3. External Redundancy

Normal Redundancy: In Normal redundancy, the disks are two way mirrored, therefore we need at least two disks of same size to create a diskgroup with normal redundancy.

High Redundancy: In High redundancy, the disks are three way mirrored, therefore we need at least three disks of same size to create a diskgroup with high redundancy.

External Redundancy: In External redundancy, the disks are not mirrored by ASM. In this case, we may want the disk mirroring to be maintained external to ASM by means of storage array or we may decide not to mirror the disks at all.

Let me elaborate the space allocation, utilization and space availability for all of these three type of diskgroups with an example.

Normal Redundancy Example:

Lets say, we want to create a ASM diskgroup with normal redundancy. Since, the disks are two way mirrored; we need at least two disks of same size to create the diskgroup.

Here I have created a normal redundancy diskgroup named ‘DATA01’ with two disk of size 5 GB each.

SQL> select dg.group_number,dg.name,dg.type Redundancy,dk.OS_MB,dk.name Disk_Name
  2  from v$asm_diskgroup dg, v$asm_disk dk
  3  where dg.group_number=dk.group_number
  4  and dg.name='DATA01';

GROUP_NUMBER NAME            REDUNDANCY           OS_MB DISK_NAME
------------ --------------- --------------- ---------- ------------------------------
           3 DATA01          NORMAL                5114 AFD_DATA04
           3 DATA01          NORMAL                5114 AFD_DATA05

Now, as per my earlier perception, if I use the query that I was using all the time, I get the following utilization report.

SQL> select name,total_mb,free_mb,TYPE, (free_mb/total_mb)*100 "%Free" from v$asm_diskgroup where name='DATA01';

NAME              TOTAL_MB    FREE_MB TYPE        %Free
--------------- ---------- ---------- ------ ----------
DATA01               10228      10120 NORMAL 98.9440751

As per the query, we have total 10 GB allocated to the diskgroup and ~ 10 GB is free for utilization. However, this is not the correct calculation. We know, here the disks are two way mirrored. Therefore, whatever space is allocated, only half of that can be utilized, since the other half would be used for mirroring.

This means, out of the 10 GB (5 GB + 5 GB) space, only 5 GB can be used to store data and the remaining 5 GB would be used for mirroring.

High Redundancy Example:

Lets say, we want to create a ASM diskgroup with high redundancy. Since, the disks are three way mirrored; we need at least three disks of same size to create the diskgroup.

Here I have created a high redundancy diskgroup named ‘DATA02’ with three disk of size 5 GB each.

SQL> select dg.group_number,dg.name,dg.type Redundancy,dk.OS_MB,dk.name Disk_Name
  2  from v$asm_diskgroup dg, v$asm_disk dk
  3  where dg.group_number=dk.group_number
  4  and dg.name='DATA02';

GROUP_NUMBER NAME            REDUNDANCY           OS_MB DISK_NAME
------------ --------------- --------------- ---------- ------------------------------
           4 DATA02          HIGH                  5114 AFD_DATA06
           4 DATA02          HIGH                  5114 AFD_DATA07
           4 DATA02          HIGH                  5114 AFD_DATA08

Now, as per my earlier perception, if I use the query that I was using all the time, I get the following utilization report.

SQL> select name,total_mb,free_mb,TYPE, (free_mb/total_mb)*100 "%Free" from v$asm_diskgroup where name='DATA02';

NAME              TOTAL_MB    FREE_MB TYPE        %Free
--------------- ---------- ---------- ------ ----------
DATA02               15342      15180 HIGH   98.9440751

As per the query, we have total 15 GB allocated to the diskgroup and ~ 15 GB is free for utilization. However, this is not the correct calculation. We know, here the disks are three way mirrored. Therefore, whatever space is allocated, only 1/3 of that can be utilized, since the other 2/3 would be used for mirroring.

This means, out of the 15 GB (5 GB + 5 GB + 5 GB) space, only 5 GB can be used to store data and the remaining 10 GB would be used for mirroring.

External Redundancy Example:

Lets say, we want to create a ASM diskgroup with external redundancy. Since, the disks are not mirrored by ASM; we need just on disk to create the diskgroup.

Here I have created a external redundancy diskgroup named ‘DATA’ with a three disks of size 10 GB each.

SQL> select dg.group_number,dg.name,dg.type Redundancy,dk.OS_MB,dk.name Disk_Name
  2  from v$asm_diskgroup dg, v$asm_disk dk
  3  where dg.group_number=dk.group_number
  4  and dg.name='DATA';

GROUP_NUMBER NAME            REDUNDANCY           OS_MB DISK_NAME
------------ --------------- --------------- ---------- ------------------------------
           1 DATA            EXTERN               10236 AFD_DATA01
           1 DATA            EXTERN               10236 AFD_DATA02
           1 DATA            EXTERN               10236 AFD_DATA03

Now, as per my earlier perception, if I use the query that I was using all the time, I get the following utilization report.

SQL> select name,total_mb,free_mb,TYPE, (free_mb/total_mb)*100 "%Free" from v$asm_diskgroup where name='DATA';

NAME              TOTAL_MB    FREE_MB TYPE        %Free
--------------- ---------- ---------- ------ ----------
DATA                 30708      27086 EXTERN  88.205028

In the case of external redundancy, my perception is correct. Here, the query is reporting that we have 30 GB of space allocated and ~ 27 GB is available for utilization. This result is correct, as we know for external redundancy there is no data mirroring done by ASM.

Correcting the Perception:

As, we have observed that the TOTAL_MB/FREE_MB queried from V$ASM_DISKGROUP is only accurate in case of external redundancy, as there is no mirroring done by ASM. However, when ASM disk redundancy (Normal/High) is in place; the TOTAL_MB/FREE_MB reported by V$ASM_DISKGROUP could be misleading, unless we don’t know the way to interpret it.

What we can probably do is, first check the ASM diskgroup redundancy level and then interpret the TOTAL_MB/FREE_MB based on the redundancy.

For example, in our case of normal redundancy diskgroup ‘DATA01’ the TOTAL_MB is reported as 10228 MB and FREE_MB is reported as 10120 MB. Since, we know this is a normal redundancy diskgroup, we would divide the TOTAL_MB/FREE_MB by two to get the actual space allocation and availability as shown below.

SQL> select NAME,TYPE,TOTAL_MB/2 Total_MB, FREE_MB/2 Free_MB from v$asm_diskgroup where name='DATA01';

NAME            TYPE     TOTAL_MB    FREE_MB
--------------- ------ ---------- ----------
DATA01          NORMAL       5114       5060

Similarly for a high redundancy diskgroup, we would divide the TOTAL_MB/FREE_MB by three to get the actual space allocation and availability as shown below.

SQL> select NAME,TYPE,TOTAL_MB/3Total_MB, FREE_MB/3 Free_MB from v$asm_diskgroup where name='DATA02';

NAME            TYPE     TOTAL_MB    FREE_MB
--------------- ------ ---------- ----------
DATA02          HIGH         5114       5060

However, this is a lengthy approach; as we need to first identify the diskgroup redundancy level and then adjust our query to get the actual result. There must be some simple method available to identify the actual diskgroup space availability. Yes, it is there……

We can query the USABLE_FILE_MB from V$ASM_DISKGROUP to know the actual free space available within a diskgroup as show below.

SQL> select NAME,TYPE,TOTAL_MB,FREE_MB,USABLE_FILE_MB Actual_Free_MB from v$asm_diskgroup order by name;

NAME            TYPE     TOTAL_MB    FREE_MB ACTUAL_FREE_MB
--------------- ------ ---------- ---------- --------------
DATA            EXTERN      30708      27086          27086
DATA01          NORMAL      10228      10120           5060
DATA02          HIGH        15342      15180           5060

We can additionally, use the lsdg command from ASMCMD prompt and check for USABLE_FILE_MB to know the actual free space availability.

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     30708    27086                0           27086              0             N  DATA/
MOUNTED  NORMAL  N         512   4096  1048576     10228    10120                0            5060              0             N  DATA01/
MOUNTED  HIGH    N         512   4096  1048576     15342    15180                0            5060              0             N  DATA02/

However, both of these methods still report the TOTAL_MB irrespective of the diskgroup redundancy level. For this reason, I have come up with the following query that can be used to view the actual space allocation and availability depending on the diskgroup redundancy.

SQL> set serveroutput on
SQL> set feed off
SQL> declare
  2  TB constant varchar2(1):=CHR(9);
  3  dg_name varchar(20);
  4  t_mb number;
  5  f_mb number;
  6  red number;
  7  cursor asm_dg is
  8  select name,type from v$asm_diskgroup;
  9  dg_rec asm_dg%ROWTYPE;
 10  begin
 11  dbms_output.put_line('Diskgroup_Name'||TB||'Total_MB'||TB||'Free_MB');
 12  dbms_output.put_line('--------------'||TB||'--------'||TB||'--------');
 13  FOR dg_rec in asm_dg
 14  LOOP
 15  IF dg_rec.type = 'EXTERN' THEN
 16  red:=1;
 17  ELSIF dg_rec.type = 'NORMAL' THEN
 18  red:=2;
 19  ELSIF dg_rec.type = 'HIGH' THEN
 20  red:=3;
 21  END IF;
 22  select name,total_mb/red, free_mb/red into dg_name, t_mb, f_mb from v$asm_diskgroup where name=dg_rec.name;
 23  dbms_output.put_line(dg_name||TB||TB||t_mb||TB||TB||f_mb);
 24  END LOOP;
 25  END;
 26  /
Diskgroup_Name  Total_MB        Free_MB
--------------  --------        --------
DATA01          5114            5060
DATA02          5114            5060
DATA            30708           25036

Since I am not an experienced programmer, my query is bit lengthy. I am sure others might have a better query (approach) to accomplish this output.

Update: 03-06-2015

Here is a simpler version of the above query to find the actual utilization of ASM Diskgroups.

SQL> select
  2  name,decode(type,'NORMAL',2,'HIGH',3,'EXTERN',1) Redundancy,
  3  (total_mb/decode(type,'NORMAL',2,'HIGH',3,'EXTERN',1)) Total_MB,
  4  (free_mb/decode(type,'NORMAL',2,'HIGH',3,'EXTERN',1)) Free_MB,
  5  ((free_mb/decode(type,'NORMAL',2,'HIGH',3,'EXTERN',1))/(total_mb/decode(type,'NORMAL',2,'HIGH',3,'EXTERN',1)))*100 "%Free"
  6  from v$asm_diskgroup;


NAME                           REDUNDANCY   TOTAL_MB    FREE_MB      %Free
------------------------------ ---------- ---------- ---------- ----------
DATA_0001                               2  254005248  153938220 60.6043463
ORCL_DG                                 2    2422560    2421096 99.9395681
FLASH_001                               2   63517440   56999860 89.7389127


One Response
  1. Yasir
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed