Role of SCN in Instance Recovery & Media Recovery

In last post we have discussed the different types of checkpoints and association of SCN. Also we have examined the status of datafile headers wrt End (checkpoint) SCN after a clean shutdown. In this post we are examining role of SCN in instance recovery and media recovery.

SCN after an Instance Crash

In this session we are going to demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:

SQL> create table dummytable (x number) tablespace test;
Table created.

SQL> insert into dummytable values(100);
1 row created.

During subsequent startup after instance crash Oracle will realize before opening database that value for Last_Change# column for datafiles are not showing SCN value , which means END Checkpoint SCN was not done, which would be indication for Oracle that instance recovery is required.

Let’s mimic instance failure and then startup mount to examine the situation

Read more of this post


Checkpoint & SCN

Checkpoint is a data structure that indicates the “checkpoint position“, determined by the oldest dirty buffer in the database buffer cache. In terms of Oracle’s clock this position is actually the SCN in the redo stream where instance recovery must begin. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header. Whenever we say checkpoint happened we mean that The writing of modified database buffers in the database buffer cache to disk. A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles and SCNs recorded in the file headers guarantee that all changes made to database blocks prior to that SCN are already written to disk. As a result, only those changes made after the checkpoint need to be applied during recovery.

Checkpoints triggered on following conditions:

  • Every 3 seconds (Incremental Checkpoint)
  • When Logswitch happened
  • When instance shutdown normal/transactional/immediate
  • Whenever Alter Tablespace [Offline Normal| Read Only| Begin Backup]
  • Controlled by internal checkpoint forced by recovery related parameters i.e. Fast_Start_MTTR_Target etc.

Purpose of Checkpoints

Oracle Database uses checkpoints to achieve the following goals:

  • Reduce the time required for recovery in case of an instance or media failure
  • Ensure that dirty buffers in the buffer cache are written to disk regularly
  • Ensure that all committed data is written to disk during a consistent shutdown

When Oracle Database Initiates Checkpoints

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Implementing full checkpoint every time would be a costly operation and a major bottleneck for concurrency, so Oracle using different types of checkpoints based on different purposes:
Read more of this post

System Change Number (SCN)

From day one of my DBA career I used to listen SCN or System Change Number or System Commit Number so many times yet I haven’t found any reliable source to find details of SCN for RDBMS concept point of view. So I am thinking to share some information about SCN which I got from different sources and am convinced too.

What is SCN?

The system change number (SCN) is a logical, internal timestamp used by the Oracle Database. It’s basically to make sure that database system will align to its primary goal to align with ACID property. When users generate updates, the RDBMS records the SCN (“time”) at which the update took place. The SCN is a monotonically increasing sequence. It gets bumped up by some events, but several events may share the same SCN, which is another way of saying that they “happened at the same time” with respect to the RDBMS. The database uses SCNs to query and track changes. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction typically have the same SCN. When a transaction commits, the database records an SCN for this commit. Multiple transactions that commit at the same time may share the same SCN.

In single-instance Oracle, the System Global Area maintains and increments SCNs from an instance that has mounted the database in exclusive mode. In RAC the SCN maintained globally. Its implementation may vary from platform to platform. The SCN may be handled by the Distributed Lock Manager, by the Lamport SCN scheme, or by using a hardware clock or dedicated SCN server. We will discuss Lamport SCN Generation in coming discussions.
Read more of this post

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Pierre blog

Pierre Forstmann Oracle Database blog


Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.


Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music