本篇文章为大家展示了如何进行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
上述内容就是如何进行oracle switch logfile日志切换以及alter system checkpoint做了什么，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
