Thursday, December 5, 2013

Unable to create datafile in ASM diskgroup.



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

-- From the above we could see there are aournd 352 GB of space is available in MAH_FRA_DG.


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.