关于undotbs1表空间损坏解决办法-dodba-ChinaUnix博客

在生产应用中遇到如下问题。

环境：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;

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

空间已经释放。