关于undotbs1表空间损坏解决办法


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

  环境: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

  空间已经释放

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

  环境: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

  空间已经释放


« 
» 
快速导航

Copyright © 2016 phpStudy | 豫ICP备2021030365号-3