oracle scn與備份恢復backup recovery(一)
前面文章中,多次涉及了scn,比如block dump中的scn, scn對於構造cr從而實現consistent read
;至少重要，而且scn與資料庫整體的備份恢復有著密不可分的關係；
oracle scn裡面的類別有很多種,讓人有些暈著不知所向；比如在控制檔案中有scn,資料庫有scn,資料檔案有scn;
資料檔案頭有scn;日誌檔案有scn;block有scn;
它們之間有何聯絡；如何有機組合服務於備份與恢復;我們一步步來講,千里之行，始於足下:
如下摘集一些關於scn的貼子：
http://logzgh.itpub.net/post/3185/42513
1,先研究下scn與checkpoint的關係；自然就要知道何為檢查點，檢查點oracle具體什什麼工作；
/**********檢查點概念及簡介*****************/
http://space.itpub.net/?uid-9240380-action-viewspace-itemid-757005
ckpt程式觸發檢查點時,把當前最新的scn寫入到資料檔案頭部和控制檔案中,同時ckpt通知dbwr把buffer cache
的dirty buffer寫入到資料檔案中
與checkpoint相關的scn有幾個呢,上面說了，發生檢查點會在資料檔案頭部和控制檔案中寫入：
所以我們檢查與資料檔案頭部和控制檔案有關的檢視即可：
/*關於v$database可參見*************************/
/***第一列會隨機變化,而第二列是最新發生checkpoint的scn******v$database還有其它的scn,比如與backup及resetslog相關的，本文不關注**/
SQL> select current_scn,checkpoint_change# from v$database;--v$database是記錄控制檔案的scn
CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
10558777 10514593
SQL> alter system checkpoint;
System altered
/*************產生檢查點發checkpoint_change#變化了*/
SQL> select current_scn,checkpoint_change# from v$database;
CURRENT_SCN CHECKPOINT_CHANGE#
----------- ------------------
10558978 10558976
/*******資料檔案的checkpoint scn*****你會發現它與控制檔案的scn一樣，因為v$datafile也是來自於控制檔案**/
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
CHECKPOINT_CHANGE#
------------------
10558976
21 rows selected
/**************資料檔案頭部的scn也與上面的一樣/
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
10558976
CHECKPOINT_CHANGE#
------------------
10558976
21 rows selected
小小結:
1,一般情況下控制檔案的checkpoint scn與資料檔案頭的checkpoint scn一樣
2,v$database與v$datafile取自控制檔案
3,v$datafile_header記錄資料檔案頭資訊
/********介質恢復概念*********/
http://space.itpub.net/?uid-9240380-action-viewspace-itemid-757008
資料庫啟動時，如果上述的checkpoint scn與datafile checkpoint scn和資料檔案頭的checkpoint scn不一樣，
就要執行介質恢復；或者說是v$database.checkpoint_change#與v$datafile.checkpoint_change#與v$datafile_header.checkpoint_change#
不一樣就要執行介質恢復
SQL> alter tablespace TBS_16K read only;
Tablespace altered
SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560282
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10558976
SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560282
SQL> create table t_hb(a int) tablespace tbs_16k;
Table created
SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560282
SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560282
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10558976
小結：1,對於read only操作觸發的檢查點,資料檔案及資料檔案頭的checkpoint scn發生變化，而
v$database.checkpoint_change#控制檔案的檢查點scn被凍結不變化
2,如一直處於read only期間，三者scn皆不再變化
--回覆表空間為正常狀態
SQL> alter tablespace TBS_16K read write;
Tablespace altered
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10558976
SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
小結：表空間恢復正常後，控制檔案的檢查點scn及資料檔案及頭部的檢查點scn開始變化
alter tablespace read only;一般用於備份表空間
SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
SQL> insert into t_hb values(1);
1 row inserted
SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
SQL> commit;
Commit complete
SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
小結：1,commit不會觸發檢查點
QL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
SQL> alter system switch logfile;
System altered
SQL> select file#,name,checkpoint_change# from v$datafile_header where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
SQL> select file#,name,checkpoint_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------
21 C:\TBS16K_1.DBF 10560682
小結：alter system switch logfile切換日誌不會觸發檢查點
SQL> select file#,name,checkpoint_change#,last_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
21 C:\TBS16K_1.DBF 10561215
SQL> alter tablespace tbs_16k read only;
Tablespace altered
SQL> select file#,name,checkpoint_change#,last_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
21 C:\TBS16K_1.DBF 10561320 10561320
SQL> alter tablespace tbs_16k read write;
Tablespace altered
SQL> select file#,name,checkpoint_change#,last_change# from v$datafile where file#=21;
FILE# NAME CHECKPOINT_CHANGE# LAST_CHANGE#
---------- -------------------------------------------------------------------------------- ------------------ ------------
21 C:\TBS16K_1.DBF 10561336
SQL>
小結：表空間或資料檔案正常情況下，last_change#為空，若read only切回到正常模式，則為上次檢查點的scn
二。SCN號與資料庫啟動
1,資料庫啟動過程中，如果v$database.checkpoint_change#,v$datafile.checkpoint_change#及v$datafile_header.checkpoint_change#三者
一致，則資料庫可以正常開啟，否則需要介質恢復
2,資料庫啟動過程中，如果end_scn即v$datafile.last_change#為null，但需要作例項恢復；
也就是說資料庫關閉時，會把v$datafile.last_change#設定為v$datafile.checkpoint_change#;
3,資料庫啟動之後，會把v$datafile.last_change#配置為null
如果在啟動的過程中，End SCN號為NULL，則需要做instance recovery.
ORACLE在啟動過程中首先檢查是否需要media recovery，然後再檢查是否需要instance recovery.
三。SCN號與資料庫關閉
如果資料庫的正常關閉的話，將會觸發一個checkpoint，同時將資料檔案的END SCN號設定為相應
資料檔案的Start SCN號。
當資料庫啟動時，發現它們是一致的，則不需要做instance recovery。在資料庫正常啟動後，ORACLE
會將END SCN號設定為NULL.
如果資料庫異常關閉的話，則END SCN號將為NULL.
/---************我們測試下例項恢復-----------------/
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
10561215
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10561336
21 rows selected
/***********非常關閉資料庫*************/
SQL> shutdown abort
ORACLE instance shut down.
/********啟動到mount***********/
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2174520 bytes
Variable Size 159384008 bytes
Database Buffers 71303168 bytes
Redo Buffers 5173248 bytes
Database mounted.
/****測試是否下面就要例項恢復***********
alter database open
SQL> alter database open;
Database altered.
/*****************看alert,確實發生了例項恢復***************/
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes --開啟了並行恢復
Started redo scan --例項恢復要應用線上日誌
Completed redo scan
read 836 KB redo, 421 data blocks need recovery
Started redo application at
Thread 1: logseq 822, block 30632 --自異常中斷庫當前重作日誌的rba為822.30632
Recovery of Online Redo Log: Thread 1 Group 8 Seq 822 Reading mem 0
Mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO08.LOG
Completed redo application of 0.61MB
Completed crash recovery at
Thread 1: logseq 822, block 32304, scn 10588920 --中止於異常中斷庫當前重作日誌的rba為822.32304
421 data blocks read, 421 data blocks written, 836 redo k-bytes read
/************附上shutdown abort之前的日誌資訊*******/
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
3 1 817 52428800 512 1 NO INACTIVE 10453013 2013/3/24 1 10454569 2013/3/24 1
4 1 818 209715200 512 1 NO INACTIVE 10454569 2013/3/24 1 10492795 2013/3/24 1
5 1 819 209715200 512 1 NO INACTIVE 10492795 2013/3/24 1 10560992 2013/3/25 1
6 1 820 209715200 512 1 NO INACTIVE 10560992 2013/3/25 1 10561002 2013/3/25 1
7 1 821 209715200 512 1 NO INACTIVE 10561002 2013/3/25 1 10561202 2013/3/25 1
8 1 822 209715200 512 1 NO CURRENT 10561202 2013/3/25 1 281474976710 ---例項恢復就是操作這個日誌檔案
9 1 816 209715200 512 1 NO INACTIVE 10446578 2013/3/24 1 10453013 2013/3/24 1
10 1 815 20971520 512 1 NO INACTIVE 10445875 2013/3/24 1 10446578 2013/3/24 1
8 rows selected
/********資料庫啟動後,last_change#結束scn也為空，除非read only或begin backup********/
SQL> select checkpoint_change#,last_change# from v$datafil
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
21 rows selected.
/*********再看下正常關閉庫結束scn的情況,是否為資料檔案的checkpoint_change#**********/
/---資料庫open期間,資料檔案的檢查點scn及結束scn************/
SQL> select checkpoint_change#,last_change# from v$datafile;
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
21 rows selected.
/******資料庫open期間資料檔案頭部**************/
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
CHECKPOINT_CHANGE#
------------------
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
10588923
21 rows selected.
/**********控制檔案***************/
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10588923
/********日誌檔案*******發現當前日誌檔案的scn與資料檔案及資料頭和控制檔案是一致的；這樣就把日誌檔案，控制檔案，資料檔案結合起來了************/
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
3 1 817 52428800 512 1 NO INACTIVE 10453013 2013/3/24 1 10454569 2013/3/24 1
4 1 818 209715200 512 1 NO INACTIVE 10454569 2013/3/24 1 10492795 2013/3/24 1
5 1 819 209715200 512 1 NO INACTIVE 10492795 2013/3/24 1 10560992 2013/3/25 1
6 1 820 209715200 512 1 NO INACTIVE 10560992 2013/3/25 1 10561002 2013/3/25 1
7 1 821 209715200 512 1 NO INACTIVE 10561002 2013/3/25 1 10561202 2013/3/25 1
8 1 822 209715200 512 1 NO INACTIVE 10561202 2013/3/25 1 10588922 2013/3/25 1
9 1 816 209715200 512 1 NO INACTIVE 10446578 2013/3/24 1 10453013 2013/3/24 1
10 1 823 20971520 512 1 NO CURRENT 10588922 2013/3/25 1 281474976710 --當前日誌檔案的first_change#比資料檔案的checkpoint_change#小１
8 rows selected
/**********關庫***********/
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
/*********啟動庫到mount**************/
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2174520 bytes
Variable Size 159384008 bytes
Database Buffers 71303168 bytes
Redo Buffers 5173248 bytes
Database mounted.
/********正常關庫啟動後控制檔案，資料檔案，資料檔案頭三者檢查點scn是一致；且資料檔案的檢查點scn與結束scn是一樣的，不用例項恢復**********/
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10590727
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
10590727
10590727
10590727
10590727
10590727
10590727
10590727
10590727
10590727
10590727
10590727
CHECKPOINT_CHANGE#
------------------
10590727
10590727
10590727
10590727
10590727
10590727
10590727
10590727
10590727
10590727
21 rows selected.
SQL> select checkpoint_change#,last_change# from v$datafile
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
10590727 10590727
21 rows selected.
SQL>
/********開啟資料庫*****未發現例項恢復操作***********/
SQL> alter database open;
Database altered.
alter database open
Mon Mar 25 19:34:24 2013
Thread 1 opened at log sequence 823
Current log# 10 seq# 823 mem# 0: D:\ORACLE11G_64BIT\ORADATA\ORCL\REDO10.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Mar 25 19:34:24 2013
SMON: enabling cache recovery
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process QMNC
Mon Mar 25 19:34:28 2013
QMNC started with pid=22, OS id=8208
Completed: alter database open
/**********正常關閉庫開啟庫資料檔案的結束scn,last_change#還是設定為空/
SQL> select checkpoint_change#,last_change# from v$datafile
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
21 rows selected.
SQL>
/*********例項恢復是從當前日誌的first_change#到下面查詢的scn之間應用日誌*************/
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
594373
如果需要進行例項恢復，則需要恢復的記錄為587705至594373中redo log中的記錄。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-757067/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle SCN機制———在備份與恢復中Oracle
- Backup And Recovery User's Guide-備份和恢復介紹-Oracle備份和恢復解決方案GUIIDEOracle
- Backup And Recovery User's Guide-備份和恢復介紹-備份恢復文件RoadmapGUIIDE
- Backup And Recovery User's Guide-備份和恢復概覽-備份和恢復介紹-備份和恢復的目的GUIIDE
- Oracle備份與恢復系列（三）alter tablspace begin backupOracle
- Oracle 備份與恢復(一):概念Oracle
- 由某公司案例進一步掌握rman備份與恢復backup restore recovery系列一REST
- rman backup recovery備份恢復效能優化tuning方法二優化
- Backup And Recovery User's Guide-備份和恢復介紹-Oracle閃回技術（一）GUIIDEOracle
- 【轉載】SCN 備份與恢復的關係
- Backup And Recovery User's Guide-備份和恢復介紹-備份和恢復的目的-資料傳輸GUIIDE
- Backup And Recovery User's Guide-備份和恢復介紹-備份和恢復的目的-資料儲存GUIIDE
- Backup And Recovery User's Guide-備份和恢復介紹-備份和恢復的目的-資料保護GUIIDE
- Oracle 備份 與 恢復 概述Oracle
- Oracle RAC備份與恢復Oracle
- Oracle備份與恢復 (zt)Oracle
- Oracle備份與恢復案例Oracle
- Oracle備份與恢復(轉)Oracle
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- Backup And Recovery User's Guide-RMAN備份概念-備份保留期策略-恢復視窗GUIIDE
- Backup And Recovery User's Guide-備份和恢復介紹-Oracle閃回技術（二）GUIIDEOracle
- 備份與恢復oracle_homeOracle
- Oracle OCR的備份與恢復Oracle
- oracle備份與恢復雜記Oracle
- Oracle備份與恢復入門Oracle
- Oracle備份與恢復案例 (zt)Oracle
- db2備份恢復（backup restore）DB2REST
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- Backup And Recovery User's Guide-備份和恢復介紹-資料恢復指導（advisor）GUIIDE資料恢復
- Oracle 12c 備份與恢復Oracle
- oracle備份與恢復測試(五)Oracle
- Oracle備份與恢復總結[轉]Oracle
- ORACLE之常用FAQ:備份與恢復Oracle
- ORACLE 備份與恢復之 思路整理Oracle
- Oracle資料庫備份與恢復之三：OS備份/使用者管理的備份與恢復Oracle資料庫
- 備份與恢復：polardb資料庫備份與恢復資料庫
- 如何恢復Hyper Backup備份的資料
- 備份恢復統計資訊 backup and restore statsREST