Tuesday, October 4, 2016

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
TOTAL_MB: Total size of a diskgroup in MB (adding up space of all disks in the diskgroup)


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 Normal Redundancy
USABLE_FILE_MB = (FREE_MB – REQUIRED_MIRROR_FREE_MB) / 2
For High Redundancy
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.
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.

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
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.
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




No comments: