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

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

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

现在只好重新调度任务。

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

By eygle on 2010-07-09 10:56 |

Comments (0) |

Case | 2574 |