Oracle checkpoint description

 

 

1. Oracle checkpoint description

 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 and SCN relationship

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

 

Roll Forward

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

The checkpoint has a lot to do with the buffer cache. For the buffer cache, the previous articles are as follows:

Oracle buffer cache principles

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

 

Recycle pool description in Oracle buffer cache

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

 

Keeppool description in Oracle buffer cache

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

 

1.3.1 buffer cache Composition

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 reference:

Description of 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

For details about RBA, refer to my blog:

Oracle RBA (redo byte address) Description

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 movable 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 takenoff CKPT-Q

(3) dbwn writes upon request

-Make free Buffers

-Checkpoint

 

1.4 conditions for triggering checkpoint

View related to Checkpoint triggering: V $ instance_recovery. For details about this view, refer to the official documentation:

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

 

Several related fields are as follows:

(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

 

The following conditions are met when ckpt sends the checkpoint signal:

1. log_checkpoint_timeout time reached
2. The current redo log has been written enough for the log_checkpoint_internavl * Operating System block size
3. redo log Switch
4. Alter system checkpoint
5. Alter tablespace XXX begin backup, end backup time
6. Alter tablespace, datafileoffline, shutdown immediate, and direct read;

 

Summary of the Triggering Conditions for Oracle dbwr, lgwr, ckpt, and arch

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

 

The specific checkpoint categories below are described in more detail.

 

Ii. Checkpoint Classification

Checkpoint can be divided into the following categories:

(1) Full checkpoint

(2) thread checkpoint

(3) file checkpoint

(4) object "checkpoint"

(5) parallel query checkpoint

(6) incremental checkpoint

(7) log switch checkpoint

 

If you want to see the specific checkpoint type, you can set the log_checkpoints_to_alert parameter to true. If it is set to true, the checkpoint will be written to the Alert Log when it occurs.

For example:

Sys @ dave2 (DB2)> alter system set log_checkpoints_to_alert = true scope = both;

System altered.

Sys @ dave2 (DB2)> alter system switchlogfile;

System altered.

 

The information in log is as follows:

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:

-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:

-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:

-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:

-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:

-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

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

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

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 chat group: 40132017 chat group 2: 69087192

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.