2.3.3 LOG_CHECKPOINTS_TO_ALTER参数

1、数据库版本：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

 2、配置

SQL> show parameter log_checkpoints

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     TRUE

 

初始值为FALSE，通过语句调整：alter system set log_checkpoints_to_alert = true;

3、执行alter system switch logfile（连续执行四次）,后观察alert日志文件

第一次：

Sat Nov 12 11:44:39 2011
Beginning log switch checkpoint up to RBA [0x15.2.10], SCN: 1140770
Thread 1 advanced to log sequence 21 (LGWR switch)
  Current log# 3 seq# 21 mem# 0: F:\APP\JOHN\ORADATA\ORCL\REDO03.LOG

第二次：

Sat Nov 12 11:46:55 2011
Beginning log switch checkpoint up to RBA [0x16.2.10], SCN: 1141048
Thread 1 advanced to log sequence 22 (LGWR switch)
  Current log# 1 seq# 22 mem# 0: F:\APP\JOHN\ORADATA\ORCL\REDO01.LOG
第三次：

Sat Nov 12 11:47:17 2011
Thread 1 cannot allocate new log, sequence 23
Checkpoint not complete
  Current log# 1 seq# 22 mem# 0: F:\APP\JOHN\ORADATA\ORCL\REDO01.LOG
Sat Nov 12 11:47:20 2011
Completed checkpoint up to RBA [0x15.2.10], SCN: 1140770
Beginning log switch checkpoint up to RBA [0x17.2.10], SCN: 1141067
Thread 1 advanced to log sequence 23 (LGWR switch)
  Current log# 2 seq# 23 mem# 0: F:\APP\JOHN\ORADATA\ORCL\REDO02.LOG

第四次： 
Sat Nov 12 11:48:01 2011
Thread 1 cannot allocate new log, sequence 24
Checkpoint not complete
  Current log# 2 seq# 23 mem# 0: F:\APP\JOHN\ORADATA\ORCL\REDO02.LOG
Sat Nov 12 11:48:02 2011
Completed checkpoint up to RBA [0x16.2.10], SCN: 1141048

 

当执行alter system switch logfile时，触发checkpoint.但不是立即执行。

第一次 log02->log03

第二次 log03->log01

第三次 log01->log02  刚开始失败，原因为第一次日志切换后，checkpoint未完成。所以日志无法切换。log02中的数据，未安全写入数据文件

大概5分钟后，SCN: 1141067的checkpoint结束。

Sat Nov 12 11:53:05 2011
Completed checkpoint up to RBA [0x17.2.10], SCN: 1141067
Sat Nov 12 11:53:53 2011

 

不知道为何，出现了SCN: 1141097的checkpoint？？？？？？

Completed checkpoint up to RBA [0x18.2.10], SCN: 1141097

 

为了保证检查点不会滞后整个日志文件，Oracle限制最长的检查点跨度不超过最小日志大小的90%。所以数据库在运行过程中会根据LOG TAIL进行计算，主动触发增量检查点。
Sat Nov 12 11:54:26 2011
Incremental checkpoint up to RBA [0x18.1e.0], current log tail at RBA [0x18.189.0]