Symptoms
An ORA-15041 ( diskgroup space exhausted ) occurs during a rebalance or adding of a disk (which implicitly does a rebalance unless told otherwise)
Cause
One or more disks have V$ASM_DISK.FREE_MB are below the threshold level needed to be able to do a successful rebalance (50-100mb)
Solution
1) Determine which (if any) disks contain no free space (ie are below the threshold)
select group_kfdat "group #",
number_kfdat "disk #",
count(*) "# AU's"
from x$kfdat a
where v_kfdat = 'V'
and not exists (select *
from x$kfdat b
where a.group_kfdat = b.group_kfdat
and a.number_kfdat = b.number_kfdat
and b.v_kfdat = 'F')
group by GROUP_KFDAT, number_kfdat;
If no rows are returned ... the following query can also be used
select disk_number "Disk #", free_mb
from v$asm_disk
where group_number = *** disk group number ***
order by 2;
If rows are returned from the first query ... or FREE_MB is less than 100mb in the second ... then there is probably insufficient disk space to allow a rebalance to occur ... Note the Disk #'s for later
2) Determine which files have allocation units on the disk(s) that are on exhausted disks
select name, file_number
from v$asm_alias
where group_number in (select group_kffxp
from x$kffxp
where group_kffxp=*** disk group number ***
and disk_kffxp in (*** disk list from #1 above ***)
and au_kffxp != 4294967294
and number_kffxp >= 256)
and file_number in (select number_kffxp
from x$kffxp
where group_kffxp=*** disk group number ***
and disk_kffxp in (*** disk list from #1 above ***)
and au_kffxp != 4294967294
and number_kffxp >= 256)
and system_created='Y';
3) Free up space so that the rebalance can occur
Using the file list from #2 above ... we will need to either drop or move tablespace(s)/datafile(s) such that all disks that are exhausted have at least 100mb free ...
NOTE ... the AU count above ... should relate to 1mb AU size ... so if a single file ... with at least 100 au's can be dropped or moved ... this
should be sufficient to free up enough space to allow the rebalance to occur
Droppable tablespaces may be things like:
* temporary tablespaces
* index tablespaces (assuming you know how to rebuild the indexes)
If none of the tablespaces are droppable then the tablespace(s)/datafile(s) will need to be
* moved to another diskgroup (at least temporarily) ...
* dropped using RMAN (with the database shutdown) and will be restored later
4) Check to see if there is sufficient FREE_MB on the problem disks
select disk_number "Disk #", free_mb
from v$asm_disk
where disk_group = *** disk group number ***
and disk_number in (*** disk list from #1 above ***)
order by 2;
If the disks do not have at least 100mb free ... repeat #3 above
5) Rebalance
alter diskgroup
6) Monitor the progress of the rebalance until finished
select sofar "AUs moved So Far", est_work "Aprox AU's to be moved"
from v$asm_operation
where group_number = *** disk group number ***;
Continue to monitor until the rebalance has completed
7) Check the balance of the disks
select disk_number, total_mb-free_mb
from v$asm_disk
where group_number = *** disk group number ***;
TOTAL_MB - FREE_MB = amount of space used on the disk
The amount of space used on each disk (without regard to size) should be approximately the same (within a few megabytes)
8) Put things back where they wereIf the datafiles were moved ... reverse the process and return them to their original diskgroup and location
if the tablespace(s) were dropped ... recreate them ... if needed
If the datafiles were dropped ... restore them using RMAN
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
