导航：首页 > 服务器 >

如何进行oracle switch logfile日志切换以及alter system checkpoint做了什么

发表于：2024-11-28 作者：千家信息网编辑

千家信息网最后更新 2024年11月28日，本篇文章为大家展示了如何进行oracle switch logfile日志切换以及alter system checkpoint做了什么，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详

千家信息网最后更新 2024年11月28日如何进行oracle switch logfile日志切换以及alter system checkpoint做了什么

本篇文章为大家展示了如何进行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