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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle rman備份歸檔日誌需要先切換日誌嗎Oracle
- 檢視Oracle的redo日誌切換頻率Oracle
- 檢視oracle的redo日誌組切換頻率Oracle
- Mysql 事務日誌(Ib_logfile)MySql
- SpringBoot切換預設日誌框架Spring Boot框架
- alter table drop unused columns checkpoint
- ORA-279 signalled during: alter database recover logfileDatabase
- alter system set ... scope=... 中的scope的含義是什麼？
- One Switch for Mac 快速切換工具Mac
- 【PG】PostgreSQL 預寫日誌(WAL)、checkpoint、LSNSQL
- Selenium多表單切換switch_to.frame
- One Switch for Mac(快速切換工具)1.23Mac
- ALTER SYSTEM FLUSH BUFFER_POOL
- oracle之 如何 dump logfileOracle
- MySQL什麼時候輪換二進位制日誌MySql
- kvm切換器是什麼?
- mysql關於redo事務日誌ib_logfile的理解MySql
- checkpoint是什麼(zt)
- PG：checkpoint是什麼
- Oracle設定日誌引數-ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;OracleDatabase
- 透過shell指令碼監控日誌切換頻率指令碼
- ORACLE之手動註冊監聽listener。alter system set local_listener="XXX"Oracle
- 日誌審計是什麼？為什麼企業需要日誌審計？
- oracle alert日誌Oracle
- 日誌脫敏是什麼意思？為什麼要做日誌脫敏？用什麼工具好？
- 【TUNE_ORACLE】你建立的索引為什麼不工作了？（一）Oracle索引
- 【TUNE_ORACLE】你建立的索引為什麼不工作了？（三）Oracle索引
- 【TUNE_ORACLE】你建立的索引為什麼不工作了？（二）Oracle索引
- 如何優雅阻止view UI 的 Switch 切換？ViewUI
- 資料庫週刊65丨TiDB 5.0 GA版釋出；Oracle日誌切換頻繁問題……資料庫TiDBOracle
- 老闆下了死命令，要把日誌系統切換到Logback
- mysql關於ib_logfile事務日誌和binary log二進位制日誌的區別MySql
- oracle dg切換操作示例Oracle
- 關於log file switch and checkpoint機制
- oracle刪除日誌Oracle
- oracle歸檔日誌Oracle
- 2.7.6.2.1 ALTER SYSTEM SET語句中的SCOPE子句
- Oracle Temp 表空間切換Oracle
- oracle11g dataguard切換Oracle