Oracle checkpoint 說明

 

 

一. Oracle Checkpoint 說明

 1.1  Checkpoint

（1）A synchronization event at aspecific point in time

（2）Causes some or all dirty blockimages to be written to the database thereby guaranteeing that blocks dirtiedprior to that point in time get written

（3）Brings administration up to date

（4）Several types of checkpoint exist

 

RedoLog Checkpoint 和 SCN關係

http://blog.csdn.net/tianlesoftware/article/details/5251916

 

Oracle 執行個體恢複時 前滾（roll forward） 後滾（rollback） 問題

http://blog.csdn.net/tianlesoftware/article/details/6286330

 

1.2  the point of Oracle Checkpoints

       The point of Oracle checkpoints is to synchronize all datafiles, some datafiles orsome objects to a point in time for consistency, performance and recoverabilitypurposes.

  

1.3 Buffer Cache

       checkpoint與Buffer Cache 的關係很大，有關Buffer cache，之前整理的相關文章如下：

Oracle Buffer Cache 原理

http://blog.csdn.net/tianlesoftware/article/details/6573438

 

Oracle Buffer Cache 中的Recycle Pool 說明

http://blog.csdn.net/tianlesoftware/article/details/6584110

 

Oracle Buffer Cache 中 KeepPool 說明

http://blog.csdn.net/tianlesoftware/article/details/6581159

 

1.3.1 buffer cache 組成

The buffer cache component structures are:

（1）Buffers

       Eachbuffer may hold an image of one data block at any one time

（2）Buffer headers

       =>Storemetadata about contents of the buffers

       =>Actas cache management structures

（3）Buffer pools

       Collectionof buffers used for the same purpose and managed accordingly

（4）Working set

       =>Allor part of a buffer pool

       =>Assigned to a DBWn process

 

1.3.2 Buffer Management

（1） Cached buffers managed by doublylinked lists:

（2） REPL

              =>Bufferscontaining block images being used

（3） REPL-AUX

             =>Buffersready to be used for I/O or CR build

              關於CR 參考：

              CR (consistent read) blockscreate 說明

              http://blog.csdn.net/tianlesoftware/article/details/6529401

（4） WRITE and CKPT-Q

             =>DirtyBuffers requiring I/O

（5） WRITE-AUX

             =>DirtyBuffers with I/O in progress

（6） Touch count is used to decide theinitial insertion location in the REPL chain

（7） AUX lists avoid wasteful scanning

 

1.3.3 Redo and the Buffer Cache

（1） Block modification dirties thebuffer containing the block image and generates redo

（2） A buffer becomes dirty at aparticular RBA which is a point in the redo stream

       關於RBA,參考我的blog：

       Oracle RBA(Redo Byte Address) 說明

       http://blog.csdn.net/tianlesoftware/article/details/6700080

（3） Redo written by LGWR makes thecorresponding part of the redo log file “active”

（4） Dirty block images written by DBWnmakes the corresponding part of the redo log file “inactive”

（5） Redo is always written priorto the corresponding block images

（6） Size of active redo in the logfile influences instance and crash recovery time

（7） Trade-off between performance andrecovery time

 

1.3.4 Buffer Cache I/O

（1）Servers look for an availablebuffer on REPL-AUX then read a data block into selected buffer

       –Buffer gets moved from REPL-AUX to REPL

       –If block is modified, buffer is added to CKPT-Q

       –Servers move dirty buffers to WRITE during free buffer search

（2） DBWn writes dirty buffer contentsto database

       –Buffer gets moved from WRITE to WRITE-AUX

       –Once block written:

       –Buffer is moved back to REPL-AUX

       –Buffer taken off CKPT-Q

（3） DBWn writes upon request

       –Make free buffers

       –Checkpoint

 

1.4 觸發checkpoint 的條件

       與checkpoint 觸發相關的視圖：v$instance_recovery。 關於這個視圖的具體說明可以參考官方文檔：

       http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_2004.htm#REFRN30106

 

幾個相關的欄位如下：

（1）WRITES_MTTR：fast_start_mttr_target

（2）WRITES_LOGFILE_SIZE：90% of the smallest online redo logfile

（3）WRITES_LOG_CHECKPOINT_SETTINGS：log_checkpoint_timeout

（4）WRITES_OTHER_SETTINGS：fast_start_io_target

（5）WRITES_AUTOTUNE：10g self tuning checkpoints

（6） WRITES_FULL_THREAD_CKPT：Manual checkpoints

 

CKPT發送CHECKPOINT訊號的觸發條件有如下幾條：

       1.log_checkpoint_timeout時間達到
       2. 當前redo日誌已經寫夠log_checkpoint_internavl*作業系統塊大小
       3. redo log switch
       4. alter system checkpoint
       5. alter tablespace XXX begin backup，end backup的時候
       6. alter tablespace , datafileoffline, shutdown immediate, direct read的時候;

 

       Oracle DBWR,LGWR,CKPT,ARCH 觸發條件 總結

       http://blog.csdn.net/tianlesoftware/article/details/6574584

 

       在下面具體的checkpoint 分類時有更詳細的說明。

 

二. Checkpoint 分類

Checkpoint 可以分為以下幾類：

（1） Full Checkpoint

（2） Thread Checkpoint

（3） File Checkpoint

（4） Object “Checkpoint”

（5） Parallel Query Checkpoint

（6） Incremental Checkpoint

（7） Log Switch Checkpoint

 

       如果想看到具體的checkpoint 類型，可以設定log_checkpoints_to_alert 參數為true。 設定為true之後，checkpoint 發生時會寫入alert log裡。

如：

SYS@dave2(db2)>alter system set log_checkpoints_to_alert=true scope=both;

System altered.

SYS@dave2(db2)> alter system switchlogfile;

System altered.

 

log裡的資訊如下：

Thu Aug 18 18:46:18 2011

ALTER SYSTEM SETlog_checkpoints_to_alert=TRUE SCOPE=BOTH;

Thu Aug 18 18:48:07 2011

Beginning logswitch checkpoint up to RBA [0xa.2.10], SCN: 2148380730

Thread 1 advanced to log sequence 10

 Current log# 3 seq# 10 mem# 0: /u01/app/oracle/oradata/dave2/redo03.log

 

 

2.1 Full Checkpoint

• Writes block images to the database forall dirty buffers from all instances

• Statistics updated:

– DBWR checkpoints

– DBWR checkpoint buffers written

– DBWR thread checkpoint buffers written

• Caused by:

– Alter system checkpoint [global]

– Alter database close

– Shutdown

• Controlfile and datafile headers areupdated

– CHECKPOINT_CHANGE#

 

2.2 Thread Checkpoint

• Writes block images to the database forall dirty buffers from one instance

• Statistics updated:

– DBWR checkpoints

– DBWR checkpoint buffers written

– DBWR thread checkpoint buffers written

• Caused by:

– Alter system checkpoint local

• Controlfile and datafile headers areupdated

– CHECKPOINT_CHANGE#

 

2.3 File Checkpoint

• Writes block images to the database forall dirty buffers for all files of a tablespace from all instances

• Statistics updated:

– DBWR tablespace checkpoint bufferswritten

– DBWR checkpoint buffers written

– DBWR checkpoints

• Caused by:

– Alter tablespace XXX offline

– Alter tablespace XXX begin backup

– Alter tablespace XXX read only

• Controlfile and datafile headers areupdated

– CHECKPOINT_CHANGE#

 

2.4 Parallel Query Checkpoint

• Writes block images to the database forall dirty buffers belonging to objects accessed by the query from all instances

• Statistics updated:

– DBWR checkpoint buffers written

– DBWR checkpoints

• Caused by:

– Parallel Query

– Parallel Query component of PDML or PDDL

– Mandatory for consistency

 

2.5 Object “Checkpoint”

• Writes block images to the database forall dirty buffers belonging to an object from all instances

• Statistics updated:

– DBWR object drop buffers written

– DBWR checkpoints

• Caused by:

– Drop table XXX

– Drop table XXX purge

– Truncate table XXX

• Mandatory for media recovery purposes

 

2.6 Incremental Checkpoint

• Writes the contents of “some” dirty buffers to the database from CKPT-Q

• Block images written in SCN order

• Checkpoint RBA updated in SGA

• Statistics updated:

– DBWR checkpoint buffers written

• Controlfile is updated every 3 seconds byCKPT

– Checkpoint progress record

 

Definition of “Some”

• Every 3 seconds CKPT calculates thecheckpoint

target RBA based on:

– The most current RBA

– log_checkpoint_timeout

– log_checkpoint_interval

– fast_start_mttr_target

– fast_start_io_target

– 90% of the size of the smallest onlineredo log file

• All buffers dirtied prior to the timecorresponding to the target RBA are written to the database

 

2.7 Log Switch Checkpoint

• Writes the contents of “some” dirtybuffers to the database

• Statistics updated:

– DBWR checkpoints

– DBWR checkpoint buffers written

– background checkpoints started

– background checkpoints completed

• Controlfile and datafile headers areupdated

– CHECKPOINT_CHANGE#

 

2.8 Checkpoint Administration

• Useful checkpoint administration views:

– V$INSTANCE_RECOVERY

– V$SYSSTAT

– V$DATABASE

– V$INSTANCE_LOG_GROUP

– V$THREAD

– V$DATAFILE

– V$DATAFILE_HEADER

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群： 83829929  DBA5群： 142216823   

DBA6 群：158654907  聊天 群：40132017   聊天2群：69087192

--加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請