SCNs

I started working with Oracle database in late 2006. It's 2014 now, which is roughly 7 and a half years later, and I am still finding things about Oracle that have totally demolished any pre-conceived notions that I have had for the entirety of my career.

Deeper Down the Rabbit Hole

Yesterday I decided to setup a Logical Standby, for the simple reason that I would soon be put in charge of managing one and I have never done so before. The initial step would be to create a physical standby, and I doing this, running commands my fingers haven't typed in over a year, was a really happy moment for me. But I digress.

Once I had setup my physical standby, I was doing checking and monitoring to ensure that the standby was being updated. And I remember a question a client had put to me before, how do they know at the most granular level that the standby is indeed being updated?

I explained the whole theory behind archivelog sequence numbers, SCNs and the like, which seemed to satisfy them. But thinking about it now, I never really satisfied my own quest for that answer.

That led me down the path of investigating of how to check at the most granular level, to see follow the trail of a transaction as it was committed on the primary, and transferred and applied on the standby. How, and could I track this through the SCN of the transaction? Which again, somehow led me to read about SCNs and what they are (and what they are not!).

I had always believed that the SCN of the database was a counter, a record for each transaction, which would only get incremented at the commit of each transaction. Shown in table form, this is how I pictured it in my mind:

Txn num SCN 

------- ------------- 

 1      99183 

 2      99184 

 3      99185 

 4      99186 

 5      99187

Makes sense right? But how come when we query CURRENT_SCN from v$database it keeps on increasing, even if there is nothing going on in the database? 

SQL> select current_scn from v$database; 

 CURRENT_SCN 

 ----------- 

 994651 

 SQL> / 

 CURRENT_SCN 

 ----------- 

 994669 

 SQL> / 

 CURRENT_SCN 

 ----------- 

 994671

My answer to this to satisfy clients (and to somewhat my degree myself) was that there was always some Oracle background processes doing work, so for each transaction they do, the SCN increases.

Then I stumbled upon this Ask Tom thread, which mentioned that to think of the SCN more of a clock. Always moving, and gets assigned to transaction when they are committed. 

Txn num SCN 

------- ------------- 

 1      99183 

 2      99188 

 3      99192 

 4      99193 

 5      99201 

So depending on when a transaction is committed, that would determine the SCN that is assigned to your transaction. This explanation BLEW MY MIND. It totally decimated whatever I had learnt and thought about the SCN for the PAST SEVEN YEARS. This also explains why the SCN in v$database keeps increasing right? I was so elated about this fact when I found out yesterday afternoon. I come into work this morning, and keep reading, and lo and behold, THIS IS NOT CORRECT.

Yes, the SCN can be thought of a counter. And yes, it is incremented by transaction that occur within the database. But the reason as to why the value of CURRENT_SCN keeps increasing is because THE SCN IS INCREASED WHENEVER WE QUERY THAT VIEW. There is truly nothing left of my mind as it has been continuously been blown apart by all these revelations, which I should've known about SIX OR SO YEARS AGO.

So my entire day has been dedicated to trying to find out as much as I could about the SCN. I'll try and collate everything in the posts to follow.