oracle switch logfile日誌切換及alter system checkpoint作了什麼
日誌切換或checkpoint到底發生了什麼？
1,剛變成active的日誌檔案（即由last_change#到next_change#的redo）被 dbwr寫入到資料檔案
2,ckpt更新所有資料檔案頭的start scn即v$datafile_header.checkpoint_change#為剛變成active的日誌檔案
的first_change#
3,ckpt同時更新控制檔案的檢查點scn,即v$database.checkpoint_change#為active狀態日誌檔案的first_change#
/***********測試開始*************/
/**********檢查點發生前的資料檔案頭scn*********/
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
CHECKPOINT_CHANGE#
------------------
10590730
21 rows selected
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
8 rows selected
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10590730
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
CHECKPOINT_CHANGE#
------------------
10590730
21 rows selected
/*********日誌切換後各個scn*****************/
SQL> alter system switch logfile;
System altered
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
CHECKPOINT_CHANGE#
------------------
10590730
21 rows selected
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10590730
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 824 209715200 512 1 NO CURRENT 10591778 2013/3/25 1 281474976710
10 1 823 20971520 512 1 NO ACTIVE 10588922 2013/3/25 1 10591778 2013/3/25 1
8 rows selected
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
CHECKPOINT_CHANGE#
------------------
10590730
21 rows selected
小結：alter system checkpoint不會觸發變更資料檔案及資料檔案頭的檢查點scn
/***********再看下日誌切換是怎麼樣的情況********************/
/**************日誌切換前*************************/
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
CHECKPOINT_CHANGE#
------------------
10590730
21 rows selected
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
10590730
CHECKPOINT_CHANGE#
------------------
10590730
21 rows selected
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10590730
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 824 209715200 512 1 NO CURRENT 10591778 2013/3/25 1 281474976710
10 1 823 20971520 512 1 NO ACTIVE 10588922 2013/3/25 1 10591778 2013/3/25 1
8 rows selected
SQL>
/***********日誌切換後****************/
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
3 1 825 52428800 512 1 NO CURRENT 10592541 2013/3/25 1 281474976710
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 824 209715200 512 1 NO ACTIVE 10591778 2013/3/25 1 10592541 2013/3/25 1
10 1 823 20971520 512 1 NO INACTIVE 10588922 2013/3/25 1 10591778 2013/3/25 1
8 rows selected
/***參考上面日誌資訊，控制檔案的檢查點scn及資料檔案及資料檔案頭的scn已經更新active日誌的first_change#/
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
10591778
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
CHECKPOINT_CHANGE#
------------------
10591778
21 rows selected
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
10591778
CHECKPOINT_CHANGE#
------------------
10591778
21 rows selected
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29618264/viewspace-2139667/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- alter system switch logfile手工日誌切換小記
- alter system archive log current和alter system switch logfileHive
- ALTER SYSTEM SWITCH LOGFILE ALTER SYSTEM ARCHIVELOG CURRENT 區別Hive
- alter system switch logfile和alter system archive log current的區別Hive
- alter system switch logfile 和 alter system archive log current 的區別Hive
- alter system archive log current / all / switch logfileHive
- [轉]alter system switch logfile和alter system archive log current的區別Hive
- zt：alter system switch logfile與ALTER SYSTEM ARCHIVE LOG CURRENT的區別Hive
- alter system switch logfile hang住的一種可能
- 通過 alter system dump logfile語句dump REDO及歸檔日誌資訊示例
- alter system switch logfile的執行時間是多久。
- [alter system dump學習1]alter system dump logfile
- v$log_v$logfile監控日誌檔案logfile切換及相關資訊_status
- Oracle日誌模式切換Oracle模式
- 在oracle10g執行alter system switch logfile並未開啟自動歸檔Oracle
- Oracle日誌切換及頻率跟蹤指令碼Oracle指令碼
- PostgreSQL切換日誌SQL
- Alter system in OracleOracle
- Oracle redo 日誌切換時間頻率Oracle Redo
- 停庫前執行alter system checkpoint 很重要!
- oracle rman備份歸檔日誌需要先切換日誌嗎Oracle
- 有關重做日誌的狀態及switch logfile時的不正常案例分析
- 幫朋友做的一個增加LOGFILE GROUP 和切換日誌的紀錄
- 檢視Oracle的redo日誌切換頻率Oracle
- REDO日誌切換頻率
- 檢視oracle的redo日誌組切換頻率Oracle
- alter database archivelog manual__導致oracle10g switch logfile不自動歸檔DatabaseHiveOracle
- dataguard之物理standby 日誌切換
- Oracle檢視每小時日誌切換量指令碼Oracle指令碼
- Oracle 11g alter日誌檔案位置Oracle
- oracle聯機日誌檔案REDO LOGFILE簡述Oracle
- oracle logfile日誌檔案常規操作小記Oracle
- alter system switch log file 與 archive log current/all 區別Hive
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌-切換日誌檔案Oracle
- oracle commit提交到底作了什麼OracleMIT
- SpringBoot切換預設日誌框架Spring Boot框架
- 【備份恢復】set newname切換日誌
- mongodb釋放記憶體-切換日誌MongoDB記憶體