[20150911]关于增量检查点.txt
--昨天被人问及我们的测试库，增量检查点的问题，我告诉对方，我设置了参数log_checkpoints_to_alert=true，当写增量检查点时会
--同时写入alert*.log日志文件，被问及时间间隔问题，实际上增量检查点的写入与许多因素相关，比如如果数据库修改很多，数据缓存
--大小，redo file的大小以及数量都存在关系。
--但是我的测试库基本我自己用，没事看看增量检查点的间隔是多少，会与什么参数有关。
$ grep -B1 "^Incremental checkpoint up to RBA" /u01/app/oracle11g/diag/rdbms/test/test/trace/alert_test.log | grep ' 2015' > /tmp/aa.txt
--使用vim编辑，删除开头4个字符。
:%s/^....//
SCOTT@test> create table t ( d date);
Table created.
--选择合适的日期格式。
SCOTT@test> alter session set NLS_DATE_FORMAT='Mon dd hh24:mi:ss YYYY';
Session altered.
SCOTT@test> select sysdate from dual ;
SYSDATE
-----------------------
Sep 11 10:25:26 2015
--修改/tmp/aa.txt文件加入如下：
LOAD DATA
INFILE *
BADFILE './a.BAD'
DISCARDFILE './a.DSC'
APPEND INTO TABLE SCOTT.T
Fields terminated by ";" Optionally enclosed by '"'
(
D DATE "Mon DD HH24:MI:SS YYYY" NULLIF (D="NULL")
)
BEGINDATA
--执行如下导入":
$ sqlldr scott/btbtms control=/tmp/aa.txt
SELECT *
FROM ( SELECT TRUNC ( (d1 - d) * 86400) d2, COUNT (*)
FROM ( SELECT d, LEAD (d, 1) OVER (ORDER BY d) d1
FROM t
ORDER BY d)
GROUP BY TRUNC ( (d1 - d) * 86400)
ORDER BY 2 DESC)
WHERE ROWNUM <= 10;
D2 COUNT(*)
---------- ----------
1830 3082
1826 1587
1829 500
1832 113
1827 60
1825 46
1833 42
1834 21
1836 3
3665 2
10 rows selected.
--可以确定大约1830秒会发出一个增量检查点。
SCOTT@test> @hide _dbwr_scan_interval
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
-------------------------------- ----------------------------------------------------- -------------- -------------- ----------------------
_dbwr_scan_interval dbwriter scan interval TRUE 300 300
SCOTT@test> @hide _disable_selftune_checkpointing
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
-------------------------------- ----------------------------------------------------- -------------- -------------- ----------------------
_disable_selftune_checkpointing Disable self-tune checkpointing TRUE FALSE FALSE
SCOTT@test> @hide log_checkpoint_timeout
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
-------------------------------- ----------------------------------------------------- -------------- -------------- ----------------------
log_checkpoint_timeout Maximum time interval between checkpoints in seconds TRUE 1800 1800
--可以发现对于我的测试库受这个参数log_checkpoint_timeout控制(因为没有什么业务)。不知道是否意味着写出需要30秒，还是我的测
--试机器磁盘IO不行(不是存储)？
$ strings spfiletest.ora | grep checkpoint
*.log_checkpoints_to_alert=TRUE
--很明显这个是默认参数，修改看看：
SCOTT@test> alter system set log_checkpoint_timeout=60 scope=memory;
System altered.
--观察alert的输出情况：
Fri Sep 11 11:13:37 2015
Incremental checkpoint up to RBA [0xe09.12332.0], current log tail at RBA [0xe09.12460.0]
Fri Sep 11 11:14:38 2015
Incremental checkpoint up to RBA [0xe09.12468.0], current log tail at RBA [0xe09.124a5.0]
Fri Sep 11 11:15:39 2015
Incremental checkpoint up to RBA [0xe09.124aa.0], current log tail at RBA [0xe09.125b8.0]
Fri Sep 11 11:16:40 2015
Incremental checkpoint up to RBA [0xe09.125bd.0], current log tail at RBA [0xe09.12601.0]
Fri Sep 11 11:17:40 2015
Incremental checkpoint up to RBA [0xe09.12606.0], current log tail at RBA [0xe09.1265e.0]
Fri Sep 11 11:18:41 2015
Incremental checkpoint up to RBA [0xe09.12662.0], current log tail at RBA [0xe09.126ac.0]
Fri Sep 11 11:19:42 2015
Incremental checkpoint up to RBA [0xe09.126af.0], current log tail at RBA [0xe09.126f1.0]
Fri Sep 11 11:20:43 2015
Incremental checkpoint up to RBA [0xe09.126f4.0], current log tail at RBA [0xe09.12738.0]
Fri Sep 11 11:21:44 2015
Incremental checkpoint up to RBA [0xe09.12739.0], current log tail at RBA [0xe09.1277e.0]
Fri Sep 11 11:22:45 2015
Incremental checkpoint up to RBA [0xe09.1277f.0], current log tail at RBA [0xe09.127f1.0]
Fri Sep 11 11:23:31 2015
Beginning global checkpoint up to RBA [0xe09.1281c.10], SCN: 13200881205
Completed checkpoint up to RBA [0xe09.1281c.10], SCN: 13200881205
Fri Sep 11 11:23:46 2015
Incremental checkpoint up to RBA [0xe09.1281d.0], current log tail at RBA [0xe09.1283a.0]
Fri Sep 11 11:24:09 2015
Beginning global checkpoint up to RBA [0xe09.12851.10], SCN: 13200881256
Completed checkpoint up to RBA [0xe09.12851.10], SCN: 13200881256
Fri Sep 11 11:24:23 2015
Beginning log switch checkpoint up to RBA [0xe0a.2.10], SCN: 13200881278
Thread 1 advanced to log sequence 3594 (LGWR switch)
Current log# 1 seq# 3594 mem# 0: /u01/app/oracle11g/oradata/test/redo01.log
Fri Sep 11 11:24:24 2015
LNS: Standby redo logfile selected for thread 1 sequence 3594 for destination LOG_ARCHIVE_DEST_2
Fri Sep 11 11:24:24 2015
Archived Log entry 5321 added for thread 1 sequence 3593 ID 0x806ffa4c dest 1:
Fri Sep 11 11:24:47 2015
Incremental checkpoint up to RBA [0xe09.12851.0], current log tail at RBA [0xe0a.f0.0]
--可以发现间隔仅仅60+1秒。另外我中间执行2次full checkpoint，可以发现它完全不受其它因素的影响。