Oracle 8i rollback segment 損毀(corrupted)

啟動DB於alert log觀察到

rollback segment RBS31

                 RBS32 corrupted

 

----

1.

 先把隱含參數加到pfile去，並重新啟動db

 _corrupted_rollback_segments =(RBS31,RBS32)

 

 

2.

 --drop rollback segments

 drop rollback segment RBS31;

 drop rollback segment RBS32;

 

 --drop tablespace

 drop tablespace RBS_3 including contents;

 create tablespace RBS_3 datafile '/u03/ofa/SMS/rbs03.dbf'  size 128M reuse;

 

 CREATE ROLLBACK SEGMENT RBS31 TABLESPACE RBS_3;

 CREATE ROLLBACK SEGMENT RBS32 TABLESPACE RBS_3;

 

3.

把RBS31 , RBS32加回pfile之參數 , ex:

rollback_segments = ( RBS31 , RBS32 , RBS11 , RBS12 ....)

 

4.

啟動DB ok後，執行以下指令查詢是否已被加入:

SQL>

select * from V$ROLLNAME;

 

       USN NAME

---------- ------------------------------

         0 SYSTEM

         1 RBS11

         2 RBS12

        13 RBS31  <---已被加入

        14 RBS32  <---已被加入

         7 RBS41

         8 RBS42

         9 RBS51

        10 RBS52

        11 RBS61

        12 RBS62

 

       USN NAME

---------- ------------------------------

         3 RBS21

         4 RBS22

 

13 rows selected.

 

 

Note:

--以下SQL可檢查rollback segment  是否need recovery

select

segment_name,

status

from

dba_rollback_segs

where

tablespace_name='undotbs_corrupt'

and

status = 'NEEDS RECOVERY';