[紧急求助]01092错误，datafile一直处于recover状态！

gamesay

Crash recovery completed successfully
Sat May 16 04:55:01 2009
Thread 1 advanced to log sequence 11258
Thread 1 opened at log sequence 11258
  Current log# 3 seq# 11258 mem# 0: Y:\ORADATA\CALS\REDO03_1.LOG
  Current log# 3 seq# 11258 mem# 1: Y:\ORADATA\CALS\REDO03_2.LOG
  Current log# 3 seq# 11258 mem# 2: Y:\ORADATA\CALS\REDO03_3.LOG
Successful open of redo thread 1.
Sat May 16 04:55:02 2009
SMON: enabling cache recovery
SMON: enabling tx recovery
SMON: about to recover undo segment 23
SMON: mark undo segment 23 as needs recovery
SMON: about to recover undo segment 24
SMON: mark undo segment 24 as needs recovery
SMON: about to recover undo segment 25
SMON: mark undo segment 25 as needs recovery
SMON: about to recover undo segment 30
SMON: mark undo segment 30 as needs recovery
SMON: about to recover undo segment 23
SMON: mark undo segment 23 as needs recovery
SMON: about to recover undo segment 24
SMON: mark undo segment 24 as needs recovery
SMON: about to recover undo segment 25
SMON: mark undo segment 25 as needs recovery
SMON: about to recover undo segment 30
SMON: mark undo segment 30 as needs recovery
Sat May 16 04:55:05 2009
Errors in file d:\oracle\admin\cals\udump\ORA02456.TRC:
ORA-00376: 此时无法读取文件 63
ORA-01110: 数据文件 63: 'Y:\ORADATA\CALS\UNDOTBS10.DBF'

Sat May 16 04:55:05 2009
Error 376 happened during db open, shutting down database
USER: terminating instance due to error 376
Instance terminated by USER, pid = 2456
ORA-1092 signalled during: alter database open...



原因是因为imp后undo空间占用将近4个G，服务器特别慢，就新建了一个UNDO表空间undotbs2并用drop tablespace undotbs including contents 删除原undo表空间，不成功，一直报使用中，又做datafile offline，结果还是drop不到，重启服务器后，一直无法打开数据库。请高手指点!

[ 本帖最后由 gamesay 于 2009-5-16 05:18 编辑 ]

freebarque

SQL> alter system set undo_management = manual scope=both
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> alter database datafile  'YOUR/UNDO/PATH/FILE.dbf'  offline drop;
SQL> ALTER DATABASE OPEN;
SQL> DROP TABLESPACE UNDOTBS1;
SQL> create UNDO tablespace undotbs1 DATAFILE 'YOUR/NEW/UNDO/FILE.DBF'  SIZE=XXXM AUTOEXTENT ON ;
SQL> alter system set undo_management = AUTO SCOPE=spfile;
SQL> SHUTDOWN IMMEDIATE;
SQL>  STARTUP;

gamesay

SQL> alter database datafile  'y:\oradata\undotbs10.dbf'  offline drop;
SQL> ALTER DATABASE OPEN;
还是同样报错ora-00376，为什么这个东西删除不掉呢?
undo_management = manual 也设为手动了呀。

guoq.lee

在init.ora中增加隐含参数_corrupted_rollback_segments,然后再用pfile启动数据库,再次drop掉表空间undotbs10
然后再用spfile重启数据库