安科网

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

myinfinity

myinfinity

2015-09-02

关注 关注

环境：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

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                      integer    900
undo_tablespace                      string      UNDOTBS1

SQL> alter system set undo_tablespace='undotbs2';

System altered.

SQL> show parameter undo

NAME                                TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                      integer    900
undo_tablespace                      string      undotbs2

2.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 tablespace

2.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  UNDOTBS1

10 rows selected.

2.2.4 此时正常关库会提示失败
SQL> select file#,status from v$datafile;

    FILE# STATUS
---------- -------
        1 SYSTEM
        2 ONLINE
        3 OFFLINE
        4 ONLINE
        5 ONLINE

SQL> 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 allowed

2.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 selected

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

发现此时一切数据文件正常,此时已经可以正常关库
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 undo

NAME                                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

最后，立即对当前恢复好的数据库做一个全备吧。

表空间 undo 数据库 recovery

myinfinity

myinfinity

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

MySQL中的undo日志

我们知道，MySQL中的redo日志记录了事务的行为，在服务器宕机的时候，可以通过重做事务来达到恢复数据的目的，然而，有的时候，事务还有回滚的需求，也就是说，我们需要知道某条在变成当前情况之前的样子，这种情况下，undo日志就派上用场了。也就是说，undo

spurity 2020-11-10

undo表空间概述-1

　　与其他表空间的段不同的是：undo表空间的undo段是自动生成且自动维护的，除此之外，oracle会自动使用undo表空间中的undo段。　　从某种意义上讲，只需要确保undo表空间的大小就可以了。　　　　select * from v$rollnam

Streamoutput 2019-12-31

Orcle11G创建表空间，创建用户，授权等操作

CREATE USER caiyl IDENTIFIED BY 123456 DEFAULT TABLESPACE caiyl_space;GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,A

赵继业 2020-08-17

Oracle和MySQL的数据导入为何差别这么大

经常会有一些朋友咨询我一些数据库的问题，我注意到一个很有意思的现象，凡是数据导入的问题，基本上都是Oracle类的，MySQL类的问题脑子里想了下竟然一次都没有。我们来梳理一下这个问题，分别从导出导入的方式来聊聊。导出有哪些工具呢，主要有exp，expdp

bfcady 2020-08-16

Oracle数据库的实例/表空间/用户/表之间关系简单讲解

完整的Oracle数据库通常由两部分组成：Oracle数据库和数据库实例。1) 数据库是一系列物理文件的集合；2) Oracle数据库实例则是一组Oracle后台进程/线程以及在服务器分配的共享内存区。Oracle有一个很大的内存快，成为全局区。Oracl

Hody 2020-08-16

浅谈入门级oracle数据库数据导入导出步骤

Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表。表空间是数据库的逻辑划分，每个数据库至少有一个表空间。

FightFourEggs 2020-08-16

Oracle如何设置表空间数据文件大小

Oracle数据文件默认大小上限是32G，如果要数据文件大于32G，需要在数据库创建之初就设置好。在windows下只能使用2K，4K，8K，16K的块大小，在文档中的描述如下。前一段说明了某些块大小在某些平台上是不可用的，具体情况受操作系统限制。比如wi

lidongliang 2020-08-16

Oracle期末复习

数据库是存储数据的多个物理文件的集合，如控制文件，数据文件，参数文件，日志文件，临时文件等 ，它是静态的、永久的，只要文件存在它就存在。数据库名就是对数据库的标识。就是由内存空间和工作进程两部分组成。容器数据库是Oracle 12c引入的新特性，允许容器数

流云追风 2020-07-04

Oracle 导入数据库dmp文件

场景：windows2008 R2系统 ，往新安装的oracle11g数据库导入同事给的dmp文件到指定的新建的用户。在导入dmp文件之前，先打开查看dmp文件的表空间名称。使用数据库dba用户创建用户并赋予相应的导入导出等权限。个人试过几次，最后选择命令

dataminer 2020-06-25

Oracle使用Impdp导入dmp文件的详细过程

DATA_PUMP_DIR 所指向的路径不一致。

娜娜 2020-06-14

06-表的操作

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等。数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。而My

Kele0 2020-06-13

Linux平台达梦数据库V7之误删除数据文件的恢复

LINUX操作系统中，被进程打开的文件仍可以在 OS系统中被删除，因此存在 DM7数据文件可能被误删的风险。如果数据文件被删除，DM7系统能够及时检测出来，并立刻停止对其继续使用并通知用户。在 dm.ini 中参数 FIL_CHECK_INTERVAL 的

一次次尝试 2020-06-13

oracle表空间不足：ORA-01653: unable to extend table

oracle表空间不足报错是比较常见的故障，尤其是没有对剩余表空间做定期巡检的系统；oracle表空间不足错误代码：ORA-01653: unable to extend table ；上述代码可以查询中数据库有哪些表空间，总空间大小，剩余空间大小以及使用

warden00 2020-06-11

mysql面试~经典题目

重做日志记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。4 双写带来的问题 1 会导致系统有更多的fsync操作, 而硬盘的fsync性能是很慢的, 所以它会降低mysql的整体性能 5 为什么日志不需要双写 1　因为re

阿亮 2020-06-10

docker, oracle创建空间表，创建数据库。

docker exec -it xxxxxxxx /bin/bash ## xxxxxxx就是上一部查看的id号。source ~/.bash_profile ## 加载一下用户环境变量，进入

zhangchaoming 2020-06-04

oracle 表空间自动扩展

修改某数据文件为不限制大小ALTER DATABASE DATAFILE ‘D:\oracle_data\xxx.DBF‘ AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;增加新的数据文件alert tablespace

FightFourEggs 2020-06-02

Oracle性能优化

压力测试或者业务刚刚上线时，通过修改应用框架或者大规模的优化SQL语句进行改进，耗时最长，难度也最大。进入生产环境，随着业务的并发量、数据量的增加出现，通过增加硬件性能或存储，优化难度一般，但代价比较高。需要数据库管理员快速定位问题，提出解决方案。优化难度

Carlos 2020-05-31

Oracle 建用户、建表空间、创建表

SELECT T.TABLE_NAME AS 数据表名称 ,F.COMMENTS AS 表说明 FROM USER_TABLES T INNER JOIN USER_TAB_COMMENTS F ON T.TABLE_NAME = F.TABLE_NAM

FightFourEggs 2020-05-28

mysql innodb 文件

通过命令 show variables [like ‘abc‘\G];show variables like ‘log_error‘\G; //错误日志 默认为 主机名.err. show variables like ‘long_query_time‘\

cyhgogogo 2020-05-27

Linux平台达梦数据库V7之表空间管理

从上图可以看出其关系：数据库--?block 簇：数据库申请空间的最小逻辑单位页：数据库存储数据的最小逻辑单位。表空间的管理操作需要 DM 服务器处于打开状态下。<表空间名> 表空间的名称，表空间名称最大长度 128 字节；数据文件的存放路径符

大慧 2020-05-27
myinfinity

myinfinity

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号