######################################################################################
# [9].回滾段恢復 #
######################################################################################參見1013221.6
28812.1
################################
# [9.1].正常shutdown,回滾數據文件丟失,
# 有備份,回滾段恢復
################################SQL> startup
Oracle 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL> archive log list
數據庫日志模式 存檔模式
自動存檔 啟用
存檔終點 C:/oracle/oradata/testdb/archive
最早的概要日志序列 1
下一個存檔日志序列 3
當前日志序列 3SQL> select us#, name, status$ from undo$; US# NAME STATUS$
---------- ------------------------------ ----------
0 SYSTEM 3
1 _SYSSMU1$ 3
2 _SYSSMU2$ 3
3 _SYSSMU3$ 3
4 _SYSSMU4$ 3
5 _SYSSMU5$ 3
6 _SYSSMU6$ 3
7 _SYSSMU7$ 3
8 _SYSSMU8$ 3
9 _SYSSMU9$ 3
10 _SYSSMU10$ 3已選擇11行。SQL> select SEGMENT_NAME,STATUS from dba_rollback_segs;SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ ONLINE
_SYSSMU2$ ONLINE
_SYSSMU3$ ONLINE
_SYSSMU4$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU10$ ONLINE已選擇11行。RMAN> backup format 'c:/noarch_%s%p1.bak' database;啟動 backup 于 03-3月 -05
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在啟動 full 數據文件備份集
通道 ORA_DISK_1: 正在指定備份集中的數據文件
在備份集中包含當前的 SPFILE
備份集中包括當前控制文件
輸入數據文件 fno=00001 name=C:/ORACLE/ORADATA/TESTDB/SYSTEM01.DBF
輸入數據文件 fno=00002 name=C:/ORACLE/ORADATA/TESTDB/UNDOTBS01.DBF
輸入數據文件 fno=00003 name=C:/ORACLE/ORADATA/TESTDB/INDX01.DBF
輸入數據文件 fno=00005 name=C:/ORACLE/ORADATA/TESTDB/USERS01.DBF
輸入數據文件 fno=00004 name=C:/ORACLE/ORADATA/TESTDB/TOOLS01.DBF
通道 ORA_DISK_1: 正在啟動段 1 于 03-3月 -05
通道 ORA_DISK_1: 已完成段 1 于 03-3月 -05
段 handle=C:/NOARCH_211.BAK comment=NONE
通道 ORA_DISK_1: 備份集已完成, 經過時間:00:00:27
完成 backup 于 03-3月 -05RMAN>RMAN>RMAN> copy current controlfile to 'c:/currcont.ctl';啟動 copy 于 03-3月 -05
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 已復制當前的控制文件
輸出文件名=C:/CURRCONT.CTL
完成 copy 于 03-3月 -05RMAN> exit
恢復治理器完成。SQL> create table arch (status varchar(2)) tablespace users;表已創建。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'ok' from dba_objects;已創建6166行。SQL> commit;提交完成。SQL> insert into arch select * from arch;已創建6166行。SQL> /已創建12332行。SQL> /已創建24664行。SQL> /已創建49328行。SQL> commit;提交完成。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'no' from dba_objects;已創建6166行。SQL> commit;提交完成。SQL> select GROUP#,SEQUENCE#,archived, STATUS from v$log; GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 5 NO CURRENT
2 3 YES ACTIVE
3 4 NO ACTIVESQL> shutdown -->>這里是正常shutdown,所以會做檢查點,當前的數據文件不需要恢復狀態
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。~~~~~~~~~~~~~~~~~~~~~~~~
模擬刪除UNDOTBS01.DBF
這時其他文件都是一致性的,
只需要UNDOTBS01.DBF恢復
~~~~~~~~~~~~~~~~~~~~~~~~
刪除UNDOTBS01.DBF
還原一個UNDOTBS01.DBF的備份SQL> startup
ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
數據庫裝載完畢。
ORA-01113: 文件 2 需要介質恢復
ORA-01110: 數據文件 2: 'C:/ORACLE/ORADATA/TESTDB/UNDOTBS01.DBF'
SQL> recover database ;
完成介質恢復。SQL> alter database open;數據庫已更改。SQL> select count(*),status from arch group by status; COUNT(*) ST
---------- --
6166 no
98656 ok
################################
# [9.2].shutdown abort(或者使用了別的老的數據文件),
# 回滾數據文件丟失,有備份,回滾段恢復 ，
# 數據庫沒有活動事務
################################
略：
也能完全恢復
~~~~~~~~~~~~~~~~~~~~~~
雖然是abort，但是只是需要做一個檢查點，
將數據寫到數據文件中，但這部分數據直接
在在線日志中可以拿到了，只需要前滾，
不需要進行回滾
~~~~~~~~~~~~~~~~~~~~~~
################################
# [9.3].shutdown abort(或者使用了別的老的數據文件),
# 回滾數據文件丟失,有備份,回滾段恢復 ，
# 數據庫有活動事務
################################
SQL> startup
ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL> archive log list
數據庫日志模式 存檔模式
自動存檔 啟用
存檔終點 C:/oracle/oradata/testdb/archive
最早的概要日志序列 1
下一個存檔日志序列 3
當前日志序列 3
RMAN> backup format 'c:/noarch_%s%p1.bak' database;啟動 backup 于 03-3月 -05
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在啟動 full 數據文件備份集
通道 ORA_DISK_1: 正在指定備份集中的數據文件
在備份集中包含當前的 SPFILE
備份集中包括當前控制文件
輸入數據文件 fno=00001 name=C:/ORACLE/ORADATA/TESTDB/SYSTEM01.DBF
輸入數據文件 fno=00002 name=C:/ORACLE/ORADATA/TESTDB/UNDOTBS01.DBF
輸入數據文件 fno=00003 name=C:/ORACLE/ORADATA/TESTDB/INDX01.DBF
輸入數據文件 fno=00005 name=C:/ORACLE/ORADATA/TESTDB/USERS01.DBF
輸入數據文件 fno=00004 name=C:/ORACLE/ORADATA/TESTDB/TOOLS01.DBF
通道 ORA_DISK_1: 正在啟動段 1 于 03-3月 -05
通道 ORA_DISK_1: 已完成段 1 于 03-3月 -05
段 handle=C:/NOARCH_211.BAK comment=NONE
通道 ORA_DISK_1: 備份集已完成, 經過時間:00:00:27
完成 backup 于 03-3月 -05RMAN>RMAN>RMAN> copy current controlfile to 'c:/currcont.ctl';啟動 copy 于 03-3月 -05
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 已復制當前的控制文件
輸出文件名=C:/CURRCONT.CTL
完成 copy 于 03-3月 -05RMAN> exit
恢復治理器完成。SQL> create table arch (status varchar(2)) tablespace users;表已創建。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'ok' from dba_objects;已創建6166行。SQL> commit;提交完成。SQL> insert into arch select * from arch;已創建6166行。SQL> /已創建12332行。SQL> /已創建24664行。SQL> /已創建49328行。SQL> commit;提交完成。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'no' from dba_objects; --這里沒有commit已創建6166行。提交完成。SQL> select GROUP#,SEQUENCE#,archived, STATUS from v$log; GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 5 NO CURRENT
2 3 YES ACTIVE
3 4 NO ACTIVESQL> shutdown abort-->>這里是正常shutdown,所以會做檢查點,當前的數據文件不需要恢復狀態
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。~~~~~~~~~~~~~~~~~~~~~~~~
模擬刪除UNDOTBS01.DBF
這時其他文件都是一致性的,
只需要UNDOTBS01.DBF恢復
~~~~~~~~~~~~~~~~~~~~~~~~
刪除UNDOTBS01.DBF
還原一個UNDOTBS01.DBF的備份SQL> startup
ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
數據庫裝載完畢。
ORA-01113: 文件 2 需要介質恢復
ORA-01110: 數據文件 2: 'C:/ORACLE/ORADATA/TESTDB/UNDOTBS01.DBF'
SQL> recover database ;
完成介質恢復。SQL> alter database open;數據庫已更改。SQL> select count(*),status from arch group by status; COUNT(*) ST
---------- --
98656 ok
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8i假如有活動的事務，那么恢復回滾表空間時，
在前滾后后滾時要讀自己回滾段里的數據，
而自己的數據文件已經損壞了，所以就不答應的，
所以在mount前將初始化參數
rollback_segments=(system)
_corrupted_rollback_segments=(rbs0,rbs1,rbs2……)
而9I，測試中回撤表空間數據文件恢復跟常規表空間恢復一樣
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
################################
# [9.4].shutdown abort(或者使用了別的老的數據文件),
# 回滾數據文件丟失,沒有備份,回滾段恢復 ，
# 數據庫有活動事務
################################
SQL> create table arch (status varchar(2)) tablespace users;表已創建。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'ok' from dba_objects;已創建6166行。SQL> commit;提交完成。SQL> insert into arch select * from arch;已創建6166行。SQL> /已創建12332行。SQL> /已創建24664行。SQL> /已創建49328行。SQL> commit;提交完成。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'no' from dba_objects;已創建6166行。SQL> shutdown abort
ORACLE 例程已經關閉。
SQL>
丟失undo文件
SQL> startup mount;
ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytesSQL> alter database datafile 'C:/ORACLE/ORADATA/TESTDB/UNDOTBS01.DBF' offline;數據庫已更改。SQL> alter database open;數據庫已更改。SQL> select US#,NAME,ts#,STATUS$,FLAGS from undo$ where STATUS$ <> 3; US# NAME TS# STATUS$ FLAGS
---------- ------------------------------ ---------- ---------- ----------
1 _SYSSMU1$ 1 2
2 _SYSSMU2$ 1 2
3 _SYSSMU3$ 1 2
4 _SYSSMU4$ 1 2
5 _SYSSMU5$ 1 2
6 _SYSSMU6$ 1 2
7 _SYSSMU7$ 1 2
8 _SYSSMU8$ 1 2
9 _SYSSMU9$ 1 2
10 _SYSSMU10$ 1 2
11 _SYSSMU11$ 1 5
SQL> select count(*) from arch; --這里表是不能讀取一致性了
select count(*) from arch
*
ERROR 位于第 1 行:
ORA-00376: ???????? 2
ORA-01110: ???? 2: 'C:/ORACLE/ORADATA/TESTDB/UNDOTBS01.DBF'
SQL> shutdown
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。
SQL>編輯INITtestdb_undo.ORA'
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
_allow_resetlogs_corruption=true
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$)
_offline_rollback_segments=true SQL> startup pfile='C:/oracle/ora92/database/INITtestdb_undo.ORA';
ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database open;數據庫已更改。SQL> select count(*) from arch; COUNT(*)
----------
104822
再創建新的undo表空間
drop 壞的undo tablespace最好做一個整庫的導出,并導入到新系統 ################################
# [9.5].shutdown abort(或者使用了別的老的數據文件),
# 回滾數據文件丟失,沒有備份,但控制文件有創建
# 該回撤段數據文件的所有歷史日志，回滾段恢復 ，
# 數據庫有活動事務
################################
SQL> startup
ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL> show parameter undoNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_supPRess_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> alter tablespace UNDOTBS1 add datafile 'C:/ORACLE/ORADATA/TESTDB/UNDOTBS02
DBF' size 1m;表空間已更改。SQL>
SQL>
SQL> create table arch (status varchar(2)) tablespace users;表已創建。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'ok' from dba_objects;已創建6166行。SQL> commit;提交完成。SQL> insert into arch select * from arch;已創建6166行。SQL> /已創建12332行。SQL> /已創建24664行。SQL> /已創建49328行。SQL> commit;提交完成。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'no' from dba_objects;已創建6166行。SQL> SHUTDOWN ABORT
ORACLE 例程已經關閉。
刪除UNDOTBS02
SQL> STARTUP
ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
數據庫裝載完畢。
ORA-01157: 無法標識/鎖定數據文件 6 - 請參閱 DBWR 跟蹤文件
ORA-01110: 數據文件 6: 'C:/ORACLE/ORADATA/TESTDB/UNDOTBS02.DBF'
SQL> ALTER DATABASE CREATE DATAFILE 'C:/ORACLE/ORADATA/TESTDB/UNDOTBS02.DBF';數據庫已更改。SQL> RECOVER DATABASE;
完成介質恢復。
SQL> ALTER DATABASE OPEN;數據庫已更改。SQL> COL NAME FORMAT A50;
SQL> SELECT NAME,STATUS FROM V$DATAFILE;NAME STATUS
-------------------------------------------------- -------
C:/ORACLE/ORADATA/TESTDB/SYSTEM01.DBF SYSTEM
C:/ORACLE/ORADATA/TESTDB/UNDOTBS01.DBF ONLINE
C:/ORACLE/ORADATA/TESTDB/INDX01.DBF ONLINE
C:/ORACLE/ORADATA/TESTDB/TOOLS01.DBF ONLINE
C:/ORACLE/ORADATA/TESTDB/USERS01.DBF ONLINE
C:/ORACLE/ORADATA/TESTDB/UNDOTBS02.DBF ONLINE已選擇6行。################################
# [9.6].數據庫打開時回撤表空間文件
# 損壞，沒有備份
################################ SQL> startup
ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL> create table arch (status varchar(2)) tablespace users;表已創建。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'ok' from dba_objects;已創建6166行。SQL> commit;提交完成。SQL> insert into arch select * from arch;已創建6166行。SQL> /已創建12332行。SQL> /已創建24664行。SQL> /已創建49328行。SQL> commit;提交完成。SQL> alter system switch logfile;系統已更改。SQL> insert into arch select 'no' from dba_objects;已創建6166行。SQL> commit;提交完成。SQL> select GROUP#,SEQUENCE#,archived, STATUS from v$log; GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 5 NO CURRENT
2 3 YES INACTIVE
3 4 YES ACTIVESQL>
SQL>
SQL>
SQL> select GROUP#,SEQUENCE#,archived, STATUS from v$log; GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 5 NO CURRENT
2 3 YES INACTIVE
3 4 YES ACTIVESQL> select us#, name, status$ from undo$; US# NAME STATUS$
---------- ------------------------------ ----------
0 SYSTEM 3
1 _SYSSMU1$ 3
2 _SYSSMU2$ 3
3 _SYSSMU3$ 3
4 _SYSSMU4$ 3
5 _SYSSMU5$ 3
6 _SYSSMU6$ 3
7 _SYSSMU7$ 3
8 _SYSSMU8$ 3
9 _SYSSMU9$ 3
10 _SYSSMU10$ 3已選擇11行。SQL> select SEGMENT_NAME,STATUS from dba_rollback_segs;SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ ONLINE
_SYSSMU2$ ONLINE
_SYSSMU3$ ONLINE
_SYSSMU4$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU10$ ONLINE已選擇11行。SQL> show parameter undoNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1~~~~~~~~~~~~~~~~~~~~~
這里模擬回撤數據文件損壞
~~~~~~~~~~~~~~~~~~~~~
用ultraedit破壞UNDOTBS01.DBFSQL> shutdown --因為要使用回滾，但回滾失敗
ORA-00603: ORACLE 服務器會話因致命錯誤而終止
SQL> startup
ORA-24324: 未初始化服務句柄
ORA-01041: 內部錯誤，hostdef 擴展名不存在
SQL> exit
從Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProdUCtion
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中斷開C:/Documents and Settings/lifeng.fang>sqlplus "sys/sunsdl as sysdba"SQL*Plus: Release 9.2.0.1.0 - Production on 星期五 3月 4 14:18:35 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.已連接。
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown
ORA-01090: shutdown in progress - connection is not permitted
SQL> shutdown abort --只有abort才可以關閉數據庫，因為不需要回滾
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL> select SEGMENT_NAME,STATUS from dba_rollback_segs;SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU1$ NEEDS RECOVERY
_SYSSMU2$ NEEDS RECOVERY
_SYSSMU3$ NEEDS RECOVERY
_SYSSMU4$ NEEDS RECOVERY
_SYSSMU5$ NEEDS RECOVERY
_SYSSMU6$ NEEDS RECOVERY
_SYSSMU7$ NEEDS RECOVERY
_SYSSMU8$ NEEDS RECOVERY
_SYSSMU9$ NEEDS RECOVERY
_SYSSMU10$ NEEDS RECOVERY
SQL> select us#, name, status$ from undo$; US# NAME STATUS$
---------- ------------------------------ ----------
0 SYSTEM 3
1 _SYSSMU1$ 5
2 _SYSSMU2$ 5
3 _SYSSMU3$ 5
4 _SYSSMU4$ 5
5 _SYSSMU5$ 5
6 _SYSSMU6$ 5
7 _SYSSMU7$ 5
8 _SYSSMU8$ 5
9 _SYSSMU9$ 5
10 _SYSSMU10$ 5已選擇11行。~~~~~~~~~~~~~~~~~~~~~
這里我們注重能夠打開數據庫
但是這里狀態是需要恢復
~~~~~~~~~~~~~~~~~~~~~~
SQL> create pfile='c:/undopfile.ora' from spfile;文件已創建。SQL> create undo tablespace UNDOTBS2 datafile 'C:/oracle/oradata/testdb/UNDOTB2.DBF' size 50m;表空間已創建。
SQL> select count(*),status from arch group by status; COUNT(*) ST
---------- --
6166 no
98656 ok
SQL> insert into arch select 'af' from dba_objects;
insert into arch select 'af' from dba_objects
*
ERROR 位于第 1 行:
ORA-01552: 非系統表空間'USERS'無法使用系統回退段
SQL> shutdown
數據庫已經關閉。
已經卸載數據庫。
ORACLE 例程已經關閉。 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
編輯c:/undopfile.ora，
將設置*.undo_tablespace='UNDOTBS2'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> startup pfile='c:/undopfile.ora'ORACLE 例程已經啟動。Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
數據庫裝載完畢。
數據庫已經打開。
SQL> insert into arch select 'af' from dba_objects;已創建6166行。SQL> select
2 trunc(id1/power(2,16)) rbs,
3 bitand(id1,to_number('ffff','xxxx'))+0 slot,
4 id2 seq,
5 lmode,request
6 from v$lock,v$session
7 where v$lock.type = 'TX'
8 and v$lock.sid = v$session.sid
9 and v$session.username is not null; RBS SLOT SEQ LMODE REQUEST
---------- ---------- ---------- ---------- ----------
17 0 2 6 0
SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU2$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU3$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU4$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU5$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU6$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU7$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU8$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU9$ UNDOTBS1 NEEDS RECOVERY
_SYSSMU10$ UNDOTBS1 NEEDS RECOVERYSEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11$ UNDOTBS2 ONLINE
_SYSSMU12$ UNDOTBS2 ONLINE
_SYSSMU13$ UNDOTBS2 ONLINE
_SYSSMU14$ UNDOTBS2 ONLINE
_SYSSMU15$ UNDOTBS2 ONLINE
_SYSSMU16$ UNDOTBS2 ONLINE
_SYSSMU17$ UNDOTBS2 ONLINE
_SYSSMU18$ UNDOTBS2 ONLINE
_SYSSMU19$ UNDOTBS2 ONLINE
_SYSSMU20$ UNDOTBS2 ONLINE SQL> select US#,NAME,ts#,STATUS$,FLAGS from undo$; US# NAME TS# STATUS$ FLAGS
---------- ------------------------------ ---------- ---------- ----------
0 SYSTEM 0 3
1 _SYSSMU1$ 1 5
2 _SYSSMU2$ 1 5
3 _SYSSMU3$ 1 5
4 _SYSSMU4$ 1 5
5 _SYSSMU5$ 1 5
6 _SYSSMU6$ 1 5
7 _SYSSMU7$ 1 5
8 _SYSSMU8$ 1 5
9 _SYSSMU9$ 1 5
10 _SYSSMU10$ 1 5 US# NAME TS# STATUS$ FLAGS
---------- ------------------------------ ---------- ---------- ----------
11 _SYSSMU11$ 6 3
12 _SYSSMU12$ 6 3
13 _SYSSMU13$ 6 3
14 _SYSSMU14$ 6 3
15 _SYSSMU15$ 6 3
16 _SYSSMU16$ 6 3
17 _SYSSMU17$ 6 3 --插入數據的回滾信息在這
18 _SYSSMU18$ 6 3
19 _SYSSMU19$ 6 3
20 _SYSSMU20$ 6 3已選擇21行。
這些非online的回撤段也需要在初始化參數中強制_corrupted_rollback_segments
且_offline_rollback_segments=true
這里跟9.4一樣，設置初始化文件
編輯INITtestdb_undo.ORA'
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
_allow_resetlogs_corruption=true
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$)
_offline_rollback_segments=true然后打開數據庫看看TOM的例子
sys@ORA920.US.ORACLE.COM> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.sys@ORA920.US.ORACLE.COM> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
[ora920@tkyte-pc-isdn ora920]$ ls
control01.ctl cwmlite01.dbf indx01.dbf redo02.log redo0B.log temp01.dbf
users01.dbf
control02.ctl drsys01.dbf odm01.dbf redo03.log redo0C.log tools01.dbf
xdb01.dbf
control03.ctl example01.dbf redo01.log redo0A.log system01.dbf
undotbs01.dbf[ora920@tkyte-pc-isdn ora920]$ mv undotbs01.dbf undotbs01.dbf.xxx
[ora920@tkyte-pc-isdn ora920]$ sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:33:29 2002Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.idle> connect / as sysdba
Connected to an idle instance.
idle> startup
ORACLE instance started.Total System Global Area 143725064 bytes
Fixed Size 451080 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2:
'/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'
idle> show parameter undoNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1idle> alter system set undo_management = manual scope=spfile;System altered.Note: disabling the undo tablespace here, this'll let us go back to "rollback
segments" and we'll use the system RBS to get back on our feet
idle> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.idle> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production[ora920@tkyte-pc-isdn ora920]$ !sql
sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 08:35:34 2002Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.idle> connect / as sysdba;
Connected to an idle instance.
idle> startup
ORACLE instance started.Total System Global Area 143725064 bytes
Fixed Size 451080 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2:
'/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'Note: we still have to get rid of that thing..idle> alter database datafile
'/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf'
2 offline drop;Database altered.idle> alter database open;Database altered.idle> drop tablespace undotbs1;Tablespace dropped.idle> create UNDO tablespace undotbs1
2 datafile '/usr/oracle/ora920/OraHome1/oradata/ora920/undotbs01.dbf' size
25m
3 autoextend on next 1m maxsize 1024m;Tablespace created.idle> alter system set undo_management = auto scope=spfile;System altered.Now, we've put the undo tablespace "back" by creating a new one. Just
bounce...idle> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
