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

备份恢复的测试库在一次不完全恢复后，没有来及做有效的全备，又一次数据库故障导致数据库无法正常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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！