[20200402]增量检查点时间间隔.txt

--//今天测试时遇到一个问题，做一个记录，生产数据库没有dg。

1.环境：

SYS@orcl> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

# ps -ef | grep ckp[t]
oracle   15386     1  0 Mar21 ?        00:05:46 ora_ckpt_orcl

# ls -l /proc/15386/fd | grep control
lrwx------. 1 oracle oinstall 64 2020-04-03 09:36:57 256 -> /u01/app/oracle/oradata/orcl/control01.ctl
lrwx------. 1 oracle oinstall 64 2020-04-03 09:36:57 257 -> /u01/app/oracle/fast_recovery_area/orcl/control02.ctl

# strace -q -f -e pwrite -rT  -p 15386  2>&1 | grep "pwrite(256,"
     0.000000 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\17M\0\0\2\0\0\0\0\0\0\0<\20\0\0"..., 16384, 49152) = 16384 <0.000133>
     3.000731 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\37L\0\0\2\0\0\0\0\0\0\0=\20\0\0"..., 16384, 49152) = 16384 <0.000134>
     3.000262 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\25W\0\0\2\0\0\0\0\0\0\0\274\17\0\0"..., 16384, 49152) = 16384 <0.000109>
     3.001270 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\333M\0\0\2\0\0\0\0\0\0\0\256\17\0\0"..., 16384, 49152) = 16384 <0.000135>
^C
--//你可以发现间隔3秒写1次控制文件。奇怪我以前遇到11.2.0.4版本都是间隔1秒写1次控制文件。这台机器不同的地方就是现在没有安装dg。
--//难道是没有安装dg的缘故吗？

2.在测试环境测试看看。

--//实际上我以前测试过：
SYS@book> alter system set log_archive_dest_state_2=defer;
System altered.

--//我的测试环境：
$ ps -ef | grep ckp[t]
oracle   34809     1  0 Apr01 ?        00:00:48 ora_ckpt_book

 $ strace -e pwrite -f -rT -p 34809 2>&1 | grep "pwrite(256"
     0.000000 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\346\212\0\0\2\0\0\0\0\0\0\0001\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     1.001185 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\350\212\0\0\2\0\0\0\0\0\0\0001\0\0\0"..., 16384, 49152) = 16384 <0.000029>
     1.001803 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\342\212\0\0\2\0\0\0\0\0\0\0001\0\0\0"..., 16384, 49152) = 16384 <0.000029>
     1.000784 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\340\212\0\0\2\0\0\0\0\0\0\0001\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     1.001273 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\356\212\0\0\2\0\0\0\0\0\0\0001\0\0\0"..., 16384, 49152) = 16384 <0.000029>
     1.001461 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\350\212\0\0\2\0\0\0\0\0\0\0001\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     1.001075 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\342\212\0\0\2\0\0\0\0\0\0\0001\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     1.002058 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\340\212\0\0\2\0\0\0\0\0\0\0001\0\0\0"..., 16384, 49152) = 16384 <0.000032>
^C
--//时间间隔1秒。

3.测试取消dg设置看看。
$ cat aa.sh
#! /bin/bash
sqlplus -s -l / as sysdba <<EOF
set define #
col spid new_value v_spid
startup pfile='/tmp/book.ora'
select  addr,pid,spid,program from v\$process where background=1 and PROGRAM like upper('%ckpt%');
prompt host strace -t -f -p #v_spid -e pwrite
host strace -rT -f -p #v_spid -e pwrite | grep "pwrite(256"
EOF

SYS@book> create pfile='/tmp/@.ora' from spfile ;
File created.

--//编辑/tmp/book.ora,注解dg配置。
$ grep "^#" /tmp/book.ora
###*.db_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'
###*.fal_client='book'
###*.fal_server='bookdg'
###*.log_archive_config='DG_CONFIG=(book,bookdg)'
###*.log_archive_dest_2='SERVICE=bookdg LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg'
###*.log_archive_dest_state_2='DEFER'
###*.log_checkpoints_to_alert=TRUE
###*.log_file_name_convert='/mnt/ramdisk/book','/mnt/ramdisk/book'

$ . aa.sh
ORACLE instance started.

Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.
ADDR                 PID SPID   PROGRAM
---------------- ------- ------ --------------------------
00000000862019E0      14 49057  oracle@xxxxx (CKPT)

host strace -t -f -p 49057 -e pwrite
     0.000000 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4|7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000040>
     3.001813 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\1777\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000042>
     3.002028 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4~7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     3.001609 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4q7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000033>
     3.000931 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4p7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.001112 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4s7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.000643 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4r7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000032>
     3.000908 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4u7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     3.001152 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4t7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.000683 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4w7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.000907 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4v7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     3.001146 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4I7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.000663 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4H7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.000901 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4K7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     3.001148 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4J7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     3.000705 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4M7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000032>
     3.000856 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4L7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.001149 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4O7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000029>
     3.001879 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4N7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000029>
     3.000721 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4A7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000033>
     3.002141 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4@7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     3.001660 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4C7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.000891 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4B7\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
^C

--//确实如果没有设置dg，增量检查点就是间隔3秒写1次。实际上lgwr也变成了3秒间隔。
--//另外我的测试开始前10个都是3秒，即使设置dg，以后才变成1秒的。

$ ps -ef | grep lgw[r]
oracle   49148     1  0 10:21 ?        00:00:00 ora_lgwr_book

 $ strace -T -tt  -f -p  49148 -e semtimedop
Process 49148 attached - interrupt to quit
10:27:01.344819 semtimedop(9797632, 0x7fff0a4883c0, 1, {0, 900000000}) = -1 EAGAIN (Resource temporarily unavailable) <0.900650>
10:27:02.245970 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000670>
10:27:05.247055 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000562>
10:27:08.248019 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000602>
10:27:11.249026 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000589>
10:27:14.250015 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000602>
10:27:17.251035 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000591>
10:27:20.252026 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000598>
10:27:23.253037 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000577>
10:27:26.254012 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000633>
10:27:29.255055 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000558>
10:27:32.256017 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000591>
10:27:35.257019 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000600>
10:27:38.258018 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000599>
10:27:41.259026 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000589>
10:27:44.260189 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = 0 <0.942013>
10:27:45.202594 semtimedop(9797632, 0x7fff0a4883c0, 1, {2, 60000000}) = -1 EAGAIN (Resource temporarily unavailable) <2.061021>
10:27:47.263981 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000656>
10:27:50.265042 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000587>
10:27:53.266038 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000590>
10:27:56.267034 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.000586>
10:27:59.268028 semtimedop(9797632, 0x7fff0a4883c0, 1, {3, 0}^C <unfinished ...>
--//注:我的测试库没有业务.

--//实际上只要你设置：
*.log_archive_dest_2='SERVICE=bookdg LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bookdg'
*.log_archive_dest_state_2='DEFER'

--//即使log_archive_dest_state_2='DEFER'都变成间隔1秒写控制文件。

--//补充dg的情况：
 $ cat aa.sh
#! /bin/bash
sqlplus -s -l / as sysdba <<EOF
set define #
col spid new_value v_spid
startup
--startup pfile=/tmp/book.ora
select  addr,pid,spid,program from v\$process where background=1 and PROGRAM like upper('%ckpt%');
prompt host strace -t -f -p #v_spid -e pwrite
host strace -rT -f -p #v_spid -e pwrite  2>&1 | grep "pwrite(256"
EOF

$ . aa.sh
ORACLE instance started.

Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.
ADDR                 PID SPID   PROGRAM
---------------- ------- ------ --------------------------
00000000862019E0      14 49827  oracle@xxxxx (CKPT)

host strace -t -f -p 49827 -e pwrite
     0.000000 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\326\17\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000037>
     3.001516 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\325\17\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.001685 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\0\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.001050 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\30\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.001999 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\24\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.001746 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\24\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.002064 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4\20\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000029>
     3.001551 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4x\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     3.000942 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4|\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     3.003560 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4|\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000027>
     1.001020 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4p\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000024>
     1.000989 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4~\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     1.001055 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4x\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000029>
     1.000758 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4z\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000030>
     1.000816 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4x\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000031>
     1.001274 pwrite(256, "\25\302\0\0\3\0\0\0\0\0\0\0\0\0\1\4n\10\0\0\2\0\0\0\0\0\0\0%\0\0\0"..., 16384, 49152) = 16384 <0.000029>

--//启动数据库开始间隔是3秒，以后变成1秒。