ASM磁盘组上对表空间添加数据文件报错ORA-15041

基本环境

SQL> create diskgroup testdg external redundancy disk '/dev/asm_arch01','/dev/asm_arch04' force ATTRIBUTE 'AU_SIZE'='4M','compatible.asm'='11.2';

Diskgroup created.

SQL> select group_number,disk_number,name,failgroup,create_date,path,writes,reads from v$asm_disk where group_number=2;

GROUP_NUMBER DISK_NUMBER NAME                           FAILGROUP                      CREATE_DA PATH                     WRITES      READS
------------ ----------- ------------------------------ ------------------------------ --------- -------------------- ---------- ----------
           2           1 TESTDG_0001                    TESTDG_0001                    24-NOV-20 /dev/asm_arch04               2         11
           2           0 TESTDG_0000                    TESTDG_0000                    24-NOV-20 /dev/asm_arch01              70         49
SQL> select path,total_mb,free_mb from v$asm_disk_stat where group_number=2;

PATH                   TOTAL_MB    FREE_MB
-------------------- ---------- ----------
/dev/asm_arch04            1024       1008
/dev/asm_arch01            3072       3000

SQL> select * from v$asm_operation;

no rows selected

SQL> select name,total_mb,free_mb from v$asm_diskgroup where group_number=2;

NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
TESTDG                               4096       4008

故障现象

数据库用户表空间不够，客户尝试加入新的数据文件，报错ORA-15041,无法加入添加新的数据文件，客户尝试在ASM磁盘组加入新磁盘也报错，ORA-15041,业务hang。

SQL> alter tablespace wahaha add datafile ‘+testdg’ size 10m;
alter tablespace wahaha add datafile ‘+testdg’ size 10m
*
ERROR at line 1:
ORA-01119: error in creating database file ‘+testdg’
ORA-17502: ksfdcre:4 Failed to create file +testdg
ORA-15041: diskgroup “TESTDG” space exhausted

故障分析

1、检查相关日志和数据库参数
1）检查ASM磁盘组的剩余空闲空间

SQL> select name,total_mb,free_mb from v$asm_diskgroup where group_number=2;

NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
TESTDG                               4096       4008

testdg磁盘组的总的剩余空间为4G，说明磁盘组本身的空间是足够的。
2、检查每个ASM磁盘的剩余空闲空间

SQL> select path,total_mb,free_mb from v$asm_disk_stat where group_number=2;

PATH                   TOTAL_MB    FREE_MB
-------------------- ---------- ----------
/dev/asm_arch04            1024       1008
/dev/asm_arch01            3072       3000

1）通过观察ASM磁盘组testdg下的每个磁盘的信息，发现testdg磁盘组下一共有7个asm disk，但是每个磁盘的剩余空闲空间并不均匀，磁盘6，磁盘7剩余空间为49G，磁盘1到磁盘5剩余空间都是20M不到。由于ASM DG存在strip条带化的功能，要求每个新加入的数据文件都条带均匀的分布在每个disk上。如果有一个磁盘剩余的空间过小，都会导致表空间无法加入新的数据文件。
2）ASM磁盘组加入新的磁盘的报错原因也类似，如果在磁盘组中的任何一个或多个磁盘空间非常少于<200M,那么添加的新磁盘不会成功，因为每添加一个磁盘，都需要进行各个磁盘的重新均衡操作，操作需要每个磁盘中的可用空间都大于200M。

解决方案

1、重新rebalance testdg，使得每个disk的freespace空间差不多，而且大于200M。
步骤如下：
1）删除testdg磁盘组下的临时表空间，这样可以让每个磁盘释放出足够的空间，并且剩余空间大于200M。
2）对testdg磁盘组重新做rebalance操作，这样可以做到每个磁盘的剩余空间一致。
alter diskgroup testdg rebalance power 10;
3)观察v$asm_operation视图，缺包rebalance操作完成。
2、后续在系统维护过程中，注意加入新磁盘的操作，一定要等rebalance操作完成，这样可以预防类似的问题发生。

参考

A friend asked me ,they add new disk to a ASM diskgroup. Disks were added successfully. and check free space available of ASM diskgroup was correct, But when tried alter tablespace add new datafile using the diskgroup one the disks were recently added, they got “ora-01119” and “ORA-17502″,”ORA-15041” errors during add new datafile.

SQL script:

alter diskgroup RAC_DISK1 add disk ‘/dev/raw/raw8’ ;
ALTER tablespace FMIS1800 ADD datafile ‘+RAC_DISK1’ size 4000Mthen reported following errors:

ORA-01119: error in creating database file ”
ORA-17502: ksfdcre:4 Failed to create file ”
ORA-15041: diskgroup space exhausted

then check raw device permissions and ASM info from dynamic view, No abnormalities.

ls -l /dev/raw/*

select group_number,disk_number,name,failgroup,create_date,path,writes,reads from v$as{m}_{d}isk;Selectgrou{p}_{n}umber,operation,state,power,actual,sofar,es{t}_{w}ork,es{t}_{r}ate,es{t}_{m}inutesfromv$asm_operation;
select path,total_mb,free_mb from v$as{m}_{d}is{k}_{s}tat;select\ast fromv$asm_operation;
select name,total_mb,free_mb from v$as{m}_{d}iskgroup;TIP:WeFoundsomedifferentbetweentheaddednewdiskandbeforeothersdisks,thenewdiskwritesandreadsfromv$asm_disk is zero.

There are two possibilities

1. ASM disk group genuinely does not have enough space to create or extend the datafile.

This possibility was ruled out, as I knew that they added 8g of space ,just trying to create a datafile of size 4g.

2. Another possibility could be, that the disks are not rebalanced (this was the problem is my case)

To check ASM instance alert log, but never rebalance diskgroup info like this:

”
NOTE: starting rebalance of group 1/0xe7a18492 (DATA_DG) at power 1
Starting background process ARB0
”

Solution:

Solution is to rebalance the disk manually like this:

alter diskgroup RAC_DISK1 rebalance power 10OR
Wait rebalance to complete (SUCCESS: rebalance completed for group in ASM alert.log for specific diskgroup)

Note:
We can also speed up the disk rebalance process by changing the value of ASM_POWER_LIMIT parameter (default is 1)

sql>show parameter asm_power_limit
sql>alter system set asm_power_limit=11;Changing the value of this parameter is only recommended, when there is not much of activity in the database. As rebalance using value 11, will incur high amount of I/O, which may impact day-to-day operations.

We can monitor the rebalance activity by selecting data from v$as{m}_{o}perationview.Wecanalsocheckthecolumn“UNBALANCED”inv$asm_diskgroup view. If the disks are correctly balanced, we’ll see default value of N in this column.

One the rebalance is done; we can add the datafile using the same command used before.

参考：
https://www.anbob.com/archives/2244.html
Exadata :Create TableSpace Fails with ORA-01119 ORA-17502 ORA-15041: Diskgroup Space Exhausted Despite Plenitude of Free Space is
Present (Doc ID 2372651.1)