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
