删除包含损坏的数据文件的回滚段表空间

最新推荐文章于 2023-07-31 23:44:50 发布

congchuling5581

最新推荐文章于 2023-07-31 23:44:50 发布

阅读量135

点赞数

文章标签：数据库

删除包含损坏的数据文件的回滚段表空间

-- Offline Drop掉损坏的rbs数据文件,然后尝试打开数据库.

SQL> alter database datafile 2 offline drop;

资料库已被更改

SQL> alter database open;

资料库已被更改

-- 尝试删除回滚段表空间

SQL> drop tablespace rbs including contents;
drop tablespace rbs including contents
*
ERROR 在行 1:
ORA-01548: active rollback segment 'RBS0' found, terminate dropping tablespace

-- 报错ORA-01548,表明有活动回滚段
01548, 00000, "active rollback segment '%s' found, terminate dropping tablespace"
// *Cause: Tried to drop a tablespace that contains active rollback segment(s)
// *Action: Shutdown instances that use the active rollback segments in the
// tablespace and then drop the tablespace

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
RBS0 RBS NEEDS RECOVERY
RBS1 RBS NEEDS RECOVERY
RBS2 RBS NEEDS RECOVERY
RBS3 RBS NEEDS RECOVERY
RBS4 RBS NEEDS RECOVERY
RBS5 RBS NEEDS RECOVERY
RBS6 RBS NEEDS RECOVERY

选取了 8 列

-- 正常关闭数据库加入隐含参数_corrupted_rollback_segments
SQL> shutdown immediate
资料库关闭.
资料库已卸载.
已关闭 ORACLE 执行项次.

-- 修改参数文件加入隐含参数
_corrupted_rollback_segments=(RBS0,RBS1,RBS2,RBS3,RBS4,RBS5,RBS6)

-- 后以restricted模式打开数据库

SQL> startup restrict
已启动 ORACLE 执行项次.

Total System Global Area 219895052 bytes
Fixed Size 70924 bytes
Variable Size 75116544 bytes
Database Buffers 144629760 bytes
Redo Buffers 77824 bytes
资料库已挂载.
资料库已开启.
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

选取了 8 列

SQL> drop tablespace rbs including contents;
drop tablespace rbs including contents
*
ERROR 在行 1:
ORA-01548: active rollback segment 'RBS0' found, terminate dropping tablespace

-- 强制删除所有需要恢复的回滚段

SQL> drop rollback segment rbs0;
倒回区段已被废弃

SQL> drop rollback segment rbs1;
倒回区段已被废弃

SQL> drop rollback segment rbs2;
倒回区段已被废弃

SQL> drop rollback segment rbs3;
倒回区段已被废弃

SQL> drop rollback segment rbs4;
倒回区段已被废弃

SQL> drop rollback segment rbs5;
倒回区段已被废弃

SQL> drop rollback segment rbs6;
倒回区段已被废弃

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE

-- 删除包含损坏的数据文件的回滚段表空间
SQL> drop tablespace rbs including contents;

表格空间已被废弃

-- 重建回滚段表空间及回滚段，并将回滚段Online.
SQL> create tablespace rbs datafile 'd:oracleoradatabccheckrbs02.dbf' size
200M autoextend off;

表格空间已被建立

SQL> create rollback segment rbs0 tablespace rbs;
倒回区段已被建立

SQL> create rollback segment rbs1 tablespace rbs;
倒回区段已被建立

SQL> create rollback segment rbs2 tablespace rbs;
倒回区段已被建立

SQL> create rollback segment rbs3 tablespace rbs;
倒回区段已被建立

SQL> create rollback segment rbs4 tablespace rbs;
倒回区段已被建立

SQL> create rollback segment rbs5 tablespace rbs;
倒回区段已被建立

SQL> create rollback segment rbs6 tablespace rbs;
倒回区段已被建立

SQL> alter rollback segment rbs0 online;
倒回区段已被更改

SQL> alter rollback segment rbs1 online;
倒回区段已被更改

SQL> alter rollback segment rbs2 online;
倒回区段已被更改

SQL> alter rollback segment rbs3 online;
倒回区段已被更改

SQL> alter rollback segment rbs4 online;
倒回区段已被更改

SQL> alter rollback segment rbs5 online;
倒回区段已被更改

SQL> alter rollback segment rbs6 online;
倒回区段已被更改

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
RBS0 RBS ONLINE
RBS1 RBS ONLINE
RBS2 RBS ONLINE
RBS3 RBS ONLINE
RBS4 RBS ONLINE
RBS5 RBS ONLINE
RBS6 RBS ONLINE

选取了 8 列

--正常关闭数据库并在参数文件中注释掉隐含参数_corrupted_rollback_segments，
--并在参数ROLLBACK_SEGMENTS中加入所有的回滚段。

SQL> shutdown immediate
资料库关闭.
资料库已卸载.
已关闭 ORACLE 执行项次.

init.ora
......
#_corrupted_rollback_segments=(RBS0,RBS1,RBS2,RBS3,RBS4,RBS5,RBS6)
rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6 )

SQL> startup
已启动 ORACLE 执行项次.

Total System Global Area 219895052 bytes
Fixed Size 70924 bytes
Variable Size 75116544 bytes
Database Buffers 144629760 bytes
Redo Buffers 77824 bytes
资料库已挂载.
资料库已开启.

SQL> select segment_name,tablespace_name,status from dba_rollback_segs;

121212.jpg