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.

----------------------------------------------------
Transaction 1
----------------------------------------------------

SQL> update test set SAL=101 where Name='AMIT';
1 row updated.

SQL> commit;
Commit complete.

SQL> alter system checkpoint;
System altered.

SQL> alter system dump datafile 1 block 86505;
System altered.

----------------------------------------------------
Transaction 2
----------------------------------------------------

SQL> update test set SAL=201 where Name='AMIT';
1 row updated.

SQL> update test set SAL=SAL+20 where Name='AMIT';
1 row updated.

SQL> commit;
Commit complete.

SQL> alter system checkpoint;
System altered.

SQL> alter system dump datafile 1 block 86505;
System altered.

We got dump of block 86505 under udump (diagnostic dest for DB). Direct way to check this is to grep two lines after “buffer tsn”. Let’s check the dump for string “buffer tsn”

[oracle@maskmt11g.lgk.nmk] grep -A3 'buffer tsn' mask11g_ora_11131.trc

buffer tsn: 0 rdba: 0x004151e9 (1/86505)
scn: 0x0000.001758ba seq: 0x02 flg: 0x06 tail: 0x58ba0602
frmt: 0x02 chkval: 0xd135 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

--

buffer tsn: 0 rdba: 0x004151e9 (1/86505)
scn: 0x0000.001758d8 seq: 0x03 flg: 0x06 tail: 0x58d80603
frmt: 0x02 chkval: 0xd033 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

As we performed only one update in transaction 1 which means sequence with only one increment ( 0x01 + 1 = 0x02). While for transaction 2 we have done 2 update statements so sequence increased by 2 (0x01+2=0x03) .

 

Now we will perform transaction 3 to do 10 updates to see the sequence number in single transaction to verify if next SCN would have higher operation sequence number or not ?

----------------------------------------------------
Transaction 3
----------------------------------------------------

SQL>begin
for i in 1..10
loop
update TEST SET SAL=SAL+i;
end loop;
commit;
end;
/

SQL> alter system checkpoint;
System altered.

SQL> alter system dump datafile 1 block 86505;
System altered.

[oracle@maskmt11g.lgk.nmk] grep -A3 'buffer tsn' mask11g_ora_11326.trc

buffer tsn: 0 rdba: 0x004151e9 (1/86505)
scn: 0x0000.00175ce5 seq: 0x0b flg: 0x06 tail: 0x5ce5060b
frmt: 0x02 chkval: 0xfaa0 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

As we have performed 10 updates so we would expect (0x01 + 10 = 0x0b) as sequence.

Here are links to our other posts in SCN series

System Change Number (SCN)
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

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 Block Sequence Number & SCN

  1. Pingback: System Change Number (SCN) | Amit Saraswat

  2. Pingback: Checkpoint & SCN | Amit Saraswat

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

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

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

  6. Pingback: Read Consistency & 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: