control中有三種SCN分别為,system SCN、datafile SCN、last SCN,資料檔案頭中有一種SCN start SCN
system scn從視圖v$database中獲得,對應checkpoint_change#字段,datafile scn、last scn分别對應視圖v$datafile中的checkpoint_change#,last_change#,而 start scn則從v$datafile_header中checkpoint_change#得到。
資料庫在正常啟動後下,system scn,datafile scn,start scn會相等,而last scn會被置于無窮大,這裡為null。
正常關閉後(immediate,noraml,translate),上面四個scn會應執行full checkpoint 而相等。
當系統在非正常關閉後,如shutdown abort,這個時候last scn依然為無窮大,那麼當重新啟動執行個體時,系統首先會比較start scn與system scn,如果一緻,那麼再比較start scn 與last scan是否一樣大,因為是非正常關閉,這裡會不一樣大,那麼就需要例程恢複。
如果打開資料庫時發現system scn>datafile scn,那麼以為着使用舊的備份資料檔案,也就是需要媒體恢複
如果是system scn<datafile scn,及控制檔案scn是舊的,代表使用了老的控制檔案,需要recover using backup controlfile進行恢複。
1、正常啟動時
SQL> select checkpoint_change# from v$database; --控制檔案中的scn
CHECKPOINT_CHANGE#
------------------
5534071
SQL> select file#,checkpoint_change# from v$datafile_header; --start scn
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5534071
2 5534071
3 5534071
4 5534071
5 5534071
SQL> select file#,checkpoint_change#,last_change# from v$datafile; --datafile scn & last scn
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 5534071
2 5534071
3 5534071
4 5534071
5 5534071
2、正常關閉後,然後在startup mount;
SQL> shutdown immediate;
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup mount;
ORACLE 例程已經啟動。
Total System Global Area 319888364 bytes
Fixed Size 453612 bytes
Variable Size 192937984 bytes
Database Buffers 125829120 bytes
Redo Buffers 667648 bytes
資料庫裝載完畢。
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5534485
2 5534485
3 5534485
4 5534485
5 5534485
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
5534485
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 5534485 5534485
2 5534485 5534485
3 5534485 5534485
4 5534485 5534485
5 5534485 5534485
--發現start scn=last scn,證明系統是正常關閉
SQL> alter database open;
資料庫已更改。
3、在正常打開狀态下進行事務操作
SQL> create table t(a number);
表已建立。
SQL> insert into t values (1);
已建立 1 行。
SQL> commit;
送出完成。
SQL> insert into t values(2);
已建立 1 行。
4、非正常關閉
SQL> shutdown abort;
ORACLE 例程已經關閉。
SQL>
5、打開到mount狀态下,觀看scn
SQL> startup mount;
ORACLE 例程已經啟動。
Total System Global Area 319888364 bytes
Fixed Size 453612 bytes
Variable Size 192937984 bytes
Database Buffers 125829120 bytes
Redo Buffers 667648 bytes
資料庫裝載完畢。
SQL> select file#,checkpoint_change#,last_change# from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
1 5534486
2 5534486
3 5534486
4 5534486
5 5534486
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
5534486
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5534486
2 5534486
3 5534486
4 5534486
5 5534486
--這時發現start scn 與last scn不等,last scn為無窮大,需要例程恢複
6、改變資料庫狀态為open,并檢視該階段運作日志
SQL> select * from wen.t;
select * from wen.t
*
ERROR 位于第 1 行:
ORA-01219: 資料庫未打開: 僅允許在固定表/視圖中查詢
SQL> alter database open;
資料庫已更改。
SQL> select * from wen.t;
A
----------
1
--發現沒有送出的事務丢失。
檢視日志如下:
Completed: ALTER DATABASE MOUNT
Wed May 17 21:35:46 2006
alter database open
Wed May 17 21:35:46 2006
Beginning crash recovery of 1 threads --會自動判斷是否需要恢複,這裡開始例程恢複
Wed May 17 21:35:46 2006
Started first pass scan
Wed May 17 21:35:47 2006
Completed first pass scan
206 redo blocks read, 90 data blocks need recovery
Wed May 17 21:35:47 2006
Started recovery at
Thread 1: logseq 167, block 271, scn 0.0
Recovery of Online Redo Log: Thread 1 Group 2 Seq 167 Reading mem 0 --恢複用的線上重做日志
Mem# 0 errs 0: D:ORACLEORADATADB1REDO02.LOG
Wed May 17 21:35:47 2006
Ended recovery at
Thread 1: logseq 167, block 477, scn 0.5554724
90 data blocks read, 90 data blocks written, 206 redo blocks read
Crash recovery completed successfully --恢複完成
Wed May 17 21:35:47 2006
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 168
Thread 1 opened at log sequence 168
Current log# 3 seq# 168 mem# 0: D:ORACLEORADATADB1REDO03.LOG
Successful open of redo thread 1.
Wed May 17 21:35:48 2006
SMON: enabling cache recovery
Wed May 17 21:35:48 2006
ARC0: Evaluating archive log 2 thread 1 sequence 167
ARC0: Beginning to archive log 2 thread 1 sequence 167
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:DBBKARC00167.001'
ARC0: Completed archiving log 2 thread 1 sequence 167
Wed May 17 21:35:48 2006
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined