Oracle8i UNDO损坏(ORA-01172及ORA-01151)情况下的数据库不完全恢复
============
現象:
============


C:/>set nls_language=american

C:/>sqlplus "/ as sysdba"

SQL*Plus: Release 8.1.6.0.0 - Production on 星期四 10月 11 11:28:47 2007

(c) Copyright 1999 Oracle Corporation. All rights reserved.



連到:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production


SQL> alter database open;
alter database open
*
ERROR 在行 1:
ORA-01172: 繫線 1 的復原停滯在區塊 579 (檔案 2 的) 上
ORA-01151: 請使用媒體復原來復原區塊, 必要時請復原備份可利用備份資料來復原

-- 嘗試打開數據庫,報錯ORA-01172及ORA-01151,經檢查發現損壞的數據文件為回滾段所在數據文件.



============
開始處理:
============


SQL> select file#,name from v$datafile where file#=2;

FILE# NAME
---------- ----------------------------------------
2 D:/ORACLE/ORADATA/BCCHECK/RBS01.DBF

SQL> select * from v$recover_file;

沒有任何資料列被選取


SQL> shutdown immediate
ORA-01109: 尚未開啟此一資料庫


資料庫已卸載.
已關閉 ORACLE 執行項次.



-- 註釋掉ROLLBACK_SEGMENTS初始化參數設置.后以Restricted模式MOUNT數據庫.

#rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6 )

SQL> startup restrict mount
已啟動 ORACLE 執行項次.

Total System Global Area 219895052 bytes
Fixed Size 70924 bytes
Variable Size 75116544 bytes
Database Buffers 144629760 bytes
Redo Buffers 77824 bytes
資料庫已掛載.



-- 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;

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 列

SQL> drop tablespace rbs including contents;
drop tablespace rbs including contents
*
ERROR 在行 1:
ORA-015