Everything you wanted to know about SCNs but was too afraid to ask

So I did some reading about SCN's from various sources, i.e. mostly forums, blogs, and Tom Kyte's site. And there is A LOT of information out there about SCN, much more than would be of any practical use. And I don't want to reinvent the wheel, a lot of other people have explored the topic and are exponentially much more knowledgeable than I am. So I tried to compile the pertinent points which I found were most significant to my understanding about SCN's

What is it?

An SCN or System Change Number is an internal logical sequence assigned to transactions, used to provide a sequence to transactions and used to satisfy recovery and ACID requirements. (Doc ID 1376995.1)

A sequence? So it's like a ticket number given to each transaction?

Not necessarilly. One way to think of it as an internal timestamp. There is a very good analogy by Hans Forbrich in this forum post, where he compares it to the security camera at the airport. It captures data from multiple feeds, but are all linked together by the same timestamp. Say an auditor wanted to know what happened in the departure lounge, the check-in counter, and the luggage claim at 2:51 PM. The camera at each location would be different transactions within the database, and the timestamp of 2:51 PM would be the SCN.

But, then that would mean the same SCN would be assigned to multiple transactions?

YES! An SCN is NOT unique to a single transaction. Transactions in different sessions that commit at the same time CAN have the same SCN! (Doc ID 1376995.1)

So going back, you mentioned it's like a clock. Does that mean it is consistently moving?

Technically, no. An action is needed to move the SCN from it's current value to the next. This can be a transaction OR a query. Jonathan Lewis mentions that there are 3 actions that can cause the SCN to move forward, but I can't find the exact documentation that states this.
On the other hand, in a normally operating database it is always moving since Oracle background processes are always doing transactions, even if there are no user transactions being done. So in a way, it is consistently moving, but because of operations that are being performed that move it forward.

But when I query CURRENT_SCN from V$DATABASE, it keeps on increasing!

This is one of the operations that increments the SCN. As answered by Jonathan Lewis in this thread, when querying V$DATABASE it triggers an increment to the SCN. Oracle calls kcmgas (Get and Advance SCN) and kcmgss (Get Snapshot SCN). Side note - kcm stands for Kernel Cache Miscellaneous functions. Additionally, he also mentions that the functions dbms_cdc_utility.get_current_scn and dbms_flashback.get_system_change_number do not increase the SCN since they only call kcmgss.

I did a small experiment to try to prove this:

I have a short query that shows the stats for the session doing a select select on CURRENT_SCN, and the session doing a dbms_flashback.get_system_change_number.

select case when t.sid = 49 then 'dbms_flashback'
    else 'current_scn'
    end "OPERATION"
 ,
n.name, t.value
from v$statname n, v$sesstat t
where t.sid in ('42','49')
and t.statistic# in ('163','164')
and t.statistic# = n.statistic#
group by t.sid, n.name, t.value
order by t.sid;

OPERATION      NAME                                                    VALUE
-------------- -------------------------------------------------- ----------
current_scn    calls to get snapshot scn: kcmgss                          23
current_scn    calls to kcmgas                                             2
dbms_flashback calls to get snapshot scn: kcmgss                         319
dbms_flashback calls to kcmgas                                             1

Then ran the dbms_flashback function:

SQL> select dbms_flashback.get_system_change_number
  2  from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1172005

And then ran the query again:

OPERATION      NAME                                                    VALUE
-------------- -------------------------------------------------- ----------
current_scn    calls to get snapshot scn: kcmgss                          23
current_scn    calls to kcmgas                                             2
dbms_flashback calls to get snapshot scn: kcmgss                         320
dbms_flashback calls to kcmgas                                             1

As you can see the kcmgss stat for the dbms_flashback session increased by 1 to 320, while the rest of the stats remain unchanged.

In another session I then selected current_scn from v$database:

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1172028

And then ran the query again;

OPERATION      NAME                                                    VALUE
-------------- -------------------------------------------------- ----------
current_scn    calls to get snapshot scn: kcmgss                          24
current_scn    calls to kcmgas                                             3
dbms_flashback calls to get snapshot scn: kcmgss                         320
dbms_flashback calls to kcmgas                                             1    

Reading about the SCN has pointed me in reading more about ITLs, which I had been putting off for a while. But for now it looks like a good topic for my next post.

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.