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

最新推荐文章于 2024-08-29 08:36:21 发布

蚁库

最新推荐文章于 2024-08-29 08:36:21 发布

阅读量183

点赞数

文章标签：数据库 oracle dba

本文链接：https://blog.csdn.net/weixin_52805903/article/details/125990468

版权

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

摘要由CSDN通过智能技术生成

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

一、查看数据文件的状态

数据文件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 RECOVER