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


    环境:RHEL 6.4 + Oracle 11.2.0.4
    背景:备份恢复的测试库在一次不完全恢复后,没有来及做有效的全备,又一次数据库故障导致数据库无法正常open。
    只能离线部分数据文件打开数据库,其中包含undo表空间数据文件。
    适用场景:无有效备份,可以丢失数据,删除回滚段状态为NEEDS RECOVERY的undo表空间。

    一、数据库当前情况

    二、删除损坏数据文件所在表空间

    一、数据库当前情况

    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.

    
    <h2 id="1.2">1.2 查看数据文件的状态</h2>
    数据文件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
    
    
    <h2 id="1.3">1.3 尝试online数据文件失败</h2>
    

    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'

    
    <h1 id="2"> 二、删除损坏数据文件所在表空间</h1>
    <h2 id="2.1">2.1 普通数据文件4所在的users表空间可以直接删除</h2>
    

    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
    
    
    <h2 id="2.2">2.2 undo数据文件3所在的undotbs1表空间尝试删除</h2>
    尝试删除直接报错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

    
    <h2 id="2.3">2.3 undo数据文件3所在的undotbs1表空间删除方法</h2>
    ### 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
    
    最后,立即对当前恢复好的数据库做一个全备吧。
  • 相关阅读:
    如何在vue项目中使用svg图标
    Failed to connect to github.com port 443 after 21035 ms: Timed out
    通过二次封装dialog深入理解自定义双向绑定是怎么回事
    eltable根据屏幕大小适配最大高度
    Java lambda 笔记
    Oracle使用regexp_substr函数实现 列转行
    php获取日期时间等
    1227 Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for operation
    git pull 每次都要输出merge合并信息
    Nacos安装步骤windows
  • 原文地址:https://www.cnblogs.com/jyzhao/p/4726835.html
Copyright © 2020-2023  润新知