rollback segments 恢复
database: oracle 7.3.4

由于停电导致数据库错误,查看alert.log时发现如下错误
Sat May 21 21:26:01 2011
ORACLE Instance RT (pid = 6) - Error 1578 encountered while recovering transaction (3, 60).
Sat May 21 21:26:01 2011
Errors in file /home/oracle/rdbms/log/smon_rt_740.trc:
ORA-01578: ORACLE data block corrupted (file # 2, block # 17020)
ORA-01110: data file 2: '/home/oracle/dbs/rbsRT.dbf'
看来是data file 损坏了,该data file 为rollback 的文件,而且没有备份
查看rollbak segment的状态如下
select * from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID STATUS
------------ ------ --------------- ---------- ---------- -------------
SYSTEM SYS SYSTEM 0 1 ONLINE
R01 SYS RBS2 2 20 ONLINE
R02 SYS RBS2 8 20 NEED RECOVERY
R03 SYS RBS2 4 20 ONLINE
R04 SYS RBS2 5 20 ONLINE
R05 SYS RBS2 6 20 ONLINE
R06 SYS RBS2 7 20 ONLINE
看来是r02损坏了,看来只有删除重建了.

1. 先建新的tablespace,并创建rollback segment
create tablespace rbs2 datafile
'/home/oracle/dbs/rbs2RT.dbf' size 1000M
default storage (
initial 5m
next 5m
pctincrease 0
minextents 2
maxextents 505);
create rollback segment r00 tablespace rbs2 storage(initial 1m next 1m minextents 12 maxextents 505 optimal 60m);

2. 将原来的rollback segment offlie并drop 掉
alter rollback segment r01 offline;
alter rollback segment r02 offline;
alter rollback segment r03 offline;
alter rollback segment r04 offline;
alter rollback segment r05 offline;
alter rollback segment r06 offline;
alter rollback segment r01 drop;
alter rollback segment r02 drop;
alter rollback segment r03 drop;
alter rollback segment r04 drop;
alter rollback segment r05 drop;
alter rollback segment r06 drop;
在drop r02时,系统给出如下错误
ORA-01545: rollback segment 'R02' specified not available
由于r02需要recovery, 系统不能drop

3. 只能使用参数强制删除r02了,修改init.ora,增加如下参数
_offline_rollback_segments=(r02)
_corrupted_rollback_segments=(r02)

4. 重启数据库再删除r02,这次可以删除了.

5. 去掉新增的参数,重建rollback segment
select * from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID STATUS
------------ ------ --------------- ---------- ---------- ------
SYSTEM SYS SYSTEM 0 1 ONLINE
R01 SYS RBS2 2 20 ONLINE
R02 SYS RBS2 8 20 ONLINE
R03 SYS RBS2 4 20 ONLINE
R04 SYS RBS2 5 20 ONLINE
R05 SYS RBS2 6 20 ONLINE
R06 SYS RBS2 7 20 ONLINE

[@more@]