参考自：http://blog.csdn.net/tianlesoftware/article/details/5251916当发生checkpoint时，会把SCN写到四个地方去：三个地方于control file内，一个在datafile header。一、实验，如下：--Control fil e三个地方为：1.1 System checkpoint SCN ===========> (SYSTEM CHECKPOINT SCN in control file)SQL> select checkpoint_change# from v$database;CHECKPOINT_CHANGE#------------------ 3779864
1.2 Datafile checkpoint SCN ===============> (DATAFILE CHECKPOINT SCN in control file)SQL> set lines 200SQL> col name for a60SQL> select name,checkpoint_change#from v$datafile;NAME CHECKPOINT_CHANGE#------------------------------------------------------------ ------------------/u01/app/oracle/oradata/DBdb/system01.dbf 3779864/u01/app/oracle/oradata/DBdb/sysaux01.dbf 3779864/u01/app/oracle/oradata/DBdb/undotbs01.dbf 3779864/u01/app/oracle/oradata/DBdb/users01.dbf 3779864/u01/app/oracle/oradata/DBdb/example01.dbf 37798641.3 Stop SCN ======================> (STOP SCN in control file)SQL> select name,last_change# from v$datafile;NAME LAST_CHANGE#------------------------------------------------------------ ------------/u01/app/oracle/oradata/DBdb/system01.dbf/u01/app/oracle/oradata/DBdb/sysaux01.dbf/u01/app/oracle/oradata/DBdb/undotbs01.dbf/u01/app/oracle/oradata/DBdb/users01.dbf/u01/app/oracle/oradata/DBdb/example01.dbf正常datafile在read-write mode下 last_change#一定是NULL--另外一个地方在datafile header内1.4 Start SCN ================================> (DATAFILE HEADER)SQL> select name, checkpoint_change# from v$datafile_header;NAME CHECKPOINT_CHANGE#------------------------------------------------------------ ------------------/u01/app/oracle/oradata/DBdb/system01.dbf 3779864/u01/app/oracle/oradata/DBdb/sysaux01.dbf 3779864/u01/app/oracle/oradata/DBdb/undotbs01.dbf 3779864/u01/app/oracle/oradata/DBdb/users01.dbf 3779864/u01/app/oracle/oradata/DBdb/example01.dbf 3779864SQL> 二、相关问题2.1 为什么储存在CONTROL FILE中要分为两个地方(SYSTEM CHECKPOINT SCN,DATAFILE CHECKPOINT SCN) ?当你把一个tbs设为read-only时，他的SCN会冻结停止，此时DATAFILE CHECKPOINT SCN是不会再递增改变的， 但是整体的SYSTEM CHECKPOINT SCN却仍然会不断递增前进。所以，这就是为什么需要分别在两个地方储存SCN2.2 正常shutdown database后，SCN会发生什么变化？我们可以把数据库开在mount mode，如下：SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL>SQL> startup mount;ORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2257840 bytesVariable Size 549456976 bytesDatabase Buffers 281018368 bytesRedo Buffers 2371584 bytesDatabase mounted.SQL>SQL> select checkpoint_change# from v$database;CHECKPOINT_CHANGE#------------------ 3782319SQL> select name,checkpoint_change# from v$datafile;NAME CHECKPOINT_CHANGE#------------------------------------------------------------ ------------------/u01/app/oracle/oradata/DBdb/system01.dbf 3782319/u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782319/u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782319/u01/app/oracle/oradata/DBdb/users01.dbf 3782319/u01/app/oracle/oradata/DBdb/example01.dbf 3782319SQL> select name,checkpoint_change#,last_change# from v$datafile;NAME CHECKPOINT_CHANGE# LAST_CHANGE#------------------------------------------------------------ ------------------ ------------/u01/app/oracle/oradata/DBdb/system01.dbf 3782319 3782319/u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782319 3782319/u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782319 3782319/u01/app/oracle/oradata/DBdb/users01.dbf 3782319 3782319/u01/app/oracle/oradata/DBdb/example01.dbf 3782319 3782319可以看到储存在control file中的三个SCN位置都是相同，注意此时的stop scn不会是NULL，而是等于start scnSQL> select name,checkpoint_change# from v$datafile_header;NAME CHECKPOINT_CHANGE#------------------------------------------------------------ ------------------/u01/app/oracle/oradata/DBdb/system01.dbf 3782319/u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782319/u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782319/u01/app/oracle/oradata/DBdb/users01.dbf 3782319/u01/app/oracle/oradata/DBdb/example01.dbf 3782319当clean shutdown时，checkpoint会进行，并且此时datafile的stop scn和start scn会相同。 等到我门开启数据库时，Oracle检查datafile header中的start scn和存于control file中的datafile的scn是否相同， 如果相同，接着检查start scn和stop scn是否相同，如果仍然相同，数据库就会正常开启，否则就需要recovery... 等到数据库开启后，储存在control file中的stop scn就会恢复为NULL值，此时表示datafile是open在正常模式下了。如果不正常SHUTDOWN (shutdown abort)，则mount数据库后，你会发现stop scn并不是等于其它位置的scn， 而是等于NULL，这表示Oracle在shutdown时没有进行checkpoint，下次开机必须进行crash recovery。crash recovery：必须先进行roll forward(从redo log file中从目前的start SCN开始，重做后面的已提交之交易)。再从roll back segment 做rollback未完成(dead transaction)交易。检验controlfile中的SCN会等于datafile header的SCN2.3 先进行备份：（数据库处于mount状态，冷备）；RMAN> backup database tag='full database';Starting backup at 28-NOV-17allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=21 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00004 name=/u01/app/oracle/oradata/DBdb/users01.dbfinput datafile file number=00001 name=/u01/app/oracle/oradata/DBdb/system01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/DBdb/undotbs01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/DBdb/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/DBdb/example01.dbfchannel ORA_DISK_1: starting piece 1 at 28-NOV-17 channel ORA_DISK_1: finished piece 1 at 28-NOV-17piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_28/o1_mf_nnndf_FULL_DATABASE_f1t8rv9q_.bkp tag=FULL DATABASE comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:03:15channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 28-NOV-17channel ORA_DISK_1: finished piece 1 at 28-NOV-17piece handle=/u01/app/oracle/fast_recovery_area/DBDB/backupset/2017_11_28/o1_mf_ncsnf_FULL_DATABASE_f1t8z23m_.bkp tag=FULL DATABASE comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 28-NOV-17RMAN>--shutdown abort数据库：SQL> select status from v$instance;STATUS------------MOUNTEDSQL> alter database open;Database altered.SQL> shutdown abort;ORACLE instance shut down.SQL>--启库：SQL> conn / as sysdbaConnected to an idle instance.SQL>SQL> startup nomount;ORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2257840 bytesVariable Size 549456976 bytesDatabase Buffers 281018368 bytesRedo Buffers 2371584 bytesSQL>SQL> alter database mount;Database altered.--查询scn状态：SQL> select checkpoint_change# from v$database;CHECKPOINT_CHANGE#------------------ 3782322SQL> select name,checkpoint_change#,last_change# from v$datafile;NAME CHECKPOINT_CHANGE# LAST_CHANGE#------------------------------------------------------------ ------------------ ------------/u01/app/oracle/oradata/DBdb/system01.dbf 3782322/u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782322/u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782322/u01/app/oracle/oradata/DBdb/users01.dbf 3782322/u01/app/oracle/oradata/DBdb/example01.dbf 3782322stop scn并不是等于其它位置的scn， 而是等于NULL，表示需要进行crash recoverySQL> select name,checkpoint_change# from v$datafile_header;NAME CHECKPOINT_CHANGE#------------------------------------------------------------ ------------------/u01/app/oracle/oradata/DBdb/system01.dbf 3782322/u01/app/oracle/oradata/DBdb/sysaux01.dbf 3782322/u01/app/oracle/oradata/DBdb/undotbs01.dbf 3782322/u01/app/oracle/oradata/DBdb/users01.dbf 3782322/u01/app/oracle/oradata/DBdb/example01.dbf 37823222.4 crash recovery 和media recovery 的比较启动数据库时，如果发现STOP SCN = NULL，表示需要进行crash recovery；启动数据库时，如果发现有datafile header的START SCN 不等于储存于CONTROLFILE的DATAFILE SCN，表示需要进行Media recoverySTOP SCN equal NULL ==> NEED CRASH RECOVERYDATAFILE HEADER START SCN not equal CONTROLFILE SCN ==> NEED MEDIA RECOVERY三、RECOVERY DATABASE 两种常见问题3.1 RECOVER DATABASE UNTIL CANCEL ==> OPEN DATABASE RESETLOG==> DATAFILE HEADER SCN一定会小于CONTROLFILE的DATAFILE SCN如果你有进行RESTORE DATAFILE，则该RESTORE的DATAFILE HEADER SCN一定会小于目前CONTROLFILE的DATAFILE SCN，此时会无法开启数据库，必须进行media recovery。 重做archive log直到该datafile header的SCN=current scnrestore datafile后，可以mount database然后去检查controlfile and datafile header的SCNselect 'controlfile' "SCN location",name,checkpoint_change#from v$datafile where name like '%users01%'unionselect 'file header',name,checkpoint_change#from v$datafile_header where name like '%users01%';3.2 RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; ===> OPEN DATABASE RESETLOG==> DATAFILE HEADER SCN一定会大于CONTROLFILE的DATAFILE SCN如果只是某TABLE被DROP掉，没有破坏数据库整体数据结构，还可以用NCOMPLETE RECOVERY解决 如果是某个TABLESPACE OR DATAFILE被DROP掉，因为档案结构已经破坏，目前的CONTROL FILE内已经没有 该DATAFILE的信息，就算你只RESTORE DATAFILE然后进行INCOMPLETE RECOVERY也无法救回被DROP的DATA FILE。只好RESOTRE 之前备份的CONTROL FILE(里头被DROP DATAFILE Metadata此时还存在)，不过RESTOREC CONTROL FILE后 此时Oracle会发现CONTROL FILE内的SYSTEM SCN会小于目前的DATAFILE HEADER SCN，也不等于目前储存于LOG FILE内的SCN， 此时就必须使用RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE到DROP DATAFILE OR DROP TABLESPACE之前的SCN。另一种特殊状况就是，万一不幸地所有CONTROL FILE都遗失了，也必须用这种方式救回，所以请做MULTIPLEXING。
网页名称：checkpoint时的SCN写文件动作
分享URL：
http://www.cdkjz.cn/article/pjscid.html
