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.

SCN is used in many places i.e. data block, redo records, datafile headers, controlfile etc. Checkpoint is a data structure that indicates the checkpoint position which is the SCN in the redo stream where instance recovery must begin in case of instance failure. In RAC, each instance has its own SCN, but they are related as Lamport SCNs.

Oracle increments SCN in SGA and when a transaction modifies data, the database writes a new SCN to the undo data segment assigned to the transaction. The log writer process then writes the commit record of the transaction immediately to the online redo log. The commit record has the unique SCN of the transaction. Oracle Database also uses SCNs as part of its instance recovery and media recovery mechanism.

When user commits a transaction, transaction is assigned a SCN which Oracle records along with transaction’s redo entries in the redo record. This will guide recovery mechanism to be synchronized manner in RAC and distributed system.

The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop. Whenever user commits a transaction, the log writer process (LGWR) writes records from the log buffer area to current online redo log group on disk. LGWR also writes the transaction’s SCN to the online redo log file. The success of this atomic write event determines whether your transaction succeeds, and it requires a synchronous (wait-until-completed) write to disk. Commits require a synchronous write because they must be guaranteed at the time they occur.

Redo Log Records, Change Vectors; Undo

Changes are logged into log files as Redo Records. In order to perform an update, it is necessary to remember the old value (for rollback) as well as to install the new value. In the simplest case, this involves changes to two separate blocks (the block where the new value is stored, and the undo block where the old value is stored). Thus, a change to a value in the database results in at least two blocks being changed. Changes to individual blocks are described by change vectors. Each change vector contains information to execute the appropriate functions to produce the described changes on one block. It also contains an old SCN, representing the SCN for the block before this change was applied.

Each Redo Record represents an atomic set of changes. It consists of a single SCN for the set of changes, and a set of change vectors, one per changed block. A Redo Record is always applied in its entirety to ensure that the changes are applied atomically (i.e., all of them or none).

Note that when all the redo records at a given SCN have been applied, the database is in a physically consistent state. Rollback of the incomplete transactions is required in order to get the database to a logically consistent (i.e., no partial transactions) state. After redo recovery has been performed, the database can be opened for new transactions while rollback is happening in the background. Standard concurrency control techniques are used to resolve conflicts between the rollback and new transactions.

For transaction rollback purposes, Undo block records are also created. The 2nd phase of recovery goes through the Undo records (using the transaction tables) in order to rollback the changes of uncommitted transactions.

Here are links to our other posts in SCN series:

Checkpoint & SCN
Role of SCN in Instance Recovery & Media Recovery
SCN Structure & Generation Schemes
SCN on Scale of Time & SCN of a ROW
Read Consistency & SCN
Block Sequence Number & SCN

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of either the companies I have worked for or Oracle Corporation and its affiliates. The comments, views and opinions expressed by visitors on this blog are theirs alone and may not reflect mine. Whatever scenarios suggested under this blog were simulated only on demo environment, so it’s advisable to test those in test systems before pushing to your production environment.


Advertisements

6 Responses to System Change Number (SCN)

  1. Pingback: Checkpoint & SCN | Amit Saraswat

  2. Pingback: SCN Structure & Generation Schemes | Amit Saraswat

  3. Pingback: Role of SCN in Instance Recovery & Media Recovery | Amit Saraswat

  4. Pingback: SCN on Scale of Time & SCN of a ROW | Amit Saraswat

  5. Pingback: Read Consistency & SCN | Amit Saraswat

  6. Pingback: Block Sequence Number & SCN | Amit Saraswat

Share Your Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

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.

Frits Hoogland Weblog

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

OraStory

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

%d bloggers like this: