ORA-01119: error in creating database file ‘+DATA’ – ORA-17502: ksfdcre:4 Failed to create file +DATA – ORA-15041: diskgroup “DATA” space exhausted

Cause:

The error creeps in ,when datafile or tempfile cant be added due to space crunch on asm diskgroup

kish@exdbx<>create temporary tablespace temp2 tempfile '+DATA' size 1G;
create temporary tablespace temp2 tempfile '+DATA' size 1G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA'
ORA-17502: ksfdcre:4 Failed to create file +DATA
ORA-15041: diskgroup "DATA" space exhausted

Workaround:

Check the space for DATA diskgroup.We have 112 MB space on the diskgroup which has external redundancy.So there is no room for incoming datafile or tempfile to accomodate in DATA diskgroup

ASMCMD> lsdg --discovery
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      5760      112                0             112              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304      1440     1304                0            1304              0             N  DATA2/
MOUNTED  EXTERN  N         512   4096  4194304      5040     1784                0            1784              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576      1440     1042                0            1042              0             Y  MGMT/

Check with your storage team to allocate the disks to the server and check disk header status in v$asm_disk

If it is exadata,then all the luns should be added as celldisks which in turn should be mapped to ASM griddisks.In my case, after storage team added 4 arrays(cell20,cell21,cell22,cell23),symbolic link should be created from the logical cylinders

[root@exceladm00 raw]# ls -lrt
total 0
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell23 -> /dev/sdad
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell22 -> /dev/sdac
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell21 -> /dev/sdab
lrwxrwxrwx 1 root root 9 Feb 25 01:38 cell20 -> /dev/sdaa

Use a shell script to generate symbolic link commands in one go

fdisk -l 2>/dev/null | grep "805 MB" | awk '{ printf "%s%02d\n", "ln -s "$2" cell", NR }'|sed "s/://"

  392  ln -s /dev/sdaa cell20
  393  ln -s /dev/sdab cell21
  394  ln -s /dev/sdac cell22
  395  ln -s /dev/sdad cell23

In storage server,check if physical disk which were allocated reflect!

CellCLI> list physicaldisk

         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell20    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell20          normal
         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell21    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell21          normal
         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell22    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell22          normal
         /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell23    /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/disks/raw/cell23          normal

Create celldisk from the physical disk with size clause to allocate only specific size for capacity

CellCLI> create celldisk  all;
CellDisk FD_06_stocell successfully created
CellDisk FD_07_stocell successfully created
CellDisk FD_08_stocell successfully created
CellDisk CD_cell20_stocell successfully created
CellDisk CD_cell21_stocell successfully created
CellDisk CD_cell22_stocell successfully created
CellDisk CD_cell23_stocell successfully created

Next map your asm to celldisks with griddisk creation

CellCLI> create griddisk all harddisk prefix=DATA
Cell disks were skipped because they had no freespace for grid disks: CD_cell10_stocell, CD_cell11_stocell, CD_cell12_stocell, CD_cell13_stocell, CD_cell14_stocell, CD_cell15_stocell, CD_cell16_stocell, CD_cell17_stocell, CD_cell18_stocell.
GridDisk DATA_CD_cell20_stocell successfully created
GridDisk DATA_CD_cell21_stocell successfully created
GridDisk DATA_CD_cell22_stocell successfully created
GridDisk DATA_CD_cell23_stocell successfully created

Newly added disk header status should reflect as CANDIDATE disk.Below,there are 4 candidate disks which can be added to the DATA diskgroup for space.

SQL> select name,header_status,state from v$asm_disk;

NAME                           HEADER_STATU STATE
------------------------------ ------------ --------
                               CANDIDATE    NORMAL
                               CANDIDATE    NORMAL
                               CANDIDATE    NORMAL
                               CANDIDATE    NORMAL
DATA_CD_CELL07_STOCELL         MEMBER       NORMAL
MGMT_CD_CELL15_STOCELL         MEMBER       NORMAL
FRA_CD_CELL08_STOCELL          MEMBER       NORMAL
MGMT_CD_CELL16_STOCELL         MEMBER       NORMAL
DATA_CD_CELL19_STOCELL         MEMBER       NORMAL
FRA_CD_CELL12_STOCELL          MEMBER       NORMAL
FRA_CD_CELL11_STOCELL          MEMBER       NORMAL

NAME                           HEADER_STATU STATE
------------------------------ ------------ --------
DATA_CD_CELL09_STOCELL         MEMBER       NORMAL
FRA_CD_CELL09_STOCELL          MEMBER       NORMAL
DATA_CD_CELL05_STOCELL         MEMBER       NORMAL
DATA_CD_CELL06_STOCELL         MEMBER       NORMAL
FRA_CD_CELL14_STOCELL          MEMBER       NORMAL
FRA_CD_CELL10_STOCELL          MEMBER       NORMAL
DATA_CD_CELL03_STOCELL         MEMBER       NORMAL
FRA_CD_CELL13_STOCELL          MEMBER       NORMAL
DATA_CD_CELL04_STOCELL         MEMBER       NORMAL
DATA_CD_CELL01_STOCELL         MEMBER       NORMAL
DATA_CD_CELL02_STOCELL         MEMBER       NORMAL

NAME                           HEADER_STATU STATE
------------------------------ ------------ --------
DATA_CD_CELL08_STOCELL         MEMBER       NORMAL

23 rows selected.

Use the below command with rebalance power 9 to speed up the rebalance operation.Make sure there is sufficient CPU power on your host so that 9 parallel I/O’s can be performed by ARB process.

SQL> alter diskgroup DATA add disk 'o/192.168.56.33/DATA_CD_cell2*' reba                                      lance power 9


NOTE: GroupBlock outside rolling migration privileged region
NOTE: Assigning number (2,8) to disk (o/192.168.56.33/DATA_CD_cell21_stocell)
NOTE: requesting all-instance membership refresh for group=2
NOTE: initializing header on grp 2 disk DATA_CD_CELL21_STOCELL
NOTE: requesting all-instance disk validation for group=2
Thu Mar 25 22:01:18 2021
NOTE: skipping rediscovery for group 2/0x1f0821be (DATA) on local instance.
NOTE: initiating PST update: grp = 2
Thu Mar 25 22:01:20 2021
GMON updating group 2 at 13 for pid 29, osid 9120
NOTE: PST update grp = 2 completed successfully
NOTE: membership refresh pending for group 2/0x1f0821be (DATA)
GMON querying group 2 at 14 for pid 19, osid 4885
NOTE: cache opening disk 8 of grp 2: DATA_CD_CELL21_STOCELL path:o/192.168.56.33                                      /DATA_CD_cell21_stocell
GMON querying group 2 at 15 for pid 19, osid 4885
Thu Mar 25 22:01:34 2021
SUCCESS: refreshed membership for 2/0x1f0821be (DATA)
Thu Mar 25 22:01:36 2021
SUCCESS: alter diskgroup DATA add disk 'o/192.168.56.33/DATA_CD_cell21_stocell'                                       rebalance power 9
NOTE: Attempting voting file refresh on diskgroup DATA
NOTE: Refresh completed on diskgroup DATA. No voting file found.
NOTE: starting rebalance of group 2/0x1f0821be (DATA) at power 9
Starting background process ARB0
Thu Mar 25 22:01:38 2021
ARB0 started with pid=36, OS id=9478
NOTE: assigning ARB0 to group 2/0x1f0821be (DATA) with 9 parallel I/Os

Disk are added to DATA diskgroup and datafile or tempfile to instance can be added

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  Y         512   4096  4194304      7920     2248                0            2248              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304      1440     1304                0            1304              0             N  DATA2/
MOUNTED  EXTERN  N         512   4096  4194304      5040     1752                0            1752              0             N  FRA/
MOUNTED  EXTERN  N         512   4096  1048576      1440     1042                0            1042              0             Y  MGMT/
kish@exdbx<>create temporary tablespace temp2 tempfile '+DATA2' size 100M autoextend on next 50M maxsize 1G;

Tablespace created.

Elapsed: 00:00:04.26

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s