Block Sequence Number & SCN

In addition to SCNs, Block Sequence Numbers which are used to distinguish updates within a block and same SCN. When a block is updated, Oracle records the current SCN in the block. In addition, Oracle also records a sequence number for the change i.e. an SCN+Sequence number identifies a change within a block. The basic idea is to store the current redo generating SCN in the cache header when making a change to a block. Since multiple changes to a block can be made at the same SCN, a sequence number is also stored in the cache header to differentiate between different changes at the same SCN. The sequence number is increased each time a change is made at the same SCN. The sequence number is reset to 1 when making a change at a higher SCN than the SCN currently in the block. This allows multiple changes to the same block to have the same SCN (though they will have different sequence numbers). An SCN is stored in the Data Block, signifying the SCN at which the latest (most recent) change to the block took place.

 

Let’s explore the SCN and sequence at the block level. We have one table TEST with two columns Name varchar2(40) and CITY varchar2(20).

SQL> create table TEST (NAME Varchar2(20) , SAL number);
Table created.

SQL> insert into Test values ('AMIT',100);
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','TEST') FNO, DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) BLK, NAME,SAL FROM SYS.TEST ;

FNO               BLK                 NAME        SAL
---------- ---------- -------------------- ----------
1               86505                 AMIT        100


So we identified that table containing record where NAME=’AMIT’ in block number 86505 of file number (absolute) 1. Now we will try to perform some transactions followed by block dump to see the change in SCN and sequence number pair at block level.

Read Consistency & SCN

Read consistency is one of the prime conditions for RDBMS, under ACID property “I”, stand for isolation. The isolation property ensures that the concurrent execution of transactions result in a system state that would be obtained if transactions were executed serially, i.e. one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method, the effects of an incomplete transaction might not even be visible to another transaction.

Managing Read Consistency

Oracle Database automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). Oracle Database can also provide read consistency to all of the queries in a transaction (transaction-level read consistency). Oracle Database uses the information maintained in its rollback segments to provide these consistent views. The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transactions.

As a query enters the execution stage, the current system change number (SCN) is determined. As data blocks are read on behalf of the query, only blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query’s execution are not observed, guaranteeing that consistent data is returned for each query.

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

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:

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.