Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1 - Release: 10.1 to 10.2
Information in this document applies to any platform.
Symptoms
When trying to add datafile to an existing DiskGroups will fail as below even if there sufficient available free space.
in the diskgroup.
.
SQL> alter tablespace testtbs add datafile '+TESTDG1' size 100M;
alter tablespace testtbs add datafile '+TESTDG1' size 100M
*
ERROR at line 1:
ORA-1119: error in creating database file '+TESTDG1'
ORA-17502: ksfdcre:4 Failed to create file +TESTDG1
ORA-15041: diskgroup space exhausted
Cause
The problem occurs when there is a lot of free space in the disk group, but a file cannot be allocated because the free space is not evenly distributed.
Solution
1. Rebalance the diskgroup manually
OR
2. Use similarly size disks.
Testcase
-----------
1. create tablespace testtbs at DB instance (1)
.
SQL> create tablespace testtbs datafile '+TESTDG1' size 500M; <==
.
2. check views at ASM instance
.
SQL> select path, free_mb,total_mb,state from v$asm_disk;
PATH FREE_MB TOTAL_MB STATE
--------------------------------- ----------- -------------- --------
/dev/vgs11/rrac1012s_disk01 24 300 NORMAL
/dev/vgs11/rrac1012s_disk02 222 500 NORMAL
SQL> select name,free_mb,total_mb,state from v$asm_diskgroup;
NAME FREE_MB TOTAL_MB STATE
----------- ----------- ------------- -----------
TESTDG1 246 800 MOUNTED
3. add datafile at DB instance (2) ==> ORA-15041
.
SQL> alter tablespace testtbs add datafile '+TESTDG1' size 100M;
alter tablespace testtbs add datafile '+TESTDG1' size 100M
*
ERROR at line 1:
ORA-1119: error in creating database file '+TESTDG1'
ORA-17502: ksfdcre:4 Failed to create file +TESTDG1
ORA-15041: diskgroup space exhausted
.
4. execute rebalance at ASM instance
.
SQL> alter diskgroup testdg1 rebalance;
.
5. check views at ASM instance
.
SQL> select path, free_mb,total_mb,state from v$asm_disk;
PATH FREE_MB TOTAL_MB STATE
--------------------------------- ----------- -------------- --------
/dev/vgs11/rrac1012s_disk01 85 300 NORMAL
/dev/vgs11/rrac1012s_disk02 161 500 NORMAL
SQL> select name,free_mb,total_mb,state from v$asm_diskgroup;
NAME FREE_MB TOTAL_MB STATE
----------- ------------ ------------- -----------
TESTDG1 246 800 MOUNTED
6. add datafile at DB instance ==> succeed
.
SQL> alter tablespace testtbs add datafile '+TESTDG1' size 100M;
.
7. check views at ASM instance
.
SQL> select path, free_mb,total_mb,state from v$asm_disk;
PATH FREE_MB TOTAL_MB STATE
------------------------------ ----------- -------------- --------
/dev/vgs11/rrac1012s_disk01 35 300 NORMAL
/dev/vgs11/rrac1012s_disk02 110 500 NORMAL
SQL> select name,free_mb,total_mb,state from v$asm_diskgroup;
NAME FREE_MB TOTAL_MB STATE
----------- ----------- ------------- -----------
TESTDG1 145 800 MOUNTED
.
Diskgroup is 800MB and when we try to create tablespace of 500M and trying to add a datafile of 100M will fail even though there is a free space of 246MB in the diskgroup but the same succeeds after rebalance.