ORACLE（7）CHECKPOINT

最新推荐文章于 2023-02-08 16:37:54 发布

原创最新推荐文章于 2023-02-08 16:37:54 发布 · 257 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

14 篇文章

订阅专栏

本文解析了Oracle数据库中的检查点机制，介绍了检查点的概念及其在确保数据一致性和快速恢复中的作用。主要内容包括检查点的分类、原理及其实现过程，特别强调了其在减少实例恢复时间和确保脏缓冲区定期写入磁盘方面的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

主要来源：官方文档concept

什么是检查点

官方文档：

A checkpoint is a crucial mechanism in consistent database shutdowns, instance recovery, and Oracle Database operation generally.

The checkpoint has the following related meanings:
1.A data structure that indicates the checkpoint position, which is the SCN in the redo stream where instance recovery must begin：The checkpoint position is determined by the oldest dirty buffer in the database buffer cache. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header.
2.The writing of modified database buffers in the database buffer cache to disk

官方文档上的表述：检查点是一个数据库一致性、实例恢复、数据操作的关键机制。

其实主要的结构就是 RBA和SCN

检查点的作用

Oracle Database uses checkpoints to achieve the following goals：
Reduce the time required for recovery in case of an instance or media failure
Ensure that dirty buffers in the buffer cache are written to disk regularly
Ensure that all committed data is written to disk during a consistent shutdown

主要的目的是减少实例恢复的时间
确认脏块都已经写到磁盘
当数据库关闭的时候确认所有提交的数据已经写入磁盘

检查点分类

这里分成：

数据库检查点：

Consistent database shutdown

ALTER SYSTEM CHECKPOINT statement

Online redo log switch

ALTER DATABASE BEGIN BACKUP statement

增量检查点：

每三秒执行一次：这是因为DBWn会没3秒检查一次，
CKPT每3秒一次的工作和CKPT定期触发DBWR

检查点的原理

当进行此DML，需要首先将数据读入Buffer cache，修改数据的同时，oracle会记录重做（redo）信息用于恢复。因为有了重做的存在，oracle不需要在事务提交时（commit）立即将变化的数据写回磁盘（立即写可能太频繁导致性能大大降低），redo的正是为了在数据库崩溃之后，数据可以恢复。

Checkpoint queues contain the dirty buffers that are ordered by the LRBA(low RBA
第一次对此数据块修改对于的Redo Byte Address)。

比如第一次做update ,修改的Rba ，比如在ckptq的第一个bh的位置。如果再次修改，会更新rba对应的位置。但是不会再CKPTQ上挂新的bh。在CKPTQ上的第一个位置就是写入到控制文件的scn。CKPTQ的第一个位置，就是检查点。每三秒会检查ckpt队列，第一个位置写入到控制文件。

那当写进程，当把脏数据写入数据文件的时候，CKPTQ 对应的bh 点被从队列中去除，CKPT进程下次来扫描的时候，将新的检查点写入控制文件。

CKPT进程的观察

strace -tf  -o /tmp/lgwrtest1.log -p 25417

无任何操作的情况：pwrite是没3秒一次

25417 15:13:28 getrusage(RUSAGE_SELF, {ru_utime={156, 868152}, ru_stime={48, 234667}, ...}) = 0
25417 15:13:28 getrusage(RUSAGE_SELF, {ru_utime={156, 868152}, ru_stime={48, 234667}, ...}) = 0
25417 15:13:28 times({tms_utime=15686, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972543643
25417 15:13:28 times({tms_utime=15686, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972543643
25417 15:13:28 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4z\211\0\0\2\0\0\0\0\0\0\0\30\0\0\0"..., 16384, 49152) = 16384
25417 15:13:28 pwrite(257, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4z\211\0\0\2\0\0\0\0\0\0\0\30\0\0\0"..., 16384, 49152) = 16384
25417 15:13:28 times({tms_utime=15686, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972543644
25417 15:13:28 semtimedop(14581770, {{16, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
25417 15:13:31 getrusage(RUSAGE_SELF, {ru_utime={156, 869152}, ru_stime={48, 234667}, ...}) = 0
25417 15:13:31 getrusage(RUSAGE_SELF, {ru_utime={156, 869152}, ru_stime={48, 234667}, ...}) = 0
25417 15:13:31 times({tms_utime=15686, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972543944
25417 15:13:31 times({tms_utime=15686, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972543944
25417 15:13:31 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4{\211\0\0\2\0\0\0\0\0\0\0\30\0\0\0"..., 16384, 49152) = 16384
25417 15:13:31 pwrite(257, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4{\211\0\0\2\0\0\0\0\0\0\0\30\0\0\0"..., 16384, 49152) = 16384
25417 15:13:31 times({tms_utime=15686, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972543944
25417 15:13:31 semtimedop(14581770, {{16, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
25417 15:13:34 getrusage(RUSAGE_SELF, {ru_utime={156, 870152}, ru_stime={48, 236666}, ...}) = 0
25417 15:13:34 getrusage(RUSAGE_SELF, {ru_utime={156, 870152}, ru_stime={48, 236666}, ...}) = 0
25417 15:13:34 times({tms_utime=15687, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972544244
25417 15:13:34 times({tms_utime=15687, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972544244
25417 15:13:34 pread(256, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4\4\351\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 16384, 16384) = 16384
25417 15:13:34 pread(256, "\25\302\0\0\7\0\0\0\2546\0\0\377\377\1\4\353\\\0\0\0 \0\0\0\0\0\0\0\0\0\0"..., 16384, 114688) = 16384
25417 15:13:34 pread(256, "\25\302\0\0\t\0\0\0\2546\0\0\377\377\1\4\375\356\0\0\0\0\0\0\0\0\0\0G\23\306;"..., 16384, 147456) = 16384
25417 15:13:34 pread(256, "\25\302\0\0\v\0\0\0\3675\0\0\377\377\1\4\367\10\0\0\17\0\0\0\3N\"\0\0\0\0\0"..., 16384, 180224) = 16384
25417 15:13:34 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4|\211\0\0\2\0\0\0\0\0\0\0\30\0\0\0"..., 16384, 49152) = 16384
25417 15:13:34 pwrite(257, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4|\211\0\0\2\0\0\0\0\0\0\0\30\0\0\0"..., 16384, 49152) = 16384
25417 15:13:34 times({tms_utime=15687, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972544245
25417 15:13:34 semtimedop(14581770, {{16, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable)
25417 15:13:37 getrusage(RUSAGE_SELF, {ru_utime={156, 871151}, ru_stime={48, 238666}, ...}) = 0
25417 15:13:37 getrusage(RUSAGE_SELF, {ru_utime={156, 871151}, ru_stime={48, 238666}, ...}) = 0
25417 15:13:37 times({tms_utime=15687, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972544545
25417 15:13:37 times({tms_utime=15687, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972544545
25417 15:13:37 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4}\211\0\0\2\0\0\0\0\0\0\0\30\0\0\0"..., 16384, 49152) = 16384
25417 15:13:37 pwrite(257, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4}\211\0\0\2\0\0\0\0\0\0\0\30\0\0\0"..., 16384, 49152) = 16384
25417 15:13:37 times({tms_utime=15687, tms_stime=4823, tms_cutime=0, tms_cstime=0}) = 4972544546

连续alter system checkpoint

25417 15:15:21 times({tms_utime=15690, tms_stime=4829, tms_cutime=0, tms_cstime=0}) = 4972554963
25417 15:15:21 times({tms_utime=15690, tms_stime=4829, tms_cutime=0, tms_cstime=0}) = 4972554963
25417 15:15:21 semtimedop(14581770, {{16, -1, 0}}, 1, {1, 20000000}) = 0
25417 15:15:21 pread(256, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4@\353\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 16384, 16384) = 16384
25417 15:15:21 pread(257, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4@\353\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 16384, 16384) = 16384
25417 15:15:21 pread(256, "\25\302\0\0\10\0\0\0\2616\0\0\377\377\1\4\274\\\0\0X \0\0\0\0\0\0\0\0\0\0"..., 16384, 131072) = 16384
25417 15:15:21 pread(256, "\25\302\0\0\n\0\0\0\2616\0\0\377\377\1\4\330\"\0\0\0\0\0\0\0\0\0\0G\23\306;"..., 16384, 163840) = 16384
25417 15:15:21 times({tms_utime=15690, tms_stime=4829, tms_cutime=0, tms_cstime=0}) = 4972555023
25417 15:15:21 times({tms_utime=15690, tms_stime=4829, tms_cutime=0, tms_cstime=0}) = 4972555023
25417 15:15:21 semctl(14581770, 14, SETVAL, 0x1) = 0
25417 15:15:21 pread(256, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4@\353\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 16384, 16384) = 16384
25417 15:15:21 pread(257, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4@\353\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 16384, 16384) = 16384
25417 15:15:21 pread(256, "\25\302\0\0\10\0\0\0\2616\0\0\377\377\1\4\274\\\0\0X \0\0\0\0\0\0\0\0\0\0"..., 16384, 131072) = 16384
25417 15:15:21 pread(256, "\25\302\0\0\n\0\0\0\2616\0\0\377\377\1\4\330\"\0\0\0\0\0\0\0\0\0\0G\23\306;"..., 16384, 163840) = 16384
25417 15:15:21 times({tms_utime=15690, tms_stime=4829, tms_cutime=0, tms_cstime=0}) = 4972555023
25417 15:15:21 pread(256, "\25\302\0\0\f\0\0\0\2616\0\0\377\377\1\4\16\222\0\0\17\0\0\0%\202\"\0\0\0\0\0"..., 16384, 196608) = 16384
25417 15:15:21 pread(256, "\25\302\0\0\20\0\0\0\2616\0\0\377\377\1\4Lm\0\0\200\242\0\0\0 \0\0\16\0\0\0"..., 16384, 262144) = 16384
25417 15:15:21 pread(258, "\v\242\0\0\1\0@\0\0\0\0\0\0\0\1\4\275\374\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pwrite(258, "\v\242\0\0\1\0@\0\0\0\0\0\0\0\1\4\243\374\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pread(259, "\v\242\0\0\1\0\200\0\0\0\0\0\0\0\1\4g\212\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pwrite(259, "\v\242\0\0\1\0\200\0\0\0\0\0\0\0\1\4y\212\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pread(260, "\v\242\0\0\1\0\300\0\0\0\0\0\0\0\1\4\270\343\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pwrite(260, "\v\242\0\0\1\0\300\0\0\0\0\0\0\0\1\4\246\343\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pread(261, "\v\242\0\0\1\0\0\1\0\0\0\0\0\0\1\4\36\256\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pwrite(261, "\v\242\0\0\1\0\0\1\0\0\0\0\0\0\1\4\0\256\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pread(262, "\v\242\0\0\1\0@\1\0\0\0\0\0\0\1\4\364\313\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pwrite(262, "\v\242\0\0\1\0@\1\0\0\0\0\0\0\1\4\362\313\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pread(263, "\v\242\0\0\1\0\200\1\0\0\0\0\0\0\1\4\205\300\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pwrite(263, "\v\242\0\0\1\0\200\1\0\0\0\0\0\0\1\4\207\300\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pread(264, "\v\242\0\0\1\0\300\1\0\0\0\0\0\0\1\4\324@\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pwrite(264, "\v\242\0\0\1\0\300\1\0\0\0\0\0\0\1\4\332@\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 8192, 8192) = 8192
25417 15:15:21 pwrite(256, "\25\302\0\0\v\0\0\0\2626\0\0\377\377\1\4\n\222\0\0\17\0\0\0&\202\"\0\0\0\0\0"..., 16384, 180224) = 16384
25417 15:15:21 pwrite(257, "\25\302\0\0\v\0\0\0\2626\0\0\377\377\1\4\n\222\0\0\17\0\0\0&\202\"\0\0\0\0\0"..., 16384, 180224) = 16384
25417 15:15:21 pwrite(256, "\25\302\0\0\17\0\0\0\2626\0\0\377\377\1\4Om\0\0\200\242\0\0\0 \0\0\16\0\0\0"..., 16384, 245760) = 16384
25417 15:15:21 pwrite(257, "\25\302\0\0\17\0\0\0\2626\0\0\377\377\1\4Om\0\0\200\242\0\0\0 \0\0\16\0\0\0"..., 16384, 245760) = 16384
25417 15:15:21 pwrite(256, "\25\302\0\0\t\0\0\0\2626\0\0\377\377\1\4\330\"\0\0\0\0\0\0\0\0\0\0G\23\306;"..., 16384, 147456) = 16384
25417 15:15:21 pwrite(257, "\25\302\0\0\t\0\0\0\2626\0\0\377\377\1\4\330\"\0\0\0\0\0\0\0\0\0\0G\23\306;"..., 16384, 147456) = 16384
25417 15:15:21 pwrite(256, "\25\302\0\0\7\0\0\0\2626\0\0\377\377\1\4\353\\\0\0\0 \0\0\0\0\0\0\0\0\0\0"..., 16384, 114688) = 16384
25417 15:15:21 pwrite(257, "\25\302\0\0\7\0\0\0\2626\0\0\377\377\1\4\353\\\0\0\0 \0\0\0\0\0\0\0\0\0\0"..., 16384, 114688) = 16384
25417 15:15:21 pwrite(256, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4C\353\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 16384, 16384) = 16384
25417 15:15:21 pwrite(257, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4C\353\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 16384, 16384) = 16384

大批量更新的时候，截取一段：

25417 16:14:44 pwrite(257, "\25\302\0\0\20\0\0\0\216<\0\0\377\377\1\4\261\21\0\0\200\242\0\0\0 \0\0\16\0\0\0"..., 16384, 262144) = 16384
25417 16:14:44 pwrite(256, "\25\302\0\0\t\0\0\0\216<\0\0\377\377\1\4\312I\0\0\0\0\0\0\0\0\0\0G\23\306;"..., 16384, 147456) = 16384
25417 16:14:44 pwrite(257, "\25\302\0\0\t\0\0\0\216<\0\0\377\377\1\4\312I\0\0\0\0\0\0\0\0\0\0G\23\306;"..., 16384, 147456) = 16384
25417 16:14:44 pwrite(256, "\25\302\0\0\7\0\0\0\216<\0\0\377\377\1\4\253\370\0\0@ \0\0\0\0\0\0\0\0\0\0"..., 16384, 114688) = 16384
25417 16:14:44 pwrite(257, "\25\302\0\0\7\0\0\0\216<\0\0\377\377\1\4\253\370\0\0@ \0\0\0\0\0\0\0\0\0\0"..., 16384, 114688) = 16384
25417 16:14:44 pwrite(256, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4#\237\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 16384, 16384) = 16384
25417 16:14:44 pwrite(257, "\25\302\0\0\1\0\0\0\0\0\0\0\0\0\1\4#\237\0\0\0\0\0\0\0\0 \v\7K\10\264"..., 16384, 16384) = 16384
25417 16:14:45 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\301\350\0\0\2\0\0\0\0\0\0\0\227\7\0\0"..., 16384, 49152) = 16384
25417 16:14:45 pwrite(257, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\301\350\0\0\2\0\0\0\0\0\0\0\227\7\0\0"..., 16384, 49152) = 16384
25417 16:14:47 pwrite(256, "\25\302\0\0\v\0\0\0\221<\0\0\377\377\1\4\0321\0\0\17\0\0\0A\316)\0\0\0j\t"..., 16384, 180224) = 16384
25417 16:14:47 pwrite(257, "\25\302\0\0\v\0\0\0\221<\0\0\377\377\1\4\0321\0\0\17\0\0\0A\316)\0\0\0j\t"..., 16384, 180224) = 16384

中间日志切换赵成的。

最后可以测试，
1.将redo设置的足够大

ALTER DATABASE ADD LOGFILE GROUP 4('/u01/app/oracle/oradata/study/redo04a.log') SIZE 2G;
ALTER DATABASE ADD LOGFILE GROUP 5('/u01/app/oracle/oradata/study/redo05a.log') SIZE 2G;

alter system switch logfile;


SQL> select group#,STATUS from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
	 1 INACTIVE
	 2 INACTIVE
	 3 ACTIVE
	 4 CURRENT
	 5 UNUSED
	 
再切几次；
SQL> select group#,STATUS,bytes/1024/1024/1024 from v$log;

    GROUP# STATUS
---------- ------------------------------------------------
	 1 INACTIVE
	 2 INACTIVE
	 3 INACTIVE
	 4 CURRENT
	 5 INACTIVE

	 
	 
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

2.将fast_start_mttr_target设置为3600

SQL> alter system set fast_start_mttr_target=3601 scope=both;

System altered.

SQL> show parameter fast_start_mttr_target

NAME				     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
fast_start_mttr_target		     integer
3600

观察，我这边看到都是没3秒一次pwrite
但是具体的为什么pwrite会连续有很多个，不是很清楚。