參考自：http://blog.csdn.net/tianlesoftware/article/details/5251916當(dāng)發(fā)生checkpoint時(shí)，會(huì)把SCN寫到四個(gè)地方去：三個(gè)地方于control file內(nèi)，一個(gè)在datafile header。一、實(shí)驗(yàn)，如下：--Control fil e三個(gè)地方為：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--另外一個(gè)地方在datafile header內(nèi)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> 二、相關(guān)問題2.1 為什么儲(chǔ)存在CONTROL FILE中要分為兩個(gè)地方(SYSTEM CHECKPOINT SCN,DATAFILE CHECKPOINT SCN) ?當(dāng)你把一個(gè)tbs設(shè)為read-only時(shí)，他的SCN會(huì)凍結(jié)停止，此時(shí)DATAFILE CHECKPOINT SCN是不會(huì)再遞增改變的， 但是整體的SYSTEM CHECKPOINT SCN卻仍然會(huì)不斷遞增前進(jìn)。所以，這就是為什么需要分別在兩個(gè)地方儲(chǔ)存SCN2.2 正常shutdown database后，SCN會(huì)發(fā)生什么變化？我們可以把數(shù)據(jù)庫開在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可以看到儲(chǔ)存在control file中的三個(gè)SCN位置都是相同，注意此時(shí)的stop scn不會(huì)是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當(dāng)clean shutdown時(shí)，checkpoint會(huì)進(jìn)行，并且此時(shí)datafile的stop scn和start scn會(huì)相同。 等到我門開啟數(shù)據(jù)庫時(shí)，Oracle檢查datafile header中的start scn和存于control file中的datafile的scn是否相同， 如果相同，接著檢查start scn和stop scn是否相同，如果仍然相同，數(shù)據(jù)庫就會(huì)正常開啟，否則就需要recovery... 等到數(shù)據(jù)庫開啟后，儲(chǔ)存在control file中的stop scn就會(huì)恢復(fù)為NULL值，此時(shí)表示datafile是open在正常模式下了。如果不正常SHUTDOWN (shutdown abort)，則mount數(shù)據(jù)庫后，你會(huì)發(fā)現(xiàn)stop scn并不是等于其它位置的scn， 而是等于NULL，這表示Oracle在shutdown時(shí)沒有進(jìn)行checkpoint，下次開機(jī)必須進(jìn)行crash recovery。crash recovery：必須先進(jìn)行roll forward(從redo log file中從目前的start SCN開始，重做后面的已提交之交易)。再從roll back segment 做rollback未完成(dead transaction)交易。檢驗(yàn)controlfile中的SCN會(huì)等于datafile header的SCN2.3 先進(jìn)行備份：（數(shù)據(jù)庫處于mount狀態(tài)，冷備）；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數(shù)據(jù)庫：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狀態(tài)：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，表示需要進(jìn)行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 的比較啟動(dòng)數(shù)據(jù)庫時(shí)，如果發(fā)現(xiàn)STOP SCN = NULL，表示需要進(jìn)行crash recovery；啟動(dòng)數(shù)據(jù)庫時(shí)，如果發(fā)現(xiàn)有datafile header的START SCN 不等于儲(chǔ)存于CONTROLFILE的DATAFILE SCN，表示需要進(jìn)行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一定會(huì)小于CONTROLFILE的DATAFILE SCN如果你有進(jìn)行RESTORE DATAFILE，則該RESTORE的DATAFILE HEADER SCN一定會(huì)小于目前CONTROLFILE的DATAFILE SCN，此時(shí)會(huì)無法開啟數(shù)據(jù)庫，必須進(jìn)行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一定會(huì)大于CONTROLFILE的DATAFILE SCN如果只是某TABLE被DROP掉，沒有破壞數(shù)據(jù)庫整體數(shù)據(jù)結(jié)構(gòu)，還可以用NCOMPLETE RECOVERY解決 如果是某個(gè)TABLESPACE OR DATAFILE被DROP掉，因?yàn)闄n案結(jié)構(gòu)已經(jīng)破壞，目前的CONTROL FILE內(nèi)已經(jīng)沒有 該DATAFILE的信息，就算你只RESTORE DATAFILE然后進(jìn)行INCOMPLETE RECOVERY也無法救回被DROP的DATA FILE。只好RESOTRE 之前備份的CONTROL FILE(里頭被DROP DATAFILE Metadata此時(shí)還存在)，不過RESTOREC CONTROL FILE后 此時(shí)Oracle會(huì)發(fā)現(xiàn)CONTROL FILE內(nèi)的SYSTEM SCN會(huì)小于目前的DATAFILE HEADER SCN，也不等于目前儲(chǔ)存于LOG FILE內(nèi)的SCN， 此時(shí)就必須使用RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE到DROP DATAFILE OR DROP TABLESPACE之前的SCN。另一種特殊狀況就是，萬一不幸地所有CONTROL FILE都遺失了，也必須用這種方式救回，所以請(qǐng)做MULTIPLEXING。
網(wǎng)頁名稱：checkpoint時(shí)的SCN寫文件動(dòng)作
網(wǎng)站網(wǎng)址：
http://weahome.cn/article/pjscid.html
重慶分公司
重慶分公司
