SCN(2)

system commit number Vs system change number
system commit number ：仅仅表示 提交时候产生的 SCN ,是 SCN 的一部分 ，不等于 SCN
system change number : 数据库发生任何变化所产生的 SCN ,指所有的 SCN

 

The SCN will -- but not the checkpoint_change#. Thats the last SCN checkpointed:

产生 SCN 的原因很多！
DML/commit/rollback/job/数据库递归调用/关闭打开数据库/日志缓冲区若是并行分配…… 很多情况，任何数据库的改变都会导致 SCN 的增加！

从这里可以查：
SQL>select GROUP#,FIRST_CHANGE# from v$log;--日志文件同步号
SQL>select file#,checkpoint_change# from v$datafile;--数据文件同步号 
SQL>select checkpoint_change# from v$database;--控制文件同步号 system change number
SQL>select * from v$logfile;

SQL>alter system checkpoint;设置
SQL>SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;查询
SQL>alter system switch logfile；切换日志

案例
> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
725781

> commit;
Commit complete.

> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
725781

> alter system checkpoint;
System altered.

> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
725798

> alter system checkpoint;
System altered.

> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
725799

It'll change after a checkpoint.

scn>=CHECKPOINT_CHANGE#
scn是一个一只增的数字，有时候在control file, datafile 和redo log file的scn会不一致，经过一段时间后经过一次checkpoint，这几个文件得scn号就同步了，然后又开始不同步，又checkpoint，就这样循环。形象一点就是scn始终要快checkpoint半拍，但同步那一刻是一样的

System Change Number (SCN)
An SCN defines a committed version of the database. A query reports the contents of the database
as it looked at some specific SCN. An SCN is allocated and saved in the header of a redo record
that commits a transaction. An SCN may also be saved in a record when it is necessary to mark the
redo as being allocated after a specific SCN. SCN’s are also allocated and stored in other data
structures such as the controlfile or datafile headers. An SCN is at least 48 bits long. Thus they can
be allocated at a rate of 16,384 SCN’s per second for over 534 years without running out of them.
We will run out of SCN’s in June, 2522 AD (we use 31 day months for time stamps).

v$database中的checkpoint_change#是谁的SCN？
是在最后一次checkpoint时
几个文件都同步的scn

10 如何设置log_checkpoint_interval的值？
1）日志转换间的检查点
如5M的重做日志文件与512字节的OS块，日志间有四个检查点。
LOG_CHECKPOINT_INTERVAL = 日志大小/OS块大小/检查点出现次数
= 5242880 / 512 / 4 = 2560
2）仅仅在一个日志转换上的检查点
LOG_CHECKPOINT_INTERVAL = CEIL（日志大小/OS块大小 + 1）
= CEIL（5242880 / 512 + 1）= 10241

那为什么checkpoint太频繁会导致not complete呢？

一个日志文件中可能发生n多次检查点，在日志切换时，需要触发一个检查点，但此时ckpt进程却没有到达检查点产生的时机，所以就会等待嘛，但如果LOG_CHECKPOINT_INTERVAL 设置得当，刚好在日志切换时产生CP，那就是最好的，不会产生cp not complete的问题