UNDOTBS01.DBF被我删掉了，noarchivelog,没有备份，数据库启动不起来了，有办法恢复么？

[xyzhh]

同上，数据库不open，我没办法新建一个undotbs.

[bzszp]

参考
http://community.csdn.net/Expert/topic/4176/4176039.xml?temp=.4484217

[black_snail]

如果我没有记错
可以采用如下方法
先mount,然后把UNDOTBS01.DBF所在的tablespace , offline,
然后再alter database open....

[xyzhh]

原来的已经offline drop 了，但是现在没有可用的undotbs,所以数据库没办法open

[microd]

重新建一个UNDOTBS01.DBF',然后还要重新建一下控制文件。再启动就可以了。

[acmly]

晕死，这个问题我也碰到过，还好当时没数据我就随即将数据库删除重建了：（不知道楼上的方法可以否，请楼主搞定了说一声：）多谢

[liuyi8903]

参照这个恢复过程试一下：
SQL> shutdown abort
ORACLE instance shut down.

C:\Documents and Settings\lilixin->sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jul 1 17:30:41 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/new as sysdba
Connected.


SQL> alter database datafile 2 offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


SQL> create pfile='c:\pfilenew.ora' from spfile;
create pfile='c:\pfilenew.ora' from spfile
*
ERROR at line 1:
ORA-03114: not connected to ORACLE


SQL> connect sys/new as sysdba
Connected to an idle instance.
SQL> startup restrict mount;
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> create pfile='c:\pnewfile.ora' from spfile;

File created.


修改c:\ pnewfile.ora，添加隐含参数：
*._corrupted_rollback_segment = (_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)





SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup restrict pfile='c:\pnewfile.ora';
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

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

11 rows selected.

SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


SQL> drop rollback segment _SYSSMU1$;
drop rollback segment _SYSSMU1$
*
ERROR at line 1:
ORA-00911: invalid character

要加双引号

SQL> drop rollback segment "_SYSSMU1$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU2$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU3$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU4$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU5$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU6$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU7$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU8$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU9$";

Rollback segment dropped.

SQL> drop rollback segment "_SYSSMU10$";

Rollback segment dropped.

SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use


SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE


SQL> CREATE UNDO TABLESPACE UNDOTBS DATAFILE 'C:\oracle\oradata\NEW\undotbs.db
' SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

Tablespace created.

SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use



SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 7200
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> ALTER SYSTEM SET undo_tablespace=UNDOTBS scope=both;
ALTER SYSTEM SET undo_tablespace=UNDOTBS scope=both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup


SQL> ALTER SYSTEM SET undo_tablespace=UNDOTBS scope=memory;

System altered.

SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

[xyzhh]

使用alter database create datafile 'D:\ORACLE\ORADATA\XYZ\UNDOTBS01.DBF'会提示
ERROR 位于第 1 行:
ORA-01178: 文件 2 在最后一个 CREATE CONTROLFILE 之前创建，无法重新创建
ORA-01110: 数据文件 2: 'D:\ORACLE\ORADATA\XYZ\UNDOTBS01.DBF'


如果另建一个新的表空间并把undo_tablespace指过去的话，会提示类型不对。

[xyzhh]

应该是_corrupted_rollback_segments
尽管过程不大一样，可帮了我的大忙了。

[sunbin6ji]

mark

[doitnow2000]

不错不错

[sanoul]

mark & up