How to find datafile with incorrect auto extend size for Exadata requirement (Doc ID 1296963.1)

Goal

When configuring a file to auto extend on an Exadata 11.2.0.2 environment, the size of the extension should cover all disks in the diskgroup to optimize balance. For example, with a 4MB AU size and 128 disks, the size of the extension should be a multiple of 512M (4*128).

To determine which files do not meet this criteria run the following query on the data server instance:

SELECT distinct df.file_name FROM dba_data_files df, v$asm_alias a,
(SELECT ad.group_number, count(*) DG_num_disks, att.value, (count(*) * att.value) Ext_Size
FROM v$asm_disk ad, V$ASM_ATTRIBUTE att
WHERE ad.group_number=att.group_number
AND att.name = 'au_size'
GROUP BY ad.group_number,att.value) v
WHERE autoextensible = 'YES'
AND status = 'AVAILABLE'
AND a.group_number=v.group_number
AND upper(df.file_name) in
(SELECT concat('+'||gname,sys_connect_by_path(aname, '/'))
FROM (SELECT vg.name gname, va.parent_index pindex, upper(va.name) aname, va.reference_index rindex, va.group_number gnum,va.file_number filnum
FROM v$asm_alias va,v$asm_diskgroup vg
WHERE va.group_number = vg.group_number)
START WITH (MOD(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
AND MOD((df.increment_by * (SELECT value FROM v$parameter WHERE name = 'db_block_size')), v.Ext_Size) > 0;

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.