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

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 列

SQL>