环境：RHEL 6.4 + Oracle 11.2.0.4
背景：备份恢复的测试库在一次不完全恢复后，没有来及做有效的全备，又一次数据库故障导致数据库无法正常open。
只能离线部分数据文件打开数据库，其中包含undo表空间数据文件。
适用场景：无有效备份，可以丢失数据，删除回滚段状态为NEEDS RECOVERY的undo表空间。
一、数据库当前情况
1.1 故障现象
1.2 查看数据文件的状态
1.3 尝试online数据文件失败
二、删除损坏数据文件所在表空间
2.1 普通数据文件4所在的users表空间可以直接删除
2.2 undo数据文件3所在的undotbs1表空间尝试删除
2.3 undo数据文件3所在的undotbs1表空间删除方法
一、数据库当前情况
1.1 故障现象
open resetlogs 打开数据库报错ORA-01152，ORA-01110，将报错的数据文件offline，先打开数据库。
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 3 was not restored from a sufficiently old backup
ORA-01110: data file 3:
'/u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf'
SQL> alter database datafile 3 offline;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 4 was not restored from a sufficiently old backup
ORA-01110: data file 4: '/u02/oracle/JINGYU/datafile/o1_mf_users_bwp1b12d_.dbf'
SQL> alter database datafile 4 offline;
Database altered.
SQL> alter database open resetlogs;
Database altered.
1.2 查看数据文件的状态
数据文件3，4OFFLINE。其中数据文件3是undo表空间的数据文件。
SQL> set linesize 150
SQL> col file_name for a56
SQL> select FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "MB", MAXBYTES/1024/1024/1024 "GB", AUTOEXTENSIBLE, STATUS, ONLINE_STATUS from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME MB GB AUT STATUS ONLINE_
---------- -------------------------------------------------------- ------------------------------ ---------- ---------- --- --------- -------
1 /u02/oracle/JINGYU/datafile/o1_mf_system_bwp198r7_.dbf SYSTEM 700 31.9999847 YES AVAILABLE SYSTEM
2 /u02/oracle/JINGYU/datafile/o1_mf_sysaux_bwp19hl8_.dbf SYSAUX 600 31.9999847 YES AVAILABLE ONLINE
3 /u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf UNDOTBS1 AVAILABLE OFFLINE
4 /u02/oracle/JINGYU/datafile/o1_mf_users_bwp1b12d_.dbf USERS AVAILABLE OFFLINE
5 /u02/oracle/JINGYU/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_JINGYU 100 31.9999847 YES AVAILABLE ONLINE
1.3 尝试online数据文件失败
SQL> alter database datafile 3 online;
alter database datafile 3 online
*
ERROR at line 1:
ORA-01190: control file or data file 3 is from before the last RESETLOGS
ORA-01110: data file 3: '/u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf'
SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS
ORA-01110: data file 4: '/u02/oracle/JINGYU/datafile/o1_mf_users_bwp1b12d_.dbf'
二、删除损坏数据文件所在表空间
2.1 普通数据文件4所在的users表空间可以直接删除
SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
SQL> alter database default tablespace DBS_D_JINGYU;
Database altered.
SQL> drop tablespace users including contents and datafiles;
Tablespace dropped.
SQL> select FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "MB", MAXBYTES/1024/1024/1024 "GB", AUTOEXTENSIBLE, STATUS, ONLINE_STATUS from dba_data_files;