ASM Diskgroup空间不足导致索引创建失败

昨日，客户数据库在创建一个索引时失败，提示ORA-1652表空间不足，索引无法在表空间上扩展。

Thu Jul 08 09:01:53 CST 2010
create tablespace SMNIDX datafile size 10240M autoextend on next 1024M
Thu Jul 08 09:03:14 CST 2010
Completed: create tablespace SMNIDX datafile size 10240M autoextend on next 1024M
Thu Jul 08 09:03:53 CST 2010
alter tablespace SMNIDX add datafile size 64M autoextend on next 1024M
Thu Jul 08 09:03:54 CST 2010
Completed: alter tablespace SMNIDX add datafile size 64M autoextend on next 1024M
Thu Jul 08 11:25:46 CST 2010
ORA-1652: unable to extend temp segment by 128 in tablespace                 SMNIDX
ORA-1652: unable to extend temp segment by 8192 in tablespace                 SMNIDX
Thu Jul 08 13:19:49 CST 2010
ORA-1652: unable to extend temp segment by 128 in tablespace                 SMNIDX
ORA-1652: unable to extend temp segment by 8192 in tablespace                 SMNIDX

为此，用户增加了可以自动扩展的表空间，重新开始索引创建。缺省的，我以为这是合理的做法，就疏忽了检查，在几个小时之后索引的创建再次失败：

Thu Jul 08 18:36:46 CST 2010
ORA-1652: unable to extend temp segment by 128 in tablespace                 SMNIDX
ORA-1652: unable to extend temp segment by 8192 in tablespace                 SMNIDX
Thu Jul 08 19:17:46 CST 2010
ORA-1652: unable to extend temp segment by 128 in tablespace                 SMNIDX
ORA-1652: unable to extend temp segment by 8192 in tablespace                 SMNIDX

这时候我意识到，其实可能是空间不足导致了表空间无法扩展。当ASM磁盘组不能扩展时，数据库的提示是不确切的，检查ASM的日志信息，就可以得到根本原因的解释：

Thu Jul 08 11:25:46 CST 2010
WARNING: allocation failure on disk VOL1 for file 441 xnum 654
WARNING: allocation failure on disk VOL1 for file 440 xnum 10830
Thu Jul 08 13:19:49 CST 2010
WARNING: allocation failure on disk VOL1 for file 441 xnum 654
WARNING: allocation failure on disk VOL1 for file 440 xnum 10830
WARNING: allocation failure on disk VOL1 for file 430 xnum 22148
WARNING: allocation failure on disk VOL1 for file 431 xnum 22148
WARNING: allocation failure on disk VOL1 for file 430 xnum 22148
WARNING: allocation failure on disk VOL1 for file 431 xnum 22148

原来是ASM磁盘组用完了啊，如果再尝试增加大文件，此时会抛出空间耗尽的异常：

SQL> alter database datafile '+ORADATA_DG/oradb/datafile/mt_idx_new.440.723805313' resize 30G;
alter database datafile '+ORADATA_DG/oradb/datafile/mt_idx_new.440.723805313' resize 30G
*
ERROR at line 1:
ORA-01237: cannot extend datafile 179
ORA-01110: data file 179:
'+ORADATA_DG/oradb/datafile/mt_idx_new.440.723805313'
ORA-17505: ksfdrsz:1 Failed to resize file to size 3932160 blocks
ORA-15041: diskgroup space exhausted

检查磁盘组，发现存放数据的DG的确是没有空间了：

SQL> select group_number,name,total_mb,free_mb from v$ASM_DISKGROUP;

GROUP_NUMBER NAME                             TOTAL_MB    FREE_MB
------------ ------------------------------ ---------- ----------
           1 ORATATA_DG                        1425894        569
           2 RECOVDT_DG                         569130     560751

只好临时在恢复盘上建立一个文件：

SQL> alter tablespace SMNIDX add datafile '+RECOVER_DG' size 10G autoextend on next 500M;

Tablespace altered.
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 from dba_data_files where tablespace_name='SMNIDX';

TABLESPACE_NAME    FILE_NAME                                                         BYTES/1024/1024/1024
------------------ ---------------------------------------------------------------------- --------------------
SMNIDX             +ORADATA_DG/oradb/datafile/mt_idx_new.440.723805313                              10
SMNIDX             +ORADATA_DG/oradb/datafile/mt_idx_new.441.723805433                           .0625
SMNIDX             +RECOVER_DG/oradb/datafile/mt_idx_new.262.723893823                              10

而在索引创建中出现的错误是这样的：

create index IDXDN on SMG (MDN)
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P003, instance oracledb3:oradb1 (1)
ORA-01652: unable to extend temp segment by 8192 in tablespace SMNIDX

现在只好重新调度任务。

在这次失误中学到的教训是：要时刻牢记从不同角度看问题，要从多角度进行审核与确认。