In case of “normal” situation (I mean no Imbalanced Disks) you should be able to easily know how much space is available into the ASM Diskgroup.
Harald van Breederode already explained it in detail into this blog post.
But now suppose the Diskgroup has imbalanced Disks: It can occurs for several reasons:
- A rebalance operation has been aborted/halted.
- A rebalance operation is waiting.
- Disks are not of the same size.
Let’s simulate an Imbalanced situation and compute the usable free space:
First I created a “balanced” diskgroup with normal redundancy.
SQL> !kfod
--------------------------------------------------------------------------------
Disk Size Path User Group
================================================================================
1: 225280 Mb /dev/san/JMO7C43D06 oracle dba
2: 56320 Mb /dev/san/JMO7C52D06 oracle dba
3: 56320 Mb /dev/san/JMO7C61D06 oracle dba
4: 225280 Mb /dev/san/WIN7C43D06 oracle dba
5: 56320 Mb /dev/san/WIN7C52D06 oracle dba
6: 56320 Mb /dev/san/WIN7C61D06 oracle dba
--------------------------------------------------------------------------------
SQL> create diskgroup BDT normal redundancy failgroup F1 disk '/dev/san/JMO7C52D06' failgroup F2 disk '/dev/san/WIN7C52D06';
Diskgroup created.
Let’s check its free space with asmcmd and with asm_free_usable_imbalance.sql (I’ll share the code later into this post) built to report the free/usable space of Imbalanced Diskgroup:
SQL> !asmcmd lsdg BDT
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 112640 112538 0 56269 0 N BDT/
SQL> @asm_free_usable_imbalance.sql
NAME FREE_MB
------------------------------ ----------
BDT 56269
Well, we can see that from asmcmd (112538 / 2) 56269 Mb is free/usable and that the asm_free_usable_imbalance.sql reports the same value.
So that the asm_free_usable_imbalance.sql script works at least with “Balanced” diskgroup ;-).
Now let’s produce an Imbalanced situation that way:
- Add datafiles into the Diskgroup.
- Add Disks into the failgroups without trigerring a rebalance operation (asm_power_limit = 0).
SQL> create tablespace BDT datafile '+BDT' size 30g;
Tablespace created.
SQL> alter tablespace BDT add datafile '+BDT' size 10g;
Tablespace altered.
SQL> alter tablespace BDT add datafile '+BDT' size 20g;
alter tablespace BDT add datafile '+BDT' size 20g
*
ERROR at line 1:
ORA-01119: error in creating database file '+BDT'
ORA-17502: ksfdcre:4 Failed to create file +BDT
ORA-15041: diskgroup "BDT" space exhausted
So, we can’t add a 20 GB datafile anymore (sounds obvious as the free space was about 55 GB and we already added 40 GB into the diskgroup).
Now add disks of different size without trigerring a rebalance operation:
SQL> !kfod
--------------------------------------------------------------------------------
Disk Size Path User Group
================================================================================
1: 225280 Mb /dev/san/JMO7C43D06 oracle dba
2: 56320 Mb /dev/san/JMO7C61D06 oracle dba
3: 225280 Mb /dev/san/WIN7C43D06 oracle dba
4: 56320 Mb /dev/san/WIN7C61D06 oracle dba
--------------------------------------------------------------------------------
SQL> alter diskgroup BDT add failgroup F1 disk '/dev/san/JMO7C43D06','/dev/san/JMO7C61D06' failgroup F2 disk '/dev/san/WIN7C43D06','/dev/san/WIN7C61D06';
Diskgroup altered.
Verify that the rebalance is waiting:
SQL> select * from v$asm_operation where group_number = (select GROUP_NUMBER from v$asm_diskgroup where NAME='BDT');
GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
8 REBAL WAIT 0
and the disks usage:
SQL> select failgroup,total_mb,FREE_MB from v$asm_disk where group_number = (select GROUP_NUMBER from v$asm_diskgroup where NAME='BDT');
FAILGROUP TOTAL_MB FREE_MB
------------------------------ ---------- ----------
F1 56320 56318
F1 225280 225277
F1 56320 15304
F2 225280 225277
F2 56320 15304
F2 56320 56318
Here we are: How much usable/free space is “really” available into this “Imbalanced” diskgroup ?
Let’s see what asmcmd and asm_free_usable_imbalance.sql report:
SQL> !asmcmd lsdg BDT
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL Y 512 4096 1048576 675840 593798 225280 184259 0 N BDT/
SQL> @asm_free_usable_imbalance.sql
NAME FREE_MB
------------------------------ ----------
BDT 91824
Now we have to remember that for primary extents, ASM will allocate new extents in such way as to distribute each file equally and evenly across all disks and to fill all disks evenly. Thus every disk is maintained at the same percentage full, regardless of the size of the disk.
So it will write 4 times more primary extents into the 225280 MB disks than into the 56320 MB disks (It will not necessary be the case for mirrored extents as you’ll see later on into this post).
So, asmcmd reports 593798/2 MB of free space: This space is “just” the sum of the free space of the disks that belongs to the diskgroup. So this is not fully “usable” due to the Imbalanced Diskgroup and the rule explained above.
My utility reports 91824 MB of free/usable space (less than 90 GB).
Let’s verify how much space is “usable”:
SQL> alter tablespace BDT add datafile '+BDT' size 30g;
Tablespace altered.
SQL> alter tablespace BDT add datafile '+BDT' size 30g;
Tablespace altered.
SQL> alter tablespace BDT add datafile '+BDT' size 30g;
alter tablespace BDT add datafile '+BDT' size 30g
*
ERROR at line 1:
ORA-01119: error in creating database file '+BDT'
ORA-17502: ksfdcre:4 Failed to create file +BDT
ORA-15041: diskgroup "BDT" space exhausted
SQL> alter tablespace BDT add datafile '+BDT' size 29g;
Tablespace altered.
So, it was not possible to add 90 GB while it has been possible to add 89 GB: My utility reported the right “free/usable” value.
Let’s see the asm_free_usable_imbalance.sql:
SQL> !cat asm_free_usable_imbalance.sql
select /* EXTERNAL REDUNDANCY */
g.name,
sum(d.TOTAL_MB) * min(d.FREE_MB / d.total_mb) /
decode(g.type, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1) "USABLE_FREE_MB"
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number
and g.type = 'EXTERN'
group by g.name, g.type
union
select /* NON EXTERNAL REDUNDANCY WITH SYMMETRIC FG */
g.name,
sum(d.TOTAL_MB) * min(d.FREE_MB / d.total_mb) /
decode(g.type, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1) "USABLE_FREE_MB"
from v$asm_disk d, v$asm_diskgroup g
where d.group_number = g.group_number
and g.group_number not in /* KEEP SYMMETRIC*/
(select distinct (group_number)
from (select group_number,
failgroup,
TOTAL_MB,
count_dsk,
greatest(lag(count_dsk, 1, 0)
over(partition by TOTAL_MB,
group_number order by TOTAL_MB,
FAILGROUP),
lead(count_dsk, 1, 0)
over(partition by TOTAL_MB,
group_number order by TOTAL_MB,
FAILGROUP)) as max_lag_lead,
count(distinct(failgroup)) over(partition by group_number, TOTAL_MB) as nb_fg_per_size,
count_fg
from (select group_number,
failgroup,
TOTAL_MB,
count(*) over(partition by group_number, failgroup, TOTAL_MB) as count_dsk,
count(distinct(failgroup)) over(partition by group_number) as count_fg
from v$asm_disk))
where count_dsk <> max_lag_lead
or nb_fg_per_size <> count_fg)
and g.type <> 'EXTERNAL'
group by g.name, g.type
union
select /* NON EXTERNAL REDUNDANCY WITH NON SYMMETRIC FG
AND DOES EXIST AT LEAST ONE DISK WITH PARTNERS OF DIFFERENT SIZE*/
name,
min(free) / decode(type, 'EXTERN', 1, 'NORMAL', 2, 'HIGH', 3, 1) "USABLE_FREE_MB"
from (select name,
disk_number,
free_mb / (factor / sum(factor) over(partition by name)) as free,
type
from (select name,
disk_number,
avg(free_mb) as free_mb,
avg(total_mb) as total_mb,
sum(factor_disk + factor_partner) as factor,
type
from (SELECT g.name,
g.type,
d.group_number as group_number,
d.disk_number disk_number,
d.total_mb as total_mb,
d.free_mb as free_mb,
p.number_kfdpartner "Partner disk#",
f.factor as factor_disk,
fp.factor as factor_partner
FROM x$kfdpartner p,
v$asm_disk d,
v$asm_diskgroup g,
(select disk_number,
group_number,
TOTAL_MB / min(total_mb) over(partition by group_number) as factor
from v$asm_disk
where state = 'NORMAL'
and mount_status = 'CACHED') f,
(select disk_number,
group_number,
TOTAL_MB / min(total_mb) over(partition by group_number) as factor
from v$asm_disk
where state = 'NORMAL'
and mount_status = 'CACHED') fp
WHERE p.disk = d.disk_number
and p.grp = d.group_number
and f.disk_number = d.disk_number
and f.group_number = d.group_number
and fp.disk_number = p.number_kfdpartner
and fp.group_number = p.grp
and d.group_number = g.group_number
and g.type <> 'EXTERN'
and g.group_number in /* KEEP NON SYMMETRIC */
(select distinct (group_number)
from (select group_number,
failgroup,
TOTAL_MB,
count_dsk,
greatest(lag(count_dsk, 1, 0)
over(partition by
TOTAL_MB,
group_number order by
TOTAL_MB,
FAILGROUP),
lead(count_dsk, 1, 0)
over(partition by
TOTAL_MB,
group_number order by
TOTAL_MB,
FAILGROUP)) as max_lag_lead,
count(distinct(failgroup)) over(partition by group_number, TOTAL_MB) as nb_fg_per_size,
count_fg
from (select group_number,
failgroup,
TOTAL_MB,
count(*) over(partition by group_number, failgroup, TOTAL_MB) as count_dsk,
count(distinct(failgroup)) over(partition by group_number) as count_fg
from v$asm_disk))
where count_dsk <> max_lag_lead
or nb_fg_per_size <> count_fg)
and d.group_number not in /* KEEP DG THAT DOES NOT CONTAIN AT LEAST ONE DISK HAVING PARTNERS OF DIFFERENT SIZE*/
(select distinct (group_number)
from (select d.group_number as group_number,
d.disk_number disk_number,
p.number_kfdpartner "Partner disk#",
f.factor as factor_disk,
fp.factor as factor_partner,
greatest(lag(fp.factor, 1, 0)
over(partition by
d.group_number,
d.disk_number order by
d.group_number,
d.disk_number),
lead(fp.factor, 1, 0)
over(partition by
d.group_number,
d.disk_number order by
d.group_number,
d.disk_number)) as max_lag_lead,
count(p.number_kfdpartner) over(partition by d.group_number, d.disk_number) as nb_partner
FROM x$kfdpartner p,
v$asm_disk d,
v$asm_diskgroup g,
(select disk_number,
group_number,
TOTAL_MB / min(total_mb) over(partition by group_number) as factor
from v$asm_disk
where state = 'NORMAL'
and mount_status = 'CACHED') f,
(select disk_number,
group_number,
TOTAL_MB / min(total_mb) over(partition by group_number) as factor
from v$asm_disk
where state = 'NORMAL'
and mount_status = 'CACHED') fp
WHERE p.disk = d.disk_number
and p.grp = d.group_number
and f.disk_number = d.disk_number
and f.group_number = d.group_number
and fp.disk_number =
p.number_kfdpartner
and fp.group_number = p.grp
and d.group_number = g.group_number
and g.type <> 'EXTERN')
where factor_partner <> max_lag_lead
and nb_partner > 1))
group by name, disk_number, type))
group by name, type;
Don’t be afraid ;-): The SQL is composed of 3 cases:
- Diskgroup with external redundancy: In that case the calculation is based on the smallest free space for a disk within the diskgroup.
- Diskgoup with “non external” redundancy and symmetric failgroups (Same number of disks grouped by size across failgroups): In that case the calculation is based on the smallest free space for a disk within the diskgroup.
- Diskgoup with “non external” redundancy and non symmetric failgroups: In that case the calculation is based on a weighting factor depending of the size of the disks and their partners.
Warning: The SQL does not yet cover the case (it will not return any result) of “non external” redundancy with non symmetric failgroups and if it exists at least one disk that has partners of different sizes.
This is due to the following remark.
Remark:
I said that the ASM allocator ensures balanced disk utilization for primary extents and not necessary for mirrored exents.
Proof:
Create an external redundancy diskgroup with 4 failgroups of disks not of the same sizes.
SQL> !kfod
--------------------------------------------------------------------------------
Disk Size Path User Group
================================================================================
1: 225280 Mb /dev/san/JMO7C43D06 oracle dba
2: 56320 Mb /dev/san/JMO7C52D06 oracle dba
3: 56320 Mb /dev/san/JMO7C61D06 oracle dba
4: 225280 Mb /dev/san/WIN7C43D06 oracle dba
5: 56320 Mb /dev/san/WIN7C52D06 oracle dba
6: 56320 Mb /dev/san/WIN7C61D06 oracle dba
--------------------------------------------------------------------------------
SQL> create diskgroup BDT normal redundancy failgroup F1 disk '/dev/san/JMO7C43D06' failgroup F2 disk '/dev/san/JMO7C52D06' failgroup F3 disk '/dev/san/JMO7C61D06' failgroup F4 disk '/dev/san/WIN7C43D06';
Diskgroup created.
Now create a 30 GB tablespace into the database:
SQL> create tablespace BDT datafile '+BDT' size 30g;
Tablespace created.
And check the primary and mirrored extents distribution within the disks into ASM:
SQL> select d.group_number,d.disk_number,d.total_mb,d.free_mb,d.failgroup,decode(LXN_KFFXP,0,'P',1,'M','MM') ,count(*)
from X$KFFXP, v$asm_disk d
where
GROUP_KFFXP = (select GROUP_NUMBER from v$asm_diskgroup where NAME='BDT')
and d.group_number=GROUP_KFFXP
and d.disk_number=DISK_KFFXP
group by d.group_number,d.disk_number,d.total_mb,d.free_mb,d.failgroup,LXN_KFFXP
order by d.failgroup,d.total_mb;
GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB FAILGROUP DE COUNT(*)
------------ ----------- ---------- ---------- ------------------------------ -- ----------
8 0 225280 202020 F1 P 12310
8 0 225280 202020 F1 M 10945
8 0 225280 202020 F1 MM 2
8 1 56320 48778 F2 P 3076
8 1 56320 48778 F2 M 4442
8 1 56320 48778 F2 MM 22
8 2 56320 48779 F3 P 3077
8 2 56320 48779 F3 M 4443
8 2 56320 48779 F3 MM 19
8 3 225280 202018 F4 P 12309
8 3 225280 202018 F4 M 10942
8 3 225280 202018 F4 MM 8
P stands for primary extents and M for mirrored “2 way” extents.
So as you can see ASM allocator writes 4 times more primary extents into the 225280 MB disks than into the 56320 MB disks (12310/3076) while this is not the case for the mirrored extents (about 10945/4442= 2.5 times).
This is the main reason why a case is still not covered into asm_free_space_imbalanced.sql. If you have any clues about the mirrored extents distribution please let me know ;-)
Conclusion:
- The sql provides a way to calculate the free space in case of Imbalanced diskgroup.
- The calculation is based on the smallest free space for a disk within the diskgroup and a weighting factor that depends of the disks size and their partners.
- The sql provides also right results for Balanced diskgroup ;-)
- The sql does not yet cover the case of ”non external” redundancy and if it exists at least one disk that has partners of different sizes.