一、数据库当前情况

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;