eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 从武汉到宜昌 百里长江波澜阔 | Blog首页 | 达梦数据库与独立知识产权的自主创新 »

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

现在只好重新调度任务。

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



历史上的今天...
    >> 2009-07-09文章:
    >> 2007-07-09文章:
    >> 2004-07-09文章:
           使用or展开进行sql调整

无觅

By eygle on 2010-07-09 10:56 | Comments (0) | Case | 2574 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com