REQUIRED_MIRROR_FREE_MB and USABLE_FILE_MB

SQL> select label,os_mb from v$asm_disk order by label;

LABEL OS_MB
——————————- ———-
ASMDISK1 4777
ASMDISK2 4777
ASMDISK3 4777
ASMDISK4 6142
ASMDISK5 5114
ASMDISK6 5122
ASMDISK7 5122
ASMDISK8 5114

Create diskgroup with external redundancy

SQL> create diskgroup DATA3 external redundancy

disk ‘ORCL:ASMDISK5’
disk ‘ORCL:ASMDISK6’
disk ‘ORCL:ASMDISK7’
disk ‘ORCL:ASMDISK8’
attribute ‘compatible.asm’ = ‘11.2.0.0.0’; 2 3 4 5 6

Diskgroup created.

SQL>
select name, state, type, total_mb, free_mb,required_mirror_free_mb req_free, usable_file_mb
from v$asm_diskgroup where name = ‘DATA3’;SQL> 2

NAME STATE TYPE TOTAL_MB FREE_MB REQ_FREE USE_FILE_MB
—————————— ———– —— ———- ———- ———- ———-
DATA3 MOUNTED EXTERN 20472 20414 0 20414

Required free space is 0 as the diskgroup is external redundancy . Entire space(USE_FILE_MB) is available for usage.

Create diskgroup with normal redundancy

SQL> Create diskgroup DATA4 normal redundancy
failgroup group1 disk
‘ORCL:ASMDISK5’
failgroup group2 disk
‘ORCL:ASMDISK6’
failgroup group3 disk
‘ORCL:ASMDISK7’
failgroup group4 disk
‘ORCL:ASMDISK8’
attribute ‘compatible.asm’ = ‘11.2.0.0.0’; 2 3 4 5 6 7 8 9 10

Diskgroup created.

Check size of each failgroup

SQL> select failgroup,sum(total_mb) from v$asm_disk
where group_number=2 group by failgroup order by failgroup; 2

FAILGROUP SUM(TOTAL_MB)
—————————— ————-
GROUP1 5114
GROUP2 5122
GROUP3 5122
GROUP4 5114

we require 41MB free space to restore redundancy incase of loss of a disk and we have 10135MB space for additional space

SQL> Select name, state, type, total_mb, free_mb, required_mirror_free_mb req_free, usable_file_mb use_mb
from v$asm_diskgroup where name = ‘DATA3′;
2
NAME STATE TYPE TOTAL_MB FREE_MB REQ_FREE USE_MB
—————————— ———– —— ———- ———- ———- ———-
DATA3 MOUNTED NORMAL 20472 20311 41 10135
select trunc((free_mb – required_mirror_free_mb) / 2) as useable
from v$asm_diskgroup where name=’DATA3’;
SQL> 2
USEABLE
———-
10135

Create some files on the diskgroup and check the required and usable space

SQL> create tablespace TEST datafile ‘+DEMO’ size 200m;
Tablespace created.

SQL> select name, state, type, total_mb, free_mb, required_mirror_free_mb req_free, usable_file_mb use_mb
from v$asm_diskgroup where name = ‘DATA3’; 2

NAME STATE TYPE TOTAL_MB FREE_MB REQ_FREE USE_MB
—————————— ———– —— ———- ———- –
DATA3 MOUNTED NORMAL 20472 19885 150 9867

SQL> SQL> alter tablespace TEST add datafile ‘+DEMO’ size 200m;
Tablespace altered.

SQL> select name, state, type, total_mb, free_mb, required_mirror_free_mb req_free, usable_file_mb use_mb
from v$asm_diskgroup where name = ‘DATA3’;
2
NAME STATE TYPE TOTAL_MB FREE_MB REQ_FREE USE_MB
—————————— ———– —— ———- ———- ———- ———-
DATA3 MOUNTED NORMAL 20472 19480 252 9614

Drop the tablespace created and check the required and usable space

SQL> SQL> drop tablespace TEST including contents and datafiles;

Tablespace dropped.

NAME STATE TYPE TOTAL_MB FREE_MB REQ_FREE USE_MB
—————————— ———– —— ———- ———- –
DATA3 MOUNTED NORMAL 20472 20290 47 10121

USABLE_FILE_MB = (FREE_MB – REQUIRED_MIRROR_FREE_MB) / [2|3]

ref: https://prutser.wordpress.com/2013/01/03/demystifying-asm-required_mirror_free_mb-and-usable_file_mb/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s