请看是否可行：UNDO表空间数据文件被误删的处理

rainyday

不小心误删了UNDO表空间的数据文件UNDOTBS01.DBF，我想采用以下步骤处理，各位请看看是否可行：

SQL>;startup mount
SQL>;alter database datafile '/../UNDOTBS01.DBF' offline;
SQL>;alter database open;
SQL>;Create undo tablespace UNDOTBS02  datafile '/../UNDOTBS02.DBF' size 100m;
SQL>; alter system set undo_tablespace=undotbs2;
SQL>; drop tablespace test including contents and datafiles;
然后在INIT文件里也修改UNOD参数：
       undo_tablespace=UNDOTBS02

请问这样是否可行？？？

txfy

If the datafile lost is Rollback related datafile with active transactions, restore from the previous backup and start the database.   
If the datafile contains rollback with no active rollback segments, you can offline the datafile (after commenting the rollback_segments parameter        assuming that they are private rollback segments) and open the database

rainyday

我后来是这样处理的：
SQL>; alter system set undo_management = manual scope=spfile;
SQL>; shutdown immediate
SQL>; startup
SQL>;create pfile=/../init.ora from spfile;
SQL>; drop tablespace UNDOTBS1;提示说有active的rollback，不能drop
SQL>; shutdown imediate
在init.ora中加上如下两行:
   rollback_segments=(system)
   _corrupted_rollback_segments=(_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
SQL>; startup pfile=/../init.ora
SQL>; drop tablespace UNDOTBS1;
SQL>; create undo tablespace UNDOTBS1 create UNDO tablespace undotbs1 datafile /../undotbs01.dbf size xxxxM;
SQL>; alter system set undo_management = auto scope=spfile;
SQL>; shutdown immediate
SQL>; startup

sshd

find out if the undo tablespace has active transactions

select SEGMENT_NAME, SEGMENT_ID, TABLESPACE_NAME from dba_rollback_segs
where SEGMENT_ID in (select XIDUSN from v$transaction);

if the undo tablespace has active transactions, you will HAVE to restore the whole database (no archive log mode), or restore the backup of the dropped datafile and recover it (archive log mode).

that's the only way to make your database consistent.