SCN、Checkpoint、實(shí)例恢復(fù)介質(zhì)恢復(fù)理解
如果LGWR的下一個(gè)日志是ACTIVE，那么LWGR會(huì)掛起，警告日志會(huì)報(bào)告"Checkpoint not complete",oracle會(huì)發(fā)起alter system checkpoint的操作
狀態(tài)為ACTIVE的日志也可能已經(jīng)是歸檔日志了，ARCn進(jìn)程會(huì)自動(dòng)將非CURRENT的在線日志歸檔
V$LOG.STATUS反應(yīng)的是完全檢查點(diǎn)的進(jìn)度，因?yàn)閍lter system switch logfile后還是會(huì)發(fā)現(xiàn)原來為ACTIVE的日志還是ACTIVE狀態(tài)，但是alter system checkpoint后就一定會(huì)把ACTIVE變成INACTIVE(完全檢查點(diǎn)寫入控制文件和數(shù)據(jù)文件頭部，增量檢查點(diǎn)只寫入控制文件)
V$LOG.STATUS=ACTIVE
Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
代表最近一次的完全檢查點(diǎn)SCN小于該日志中最后一條重做記錄的SCN,說明完全檢查點(diǎn)還沒有越過這個(gè)在線日志
V$LOG.STATUS=INACTIVE
Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.
代表最近一次的完全檢查點(diǎn)SCN大于該日志中最后一條重做記錄的SCN,說明完全檢查點(diǎn)已經(jīng)越過這個(gè)在線日志
v$log.FIRST_CHANGE#:等于上一個(gè)online redo的v$log.NEXT_CHANGE#或上一個(gè)archive redo log的$archived_log.NEXT_CHANGE#，等于下一個(gè)archive redo log的v$archived_log.FIRST_CHANGE#
v$log.NEXT_CHANGE#:Highest change number (SCN) in the log. When STATUS=CURRENT, NEXT_CHANGE# is set to the highest possible SCN, 281474976710655
V$DATABASE displays information about the database from the control file.
V$DATABASE.CHECKPOINT_CHANGE#：Last SCN checkpointed
V$DATABASE.CONTROLFILE_CHANGE#：Last SCN in backup control file; null if the control file is not a backup
V$DATABASE.CURRENT_SCN：Current SCN; null if the database is not currently open. For a standby database, it is the checkpoint SCN of the mounted physical standby database during media recovery and is always less than the last applied SCN tracked in V$RECOVERY_PROGRESS.
V$DATAFILEdisplays datafile information from the control file.
V$DATAFILE.CHECKPOINT_CHANGE#：SCN at last checkpoint
V$DATAFILE_HEADERdisplays datafile information from the datafile headers.
V$DATAFILE_HEADER.CHECKPOINT_CHANGE#：Datafile checkpoint change#
控制文件中保存的控制文件SCN是指v$database.CONTROLFILE_CHANGE#
控制文件中保存的數(shù)據(jù)庫(kù)SCN(也稱系統(tǒng)檢查點(diǎn)SCN)是指v$database.CHECKPOINT_CHANGE#
控制文件中保存的數(shù)據(jù)文件SCN是指v$datafile.CHECKPOINT_CHANGE#
控制文件中保存的數(shù)據(jù)文件結(jié)束SCN是指v$datafile.last_change#，open狀態(tài)下是null，mount下和數(shù)據(jù)庫(kù)檢查點(diǎn)scn一致
數(shù)據(jù)文件中保存的數(shù)據(jù)文件頭SCN是指v$datafile_header.CHECKPOINT_CHANGE#，叫start SCN，也叫啟動(dòng)SCN。
redo logfile中保存的SCN：每一個(gè)日志文件有一個(gè)first scn和一個(gè)next scn。歸檔日志中，下一個(gè)日志文件的first SCN等于上一個(gè)日志文件的next SCN，在線日志中，first SCN等于最后一個(gè)歸檔日志的next SCN，而next SCN不論是在open還是mount狀態(tài)下都是無窮大
數(shù)據(jù)文件SCN會(huì)出現(xiàn)不一致現(xiàn)象，因?yàn)閿?shù)據(jù)文件的狀態(tài)會(huì)出現(xiàn)online和offline或begin backup
v$datafile.checkpoint_change#是控制文件里記錄某個(gè)數(shù)據(jù)文件比如A的SCN值
v$datafile_header.checkpoint_change#是數(shù)據(jù)文件比如A記錄自己的SCN值
默認(rèn)情況下這兩都應(yīng)該是一樣的。關(guān)于兩個(gè)checkpoint_change#值的比較：
如果datafile大于datafile_header，那么就需要recover datafile，說明控制文件里記錄的SCN比數(shù)據(jù)文件記錄的要新。產(chǎn)生原因：shutdown abort等。
如果datafile小于datafile_header，任何情況下都不可能發(fā)生，控制文件里的scn不可能比數(shù)據(jù)文件
執(zhí)行alter system switch logfile時(shí)，只有V$DATABASE.CONTROLFILE_CHANGE#變大了，其他V$DATABASE.CHECKPOINT_CHANGE#、V$DATAFILE.CHECKPOINT_CHANGE#、V$DATAFILE_HEADER.CHECKPOINT_CHANGE#都沒有變
執(zhí)行ALTER DATABASE BEGIN BACKUP時(shí)，V$DATABASE.CONTROLFILE_CHANGE#、V$DATAFILE.CHECKPOINT_CHANGE#、V$DATAFILE_HEADER.CHECKPOINT_CHANGE#都變大了，只有V$DATABASE.CHECKPOINT_CHANGE#沒變
執(zhí)行alter tablespace users begin backup時(shí)，V$DATABASE.CONTROLFILE_CHANGE#、對(duì)應(yīng)的表空間文件V$DATAFILE.CHECKPOINT_CHANGE#、對(duì)應(yīng)的表空間文件V$DATAFILE_HEADER.CHECKPOINT_CHANGE#變大了，$DATABASE.CHECKPOINT_CHANGE#沒變，其他表空間文件的V$DATAFILE.CHECKPOINT_CHANGE#、$DATAFILE_HEADER.CHECKPOINT_CHANGE#也沒變
執(zhí)行alter system checkpoint時(shí)，則V$DATABASE.CONTROLFILE_CHANGE#、V$DATABASE.CHECKPOINT_CHANGE#、V$DATAFILE.CHECKPOINT_CHANGE#、V$DATAFILE_HEADER.CHECKPOINT_CHANGE#都變大了
查詢相應(yīng)SCN的一些語句
select CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE#,CURRENT_SCN from v$database;
select distinct CHECKPOINT_CHANGE#,last_change# from v$datafile;
select distinct CHECKPOINT_CHANGE# from v$datafile_header;
select distinct FIRST_CHANGE#,NEXT_CHANGE#,sequence#,first_time from v$archived_log order by first_time desc;
select SEQUENCE#,STATUS,ARCHIVED,FIRST_CHANGE#,NEXT_CHANGE# from v$log
mount狀態(tài)下scn不會(huì)改變
查詢當(dāng)前SCN的SQL
一般使用如下
select to_char(dbms_flashback.get_system_change_number) scn from dual
mount狀態(tài)執(zhí)行上述語句會(huì)報(bào)錯(cuò)ORA-00904
下面這條查詢語句，每執(zhí)行一次scn就會(huì)增加一次
select CURRENT_SCN from v$database;
mount狀態(tài)下執(zhí)行上述語句結(jié)果是0
COMMIT Statement
The COMMIT statement ends the current transaction, making its changes permanent and visible to other users.
COMMIT不會(huì)觸發(fā)任何的checkpoint，只是觸發(fā)lgwr把日志緩沖數(shù)據(jù)寫入在線重做日志并把事務(wù)對(duì)應(yīng)的數(shù)據(jù)塊的最新scn和是否的提交狀態(tài)記錄在控制文件中，但是不會(huì)記錄在任何視圖中，V$DATABASE.CURRENT_SCN雖然也來自來自控制文件，但是記錄當(dāng)前最新的SCN
database checkpoint
The thread checkpoint that has the lowest SCN. All changes in all enabled redo threads with SCNs before the database checkpoint SCN are guaranteed to have been written to disk.
具有最低SCN的線程檢查點(diǎn)。 在數(shù)據(jù)庫(kù)檢查點(diǎn)SCN之前所有啟用的具有SCN的重做線程的所有更改都保證已寫入磁盤。
data file checkpoint
A data structure that defines an SCN in the redo thread of a database for a particular data file. Every data file has a checkpoint SCN, which you can view in V$DATAFILE.CHECKPOINT_CHANGE#. All changes with an SCN lower than this SCN are guaranteed to be in the data file.
數(shù)據(jù)結(jié)構(gòu)，用于定義特定數(shù)據(jù)文件的數(shù)據(jù)庫(kù)重做線程中的SCN。 每個(gè)數(shù)據(jù)文件都有一個(gè)檢查點(diǎn)SCN，您可以在V$DATAFILE.CHECKPOINT_CHANGE＃中查看。 SCN低于此SCN的所有更改都將保證在數(shù)據(jù)文件中。
Overview of Checkpoints
A checkpoint is a crucial mechanism in consistent database shutdowns, instance recovery, and Oracle Database operation generally. The term checkpoint has the following related meanings:
A data structure that indicates the checkpoint position, which is the SCN in the redo stream where instance recovery must begin
The checkpoint position is determined by the oldest dirty buffer in the database buffer cache. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header.
The writing of modified database buffers in the database buffer cache to disk
檢查點(diǎn)是一致的數(shù)據(jù)庫(kù)關(guān)閉，實(shí)例恢復(fù)和Oracle數(shù)據(jù)庫(kù)操作的關(guān)鍵機(jī)制。 檢查點(diǎn)一詞具有以下相關(guān)含義：
指示檢查點(diǎn)位置的數(shù)據(jù)結(jié)構(gòu)，該位置是實(shí)例恢復(fù)必須開始的重做流中的SCN
檢查點(diǎn)位置由數(shù)據(jù)庫(kù)緩沖區(qū)緩存中最舊的臟緩沖區(qū)確定。 檢查點(diǎn)位置用作指向重做流的指針，存儲(chǔ)在控制文件和每個(gè)數(shù)據(jù)文件頭中。
將數(shù)據(jù)庫(kù)緩沖區(qū)中的修改后的數(shù)據(jù)庫(kù)緩沖區(qū)寫入磁盤
When Oracle Database Initiates Checkpoints
The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints:
Thread checkpoints
The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is a database checkpoint. Thread checkpoints occur in the following situations:
Consistent database shutdown
ALTER SYSTEM CHECKPOINT statement
Online redo log switch
ALTER DATABASE BEGIN BACKUP statement
Tablespace and data file checkpoints
The database writes to disk all buffers modified by redo before a specific target. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing ALTER TABLESPACE BEGIN BACKUP.
檢查點(diǎn)進(jìn)程（CKPT）負(fù)責(zé)將檢查點(diǎn)寫入數(shù)據(jù)文件頭文件和控制文件。 檢查點(diǎn)發(fā)生在各種情況。 例如，Oracle數(shù)據(jù)庫(kù)使用以下類型的檢查點(diǎn)：
線程檢查點(diǎn)
數(shù)據(jù)庫(kù)通過在某個(gè)目標(biāo)之前的特定線程中重做修改的所有緩沖區(qū)寫入磁盤。 數(shù)據(jù)庫(kù)中所有實(shí)例上的一組線程檢查點(diǎn)是一個(gè)數(shù)據(jù)庫(kù)檢查點(diǎn)。 線程檢查點(diǎn)在以下情況下發(fā)生：
一致的數(shù)據(jù)庫(kù)關(guān)機(jī)
ALTER SYSTEM CHECKPOINT語句
在線重做日志切換
ALTER DATABASE BEGIN BACKUP語句
表空間和數(shù)據(jù)文件檢查點(diǎn)
數(shù)據(jù)庫(kù)將磁盤上的所有緩沖區(qū)寫入特定目標(biāo)之前通過重做修改。 表空間檢查點(diǎn)是一組數(shù)據(jù)文件檢查點(diǎn)，一個(gè)用于表空間中的每個(gè)數(shù)據(jù)文件。 這些檢查點(diǎn)發(fā)生在各種情況下，包括使表空間為只讀或正常脫機(jī)，收縮數(shù)據(jù)文件或執(zhí)行ALTER TABLESPACE BEGIN BACKUP。
oracle規(guī)定：保證重做記錄先于對(duì)應(yīng)的臟數(shù)據(jù)塊寫入持久層
所以，令同一個(gè)更改產(chǎn)生的重做記錄為R、臟數(shù)據(jù)塊為D，那么lgwr沒有把R寫入在線日志的情況下，oracle是不允許dbwr把D先行寫入數(shù)據(jù)文件的。即便是dbwr首先發(fā)出請(qǐng)求，也必須等待lgwr先清空日志緩沖。這樣，數(shù)據(jù)文件中的內(nèi)容永遠(yuǎn)沒有在線日志的內(nèi)容更新得快，也就是在數(shù)據(jù)庫(kù)打開的情況下，數(shù)據(jù)文件永遠(yuǎn)比在線日志“舊”，為了標(biāo)識(shí)數(shù)據(jù)文件“舊”到什么程度，oracle便引入了檢查點(diǎn)。
Oracle通過檢查點(diǎn)（Checkpoint）來縮減恢復(fù)時(shí)間。檢查點(diǎn)只是一個(gè)數(shù)據(jù)庫(kù)事件，它存在的根本意義在于減少恢復(fù)時(shí)間。
完全檢查點(diǎn)步驟
1.在日志緩沖中確定當(dāng)前的（也是最新的）重做記錄，提取其RBA和SCN作為檢查點(diǎn)的目標(biāo)
2.lgwr清空日志緩沖，將重做記錄寫入在線日志
3.dbwr進(jìn)程將檢查點(diǎn)目標(biāo)（RBA與SCN）產(chǎn)生的及檢查點(diǎn)目標(biāo)之前產(chǎn)生的臟數(shù)據(jù)塊，按RBA的順序?qū)懭霐?shù)據(jù)文件
4.ckpt進(jìn)程將檢查點(diǎn)目標(biāo)（RBA與SCN）寫入數(shù)據(jù)文件的頭部和控制文件
這樣數(shù)據(jù)文件頭部的檢查點(diǎn)目標(biāo)（RBA與SCN）便能提供如下信息
1.讀取數(shù)據(jù)文件頭的檢查點(diǎn)scn與在線日志重做記錄的scn比較，就可以知道該數(shù)據(jù)文件是否需要恢復(fù)
2.如果該數(shù)據(jù)文件需要恢復(fù)，數(shù)據(jù)文件SCN用來表示從這個(gè)SCN開始恢復(fù)
完全檢查點(diǎn)發(fā)生時(shí)機(jī)
1.shutdown、shutdown normal、shutdown transactional、shutdown immediate命令
2.alter system checkpoint
3.ALTER DATABASE BEGIN BACKUP
4.執(zhí)行部分表空間維護(hù)命令如alter tablespace tablespacename offline|online|begein backup|end backup|read only|read write
--Online redo log switch時(shí)只有V$DATABASE.CONTROLFILE_CHANGE#變大
--ALTER DATABASE BEGIN BACKUP時(shí)$DATABASE.CHECKPOINT_CHANGE#沒變，雖然V$DATABASE.CONTROLFILE_CHANGE#、V$DATAFILE.CHECKPOINT_CHANGE#、V$DATAFILE_HEADER.CHECKPOINT_CHANGE#都變大
--alter tablespace users begin backup時(shí)，V$DATABASE.CONTROLFILE_CHANGE#、對(duì)應(yīng)的表空間文件V$DATAFILE.CHECKPOINT_CHANGE#、對(duì)應(yīng)的表空間文件V$DATAFILE_HEADER.CHECKPOINT_CHANGE#變大，$DATABASE.CHECKPOINT_CHANGE#和?其他表空間對(duì)應(yīng)數(shù)據(jù)文件CHECKPOINT_CHANGE#沒變
完全檢查點(diǎn)：個(gè)人更愿意理解只有V$DATABASE.CHECKPOINT_CHANGE#增加了才算發(fā)生了一次完全檢查點(diǎn)。雖然完全檢查點(diǎn)的定義是只要同時(shí)寫入數(shù)據(jù)文件頭部和控制文件就認(rèn)為是完全檢查點(diǎn)。不過按個(gè)人理解其實(shí)上面BEGIN BACKUP這樣的操作就不算完全檢查點(diǎn)了。
Incremental checkpoints
An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.
Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.
增量檢查點(diǎn)是一種類型的線程檢查點(diǎn)，部分是為了避免在線重做日志切換中寫入大量塊。 DBWn至少每三秒檢查一次，以確定是否有工作要做。 當(dāng)DBWn寫入臟緩沖區(qū)時(shí)，它會(huì)提前檢查點(diǎn)位置，導(dǎo)致CKPT將檢查點(diǎn)位置寫入控制文件，而不是數(shù)據(jù)文件頭。
其他類型的檢查點(diǎn)在模式對(duì)象被刪除或截?cái)鄷r(shí)包括實(shí)例和介質(zhì)恢復(fù)檢查點(diǎn)和檢查點(diǎn)。
增量檢查點(diǎn)
增量檢查點(diǎn)會(huì)推動(dòng)dbwr將部分臟數(shù)據(jù)塊寫回?cái)?shù)據(jù)文件，但是檢查點(diǎn)SCN只是記錄到控制文件即V$DATABASE.CONTROLFILE_CHANGE#，而沒有寫入數(shù)據(jù)文件頭部。實(shí)例恢復(fù)操作并不會(huì)因此而少索取日志記錄。比如：增量檢查點(diǎn)SCN到200了，意味著數(shù)據(jù)文件內(nèi)數(shù)據(jù)塊的最高的SCN也已經(jīng)是200了，但是數(shù)據(jù)文件頭部可能還寫著檢查點(diǎn)SCN是100,如果此時(shí)實(shí)例崩潰，實(shí)例恢復(fù)的前滾會(huì)從scn為100的重做記錄開始，實(shí)際上從scn號(hào)100~200的重做記錄根本不需要前滾。oracle當(dāng)然了解這一點(diǎn)，所以每次寫完臟數(shù)據(jù)塊之后，dbwr會(huì)添加一條被稱為BWR（block written record，數(shù)據(jù)塊已寫）的重做記錄，該記錄的變更矢量不代表任何變更，只是用來標(biāo)記哪些數(shù)據(jù)塊已經(jīng)被寫回?cái)?shù)據(jù)文件了。
因?yàn)橛蠦WR這樣的記錄，oracle在進(jìn)行自動(dòng)前滾時(shí)實(shí)際上采用“兩次讀取法”讀取在線日志，仍然假設(shè)數(shù)據(jù)文件檢查點(diǎn)scn為100，增量檢查點(diǎn)已經(jīng)到200了，第一次讀取日志中scn號(hào)100后的所有重做記錄，目的是確定所有的真正的需要恢復(fù)的數(shù)據(jù)塊有哪些，BWR記錄就像一個(gè)過濾器，可以大量減少這樣的數(shù)據(jù)塊。第二次讀取在線日志時(shí)oracle只對(duì)真正需要恢復(fù)的數(shù)據(jù)塊，即沒有被BWR記錄點(diǎn)到名的數(shù)據(jù)塊的重做記錄感興趣。結(jié)果是oracle會(huì)跳過scn從100到200之間的重做記錄，也就會(huì)使前滾相應(yīng)變快
增量檢查點(diǎn)作用：
1.減少發(fā)生完全檢查點(diǎn)是dbwr進(jìn)程的工作負(fù)擔(dān)
2.提高實(shí)例恢復(fù)的速度
增量檢查點(diǎn)發(fā)生時(shí)機(jī)
1.oracle自動(dòng)控制(當(dāng)三個(gè)參數(shù)都不設(shè)置或三個(gè)參數(shù)都設(shè)置不當(dāng)時(shí)FAST_START_MTTR_TARGET、LOG_CHECKPOINT_TIMEOUT、LOG_CHECKPOINT_INTERVAL)
2.三個(gè)參數(shù)取最嚴(yán)厲的那個(gè)(FAST_START_MTTR_TARGET、LOG_CHECKPOINT_TIMEOUT、LOG_CHECKPOINT_INTERVAL)
3.lgwr切換在線日志
--alter system flush buffer_cache不會(huì)引發(fā)增加檢查點(diǎn)
因?yàn)閘gwr切換在線日志產(chǎn)生增量檢查點(diǎn)，說明完全檢查點(diǎn)（數(shù)據(jù)文件頭部scn）還沒有越過active的在線日志，但是增量檢查點(diǎn)（控制文件scn）已經(jīng)越過了active的在線日志可能已經(jīng)在current的在線日志了，因?yàn)閞ecover時(shí)使用兩次讀取法，先讀完全檢查點(diǎn)再去增量檢查點(diǎn)，所有recover的時(shí)候還是需要active的在線日志和current的在線日志，也就是說，current在線日志丟失時(shí)，如果還有active的在線日志，不能只恢復(fù)到active的在線日志，還需要current的日志，因?yàn)樵隽繖z查點(diǎn)可能已經(jīng)在current的日志。
增量檢查點(diǎn)的三個(gè)參數(shù)
FAST_START_MTTR_TARGET默認(rèn)為0，LOG_CHECKPOINT_INTERVAL默認(rèn)為0，LOG_CHECKPOINT_TIMEOUT默認(rèn)為1800
FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL.
當(dāng)設(shè)置了LOG_CHECKPOINT_INTERVAL時(shí)，LOG_CHECKPOINT_INTERVAL的設(shè)置會(huì)覆蓋FAST_START_MTTR_TARGET的設(shè)置，而不是說11G已經(jīng)取消了FAST_START_MTTR_TARGET這個(gè)參數(shù)的功能。
LOG_CHECKPOINT_INTERVALspecifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks
這里指的blocks是OS的block，而不是DATABASE的block
Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. Therefore, if the value exceeds the actual redo log file size,checkpoints occur only when switching logs. Checkpoint frequency is one of the factors that influence the time required for the database to recover from an unexpected failure
當(dāng)LOG_CHECKPOINT_INTERVAL值大于redo log file size時(shí)，增量檢查點(diǎn)發(fā)生情況就是在線日志切換取代LOG_CHECKPOINT_INTERVAL
Specifying a value of 0 (zero) for LOG_CHECKPOINT_INTERVAL has the same effect as setting the parameter to infinity and causes the parameter to be ignored. Only nonzero values of this parameter are considered meaningful.
當(dāng)LOG_CHECKPOINT_INTERVAL為0時(shí)，LOG_CHECKPOINT_INTERVAL這個(gè)參數(shù)就不起作用了
Recovery I/O can also be limited by setting the LOG_CHECKPOINT_TIMEOUT parameter or by the size specified for the smallest redo log.
LOG_CHECKPOINT_TIMEOUT和LOG_CHECKPOINT_INTERVAL都生效，但是取兩者更嚴(yán)厲的那個(gè)
LOG_CHECKPOINT_TIMEOUTspecifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log(sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.
Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set
不建議設(shè)置LOG_CHECKPOINT_TIMEOUT為0，除非你設(shè)置了FAST_START_MTTR_TARGET
FAST_START_MTTR_TARGET、LOG_CHECKPOINT_INTERVAL為0時(shí)，LOG_CHECKPOINT_TIMEOUT也生效的
實(shí)例恢復(fù)的2中情況：
1、增量檢查點(diǎn)SCN和在線日志中的SCN進(jìn)行對(duì)比，如果大于在線日志的first_change#，說明和在線日志接上了，可以實(shí)例恢復(fù)
因?yàn)閘ogfile switch就會(huì)發(fā)生一次增量檢查點(diǎn)，所以歸檔日志總是落后實(shí)例的增量檢查點(diǎn)，狀態(tài)為ACTIVE的日志也可能已經(jīng)是歸檔日志了，所以實(shí)例恢復(fù)永遠(yuǎn)是使用current的在線日志
2、在mount情況下，$datafile.last_change#為空則必須要實(shí)例恢復(fù)，mount下$datafile.last_change#為空的情況有兩種
2.1、shutdown abort
2.2、控制是恢復(fù)過來的
數(shù)據(jù)庫(kù)OPEN狀態(tài)下v$datafile.last_change#的SCN號(hào)始終為NULL，而當(dāng)數(shù)據(jù)庫(kù)正常關(guān)閉時(shí)，會(huì)進(jìn)行完全檢查點(diǎn)，并將檢查點(diǎn)SCN號(hào)更新到該字段，
所以可以在mount狀態(tài)通過v$datafile.last_change#的SCN號(hào)是否為null來判斷是不是需要實(shí)例恢復(fù)。而崩潰時(shí)，Oracle還來不及更新該字段，則該字段仍然為NULL。當(dāng)SMON進(jìn)程發(fā)現(xiàn)該字段為空時(shí)，就知道實(shí)例在上次沒有正常關(guān)閉，于是由SMON進(jìn)程就開始進(jìn)行實(shí)例恢復(fù)了。SMON進(jìn)程進(jìn)行實(shí)例恢復(fù)時(shí)，會(huì)從控制文件中獲得檢查點(diǎn)位置。于是，SMON進(jìn)程到聯(lián)機(jī)日志文件中，找到該檢查點(diǎn)位置，然后從該檢查點(diǎn)位置開始往下，應(yīng)用所有的重做條目，從而在buffer cache里又恢復(fù)了實(shí)例崩潰那個(gè)時(shí)間點(diǎn)的狀態(tài)。這個(gè)過程叫做前滾，前滾完成后，buffer cache里既有崩潰時(shí)已經(jīng)提交還沒有寫入數(shù)據(jù)文件的臟數(shù)據(jù)塊，也還有事務(wù)被突然終止，而導(dǎo)致的既沒有提交又沒有回滾的事務(wù)所弄臟的數(shù)據(jù)塊，這類臟數(shù)據(jù)塊分兩種，一種是還在buffer cache中，一種是已經(jīng)被dbwr寫入了磁盤文件。前滾一旦完畢，SMON進(jìn)程立即打開數(shù)據(jù)庫(kù)。但是，這時(shí)的數(shù)據(jù)庫(kù)中還含有那些既沒有提交又沒有回滾的臟塊，這種臟塊是不能存在于數(shù)據(jù)庫(kù)中的，因?yàn)樗鼈儾]有被提交，必須被回滾。打開數(shù)據(jù)庫(kù)以后，SMON進(jìn)程會(huì)在后臺(tái)進(jìn)行回滾。
介質(zhì)恢復(fù)的情況：
各個(gè)數(shù)據(jù)文件頭部檢查點(diǎn)scn不一致，增加檢查點(diǎn)的SCN遠(yuǎn)遠(yuǎn)小于在線日志的first_change#，說明和在線日志接不上，需要介質(zhì)恢復(fù)，需要用到歸檔日志進(jìn)行recover(更嚴(yán)重的情況是還可能需要restore數(shù)據(jù)文件并使用歸檔日志recover)
RAMN經(jīng)常遇到的1號(hào)文件system太新的問題
如下三者都是說file 1太新了，file 1需要更多的恢復(fù)
ORA-01194: file 1 needs more recovery to be consistent
ORA-01113: file 1 needs media recovery
RMAN-06556: datafile 1 must be restored from backup older than scn 919248820
datafile 1的scn大于919248820，也就是datafile 1太新了，不夠舊不夠老
比如正常關(guān)機(jī)后，startup mount狀態(tài)，
sql直接recover database會(huì)報(bào)錯(cuò)ORA-00264: no recovery required
rman直接recover database until sequence到前面幾個(gè)archivelog就會(huì)報(bào)RMAN-06556
狀態(tài)為ACTIVE的日志也可能已經(jīng)是歸檔日志了，ARCn進(jìn)程會(huì)自動(dòng)將非CURRENT的在線日志歸檔
V$LOG.STATUS反應(yīng)的是完全檢查點(diǎn)的進(jìn)度，因?yàn)閍lter system switch logfile后還是會(huì)發(fā)現(xiàn)原來為ACTIVE的日志還是ACTIVE狀態(tài)，但是alter system checkpoint后就一定會(huì)把ACTIVE變成INACTIVE(完全檢查點(diǎn)寫入控制文件和數(shù)據(jù)文件頭部，增量檢查點(diǎn)只寫入控制文件)
V$LOG.STATUS=ACTIVE
Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
代表最近一次的完全檢查點(diǎn)SCN小于該日志中最后一條重做記錄的SCN,說明完全檢查點(diǎn)還沒有越過這個(gè)在線日志
V$LOG.STATUS=INACTIVE
Log is no longer needed for instance recovery. It may be in use for media recovery. It may or may not be archived.
代表最近一次的完全檢查點(diǎn)SCN大于該日志中最后一條重做記錄的SCN,說明完全檢查點(diǎn)已經(jīng)越過這個(gè)在線日志
v$log.FIRST_CHANGE#:等于上一個(gè)online redo的v$log.NEXT_CHANGE#或上一個(gè)archive redo log的$archived_log.NEXT_CHANGE#，等于下一個(gè)archive redo log的v$archived_log.FIRST_CHANGE#
v$log.NEXT_CHANGE#:Highest change number (SCN) in the log. When STATUS=CURRENT, NEXT_CHANGE# is set to the highest possible SCN, 281474976710655
V$DATABASE displays information about the database from the control file.
V$DATABASE.CHECKPOINT_CHANGE#：Last SCN checkpointed
V$DATABASE.CONTROLFILE_CHANGE#：Last SCN in backup control file; null if the control file is not a backup
V$DATABASE.CURRENT_SCN：Current SCN; null if the database is not currently open. For a standby database, it is the checkpoint SCN of the mounted physical standby database during media recovery and is always less than the last applied SCN tracked in V$RECOVERY_PROGRESS.
V$DATAFILEdisplays datafile information from the control file.
V$DATAFILE.CHECKPOINT_CHANGE#：SCN at last checkpoint
V$DATAFILE_HEADERdisplays datafile information from the datafile headers.
V$DATAFILE_HEADER.CHECKPOINT_CHANGE#：Datafile checkpoint change#
控制文件中保存的控制文件SCN是指v$database.CONTROLFILE_CHANGE#
控制文件中保存的數(shù)據(jù)庫(kù)SCN(也稱系統(tǒng)檢查點(diǎn)SCN)是指v$database.CHECKPOINT_CHANGE#
控制文件中保存的數(shù)據(jù)文件SCN是指v$datafile.CHECKPOINT_CHANGE#
控制文件中保存的數(shù)據(jù)文件結(jié)束SCN是指v$datafile.last_change#，open狀態(tài)下是null，mount下和數(shù)據(jù)庫(kù)檢查點(diǎn)scn一致
數(shù)據(jù)文件中保存的數(shù)據(jù)文件頭SCN是指v$datafile_header.CHECKPOINT_CHANGE#，叫start SCN，也叫啟動(dòng)SCN。
redo logfile中保存的SCN：每一個(gè)日志文件有一個(gè)first scn和一個(gè)next scn。歸檔日志中，下一個(gè)日志文件的first SCN等于上一個(gè)日志文件的next SCN，在線日志中，first SCN等于最后一個(gè)歸檔日志的next SCN，而next SCN不論是在open還是mount狀態(tài)下都是無窮大
數(shù)據(jù)文件SCN會(huì)出現(xiàn)不一致現(xiàn)象，因?yàn)閿?shù)據(jù)文件的狀態(tài)會(huì)出現(xiàn)online和offline或begin backup
v$datafile.checkpoint_change#是控制文件里記錄某個(gè)數(shù)據(jù)文件比如A的SCN值
v$datafile_header.checkpoint_change#是數(shù)據(jù)文件比如A記錄自己的SCN值
默認(rèn)情況下這兩都應(yīng)該是一樣的。關(guān)于兩個(gè)checkpoint_change#值的比較：
如果datafile大于datafile_header，那么就需要recover datafile，說明控制文件里記錄的SCN比數(shù)據(jù)文件記錄的要新。產(chǎn)生原因：shutdown abort等。
如果datafile小于datafile_header，任何情況下都不可能發(fā)生，控制文件里的scn不可能比數(shù)據(jù)文件
執(zhí)行alter system switch logfile時(shí)，只有V$DATABASE.CONTROLFILE_CHANGE#變大了，其他V$DATABASE.CHECKPOINT_CHANGE#、V$DATAFILE.CHECKPOINT_CHANGE#、V$DATAFILE_HEADER.CHECKPOINT_CHANGE#都沒有變
執(zhí)行ALTER DATABASE BEGIN BACKUP時(shí)，V$DATABASE.CONTROLFILE_CHANGE#、V$DATAFILE.CHECKPOINT_CHANGE#、V$DATAFILE_HEADER.CHECKPOINT_CHANGE#都變大了，只有V$DATABASE.CHECKPOINT_CHANGE#沒變
執(zhí)行alter tablespace users begin backup時(shí)，V$DATABASE.CONTROLFILE_CHANGE#、對(duì)應(yīng)的表空間文件V$DATAFILE.CHECKPOINT_CHANGE#、對(duì)應(yīng)的表空間文件V$DATAFILE_HEADER.CHECKPOINT_CHANGE#變大了，$DATABASE.CHECKPOINT_CHANGE#沒變，其他表空間文件的V$DATAFILE.CHECKPOINT_CHANGE#、$DATAFILE_HEADER.CHECKPOINT_CHANGE#也沒變
執(zhí)行alter system checkpoint時(shí)，則V$DATABASE.CONTROLFILE_CHANGE#、V$DATABASE.CHECKPOINT_CHANGE#、V$DATAFILE.CHECKPOINT_CHANGE#、V$DATAFILE_HEADER.CHECKPOINT_CHANGE#都變大了
查詢相應(yīng)SCN的一些語句
select CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE#,CURRENT_SCN from v$database;
select distinct CHECKPOINT_CHANGE#,last_change# from v$datafile;
select distinct CHECKPOINT_CHANGE# from v$datafile_header;
select distinct FIRST_CHANGE#,NEXT_CHANGE#,sequence#,first_time from v$archived_log order by first_time desc;
select SEQUENCE#,STATUS,ARCHIVED,FIRST_CHANGE#,NEXT_CHANGE# from v$log
mount狀態(tài)下scn不會(huì)改變
查詢當(dāng)前SCN的SQL
一般使用如下
select to_char(dbms_flashback.get_system_change_number) scn from dual
mount狀態(tài)執(zhí)行上述語句會(huì)報(bào)錯(cuò)ORA-00904
下面這條查詢語句，每執(zhí)行一次scn就會(huì)增加一次
select CURRENT_SCN from v$database;
mount狀態(tài)下執(zhí)行上述語句結(jié)果是0
COMMIT Statement
The COMMIT statement ends the current transaction, making its changes permanent and visible to other users.
COMMIT不會(huì)觸發(fā)任何的checkpoint，只是觸發(fā)lgwr把日志緩沖數(shù)據(jù)寫入在線重做日志并把事務(wù)對(duì)應(yīng)的數(shù)據(jù)塊的最新scn和是否的提交狀態(tài)記錄在控制文件中，但是不會(huì)記錄在任何視圖中，V$DATABASE.CURRENT_SCN雖然也來自來自控制文件，但是記錄當(dāng)前最新的SCN
database checkpoint
The thread checkpoint that has the lowest SCN. All changes in all enabled redo threads with SCNs before the database checkpoint SCN are guaranteed to have been written to disk.
具有最低SCN的線程檢查點(diǎn)。 在數(shù)據(jù)庫(kù)檢查點(diǎn)SCN之前所有啟用的具有SCN的重做線程的所有更改都保證已寫入磁盤。
data file checkpoint
A data structure that defines an SCN in the redo thread of a database for a particular data file. Every data file has a checkpoint SCN, which you can view in V$DATAFILE.CHECKPOINT_CHANGE#. All changes with an SCN lower than this SCN are guaranteed to be in the data file.
數(shù)據(jù)結(jié)構(gòu)，用于定義特定數(shù)據(jù)文件的數(shù)據(jù)庫(kù)重做線程中的SCN。 每個(gè)數(shù)據(jù)文件都有一個(gè)檢查點(diǎn)SCN，您可以在V$DATAFILE.CHECKPOINT_CHANGE＃中查看。 SCN低于此SCN的所有更改都將保證在數(shù)據(jù)文件中。
Overview of Checkpoints
A checkpoint is a crucial mechanism in consistent database shutdowns, instance recovery, and Oracle Database operation generally. The term checkpoint has the following related meanings:
A data structure that indicates the checkpoint position, which is the SCN in the redo stream where instance recovery must begin
The checkpoint position is determined by the oldest dirty buffer in the database buffer cache. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header.
The writing of modified database buffers in the database buffer cache to disk
檢查點(diǎn)是一致的數(shù)據(jù)庫(kù)關(guān)閉，實(shí)例恢復(fù)和Oracle數(shù)據(jù)庫(kù)操作的關(guān)鍵機(jī)制。 檢查點(diǎn)一詞具有以下相關(guān)含義：
指示檢查點(diǎn)位置的數(shù)據(jù)結(jié)構(gòu)，該位置是實(shí)例恢復(fù)必須開始的重做流中的SCN
檢查點(diǎn)位置由數(shù)據(jù)庫(kù)緩沖區(qū)緩存中最舊的臟緩沖區(qū)確定。 檢查點(diǎn)位置用作指向重做流的指針，存儲(chǔ)在控制文件和每個(gè)數(shù)據(jù)文件頭中。
將數(shù)據(jù)庫(kù)緩沖區(qū)中的修改后的數(shù)據(jù)庫(kù)緩沖區(qū)寫入磁盤
When Oracle Database Initiates Checkpoints
The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints:
Thread checkpoints
The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is a database checkpoint. Thread checkpoints occur in the following situations:
Consistent database shutdown
ALTER SYSTEM CHECKPOINT statement
Online redo log switch
ALTER DATABASE BEGIN BACKUP statement
Tablespace and data file checkpoints
The database writes to disk all buffers modified by redo before a specific target. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing ALTER TABLESPACE BEGIN BACKUP.
檢查點(diǎn)進(jìn)程（CKPT）負(fù)責(zé)將檢查點(diǎn)寫入數(shù)據(jù)文件頭文件和控制文件。 檢查點(diǎn)發(fā)生在各種情況。 例如，Oracle數(shù)據(jù)庫(kù)使用以下類型的檢查點(diǎn)：
線程檢查點(diǎn)
數(shù)據(jù)庫(kù)通過在某個(gè)目標(biāo)之前的特定線程中重做修改的所有緩沖區(qū)寫入磁盤。 數(shù)據(jù)庫(kù)中所有實(shí)例上的一組線程檢查點(diǎn)是一個(gè)數(shù)據(jù)庫(kù)檢查點(diǎn)。 線程檢查點(diǎn)在以下情況下發(fā)生：
一致的數(shù)據(jù)庫(kù)關(guān)機(jī)
ALTER SYSTEM CHECKPOINT語句
在線重做日志切換
ALTER DATABASE BEGIN BACKUP語句
表空間和數(shù)據(jù)文件檢查點(diǎn)
數(shù)據(jù)庫(kù)將磁盤上的所有緩沖區(qū)寫入特定目標(biāo)之前通過重做修改。 表空間檢查點(diǎn)是一組數(shù)據(jù)文件檢查點(diǎn)，一個(gè)用于表空間中的每個(gè)數(shù)據(jù)文件。 這些檢查點(diǎn)發(fā)生在各種情況下，包括使表空間為只讀或正常脫機(jī)，收縮數(shù)據(jù)文件或執(zhí)行ALTER TABLESPACE BEGIN BACKUP。
oracle規(guī)定：保證重做記錄先于對(duì)應(yīng)的臟數(shù)據(jù)塊寫入持久層
所以，令同一個(gè)更改產(chǎn)生的重做記錄為R、臟數(shù)據(jù)塊為D，那么lgwr沒有把R寫入在線日志的情況下，oracle是不允許dbwr把D先行寫入數(shù)據(jù)文件的。即便是dbwr首先發(fā)出請(qǐng)求，也必須等待lgwr先清空日志緩沖。這樣，數(shù)據(jù)文件中的內(nèi)容永遠(yuǎn)沒有在線日志的內(nèi)容更新得快，也就是在數(shù)據(jù)庫(kù)打開的情況下，數(shù)據(jù)文件永遠(yuǎn)比在線日志“舊”，為了標(biāo)識(shí)數(shù)據(jù)文件“舊”到什么程度，oracle便引入了檢查點(diǎn)。
Oracle通過檢查點(diǎn)（Checkpoint）來縮減恢復(fù)時(shí)間。檢查點(diǎn)只是一個(gè)數(shù)據(jù)庫(kù)事件，它存在的根本意義在于減少恢復(fù)時(shí)間。
完全檢查點(diǎn)步驟
1.在日志緩沖中確定當(dāng)前的（也是最新的）重做記錄，提取其RBA和SCN作為檢查點(diǎn)的目標(biāo)
2.lgwr清空日志緩沖，將重做記錄寫入在線日志
3.dbwr進(jìn)程將檢查點(diǎn)目標(biāo)（RBA與SCN）產(chǎn)生的及檢查點(diǎn)目標(biāo)之前產(chǎn)生的臟數(shù)據(jù)塊，按RBA的順序?qū)懭霐?shù)據(jù)文件
4.ckpt進(jìn)程將檢查點(diǎn)目標(biāo)（RBA與SCN）寫入數(shù)據(jù)文件的頭部和控制文件
這樣數(shù)據(jù)文件頭部的檢查點(diǎn)目標(biāo)（RBA與SCN）便能提供如下信息
1.讀取數(shù)據(jù)文件頭的檢查點(diǎn)scn與在線日志重做記錄的scn比較，就可以知道該數(shù)據(jù)文件是否需要恢復(fù)
2.如果該數(shù)據(jù)文件需要恢復(fù)，數(shù)據(jù)文件SCN用來表示從這個(gè)SCN開始恢復(fù)
完全檢查點(diǎn)發(fā)生時(shí)機(jī)
1.shutdown、shutdown normal、shutdown transactional、shutdown immediate命令
2.alter system checkpoint
3.ALTER DATABASE BEGIN BACKUP
4.執(zhí)行部分表空間維護(hù)命令如alter tablespace tablespacename offline|online|begein backup|end backup|read only|read write
--Online redo log switch時(shí)只有V$DATABASE.CONTROLFILE_CHANGE#變大
--ALTER DATABASE BEGIN BACKUP時(shí)$DATABASE.CHECKPOINT_CHANGE#沒變，雖然V$DATABASE.CONTROLFILE_CHANGE#、V$DATAFILE.CHECKPOINT_CHANGE#、V$DATAFILE_HEADER.CHECKPOINT_CHANGE#都變大
--alter tablespace users begin backup時(shí)，V$DATABASE.CONTROLFILE_CHANGE#、對(duì)應(yīng)的表空間文件V$DATAFILE.CHECKPOINT_CHANGE#、對(duì)應(yīng)的表空間文件V$DATAFILE_HEADER.CHECKPOINT_CHANGE#變大，$DATABASE.CHECKPOINT_CHANGE#和?其他表空間對(duì)應(yīng)數(shù)據(jù)文件CHECKPOINT_CHANGE#沒變
完全檢查點(diǎn)：個(gè)人更愿意理解只有V$DATABASE.CHECKPOINT_CHANGE#增加了才算發(fā)生了一次完全檢查點(diǎn)。雖然完全檢查點(diǎn)的定義是只要同時(shí)寫入數(shù)據(jù)文件頭部和控制文件就認(rèn)為是完全檢查點(diǎn)。不過按個(gè)人理解其實(shí)上面BEGIN BACKUP這樣的操作就不算完全檢查點(diǎn)了。
Incremental checkpoints
An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.
Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.
增量檢查點(diǎn)是一種類型的線程檢查點(diǎn)，部分是為了避免在線重做日志切換中寫入大量塊。 DBWn至少每三秒檢查一次，以確定是否有工作要做。 當(dāng)DBWn寫入臟緩沖區(qū)時(shí)，它會(huì)提前檢查點(diǎn)位置，導(dǎo)致CKPT將檢查點(diǎn)位置寫入控制文件，而不是數(shù)據(jù)文件頭。
其他類型的檢查點(diǎn)在模式對(duì)象被刪除或截?cái)鄷r(shí)包括實(shí)例和介質(zhì)恢復(fù)檢查點(diǎn)和檢查點(diǎn)。
增量檢查點(diǎn)
增量檢查點(diǎn)會(huì)推動(dòng)dbwr將部分臟數(shù)據(jù)塊寫回?cái)?shù)據(jù)文件，但是檢查點(diǎn)SCN只是記錄到控制文件即V$DATABASE.CONTROLFILE_CHANGE#，而沒有寫入數(shù)據(jù)文件頭部。實(shí)例恢復(fù)操作并不會(huì)因此而少索取日志記錄。比如：增量檢查點(diǎn)SCN到200了，意味著數(shù)據(jù)文件內(nèi)數(shù)據(jù)塊的最高的SCN也已經(jīng)是200了，但是數(shù)據(jù)文件頭部可能還寫著檢查點(diǎn)SCN是100,如果此時(shí)實(shí)例崩潰，實(shí)例恢復(fù)的前滾會(huì)從scn為100的重做記錄開始，實(shí)際上從scn號(hào)100~200的重做記錄根本不需要前滾。oracle當(dāng)然了解這一點(diǎn)，所以每次寫完臟數(shù)據(jù)塊之后，dbwr會(huì)添加一條被稱為BWR（block written record，數(shù)據(jù)塊已寫）的重做記錄，該記錄的變更矢量不代表任何變更，只是用來標(biāo)記哪些數(shù)據(jù)塊已經(jīng)被寫回?cái)?shù)據(jù)文件了。
因?yàn)橛蠦WR這樣的記錄，oracle在進(jìn)行自動(dòng)前滾時(shí)實(shí)際上采用“兩次讀取法”讀取在線日志，仍然假設(shè)數(shù)據(jù)文件檢查點(diǎn)scn為100，增量檢查點(diǎn)已經(jīng)到200了，第一次讀取日志中scn號(hào)100后的所有重做記錄，目的是確定所有的真正的需要恢復(fù)的數(shù)據(jù)塊有哪些，BWR記錄就像一個(gè)過濾器，可以大量減少這樣的數(shù)據(jù)塊。第二次讀取在線日志時(shí)oracle只對(duì)真正需要恢復(fù)的數(shù)據(jù)塊，即沒有被BWR記錄點(diǎn)到名的數(shù)據(jù)塊的重做記錄感興趣。結(jié)果是oracle會(huì)跳過scn從100到200之間的重做記錄，也就會(huì)使前滾相應(yīng)變快
增量檢查點(diǎn)作用：
1.減少發(fā)生完全檢查點(diǎn)是dbwr進(jìn)程的工作負(fù)擔(dān)
2.提高實(shí)例恢復(fù)的速度
增量檢查點(diǎn)發(fā)生時(shí)機(jī)
1.oracle自動(dòng)控制(當(dāng)三個(gè)參數(shù)都不設(shè)置或三個(gè)參數(shù)都設(shè)置不當(dāng)時(shí)FAST_START_MTTR_TARGET、LOG_CHECKPOINT_TIMEOUT、LOG_CHECKPOINT_INTERVAL)
2.三個(gè)參數(shù)取最嚴(yán)厲的那個(gè)(FAST_START_MTTR_TARGET、LOG_CHECKPOINT_TIMEOUT、LOG_CHECKPOINT_INTERVAL)
3.lgwr切換在線日志
--alter system flush buffer_cache不會(huì)引發(fā)增加檢查點(diǎn)
因?yàn)閘gwr切換在線日志產(chǎn)生增量檢查點(diǎn)，說明完全檢查點(diǎn)（數(shù)據(jù)文件頭部scn）還沒有越過active的在線日志，但是增量檢查點(diǎn)（控制文件scn）已經(jīng)越過了active的在線日志可能已經(jīng)在current的在線日志了，因?yàn)閞ecover時(shí)使用兩次讀取法，先讀完全檢查點(diǎn)再去增量檢查點(diǎn)，所有recover的時(shí)候還是需要active的在線日志和current的在線日志，也就是說，current在線日志丟失時(shí)，如果還有active的在線日志，不能只恢復(fù)到active的在線日志，還需要current的日志，因?yàn)樵隽繖z查點(diǎn)可能已經(jīng)在current的日志。
增量檢查點(diǎn)的三個(gè)參數(shù)
FAST_START_MTTR_TARGET默認(rèn)為0，LOG_CHECKPOINT_INTERVAL默認(rèn)為0，LOG_CHECKPOINT_TIMEOUT默認(rèn)為1800
FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified,FAST_START_MTTR_TARGET is overridden by LOG_CHECKPOINT_INTERVAL.
當(dāng)設(shè)置了LOG_CHECKPOINT_INTERVAL時(shí)，LOG_CHECKPOINT_INTERVAL的設(shè)置會(huì)覆蓋FAST_START_MTTR_TARGET的設(shè)置，而不是說11G已經(jīng)取消了FAST_START_MTTR_TARGET這個(gè)參數(shù)的功能。
LOG_CHECKPOINT_INTERVALspecifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks
這里指的blocks是OS的block，而不是DATABASE的block
Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. Therefore, if the value exceeds the actual redo log file size,checkpoints occur only when switching logs. Checkpoint frequency is one of the factors that influence the time required for the database to recover from an unexpected failure
當(dāng)LOG_CHECKPOINT_INTERVAL值大于redo log file size時(shí)，增量檢查點(diǎn)發(fā)生情況就是在線日志切換取代LOG_CHECKPOINT_INTERVAL
Specifying a value of 0 (zero) for LOG_CHECKPOINT_INTERVAL has the same effect as setting the parameter to infinity and causes the parameter to be ignored. Only nonzero values of this parameter are considered meaningful.
當(dāng)LOG_CHECKPOINT_INTERVAL為0時(shí)，LOG_CHECKPOINT_INTERVAL這個(gè)參數(shù)就不起作用了
Recovery I/O can also be limited by setting the LOG_CHECKPOINT_TIMEOUT parameter or by the size specified for the smallest redo log.
LOG_CHECKPOINT_TIMEOUT和LOG_CHECKPOINT_INTERVAL都生效，但是取兩者更嚴(yán)厲的那個(gè)
LOG_CHECKPOINT_TIMEOUTspecifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log(sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.
Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set
不建議設(shè)置LOG_CHECKPOINT_TIMEOUT為0，除非你設(shè)置了FAST_START_MTTR_TARGET
FAST_START_MTTR_TARGET、LOG_CHECKPOINT_INTERVAL為0時(shí)，LOG_CHECKPOINT_TIMEOUT也生效的
實(shí)例恢復(fù)的2中情況：
1、增量檢查點(diǎn)SCN和在線日志中的SCN進(jìn)行對(duì)比，如果大于在線日志的first_change#，說明和在線日志接上了，可以實(shí)例恢復(fù)
因?yàn)閘ogfile switch就會(huì)發(fā)生一次增量檢查點(diǎn)，所以歸檔日志總是落后實(shí)例的增量檢查點(diǎn)，狀態(tài)為ACTIVE的日志也可能已經(jīng)是歸檔日志了，所以實(shí)例恢復(fù)永遠(yuǎn)是使用current的在線日志
2、在mount情況下，$datafile.last_change#為空則必須要實(shí)例恢復(fù)，mount下$datafile.last_change#為空的情況有兩種
2.1、shutdown abort
2.2、控制是恢復(fù)過來的
數(shù)據(jù)庫(kù)OPEN狀態(tài)下v$datafile.last_change#的SCN號(hào)始終為NULL，而當(dāng)數(shù)據(jù)庫(kù)正常關(guān)閉時(shí)，會(huì)進(jìn)行完全檢查點(diǎn)，并將檢查點(diǎn)SCN號(hào)更新到該字段，
所以可以在mount狀態(tài)通過v$datafile.last_change#的SCN號(hào)是否為null來判斷是不是需要實(shí)例恢復(fù)。而崩潰時(shí)，Oracle還來不及更新該字段，則該字段仍然為NULL。當(dāng)SMON進(jìn)程發(fā)現(xiàn)該字段為空時(shí)，就知道實(shí)例在上次沒有正常關(guān)閉，于是由SMON進(jìn)程就開始進(jìn)行實(shí)例恢復(fù)了。SMON進(jìn)程進(jìn)行實(shí)例恢復(fù)時(shí)，會(huì)從控制文件中獲得檢查點(diǎn)位置。于是，SMON進(jìn)程到聯(lián)機(jī)日志文件中，找到該檢查點(diǎn)位置，然后從該檢查點(diǎn)位置開始往下，應(yīng)用所有的重做條目，從而在buffer cache里又恢復(fù)了實(shí)例崩潰那個(gè)時(shí)間點(diǎn)的狀態(tài)。這個(gè)過程叫做前滾，前滾完成后，buffer cache里既有崩潰時(shí)已經(jīng)提交還沒有寫入數(shù)據(jù)文件的臟數(shù)據(jù)塊，也還有事務(wù)被突然終止，而導(dǎo)致的既沒有提交又沒有回滾的事務(wù)所弄臟的數(shù)據(jù)塊，這類臟數(shù)據(jù)塊分兩種，一種是還在buffer cache中，一種是已經(jīng)被dbwr寫入了磁盤文件。前滾一旦完畢，SMON進(jìn)程立即打開數(shù)據(jù)庫(kù)。但是，這時(shí)的數(shù)據(jù)庫(kù)中還含有那些既沒有提交又沒有回滾的臟塊，這種臟塊是不能存在于數(shù)據(jù)庫(kù)中的，因?yàn)樗鼈儾]有被提交，必須被回滾。打開數(shù)據(jù)庫(kù)以后，SMON進(jìn)程會(huì)在后臺(tái)進(jìn)行回滾。
介質(zhì)恢復(fù)的情況：
各個(gè)數(shù)據(jù)文件頭部檢查點(diǎn)scn不一致，增加檢查點(diǎn)的SCN遠(yuǎn)遠(yuǎn)小于在線日志的first_change#，說明和在線日志接不上，需要介質(zhì)恢復(fù)，需要用到歸檔日志進(jìn)行recover(更嚴(yán)重的情況是還可能需要restore數(shù)據(jù)文件并使用歸檔日志recover)
RAMN經(jīng)常遇到的1號(hào)文件system太新的問題
如下三者都是說file 1太新了，file 1需要更多的恢復(fù)
ORA-01194: file 1 needs more recovery to be consistent
ORA-01113: file 1 needs media recovery
RMAN-06556: datafile 1 must be restored from backup older than scn 919248820
datafile 1的scn大于919248820，也就是datafile 1太新了，不夠舊不夠老
比如正常關(guān)機(jī)后，startup mount狀態(tài)，
sql直接recover database會(huì)報(bào)錯(cuò)ORA-00264: no recovery required
rman直接recover database until sequence到前面幾個(gè)archivelog就會(huì)報(bào)RMAN-06556
文章題目：SCN、Checkpoint、實(shí)例恢復(fù)介質(zhì)恢復(fù)理解
標(biāo)題路徑：http://bm7419.com/article24/iiodce.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、Google、建站公司、軟件開發(fā)、手機(jī)網(wǎng)站建設(shè)、面包屑導(dǎo)航
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)
- 如何辨別虛擬主機(jī)的好壞？ 2022-10-08
- 虛擬主機(jī)性能提升的五個(gè)關(guān)鍵點(diǎn)，你必須知道 2022-10-04
- 新加坡虛擬主機(jī)有什么優(yōu)勢(shì)？虛擬主機(jī)有什么用？ 2022-10-04
- 什么是虛擬主機(jī)？虛擬主機(jī)的優(yōu)點(diǎn)和缺點(diǎn)有什么？ 2023-03-07
- 成都婚紗攝影類的網(wǎng)站建設(shè)應(yīng)該怎么選擇虛擬主機(jī)呢？ 2016-08-29
- 國(guó)內(nèi)虛擬主機(jī)租用服務(wù)到底好不好？ 2022-10-06
- 網(wǎng)站優(yōu)化對(duì)虛擬主機(jī)的要求有哪些？ 2022-10-04
- 香港虛擬主機(jī)和香港服務(wù)器有什么區(qū)別 2022-10-02
- 虛擬主機(jī)空間怎么選擇？ 2022-10-09
- 新手建設(shè)網(wǎng)站需要注意的三要素 2022-06-19
- 企業(yè)網(wǎng)站建設(shè)使用虛擬主機(jī) 這幾點(diǎn)要測(cè)一下 2016-08-06
- 從價(jià)格角度看虛擬主機(jī)空間性價(jià)比 2022-10-07