undo表空间被误删解决办法

·         启动oracle时报错，datafile找不到，文件系统该文件被误删

oracle% sqlplus / as sysdba
SQL> startup
ORACLE instance started.
 
Total System Global Area 3507474432 bytes
Fixed Size                  2164688 bytes
Variable Size            2617247792 bytes
Database Buffers          872415232 bytes
Redo Buffers               15646720 bytes
Database mounted.
ORA-01110: data file 27: '/tellin/undotbs02.dbf'
ORA-01115: IO error reading block from file 27(block # 1)
ORA-27069: attemp to do I/O beyond the range of the file

·         shutdown后，以mount方式重启oracle

SQL> shutdown abort
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup mount
ORACLE instance started.
 
Total System Global Area 3507474432 bytes
Fixed Size                  2164688 bytes
Variable Size            2617247792 bytes
Database Buffers          872415232 bytes
Redo Buffers               15646720 bytes
Database mounted.

·         将datafile修改为offline状态

SQL> alter database datafile '/tellin/undotbs02.dbf' offline drop;
 
Database altered.

·         启动数据库后尝试删除表空间报“currently in use”

SQL> alter database open;
 
Database altered.
 
SQL> drop tablespace undotbs2 including contents and datafiles;
drop tablespace UNDOTBS2 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use

·         检查当前使用的undo表空间为出错的表空间“UNDOTBS2”

SQL> show parameters undo
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

·         修改当前undo表空间为“UNDOTBS1”（如果只有一个undo并且坏了的话就新建一个create tablespace...）

SQL> alter system set undo_tablespace='UNDOTBS1' scope=both;
 
System altered.

·         再尝试删除“UNDOTBS2”，报错有活动的rollback segment

SQL> drop tablespace undotbs2 including contents and datafiles;
drop tablespace UNDOTBS2 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU19_1323759427$' found, teminate dropping tablespace
Cause of The Problem
An attempt was made to drop a tablespace that contains active rollback segment.

·         检查rollback segments，有10条数据

SQL> select SEGMENT_NAME, TABLESPACE_NAME, STATUS from dba_rollback_segs where tablespace_name='UNDOTBS2';
 
 
SEGMENT_NAME                   TABLESPACE_NAME STATUS
------------------------------ --------------- --------------------
_SYSSMU19_1323759427$          UNDOTBS2        NEEDS RECOVERY
_SYSSMU20_1323759427$          UNDOTBS2        NEEDS RECOVERY
_SYSSMU21_1323759427$          UNDOTBS2        NEEDS RECOVERY
_SYSSMU22_1323759427$          UNDOTBS2        NEEDS RECOVERY
_SYSSMU23_1323759427$          UNDOTBS2        NEEDS RECOVERY
_SYSSMU24_1323759427$          UNDOTBS2        NEEDS RECOVERY
_SYSSMU25_1323759427$          UNDOTBS2        NEEDS RECOVERY
_SYSSMU26_1323759427$          UNDOTBS2        NEEDS RECOVERY
_SYSSMU27_1323759427$          UNDOTBS2        NEEDS RECOVERY
_SYSSMU28_1323759427$          UNDOTBS2        NEEDS RECOVERY

·         创建一个新的pfile(create pfile from spfile)，将上述segment_name加到_CORRUPED_ROLLBACK_SEGMENTS参数中

SQL> shutdown abort
 
oracle% vi /oracle/app/admin/ora11g/pfile/init.ora.5122011194851
_CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU19_1323759427$,_SYSSMU20_1323759427$,_SYSSMU21_1323759427$,_SYSSMU22_1323759427$,_SYSSMU23_1323759427$,_SYSSMU24_1323759427$,_SYSSMU25_1323759427$,_SYSSMU26_1323759427$,_SYSSMU27_1323759427$,_SYSSMU28_1323759427$)
 

·         指定pfile启动，再drop表空间就可以了

oracle% sqlplus / as sysdba
SQL> startup pfile='/oracle/app/admin/ora11g/pfile/init.ora.5122011194851'


SQL> drop tablespace undotbs2 including contents and datafiles;
 
Tablespace dropped.
 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area 3507474432 bytes
Fixed Size                  2164688 bytes
Variable Size            2617247792 bytes
Database Buffers          872415232 bytes
Redo Buffers               15646720 bytes
Database mounted.
Database opened.

添加新附件

要向该页面添加新附件，请用下面的框找到文件，然后单击“上传”。
选择文件：
变更注释：