备份恢复的测试库在一次不完全恢复后，没有来及做有效的全备，又一次数据库故障导致数据库无法正常open。只能离线部分数据文件打
环境：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;
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
5 /u02/oracle/jingyu/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf dbs_d_jingyu 100 31.9999847 yes available online
2.2 undo数据文件3所在的undotbs1表空间尝试删除
尝试删除直接报错ora-01548.同时无法正常关闭数据库，无法删除活动的回滚段。具体如下：
2.2.1 创建新的undo表空间undotbs2并设置为默认的undo表空间
sql> create undo tablespace undotbs2;
tablespace created.
sql> show parameter undo