UNDO和普通表空间损坏后的处理方案

最新推荐文章于 2025-08-31 15:06:25 发布

原创最新推荐文章于 2025-08-31 15:06:25 发布 · 262 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle #dba

当Oracle数据库的UNDO和普通表空间损坏时，可以通过一系列步骤来处理。首先，查看数据文件状态，尝试将OFFLINE状态的表空间置为ONLINE，但可能遇到错误。接着，可以尝试删除损坏的表空间，但必须先更改默认表空间。如果删除失败，可能是因为回滚段仍有活动，需要检查并删除NEEDS RECOVERY的回滚段。通过修改初始化参数文件(pfile)，跳过无法删除的回滚段，然后启动数据库。最后，删除回滚段和损坏的表空间，确保所有数据文件恢复正常状态。

表空间损坏后，导致整个数据库运行报错的情况。如何处理损坏的表空间？

一、查看数据文件的状态

数据文件3，4 两个表空间是OFFLINE状态。其中数据文件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

二、尝试把OFFLINE状态的表空间置为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’

注：上述可以看到，不管是undo表空间，还是普通表空间都无法启动。

三、尝试先把普通数据文件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

注：先将默认表空间更改好，再删除损坏的表空间。特殊注意：原有表空间的数据会丢失，通过备份数据进行数据恢复

四、按上述方法，先把默认的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

五、删除旧的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

六、查看回滚段的状态（上述不能删除，说明回滚段还有待恢复的数据，确定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.

七、此时正常关库会提示失败
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’
八、此时删除回滚段也会提示失败
目前数据库无有效备份，需要把这些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

九、通过修改pfile文件，跳过这几个待恢复的回滚段的数据
SQL> create pfile=‘/tmp/pfile.ora’;
在pfile.ora文件中增加如下内容：
其中_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’
十、使用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.
十一、删除回滚段成功
SQL> drop rollback segment “_SYSSMU10_1695440836$”;

Rollback segment dropped.
十二、删除回滚表空间undotbs1成功
SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.
十三、此时查询数据库的相关信息
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.
十四、以spfile正常启动数据库，检查一切正常
SQL> startup
ORACLE instance started.

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