source :
https://community.toadworld.com/platforms/oracle/b/weblog/archive/2015/05/11/interpreting-free-space-in-asm-diskgroup
http://www.oraclenext.com/2016/10/calculating-asm-diskgroup-usable-size.html
https://community.toadworld.com/platforms/oracle/b/weblog/archive/2015/05/11/interpreting-free-space-in-asm-diskgroup
http://www.oraclenext.com/2016/10/calculating-asm-diskgroup-usable-size.html
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_MBfrom 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_MBto 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
No comments:
Post a Comment