This is to discuss why we are not able to create a datafile in asm diskgroup as it shows diskgorup space exhausted even though there are sufficient space in the diskgroup.
Step 1.
SQL> ALTER TABLESPACE MAH_CUSTOM_TABLES ADD DATAFILE '+MAH_FRA_DG/nmahprd/datafile/mah_custom_tables_29.dbf' size 30g;
ALTER TABLESPACE MAH_CUSTOM_TABLES ADD DATAFILE '+MAH_FRA_DG/nmahprd/datafile/mah_custom_tables_29.dbf' size 30g
*
ERROR at line 1:
ORA-01119: error in creating database file '+MAH_FRA_DG/nmahprd/datafile/mah_custom_tables_29.dbf'
ORA-17502: ksfdcre:4 Failed to create file +MAH_FRA_DG/nmahprd/datafile/mah_custom_tables_29.dbf
ORA-15041: diskgroup "MAH_FRA_DG" space exhausted
-- Not able to add dataile to MAH_FRA_DG as it shows space exhausted.
Step 2.
SQL> select name, total_mb, usable_file_mb from V$asm_diskgroup;
NAME TOTAL_MB USABLE_FILE_MB
------------------------------ ---------- --------------
MAH_DATA_DG 6656000 49416
MAH_FRA_DG2 1024000 808972
MAH_FRA_DG 1024200 352056
DATA 512000 511568
MAH_OGG_DG 256000 924
Step 3.
Connect to asm instance.
SQL> select group_number, name , total_mb, usable_file_mb, state, TYPE from V$asm_diskgroup;
GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB STATE TYPE
------------ ------------------------------ ---------- -------------- ----------- ------
1 MAH_DATA_DG 6656000 49416 MOUNTED EXTERN
2 MAH_FRA_DG2 1024000 807656 MOUNTED EXTERN
3 MAH_FRA_DG 1024200 352056 MOUNTED EXTERN
4 DATA 512000 511568 MOUNTED EXTERN
5 MAH_OGG_DG 256000 924 MOUNTED EXTERN
SQL> select MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, TOTAL_MB, FREE_MB, PATH, os_mb from V$asm_disk where GROUP_NUMBER = 3;
MOUNT_S HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB PATH OS_MB
------- ------------ ------- -------- ---------- ---------- ---------------------------------------- ----------
CACHED MEMBER ONLINE NORMAL 512000 178088 ORCL:MAH_FRA_DISK17 512000
CACHED MEMBER ONLINE NORMAL 512000 173964 ORCL:MAH_FRA_DISK18 512000
CACHED MEMBER ONLINE NORMAL 200 4 ORCL:MAH_FRA_DISK21 200
-- From the above we can see that the MAH_FRA_DG has 3 disks in which one disk is 200 MB disk.
As we know the asm will stripe the data and place it across all the disks in the asm diskgroup. Here the available space in the 3rd disk is only 4 MB, which is why we got the error as space exhausted.
So it is always recommended to have same sized disks in the disk group.
For this issue we have dropped the undersized disk from the diskgroup.
SQL> alter diskgroup MAH_FRA_DG drop disk 'MAH_FRA_DISK21';
Diskgroup altered.
SQL> select * from V$asm_operation;
GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
3 REBAL RUN 1 1 536 1336 1346 0
SQL> /
no rows selected
-- The Disk has been droped and the rebalance has been completed.
SQL> select MOUNT_STATUS, HEADER_STATUS, MODE_STATUS, STATE, TOTAL_MB, FREE_MB, PATH, os_mb , name from V$asm_disk where GROUP_NUMBER = 3;
MOUNT_S HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB PATH OS_MB NAME
------- ------------ ------- -------- ---------- ---------- ---------------------------------------- ---------- ------------------------------
CACHED MEMBER ONLINE NORMAL 512000 175936 ORCL:MAH_FRA_DISK17 512000 MAH_FRA_DISK17
CACHED MEMBER ONLINE NORMAL 512000 175928 ORCL:MAH_FRA_DISK18 512000 MAH_FRA_DISK18
-- Now only two disks are getting listed for the MAH_FRA_DG diskgroup.
Now connect to the db instance and re-trigger the add datafile command.
SQL> alter tablespace MAH_CUSTOM_TABLES add datafile '+MAH_FRA_DG/nmahprd/datafile/mah_custom_tables_29.dbf' size 30g;
Tablespace altered.
-- Now the datafile has been added successfully.
