如何删除回滚段状态为NEEDS RECOVERY的undo表空间

环境：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 ONLINE1.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 tablespaceSQL> 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 ONLINE2.2 undo数据文件3所在的undotbs1表空间尝试删除尝试删除直接报错ORA-01548.同时无法正常关闭数据库，无法删除活动的回滚段。具体如下：2.2.1 创建新的undo表空间undotbs2并设置为默认的undo表空间
SQL> create undo tablespace undotbs2; Tablespace created.SQL> show parameter undoNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1SQL> alter system set undo_tablespace="undotbs2";System altered.SQL> show parameter undoNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs22.2.2 删除旧的undotbs1表空间失败
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment "_SYSSMU1_1401565358$" found, terminate dropping tablespace2.2.3 查看回滚段的状态，确定undotbs1表空间的回滚段状态都是NEEDS RECOVERY
SQL> select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in （"ONLINE","OFFLINE"）;SEGMENT_ID SEGMENT_NAME STATUS TABLESPACE_NAME
---------- ------------------------------ ---------------- ------------------------------
1 _SYSSMU1_1401565358$ NEEDS RECOVERY UNDOTBS1
2 _SYSSMU2_3125365238$ NEEDS RECOVERY UNDOTBS1
3 _SYSSMU3_1538315859$ NEEDS RECOVERY UNDOTBS1
4 _SYSSMU4_1640924022$ NEEDS RECOVERY UNDOTBS1
5 _SYSSMU5_2892967416$ NEEDS RECOVERY UNDOTBS1
6 _SYSSMU6_3276341082$ NEEDS RECOVERY UNDOTBS1
7 _SYSSMU7_387283697$ NEEDS RECOVERY UNDOTBS1
8 _SYSSMU8_2299136685$ NEEDS RECOVERY UNDOTBS1
9 _SYSSMU9_909303715$ NEEDS RECOVERY UNDOTBS1
10 _SYSSMU10_1695440836$ NEEDS RECOVERY UNDOTBS110 rows selected.2.2.4 此时正常关库会提示失败
SQL> select file#,status from v$datafile; FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 OFFLINE
4 ONLINE
5 ONLINESQL> shutdown immediate;
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: "/u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf"2.2.5 此时删除回滚段也会提示失败目前数据库无有效备份，需要把这些NEEDS RECOVERY的undo rollback segs删除
SQL> drop rollback segment "_SYSSMU10_1695440836$";
drop rollback segment "_SYSSMU10_1695440836$"
*
ERROR at line 1:
ORA-30025: DROP segment "_SYSSMU10_1695440836$" （in undo tablespace） not allowed2.3 undo数据文件3所在的undotbs1表空间删除方法2.3.1 修改pfile文件这几行内容其中_offline_rollback_segments参数中的回滚段的名字在上面查到过。
*.undo_management="MANUAL"
*._offline_rollback_segments=（_SYSSMU1_1401565358$,_SYSSMU2_3125365238$,_SYSSMU3_1538315859$,_SYSSMU4_1640924022$,_SYSSMU5_2892967416$,_SYSSMU6_3276341082$,_SYSSMU7_387283697$,_SYSSMU8_2299136685$,_SYSSMU9_909303715$,_SYSSMU10_1695440836$）
#*.undo_tablespace="undotbs2"2.3.2 使用pfile文件启动数据库
SQL> startup pfile="/tmp/pfile.ora";
ORACLE instance started.Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
Database opened.2.3.3 删除回滚段成功
SQL> drop rollback segment "_SYSSMU10_1695440836$";Rollback segment dropped.2.3.4 删除回滚表空间undotbs1成功
SQL> drop tablespace undotbs1 including contents and datafiles;Tablespace dropped.2.3.5 此时查询数据库的相关信息
SQL> select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in （"ONLINE","OFFLINE"）
2 ;no rows selectedSQL> 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
4 /u02/oracle/JINGYU/datafile/o1_mf_undotbs2_bwqxbnxo_.dbf UNDOTBS2 100 31.9999847 YES AVAILABLE ONLINE
5 /u02/oracle/JINGYU/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_JINGYU 100 31.9999847 YES AVAILABLE ONLINE发现此时一切数据文件正常,此时已经可以正常关库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.2.3.6 以spfile正常启动数据库，检查一切正常
SQL> startup
ORACLE instance started.Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
Database opened.
SQL> show parameter undoNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs2
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
4 /u02/oracle/JINGYU/datafile/o1_mf_undotbs2_bwqxbnxo_.dbf UNDOTBS2 100 31.9999847 YES AVAILABLE ONLINE
5 /u02/oracle/JINGYU/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_JINGYU 100 31.9999847 YES AVAILABLE ONLINE最后，立即对当前恢复好的数据库做一个全备吧。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址