ORACLE CHECKPOINT

ORACLE CHECKPOINT

Basic purpose of Checkpoint: 

Database blocks are temporarily stored in Database buffer cache. Once the database blocks are read, they will be stored in the DB buffer cache so that if any other user accesses the same data later point of time, the data will be available in memory and this will avoid the Disk reads. 

When we update any row, the buffer in DB buffer cache corresponding to the block containing that row is updated in memory. Record of the change made is kept in redo log buffer . On commit, the changes will be written to the disk thereby making them permanent. 

But where are those changes written? Is it to the datafiles containing data blocks? No !!! The changes are recorded in online redo log files by flushing the contents of redo log buffer to them.This is called write ahead logging.  If the instance crashed right now, the DB buffer cache will be wiped out but on restarting the database, Oracle will apply the changes recorded in redo log files to the datafiles.

Why doesn’t Oracle write the changes to datafiles right away when we commit the transaction? The reason is simple. If it chose to write directly to the datafiles, it will have to physically locate the data block in the datafile first and then update it which means that after committing, user has to wait until DBWR searches for the block and then writes it before he can issue next command. This will bring down the performance drastically. That is where the role of redo logs comes in. The writes to the redo logs are sequential writes – LGWR just dumps the info in redologs to log files sequentially and synchronously so that the user does not have to wait for long. Moreover, DBWR will always write in units of Oracle blocks whereas LGWR will write only the changes made. Hence,  write ahead logging also improves performance by reducing the amount of data written synchronously. 

When will the changes be applied to the datablocks in datafiles? The data blocks in the datafiles will be updated by the DBWR asynchronously in response to certain triggers. These triggers are called checkpoints.

Checkpoint is a synchronization event at a specific point in time which causes some / all dirty blocks to be written to disk thereby guaranteeing that blocks dirtied prior to that point in time get written.

Whenever dirty blocks are written to datafiles, it allows oracle : 

• to reuse a redo log : A redo log can’t be reused until DBWR writes all the dirty blocks protected by that logfile to disk. If we attempt to reuse it before DBWR has finished its checkpoint, we get the following message in alert log : Checkpoint not complete.

• to reduce instance recovery time : As the memory available to a database instance increases, it is possible to have database buffer caches as large as several million buffers. It requires that the database checkpoint advance frequently to limit recovery time, since infrequent checkpoints and large buffer caches can exacerbate crash recovery times significantly.

• to free buffers for reads : Dirtied blocks can’t be used to read new data into them until they are written to disk. Thus DBWrR writes dirty blocks from the buffer cache, to make room in the cache.

Various types of checkpoints  in Oracle :

•         Full checkpoint
•        Thread checkpoint
•         File checkpoint
•         Parallel Query checkpoint
•         Object checkpoint
•        Log switch checkpoint
•            Incremental checkpoint

Whenever a checkpoint is triggered :

• DBWR writes some /all dirty blocks to datafiles
• CKPT process updates the control file and datafile headers

FULL CHECKPOINT

Writes block images to the database for all 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 begin backup

Alter database close

Shutdown [immediate]

Controlfile and datafile headers are updated

Checkpoint_change#

THREAD CHECKPOINT

Writes block images to the database for all 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 are updated
• Checkpoint_change#

FILE CHECKPOINT

• When a tablespace is put into backup mode or take it offline, oracle writes all the dirty blocks from the tablespace to disk before changing the state of the tablespace.
• Writes block images to the database for all dirty buffers for all files of a tablespace from all instances

Statistics updated

• DBWR checkpoints
• DBWR tablespace checkpoint buffers written
• DBWR checkpoint buffers written

Caused by :

• Alter tablespace xxx offline
• Alter tablespace xxx begin backup
• Alter tablespace xxx read only
• Controlfile and datafile headers are updated
• Checkpoint_change#

PARALLEL QUERY CHECKPOINT

Parallel query often results in direct path reads (Full tablescan or index fast full scan). This means that blocks are read straight into the session’s PGA, bypassing the data cache; but that means if there are dirty buffers in the data cache, the session won’t see the most recent versions of the blocks unless they are copied to disk before the query starts – so parallel queries start with a checkpoint.

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

Statistics updated

• DBWR checkpoints
• DBWR checkpoint buffers written

Caused by :

• Parallel Query
• Parallel Query component of Parallel DML (PDML) or Parallel DDL (PDDL)
• Mandatory for consistency
• Controlfile and datafile headers are updated
• Checkpoint_change#

OBJECT CHECKPOINT

When an object is dropped/truncated, the session initiates an object checkpoint telling DBWR to copy any dirty buffers for that object to disk and the state of those buffers is changed to free.

Writes block images to the database for all dirty buffers belonging to an object from all instances.

Statistics updated

• DBWR checkpoints
• DBWR object drop buffers written

Caused by dropping or truncating a segment:

• Drop table XXX
• Drop table XXX Purge
• Truncate table xxx
• Drop index xxx

Mandatory for media recovery purposes

• Controlfile and datafile headers are updated
• Checkpoint_change#

LOG SWITCH CHECKPOINT

Writes the contents of the  dirty buffers whose information is protected by a redo log to the database .

Statistics updated

• DBWR checkpoints
• DBWR checkpoint buffers written
• background checkpoints started
• background checkpoints completed
• Caused by log switch
• Controlfile and datafile headers are updated
• Checkpoint_change#

INCREMENTAL CHECKPOINT

Prior to Oracle 8i, only well known checkpoint was log switch checkpoint. Whenever LGWR filled an online logfile, DBWR would go into a frenzy writing data blocks to disks, and when it had finished, Oracle would update each data file header block with the SCN to show that file was updated up to that point in time.

ORACLE Architecture

Basic Knowledge on Oracle Database

Today I wanted to publish the overview of an Oracle database.  By the end of this post, You will came to know the following information.

·         Definition of an Oracle, Oracle Server and Oracle Database.

      ·         Physical and Logical structure of the database.

Oracle is the name of database management system and is developed by Oracle Corporation.

Oracle Server manages the data in the database. Users will access the oracle server using the SQL commands. So Oracle server receives SQL commands from users and executes them on the database.

An Oracle database is a logical collection of inter related data or is a collection of data, treated as a single unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers.

The database has Logical structures and the Physical Structures.

Oracle Database Physical Structure:

The Physical structure of the database is placed in Physical level. It is physically a set of three operating system files.

o   Data Files : It contains the data of the database.  Every table that is stored in the database is a part of these files. Only Oracle Server can interpret these datafiles.

o   Redo log files : Every database has a set of two or more Redo Log files. The set of redo log files is known as databases redo log. Redo Log files are used in the failure recovery. All the changes made to the database are written to redo log files.

o   Control files : This can be treated as a heart of a database. Contain information required to verify the integrity of the database.   The control file will record the following information

·         Database name

·         Name and location of data files and redo log files and ect.  

I will publish the detailed information on these Physical structure of the database in the further posts.

Oracle Database Logical Structure: 

Oracle database is divided into logical storage units known as Tablespaces, which can be used to group related logical structures together. Logical structure mainly consists of following structures starting with the bigger unit and going down to the smallest. 

• Tablespace
• Segments
• Extents
• Data blocks

Tablespace:  Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace. 

Segment :  Tablespace consists of one or more segments. A segment is a set of extents allocated for a certain logical structure stored in the same tablespace. The segments can be of one of following types:

o   Data segment  (stores user data within the database)

o   Index Segment (store Indexes)

o   Temporary Segment ( stores rollback segment)

o   Rollback/Undo segment (created when sql statement needs a temporary work area)

Extent : An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information in one data file. 

Data Blocks : Oracle database data is stored in data blocks(at the lowest logical level).  One data block corresponds to a specific number of bytes of physical database space on disk(default 8K). The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

Oracle Data blocks are the smallest unit of I/O in the database and they are mapped to OS block on the storage device.