一次UNDOTBS1表空间的损坏恢复在线操作过程-dodba-ChinaUnix博客

在客户生产系统中遇到服务器意外宕机的状况，查看后台日志发现大量报错信息如下：
Sun Jul 26 09:40:41 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:40:46 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:40:51 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:40:56 2009

当前环境为：OS：RedFlag Server5 db：oracle10.2.1.0 两台服务器做RAC

既然是UNDOTBS1数据库文件损坏，就需要删除UNDOTBS1，重新创建UNDOTBS1数据文件了
关于undotbs1表空间做为事务回滚，主要用于
（TransactionRollback）：程序执行rollback操作
事务修复（TransactionRecovery）rollback为recovery de ─部分。
读取─致（ReadConsistency）：根据SCN（systemchangenumber）来保证读取数据的─致性

登陆服务器检查回滚段表空间的使用情况。
[oracle@postdb1 /]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 30 14:32:25 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g, round(f.sumbytes/1024/1024/1024,2) free_g,round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percent 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 tablespace_name) d where f.tablespace_name= d.tablespace_name 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.8        .01           .1
PCC_LS_YWXX_SPACE04                  7.81       7.79        .02          .26
POST_KF                             31.25      27.62       3.63        11.61
SYSAUX                               2.27       1.45        .81         35.9

TABLESPACE_NAME                   TOTAL_G     FREE_G     USED_G USED_PERCENT
------------------------------ ---------- ---------- ---------- ------------
SYSTEM                                  1        .51        .49        49.08
TS_PCC_LS_YWXX                      15.63      15.62          0            0
TS_PCC_REC_AUDIO                    15.63      15.62          0            0
UNDOTBS1                             3.91        3.9          0          .03
UNDOTBS2                                4        .01       3.99        99.79
USERS                                 .49        .49          0           .2
VADIO_DATA                           7.81       7.81          0            0

18 rows selected.

发现UNDOTBS1表空间使用率已经达到99.79%

为了保证不尽可能小的影响生产操作，决定在节点1上在线重建UNDOTBS1

首先创建一个备用的回滚标空间UNDOTBS3，因为UNDOTBS2已经被节点2使用，所以不能创建名字为UNDOTBS2表空间。

SQL> create undo tablespace undotbs3 datafile '+DATA1' size 4000M;

如果生产系统可以停止的话，执行如下语句可以切换UNDO表空间为新的UNDO表空间

SQL> alter system set undo_tablespace=undotbs3

但为了尽量减小生产影响，当前还有一个节点2在运行，所以直接在节点1上把实例1 down掉。

SQL> shutdown immediate;

修改参数文件
[oracle@postdb1 pfile]$ vi init.ora.8222008182349
。。。。。
###########################################
undo_management=AUTO

###########################################
# Cluster Database
###########################################
cluster_database_instances=2
remote_listener=LISTENERS_POSTDB

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/opt/app/oracle/admin/postdb/bdump
core_dump_dest=/opt/app/oracle/admin/postdb/cdump
user_dump_dest=/opt/app/oracle/admin/postdb/udump

###########################################
# Processes and Sessions
###########################################
processes=500
sessions=555

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16

control_files=("+DATA1/postdb/controlfile/current.256.666122845", "+FLASH_RECOVERY/postdb/controlfile/current.256.666122845")
cluster_database=true
postdb2.instance_number=2
postdb1.instance_number=1
postdb2.thread=2
postdb2.undo_tablespace=UNDOTBS2
postdb1.undo_tablespace=UNDOTBS3 #该为新创建的表空间
postdb1.thread=1

通过指定初始化参数文件启动数据库

[oracle@postdb1 pfile]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 30 14:50:47 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> startup pfile='/opt/app/oracle/admin/postdb/pfile/init.ora.8222008182349'
ORACLE instance started.

Total System Global Area 133765984 bytes
Fixed Size                   453472 bytes
Variable Size              79691776 bytes
Database Buffers           50331648 bytes
Redo Buffers                3289088 bytes
Database mounted.
Database opened.

检查undo参数信息，已经可以看到undotbs3已经起作用了
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS3

删除undotbs1数据文件

SQL> drop tablespace undotbs1 INCLUDING CONTENTS;

drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU5$' found, terminate
dropping
tablespace

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU4$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU5$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU6$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU7$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU8$                      UNDOTBS1                       NEEDS RECOVERY
_SYSSMU9$                      UNDOTBS1                       OFFLINE
_SYSSMU10$                     UNDOTBS1                       OFFLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$                     UNDOTBS2                       ONLINE
_SYSSMU12$                     UNDOTBS2                       ONLINE
_SYSSMU13$                     UNDOTBS2                       ONLINE
_SYSSMU14$                     UNDOTBS2                       ONLINE
_SYSSMU15$                     UNDOTBS2                       ONLINE
_SYSSMU16$                     UNDOTBS2                       ONLINE
_SYSSMU17$                     UNDOTBS2                       ONLINE
_SYSSMU18$                     UNDOTBS2                       ONLINE
_SYSSMU19$                     UNDOTBS2                       ONLINE
_SYSSMU20$                     UNDOTBS2                       ONLINE
_SYSSMU31$                     UNDOTBS2                       OFFLINE
_SYSSMU30$                     UNDOTBS2                       OFFLINE
------------------------------ ------------------------------ ----------------
_SYSSMU57$                     UNDOTBS3                       ONLINE
_SYSSMU58$                     UNDOTBS3                       ONLINE
_SYSSMU59$                     UNDOTBS3                       ONLINE
_SYSSMU60$                     UNDOTBS3                       ONLINE
_SYSSMU61$                     UNDOTBS3                       ONLINE
_SYSSMU62$                     UNDOTBS3                       ONLINE
_SYSSMU63$                     UNDOTBS3                       ONLINE
_SYSSMU64$                     UNDOTBS3                       ONLINE

再此修改初始化参数文件

[oracle@postdb1 pfile]$ vi init.ora.8222008182349

。。。。。。
processes=500
sessions=555

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16

SQL> shutdown immediate;

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU4$                      UNDOTBS1                       OFFLINE
_SYSSMU5$                      UNDOTBS1                       OFFLINE
_SYSSMU6$                      UNDOTBS1                       OFFLINE
_SYSSMU7$                      UNDOTBS1                       OFFLINE
_SYSSMU8$                      UNDOTBS1                       OFFLINE
_SYSSMU9$                      UNDOTBS1                       OFFLINE
_SYSSMU10$                     UNDOTBS1                       OFFLINE

删除UNDOTBS1表空间
SQL> drop tablespace undotbs1 INCLUDING CONTENTS;
Tablespace dropped.

创建UNDOTBS1表空间

SQL> create undo tablespace undotbs1 datafile '+DATA1' size 4000M;
Tablespace created.

用同样的方法修改初始化参数文件为原来加载回滚段标空间名字

[oracle@postdb1 pfile]$ vi init.ora.8222008182349
。。。。。
###########################################
undo_management=AUTO

###########################################
# Cluster Database
###########################################
cluster_database_instances=2
remote_listener=LISTENERS_POSTDB

###########################################
# Processes and Sessions
###########################################
processes=500
sessions=555

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16

再次重启postdb1实例
SQL> SQL> shutdown immediate;

检查回滚表空间
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU2$                      UNDOTBS1                       ONLINE
_SYSSMU3$                      UNDOTBS1                       ONLINE
_SYSSMU4$                      UNDOTBS1                       ONLINE
_SYSSMU5$                      UNDOTBS1                       ONLINE
_SYSSMU6$                      UNDOTBS1                       ONLINE
_SYSSMU7$                      UNDOTBS1                       ONLINE
_SYSSMU8$                      UNDOTBS1                       ONLINE
_SYSSMU9$                      UNDOTBS1                       ONLINE
_SYSSMU10$                     UNDOTBS1                       ONLINE

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$                     UNDOTBS2                       ONLINE
_SYSSMU12$                     UNDOTBS2                       ONLINE
_SYSSMU13$                     UNDOTBS2                       ONLINE
_SYSSMU14$                     UNDOTBS2                       ONLINE
_SYSSMU15$                     UNDOTBS2                       ONLINE
_SYSSMU16$                     UNDOTBS2                       ONLINE
_SYSSMU17$                     UNDOTBS2                       ONLINE
_SYSSMU18$                     UNDOTBS2                       ONLINE
_SYSSMU19$                     UNDOTBS2                       ONLINE
_SYSSMU20$                     UNDOTBS2                       ONLINE
_SYSSMU31$                     UNDOTBS2                       OFFLINE
_SYSSMU30$                     UNDOTBS2                       OFFLINE
------------------------------ ------------------------------ ----------------
_SYSSMU57$                     UNDOTBS3                       OFFLINE
_SYSSMU58$                     UNDOTBS3                       OFFLINE
_SYSSMU59$                     UNDOTBS3                       OFFLINE
_SYSSMU60$                     UNDOTBS3                       OFFLINE
_SYSSMU61$                     UNDOTBS3                       OFFLINE
_SYSSMU62$                     UNDOTBS3                       OFFLINE
_SYSSMU63$                     UNDOTBS3                       OFFLINE
_SYSSMU64$                     UNDOTBS3                       OFFLINE

检查undo参数信息，已经可以看到undotbs1已经起作用了
SQL> show parameter undo