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

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

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:

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 created

SQL> shutdown

Database 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;

     File Created

SQL>startup;

ORACLE instance started

Total System Global Area  101784276 bytes

Fixed Size                   453332 bytes

Variable Size              75497472 bytes

Database Buffers           25165824 bytes

Redo Buffers                 667648 bytes

Alter database mount

Alter database open

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> create UNDO tablespace undotbs1

datafile '/orasys/oracle/oradata/orasvr2/undotbs01.dbf' size 

250m  autoextend on next 1m maxsize 1024m;

Tablespace created.

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

System altered.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

把加入的这段参数去掉：_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