source :
http://www.oraclenext.com/2016/10/calculating-asm-diskgroup-usable-size.html
lsdg command shows that TOTAL_MB and USABLE_FILE_MB is 24G; as full disk space is available and will be utilized to store the data - no mirroring would be done. Alternatively you can query same information from v$asm_sidkgroup as you can see below.
USABLE_FILE_MB has now been reduced from 4007 MB to 5 MB only, after consuming further 4000 MB. FREE_MB is now 4104 MB because 4000 MB addition in the tablespace actually consumed 8000 MB because of 2 way mirroring.
http://www.oraclenext.com/2016/10/calculating-asm-diskgroup-usable-size.html
Calculating ASM diskgroup Usable Size
Sometimes sizing information of an ASM diskgroup returned by “lsdg” command from “asmcmd” prompt (similar information is also available under v$asm_diskgroup view) leaves DBAs perplexed. Sizing information and usable space for diskgroups with EXTERNAL REDUNDANCY is quite easy to understand, but when it comes to NORMAL or HIGH redundancy diskgroups, it is not very straightforward. Here I would explain ASM diskgroup space calculation in detail to make it easier to understand. Before I explain how to calculate usable space in an ASM diskgroup, some useful information to note is as follows
- Diskgroups with EXTERNAL REDUNDANCY don’t mirror any data at all
- Diskgroups with NORMAL REDUNDANCY keep 2 copies of each extant (ASM extent, which is made up of one or more ASM Allocation Units) on 2 different ASM failure groups in that diskgroup.
- Diskgroups with HIGH REDUNDANCY keep 3 copies of each extant (ASM extent, which is made up of one or more ASM Allocation Units) on 3 different ASM failure groups in that diskgroup.
- By default each ASM disk is in its own FAILURE GROUP, unless disks are put in a specific failure group during diskgroup creation.
- Disks which are prone to become unavailable together; are usually put in a single failure group. For example, if we have 2 disk controllers with each controller having 10 disks in it, all disks from each controller should be in a single failure group because in case of a controller failure, all 10 disks of that controller would become unavailable. In ASM mirroring, ASM would always keep copies of an extent on the disks in different failure groups; thus keeping a copy of data always available in case a controller failure makes all 10 disks unavailable in the failure group.
- If we use lsdg command, or query sizing details of a diskgroup from v$asm_diskgroup we find following columns with sizing details
FREE_MB: Current free space available in the diskgroup. In case of external redundancy, full space listed under this column is available for the usage. In case of normal redundancy, divide this value by 2 to get actual amount of space that can be utilized since 2 copies of each extent have to be stored. In case of high redundancy, this value should be divided by 3 because total 3 copies of data are to be stored in this free space.
REQUIRED_MIRROR_FREE_MB: Value under this column shows the amount of space required to re-mirror or “restore redundancy” of the data in case of a worst failure that can be tolerated by this diskgroup. In case of normal redundancy, a diskgroup can tolerate loss of one failure group and in case of high redundancy; diskgroup can tolerate loss of 2 failure groups. After the loss, this much space would be required to re-mirror (or restore redundancy of) the diskgroup. After a failure, rebalance will happen and redundancy will be restored, and then value under this column would be updated to a new value to show current requirement to restore redundancy in case of a failure.
USABLE_FILE_MB: Value under this column shows the amount of space that can be used/allocated from the diskgroup. This value can be negative (as you will see in bellow examples) which would mean that there is not enough space available in the diskgroup to re-mirror (restore redundancy) after the worst failure this diskgroup can tolerate.
Here the point to note is that value under REQUIRED_MIRROR_FREE_MB does not mean that this amount of space has been reserved and cannot be used, but this value only tells us that if we want to restore redundancy (2 way redundancy for normal and 3 way for high redundancy) after a failure, we should keep this much space available (never use it) – and negative value under USABLE_FILE_MB actually tells us that we have started utilizing space which was reserved for restoring redundancy.
Formula to calculate USABLE_FILE_MB
For External redundancy
USABLE_FILE_MB = FREE_MB
For External redundancy
USABLE_FILE_MB = FREE_MB
For Normal Redundancy
USABLE_FILE_MB = (FREE_MB – REQUIRED_MIRROR_FREE_MB) / 2
USABLE_FILE_MB = (FREE_MB – REQUIRED_MIRROR_FREE_MB) / 2
For High Redundancy
USABLE_FILE_MB = (FREE_MB – REQUIRED_MIRROR_FREE_MB) / 3
USABLE_FILE_MB = (FREE_MB – REQUIRED_MIRROR_FREE_MB) / 3
Following examples would explain how disk space is allocated and used in an ASM diskgroup. I have total 6 disks with 4G size each to work with during these examples.
External Redundancy
Let’s create a disk group with EXTERNAL REDUNDANCY.
Let’s create a disk group with EXTERNAL REDUNDANCY.
CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK 'ORCL:DISK1','ORCL:DISK2','ORCL:DISK3', 'ORCL:DISK4', 'ORCL:DISK5', 'ORCL:DISK6';
|
lsdg command shows that TOTAL_MB and USABLE_FILE_MB is 24G; as full disk space is available and will be utilized to store the data - no mirroring would be done. Alternatively you can query same information from v$asm_sidkgroup as you can see below.
ASMCMD> lsdg data
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 24564 24504 0 24504 0 N DATA/
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where group_number=2;
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------------------------------- ---------------------------
DATA 24564 24505 0 24504
|
After creating a tablespace of 6g size, space usage of diskgroup can be seen.
SQL> create tablespace mytbs datafile '+data' size 6g;
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where group_number=2;
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------------------------------- ---------------------------
DATA 24564 18357 0 18357
|
Normal Redundancy
Now we create a diskgroup using these 6 disks, with Normal Redundancy.
Now we create a diskgroup using these 6 disks, with Normal Redundancy.
SQL> CREATE DISKGROUP data NORMAL REDUNDANCY DISK 'ORCL:DISK1','ORCL:DISK2','ORCL:DISK3', 'ORCL:DISK4','ORCL:DISK5','ORCL:DISK6';
Diskgroup created.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------------------------------- ---------------------------
DATA 24564 24405 4094 10155
|
REQUIRED_MIRROR_FREE_MB is 4094 which is equal to the size of biggest failure group (each disk is in its own failure group here) so that in case of one disk/failuregroup failure, the mirroring can be re-done using this free space.
Now we create a tablespace with 6G size in +DATA diskgroup and check size.
SQL> create tablespace mytbs datafile '+data' size 6g;
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------------------------------- ---------------------------
DATA 24564 12109 4094 4007
|
As you see that FREE_MB has decreased from 24405 MB to 12109 (almost half). This shows that although 6G space was used by newly added tablespace, but actually 12G was consumed because of 2 way mirroring. USABLE_FILE_MB space reduced from 10155 MB to 4007 MB i.e. (12109 – 4094) /2 => 4007.
Let’s add another 4000 MB in mytbs tablespace
Let’s add another 4000 MB in mytbs tablespace
SQL> alter tablespace mytbs add datafile '+data' size 4000m;
Tablespace altered.
SQL> select sum(bytes/1024/1024) from dba_data_files where tablespace_name='MYTBS';
SUM(BYTES/1024/1024)
--------------------
10144
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------------------------------- ---------------------------
DATA 24564 4104 4094 5
|
USABLE_FILE_MB has now been reduced from 4007 MB to 5 MB only, after consuming further 4000 MB. FREE_MB is now 4104 MB because 4000 MB addition in the tablespace actually consumed 8000 MB because of 2 way mirroring.
High Redundancy
Now let’s create a high redundancy diskgroup and see how space usage works.
SQL> drop tablespace mytbs;
Tablespace dropped.
ASM Instance
SQL> drop diskgroup data;
Diskgroup dropped.
SQL> CREATE DISKGROUP data HIGH REDUNDANCY DISK 'ORCL:DISK1','ORCL:DISK2','ORCL:DISK3', 'ORCL:DISK4', 'ORCL:DISK5','ORCL:DISK6';
Diskgroup created.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------------------------------- ---------------------------
DATA 24564 24405 8188 5405
|
REQUIRED_MIRROR_FREE_MB is 8188 which is equal to the size of 2 biggest failure groups (each disk is in its own failure group here) so that in case of 2 disks/failuregroups failure, the mirroring can be re-done using this free space and 3 copies of data exist after re-mirroring.
Now we create a tablespace with 6G size in +DATA diskgroup and check the size.
Now we create a tablespace with 6G size in +DATA diskgroup and check the size.
SQL> create tablespace mytbs datafile '+data' size 6g;
Tablespace created.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------------------------------- ---------------------------
DATA 24564 5964 8188 -741
|
After adding 6G (6144 MB) tablespace, FREE_MB has reduced to 5964 i.e. 24405 – (6144 x 3). USABLE_FILE_MB is now in negative which means that there is not enough space available in the diskgroup to restore 3-way redundancy if 2 disks/gailuregroups are gone. At this point if one disk/failuregroup fails, ASM can still restore redundancy because FREE_MB value is more than size of a single disk/failuregroup which is 4G.
Now I am adding a new file the tablespace to increase size of tablespace from 6144 MM to 8124 MB
SQL> alter tablespace mytbs add datafile '+data' size 1980m;
Tablespace altered.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ---------------------------------------- ---------------------------
DATA 24564 18 8188 -2723
|
FREE_MB is only 18 MB and if I drop (in order to simulate a disk failure) a disk now, ASM would not be able to restore redundancy even after a disk failure/drop as you can see in the following.
SQL> alter diskgroup data drop disk disk1;
alter diskgroup data drop disk disk1
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15250: insufficient diskgroup space for rebalance completion
|
Now If I drop a newly added datafile and then try to drop a disk, I would be able to do this because ASM would be able to restore redundancy because sufficient space is available under FREE_MB to perform re-mirroring
SQL> alter tablespace mytbs drop datafile '+DATA/ORCL/DATAFILE/mytbs.257.886505679';
Tablespace altered.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- -------------- ---------------- -----------------------
DATA 24564 5964 8188 -741
As you see, FREE_MB is now 5964 M which is sufficient to re-mirror the 4G (maximum) data from a disk if a disk is dropped.
SQL> alter diskgroup data drop disk disk1;
Diskgroup altered.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- --------------- --------------------------------------------
DATA 20470 1870 8188 -2106
|
Now we add disk back again and then reduce tablespace size to 5G. by doing this, FREE_MB would be 9036 and this would mean that now ASM can restore a full 3 way redundancy even if we drop di sisks/failuregroups.
SQL> alter diskgroup data add disk 'ORCL:DISK1';
Diskgroup altered.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- --------------- --------------------------------------------
DATA 20470 5964 8188 -741
Reduce the file size to 5G and see if we can now drop 2 disks. As long as FREE_MB more than REQUIRED_MIRROR_FREE_MB, we can now drop 2 diks
SQL> alter database datafile '+DATA/ORCL/DATAFILE/mytbs.256.886519815' resize 5g;
Database altered.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- --------------- --------------------------------------------
DATA 20470 9036 8188 282
SQL> alter diskgroup data drop disk disk1;
Diskgroup altered.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- -------------- ---------------------------------------------------
DATA 20470 4942 8188 -1082
SQL> alter diskgroup data drop disk disk2;
Diskgroup altered.
SQL> select name, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB from v$asm_diskgroup where NAME='DATA';
NAME TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ---------- ---------- ----------------------- ------------------------------------------- --------------------------
DATA 16376 848 8188 -2446
|