1,当数据库clean shutdown时 (normal，immediate)，ckpt进程会将scn写到四个地方去，其中control file中有3个，datafile中1个
----control file的三个地方
1.System checkpoint SCN ===========> (SYSTEM CHECKPOINT SCN in control file)
SQL> select checkpoint_change# from v$database;
2.Datafile checkpoint SCN ===============> (DATAFILE CHECKPOINT SCN in control file)
SQL> select name,checkpoint_change# from v$datafile
3.Stop SCN ======================> (STOP SCN in control file)
SQL> select name,last_change# from v$datafile --正常情况下，stop scn一定是null
---datafile 的一个地方
4.Start SCN ================================> (DATAFILE HEADER)
SQL> select name,checkpoint_change# from v$datafile_header
问题1： 为什么control file 要在两个地方同时写scn呢 即 第 1,2 两个地方
回答： 1， 因为如果一个tablespace被设置为read-only mode 则他的scn将停止增长，而system checkpoint 还在不断的增加，所以要填两个地方
2，还有一种情况就是， create tablespace tbname datafile '/.../tbname01.dbf' reuse 这个datafile的scn也是不一样的
########################################实验1 ： 正常shutdown 数据库查看scn情况##########################
首先将数据库mount
###(1) System checkpoint SCN ===========> (SYSTEM CHECKPOINT SCN in control file)
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
494794
###(2) Datafile checkpoint SCN ===============> (DATAFILE CHECKPOINT SCN in control file)
SQL> select name,checkpoint_change# from v$datafile ;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/home/oracle/datafiles/JEFF01/system01.dbf 494794
/home/oracle/datafiles/JEFF01/undotbs01.dbf 494794
/home/oracle/datafiles/JEFF01/sysaux01.dbf 494794
/home/oracle/datafiles/JEFF01/users01.dbf 494794
###(3).Stop SCN ======================> (STOP SCN in control file)
SQL> select name,last_change# from v$datafile;
NAME LAST_CHANGE#
-------------------------------------------------- ------------
/home/oracle/datafiles/JEFF01/system01.dbf 494794
/home/oracle/datafiles/JEFF01/undotbs01.dbf 494794
/home/oracle/datafiles/JEFF01/sysaux01.dbf 494794
/home/oracle/datafiles/JEFF01/users01.dbf 494794
###4.Start SCN ================================> (DATAFILE HEADER)
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/home/oracle/datafiles/JEFF01/system01.dbf 494794
/home/oracle/datafiles/JEFF01/undotbs01.dbf 494794
/home/oracle/datafiles/JEFF01/sysaux01.dbf 494794
/home/oracle/datafiles/JEFF01/users01.dbf 494794
结论：此时可以看到，正常shutdown时 stop scn不为null 而是等于 第4步的 start scn
---此时打开数据库 则发现 stop scn被更新为 null
SQL> alter database open;
Database altered.
SQL> select name,last_change# from v$datafile;
NAME LAST_CHANGE#
-------------------------------------------------- ------------
/home/oracle/datafiles/JEFF01/system01.dbf
/home/oracle/datafiles/JEFF01/undotbs01.dbf
/home/oracle/datafiles/JEFF01/sysaux01.dbf
/home/oracle/datafiles/JEFF01/users01.dbf
所以数据库open时会按如下两个顺序进行检查是否需要recovery
1， datafile_header.scn(即start scn) 是否等于 (DATAFILE CHECKPOINT SCN in control file).scn
即：v$datafile_header.checkpoint_change# 是否等于 v$datafile.checkpoint_change#
2， datafile_header.scn(即start scn) 是否等于 v$datafile.last_change# (即stop scn) 或 stop scn 是否等于null
即：v$datafile_header.checkpoint_change# 是否等于 v$datafile.last_change#
3，如果检查都相等，则打开数据库，否则进行media recovery
#############################################################################################################################
########################################实验2 ： 非正常shutdown （shutdown abort） 数据库查看scn情况##########################
首先将数据库mount
###(1) System checkpoint SCN ===========> (SYSTEM CHECKPOINT SCN in control file)
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
494795
###(2) Datafile checkpoint SCN ===============> (DATAFILE CHECKPOINT SCN in control file)
SQL> select name,checkpoint_change# from v$datafile ;
NAME CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
/home/oracle/datafiles/JEFF01/system01.dbf 494795
/home/oracle/datafiles/JEFF01/undotbs01.dbf 494795
/home/oracle/datafiles/JEFF01/sysaux01.dbf 494795
/home/oracle/datafiles/JEFF01/users01.dbf 494795
###(3).Stop SCN ======================> (STOP SCN in control file)
SQL> select name,last_change# from v$datafile;
NAME LAST_CHANGE#
---------------------------------------------------------------------------------------------------- ------------
/home/oracle/datafiles/JEFF01/system01.dbf
/home/oracle/datafiles/JEFF01/undotbs01.dbf
/home/oracle/datafiles/JEFF01/sysaux01.dbf
/home/oracle/datafiles/JEFF01/users01.dbf
###4.Start SCN ================================> (DATAFILE HEADER)
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
/home/oracle/datafiles/JEFF01/system01.dbf 494795
/home/oracle/datafiles/JEFF01/undotbs01.dbf 494795
/home/oracle/datafiles/JEFF01/sysaux01.dbf 494795
/home/oracle/datafiles/JEFF01/users01.dbf 494795
结论： 可以看到，数据库如果是非正常关闭shutdown abort 断电，等 则stop scn 为 null 需要进行recovery
#########################################################################################################################
###########################crash recovery vs media recovery##################################################################
1, crash recovery：
在数据库启动时如果STOP SCN = NULL
即：v$datafile.last_change# = null 则数据库需要crash recovery
crash recovery 干两件事
(1), 根据start scn，roll forward redo log 重做已经提交了的日志
(2), 根据start scn , 从 rollback segments 里 回滚未完成的事务
2, media recovery
datafile_header.scn(即start scn) <> (DATAFILE CHECKPOINT SCN in control file).scn
即：
v$datafile_header.checkpoint_change# <>v$datafile.checkpoint_change# 则数据库需要media recovery
#########################################################################################################################
######################################实验3 删除数据文件，然后将数据文件从rman中restore出来观察scn的情况###################################################################################
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
577669
SQL> select name,checkpoint_change# from v$datafile ;
NAME CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
/home/oracle/datafiles/JEFF01/system01.dbf 577669
/home/oracle/datafiles/JEFF01/undotbs01.dbf 577669
/home/oracle/datafiles/JEFF01/sysaux01.dbf 577669
/home/oracle/datafiles/JEFF01/users01.dbf 577669
/home/oracle/datafiles/JEFF01/T01.dbf
577669
SQL> select name,last_change# from v$datafile;
NAME LAST_CHANGE#
---------------------------------------------------------------------------------------------------- ------------
/home/oracle/datafiles/JEFF01/system01.dbf
/home/oracle/datafiles/JEFF01/undotbs01.dbf
/home/oracle/datafiles/JEFF01/sysaux01.dbf
/home/oracle/datafiles/JEFF01/users01.dbf
/home/oracle/datafiles/JEFF01/T01.dbf
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
/home/oracle/datafiles/JEFF01/system01.dbf 0 --这里可以看到有0 ，是因为这个语句是我在restore的过程中执行的，0的说明还没有restore回来
/home/oracle/datafiles/JEFF01/undotbs01.dbf 0
/home/oracle/datafiles/JEFF01/sysaux01.dbf 0
/home/oracle/datafiles/JEFF01/users01.dbf 537050
/home/oracle/datafiles/JEFF01/T01.dbf 537051
从这里可以看出： v$datafile_header.checkpoint_change# < v$datafile.checkpoint_change#。
原因是： 数据文件是从rman中恢复出来的，所以scn一定比当前控制文件中记录的scn 要小
######################################实验4控制文件丢失###################################################################################
1，从以前的rman备份中将控制文件restore出来
RMAN> restore controlfile from '/home/oracle/rman/backup_p1_s8_t740902469_20110120.bak';
2，从这里我们可以看到scn的情况
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
536658
SQL> select name,checkpoint_change# from v$datafile ;
NAME CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
/home/oracle/datafiles/JEFF01/system01.dbf 537050
/home/oracle/datafiles/JEFF01/undotbs01.dbf 537051
/home/oracle/datafiles/JEFF01/sysaux01.dbf 537051
/home/oracle/datafiles/JEFF01/users01.dbf 537050
/home/oracle/datafiles/JEFF01/T01.dbf 537051
SQL> select name,last_change# from v$datafile;
NAME LAST_CHANGE#
---------------------------------------------------------------------------------------------------- ------------
/home/oracle/datafiles/JEFF01/system01.dbf
/home/oracle/datafiles/JEFF01/undotbs01.dbf
/home/oracle/datafiles/JEFF01/sysaux01.dbf
/home/oracle/datafiles/JEFF01/users01.dbf
/home/oracle/datafiles/JEFF01/T01.dbf
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
---------------------------------------------------------------------------------------------------- ------------------
/home/oracle/datafiles/JEFF01/system01.dbf 598203
/home/oracle/datafiles/JEFF01/undotbs01.dbf 598203
/home/oracle/datafiles/JEFF01/sysaux01.dbf 598203
/home/oracle/datafiles/JEFF01/users01.dbf 598203
0
从这里我们可以看到 v$datafile_header.checkpoint_change# > v$datafile.checkpoint_change#。
原因： 因为控制文件是从rman中restore出来的，所以肯定要比当前数据文件中记录的scn要小
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/7668955/viewspace-1052410/ ，如需转载，请注明出处，否则将追究法律责任。
