Oracle数据库回滚段表空间数据文件损坏解决方法

症状：回滚表空间数据文件显示脱机，需要恢复,数据文件已经损坏,但回滚表空间联机正常,发出命令使数据文件联机时,出现以下错误

ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/orasys/oracle/oradata/orasvr2/undotbs01.dbf'

查看系统回滚段的情况
SELECT segment_name,tablespace_name,owner,status FROM dba_rollback_segs;

结果: _SYSSMU1$ 到 _SYSSMU10$ 的状态都是 "NEED RECOVERY"

解决过程：
-bash-2.05b$ sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Aug 1 15:41:28 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

-bash-2.05b$ ls
control01.ctl indx01.dbf temp01.dbf users01.dbf
drsys01.dbf redo01.log tools01.dbf xdb01.dbf
example01.dbf system01.dbf undotbS01.dbf

-bash-2.05b$ mv undotbs01.dbf undotbs01.dbf.xxx

-bash-2.05b$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Aug 1 15:41:28 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 143725064 bytes
Fixed Size 451080 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/orasys/oracle/oradata/orasvr2/undotbs01.dbf'

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1

SQL> alter system set undo_management = manual scope=spfile;

System altered.

SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

-bash-2.05b$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Aug 1 15:41:28 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2:

SQL> alter database datafile '/orasys/oracle/oradata/orasvr2/undotbs01.dbf' offline drop;
Database altered.

SQL> alter database open;
Database altered.

SQL> drop tablespace undotbs1;
ORA-01548: active rollback segment '_SYSSMU1$' found, terminate dropping tablespace
发现有活动的事物还在undo表空间,不能删除undo表空间

SQL> create pfile from spfile;File createdSQL> shutdownDatabase closed.
Database dismounted.
ORACLE instance shut down.

SQL> 编辑initorasvr2.ORA 参数
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

SQL>create spfile from pfile;

SQL>startup;

SQL> drop tablespace undotbs1;

SQL> create UNDO tablespace undotbs1datafile '/orasys/oracle/oradata/orasvr2/undotbs01.dbf' size 250m autoextend on next 1m maxsize 1024m;

SQL> alter system set undo_management = auto scope=spfile;
System altered.

SQL> shutdown

把加入的这段参数去掉：
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
重新启动数据库后即可
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE_SYSSMU1$ ONLINE_SYSSMU2$ ONLINE_SYSSMU3$ ONLINE_SYSSMU4$ ONLINE_SYSSMU5$ ONLINE_SYSSMU6$ ONLINE_SYSSMU7$ ONLINE_SYSSMU8$ ONLINE_SYSSMU9$ ONLINE_SYSSMU10$ ONLINE 11 rows selected