[20200402]增量檢查點時間間隔.txt

[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秒。