The views expressed on this blog are my own and do not necessarily reflect the views of Oracle

September 27, 2014

REQUIRED_MIRROR_FREE_MB

The REQUIRED_MIRROR_FREE_MB and the USABLE_FILE_MB are two very interesting columns in the V$ASM_DISKGROUP[_STAT] view. Oracle Support gets many questions about the meaning of those columns and how the values are calculated. I wanted to write about this, but I realised that I could not do it better than Harald van Breederode, so I asked him for permission to simply reference his write up. He agreed, so please have a look at his excellent post Demystifying ASM REQUIRED_MIRROR_FREE_MB and USABLE_FILE_MB.

How much space can I use

Now that the REQUIRED_MIRROR_FREE_MB and the USABLE_FILE_MB have been explained, I would like to add that the ASM does not prevent you from using all available space - half of the total space for a normal redundancy disk group and one third of the total space for a high redundancy disk group. But if you do fill up your disk group to the brim, there will be no room to grow or add any files, and in the case of a disk failure, there will be no room to restore the redundancy for some data - until the failed disk is replaced and the rebalance completed.

Exadata with ASM version 11gR2

In Exadata with ASM version 11.2 the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest failgroup [1] in the disk group. To demonstrate, let's look at an Exadata system with ASM version 11.2.0.4.

As in most Exadata installations, I have three disk groups.

[grid@exadb01 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on [date]

SQL> select NAME, GROUP_NUMBER from v$asm_diskgroup_stat;

NAME      GROUP_NUMBER
--------- ------------
DATA                 1
DBFS_DG              2
RECO                 3

SQL>

For the purpose of this example, we will look at the disk group DBFS_DG. Normally there would be 10 disks per failgroup for disk group DBFS_DG. I have dropped few disks to demonstrate that the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest failgroup.

SQL> select FAILGROUP, count(NAME) "Disks", sum(TOTAL_MB) "MB"
from v$asm_disk_stat
where GROUP_NUMBER=2
group by FAILGROUP
order by 3;

FAILGROUP       Disks         MB
---------- ---------- ----------
EXACELL04           7     180096
EXACELL01           8     205824
EXACELL02           9     231552
EXACELL03          10     257280

SQL>

Note that the total space in the largest failgroup is 257280 MB.

Finally, we see that the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest failgroup:

SQL> select NAME, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB
from v$asm_diskgroup_stat
where GROUP_NUMBER=2;

NAME         TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------- ---------- ---------- ----------------------- --------------
DBFS_DG        874752     801420                  257280         272070

SQL>

The ASM calculates the USABLE_FILE_MB using the following formula:

USABLE_FILE_MB = (FREE_MB - REQUIRED_MIRROR_FREE_MB) / 2

Which gives 272070 MB.

[1] In Exadata, all failgroups are typically of the same size

Exadata with ASM version 12cR1

In Exadata with ASM version 12cR1, the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest disk [2] in the disk group.

Here is an example from an Exadata system with ASM version 12.1.0.2.0.

[grid@exadb03 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on [date]

SQL> select NAME, GROUP_NUMBER from v$asm_diskgroup_stat;

NAME     GROUP_NUMBER
-------- ------------
DATA                1
DBFS_DG             2
RECO                3

SQL>

Again, I have the failgroups of different sizes in the disk group DBFS_DG:

SQL> select FAILGROUP, count(NAME) "Disks", sum(TOTAL_MB) "MB"
from v$asm_disk_stat
where GROUP_NUMBER=2
group by FAILGROUP
order by 3;

FAILGROUP       Disks         MB
---------- ---------- ----------
EXACELL05           8     238592
EXACELL07           9     268416
EXACELL06          10     298240

SQL>

The total space in the largest failgroup is 298240 MB, but this time the REQUIRED_MIRROR_FREE_MB is reported as 29824 MB:

SQL> select NAME, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB, USABLE_FILE_MB
from v$asm_diskgroup_stat
where GROUP_NUMBER=2;  2    3

NAME         TOTAL_MB    FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
---------- ---------- ---------- ----------------------- --------------
DBFS_DG        805248     781764                   29824         375970

SQL>

As we can see, that is the size of the largest disk, in the diskgroup:

SQL> select max(TOTAL_MB) from v$asm_disk_stat where GROUP_NUMBER=2;

MAX(TOTAL_MB)
-------------
        29824

SQL>

The USABLE_FILE_MB was calculated using the same formula:

USABLE_FILE_MB = (FREE_MB - REQUIRED_MIRROR_FREE_MB) / 2

Which gives 375970 MB.

[2] In Exadata, all disks are typically of the same size

Conclusion

The REQUIRED_MIRROR_FREE_MB and the USABLE_FILE_MB are intended to assist the DBAs and storage administrators with planning the disk group capacity and redundancy. The values are reported, but not enforced by the ASM.

In Exadata with ASM version 12cR1, the REQUIRED_MIRROR_FREE_MB is reported as the size of the largest disk in the disk group. This is by design, to reflect the experience from the field, which shows that the disks are the components that are failing, not the whole storage cells.

August 9, 2014

How to resize grid disks in Exadata

This document explains how to resize the grid disks in Exadata (to make them larger), when there is free space in the cell disks. The free space can be anywhere on the cell disks. In other words, the grid disks can be built from and extended with the non-contiguous free space.

Typically, there is no free space in Exadata cell disks, in which case the MOS Doc ID 1465230.1 needs to be followed. But if there is free space in the cell disks, the procedure is much simpler and it can be accomplished with a single ASM rebalance operation.

This document has an example of performing this task on a quarter rack system (two database servers and three storage cells). With an Exadata with more storage cells, the only additional steps would be to resize the grid disks on additional storage cells.

Storage cells in the example are exacell01, exacell02 and exacell03, the disk group is DATA and the new grid disk size is 100000 MB.

Resize grid disks on storage cells

Log in as root to storage cell 1, and run the following command:

# cellcli -e alter griddisk  DATA_CD_00_exacell01, DATA_CD_01_exacell01, DATA_CD_02_exacell01, DATA_CD_03_exacell01, DATA_CD_04_exacell01, DATA_CD_05_exacell01, DATA_CD_06_exacell01, DATA_CD_07_exacell01, DATA_CD_08_exacell01, DATA_CD_09_exacell01, DATA_CD_10_exacell01, DATA_CD_11_exacell01 size=100000M;

Log in as root to storage cell 2, and run the following command:

# cellcli -e alter griddisk  DATA_CD_00_exacell02, DATA_CD_01_exacell02, DATA_CD_02_exacell02, DATA_CD_03_exacell02, DATA_CD_04_exacell02, DATA_CD_05_exacell02, DATA_CD_06_exacell02, DATA_CD_07_exacell02, DATA_CD_08_exacell02, DATA_CD_09_exacell02, DATA_CD_10_exacell02, DATA_CD_11_exacell02 size=100000M;

Log in as root to storage cell 3, and run the following command:

# cellcli -e alter griddisk  DATA_CD_00_exacell03, DATA_CD_01_exacell03, DATA_CD_02_exacell03, DATA_CD_03_exacell03, DATA_CD_04_exacell03, DATA_CD_05_exacell03, DATA_CD_06_exacell03, DATA_CD_07_exacell03, DATA_CD_08_exacell03, DATA_CD_09_exacell03, DATA_CD_10_exacell03, DATA_CD_11_exacell03 size=100000M;

As noted earlier, If you have a larger system, e.g. Exadata half rack with 7 storage cells, resize the grid disks for disk group DATA on all other storage cells.

Resize ASM disks

Log in as the Grid Infrastructure owner to database server 1, and log in to ASM instance 1 as sysasm.

$ sqlplus / as sysasm

Resize all disks in disk group DATA, with the following command:

SQL> ALTER DISKGROUP DATA RESIZE ALL;

Note that there was no need to specify the new disk size, as ASM will get that from the grid disks. If you would like to speed up the rebalance, add REBALANCE POWER 32 to the above command.

The command will trigger the rebalance operation for disk group DATA.

Monitor the rebalance with the following command:

SQL> select * from gv$asm_operation;

Once the command returns "no rows selected", the rebalance would have completed and all disks in disk group DATA should show new size.

Check that the ASM sees the new size, with the following command:

SQL> select name, total_mb from v$asm_disk_stat where name like 'DATA%';

The TOTAL_MB should show 100000M for all disks in disk group DATA.

Conclusion

If there is free space in Exadata cell disks, the disk group resize can be accomplished in two steps - grid disk resize on all storage cells followed by the disk resize in ASM. This requires a single ASM rebalance operation.

I have published this on MOS as Doc ID 1684112.1.