在生产应用中遇到如下问题。
环境：OS redflag Server 5
DB Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 RAC
在两台数据库日志中提示为
Errors in file /opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 2, 块号 104420)
ORA-01110: 数据文件 2: '+DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:23 2009
Errors in file /opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 2, 块号 104420)
ORA-01110: 数据文件 2: '+DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:24 2009
Errors in file /opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 2, 块号 104420)
ORA-01110: 数据文件 2: '+DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:25 2009
Errors in file /opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 2, 块号 104420)
ORA-01110: 数据文件 2: '+DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:25 2009
Errors in file /opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
ORA-01578: ORACLE 数据块损坏 (文件号 2, 块号 104420)
ORA-01110: 数据文件 2: '+DATA1/postdb/datafile/undotbs1.260.666122861'
Sun Jul 26 09:19:30 2009
Errors in file /opt/app/oracle/admin/postdb/bdump/postdb2_smon_7618.trc:
登陆检查，用如下语句查看标空间的使用情况。
SQL> select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,
2 round(f.sumbytes/1024/1024/1024,2) free_g,
round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,
3 4 round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percent
5 from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,
(select tablespace_name,sum(bytes) sumbytes from dba_data_files group by tablesp 6 ace_name) d
7 where f.tablespace_name= d.tablespace_name
8 order by d.tablespace_name;
TABLESPACE_NAME TOTAL_G FREE_G USED_G USED_PERCENT
------------------------------ ---------- ---------- ---------- ------------
INDEX_DATA 7.81 7.81 0 0
MONTH_SPACE01 7.81 7.78 .03 .4
MONTH_SPACE02 7.81 7.78 .03 .38
MONTH_SPACE03 7.81 7.81 0 .05
MONTH_SPACE04 7.81 7.81 0 .05
PCC_LS_YWXX_SPACE01 7.81 7.76 .05 .69
PCC_LS_YWXX_SPACE02 7.81 7.76 .05 .65
PCC_LS_YWXX_SPACE03 7.81 7.81 .01 .09
PCC_LS_YWXX_SPACE04 7.81 7.79 .02 .26
POST_KF 31.25 27.66 3.59 11.5
SYSAUX 2.27 1.17 1.1 48.42
TABLESPACE_NAME TOTAL_G FREE_G USED_G USED_PERCENT
------------------------------ ---------- ---------- ---------- ------------
SYSTEM 1 .51 .49 48.99
TS_PCC_LS_YWXX 15.63 15.62 0 0
TS_PCC_REC_AUDIO 15.63 15.62 0 0
UNDOTBS1 4 .01 3.99 99.79
UNDOTBS2 4 3.99 .01 .33
USERS .49 .49 0 .19
VADIO_DATA 7.81 7.81 0 0
18 rows selected.
发现是UNDOTBS1表空间空间用尽,不能扩展。
显然曾经有大事务占用了大量的UNDO表空间。Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性).
现在我们可以采用如下步骤回收UNDO空间:
（1） 确认文件
SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';
FILE_NAME
----------------------------------------------------------------------------------------------------
BYTES/1024/1024
---------------
+DATA1/postdb/datafile/undotbs1.260.666122861
4096
（2）检查UNDO Segment状态
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
15 0 .000297546 .002128601 10
0 0 .000358582 .000358582 0
19 0 .001091003 .013786316 4
12 1 .001091003 .003044128 1
13 0 .001091003 .004997253 3
14 0 .001091003 .002067566 1
16 0 .001091003 .002067566 3
11 0 .001091003 .003044128 4
18 0 .001091003 .002067566 1
17 0 .002067566 .006950378 1
20 0 .002067566 .002067566 3
11 rows selected.
（3）创建新的UNDO表空间
SQL> create undo tablespace undotbs2 datafile '+DATA1' size 4000M;;
Tablespace created.
（4）切换UNDO表空间为新的UNDO表空间
SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.
（5）等待原UNDO表空间所有UNDO SEGMENT OFFLINE
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
15 0 ONLINE .000297546 .002128601 10
0 0 ONLINE .000358582 .000358582 0
19 0 ONLINE .001091003 .013786316 4
12 1 ONLINE .001091003 .003044128 1
13 0 ONLINE .001091003 .004997253 3
14 0 ONLINE .001091003 .002067566 1
16 0 ONLINE .001091003 .002067566 3
11 0 ONLINE .001091003 .003044128 4
18 0 ONLINE .001091003 .002067566 1
17 0 ONLINE .002067566 .006950378 1
20 0 ONLINE .002067566 .002067566 3
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
6 0 PENDING OFFLINE 2.9671936 2.9671936 0
12 rows selected.
再看:
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
15 0 ONLINE .000297546 .002128601 10
0 0 ONLINE .000358582 .000358582 0
19 0 ONLINE .001091003 .013786316 4
12 1 ONLINE .001091003 .003044128 1
13 0 ONLINE .001091003 .004997253 3
14 0 ONLINE .001091003 .002067566 1
16 0 ONLINE .001091003 .002067566 3
11 0 ONLINE .001091003 .003044128 4
18 0 ONLINE .001091003 .002067566 1
17 0 ONLINE .002067566 .006950378 1
20 0 ONLINE .002067566 .002067566 3
11 rows selected.
（6）删除原UNDO表空间
SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
Elapsed: 00:00:03.13
（7）检查空间情况
由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.
[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD> du
Used_MB Mirror_used_MB
21625 21625
ASMCMD> exit
空间已经释放
相关undotbs1表空间损坏如何解决