Adding Datafile in ASM Database

Adding Datafile in ASM Database

Before adding the datafile in ASM we need below 2 details
1) Diskgroup Name
2) Tablespace Name

Here I have taken Diskgroup Name "DATA" and Tablespace Name "SYSAUX"

1) Check Free Space in the diskgroup .
SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

NAME            FREE_MB     TOTAL_MB      PERCENTAGE
---------- ------------ ------------ ------------------------------
DATA              380,349      511,994          74.2877846
OCRVOTE           102,002      102,397          99.6142465
RECO              495,530      511,997          96.7837702


2) Check the diskgroup name which is mapped to SYSAUX tablesapce .
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name='SYSAUX';

TABLESPACE                                FILE_NAME
---------- --------------------------------------------------------------------------
SYSAUX             +DATA/ieclive/datafile/sysaux.264.860628911

Here DATA diskgroup is mapped to the sysaux tablespace.

3) Add the datafile to tablespace in diskgroup.
SQL> alter tablespace SYSAUX add datafile'+DATA' size 5G autoextend on next 1024M;

     Database altered.

4) Verify the same.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 from dba_data_files;

TABLESPACE             FILE_NAME                                     BYTES/1024/1024/1024
---------- --------------------------------------------- --------------------------------------------
SYSTEM              +DATA/ieclive/datafile/system.258.860628911                  5
SYSAUX              +DATA/ieclive/datafile/sysaux.264.860628911                   5
UNDOTBS1            +DATA/ieclive/datafile/undotbs1.262.860628913           5
SYSAUX              +DATA/ieclive/datafile/sysaux.266.860728913                   5
UNDOTBS2         +DATA/ieclive/datafile/undotbs2.269.860629051               5
USERS               +DATA/ieclive/datafile/users.256.860630027                       10
USERS               +DATA/ieclive/datafile/users.274.860630073                       10


7 rows selected