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@]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/106358/viewspace-1050215/，如需轉載，請注明出處，否則将追究法律責任。
轉載于:http://blog.itpub.net/106358/viewspace-1050215/