Have you ever checked the value of the SCN number – System Change Number in your databases?

Were you surprised to find out that some databases had very close SCN numbers?

The following story is real! I am not making this up!

I had to refresh three databases, which are related in a sense that the application that runs on them needs all three databases. These databases are refreshed together, to the same date and time. I was getting the SCN number associated with a specific date and time, the point in time I had to run the refresh.

I was issuing the command to get the SCN number for all three databases (if you don’t know how, check out last week’s post).

When I got the SCN numbers, I couldn’t believe my eyes! The numbers were so close to each other, a few hundreds or thousand difference.

I even engaged one of my fellow DBA to have a look, and confirm I am not imagining things. For real the three SCN numbers were almost identical.

Out of the three databases, one of them was very transactional, the other two not so much. Based on what we learned last week about the SCN, I would have expected the SCN numbers to be miles apart!

So what has happened?

SCN numbers get synchronized when a database link connection is established between two databases. The purpose of this is to maintain the distributed transaction read consistency. The lower SCN number is incremented to the higher one.

What does this mean in easy to understand words?

Let’s pretend we have database A with the SCN number 1,000, and database B with SCN number 1,000,000.
If I connect from database A to database B through a database link, the SCN number of database A will be increased from 1,000 to 1,000,000 (approximately, whatever the current SCN is at the time the transaction ends).

Going back to the three databases from my example above, I know for sure they are using database links extensively between them. This then explains why the three SCN numbers are so close to each other.

To better understand this phenomenon and to actually remember it in the future, let’s look at an example:

 
Step 1. Get the SCN number in database TEST1 and database TEST2:


21:35:12 TEST1 (SYS) >select CURRENT_SCN from v$database;

           CURRENT_SCN
----------------------
          304107271343  --> the difference between the 2 numbers is about 37K


21:35:17 TEST2 (SYS) >select CURRENT_SCN from v$database;

           CURRENT_SCN
----------------------
          304107234103


Step2. Connect through a database link from TEST2 to TEST1

21:38:52 TEST2 (SYS) >select * from dual@test1.world;

D
-
X


Step3. Confirm SCN numbers are indeed synchronizing


21:39:33 TEST1 (SYS) >select CURRENT_SCN from v$database;

          CURRENT_SCN
---------------------
         304107272826  --> the difference between the 2 numbers is about 81


21:40:20 TEST2 (SYS) >select CURRENT_SCN from v$database;

         CURRENT_SCN
---------------------
         304107272745

The above behaviour was tested in 11.2.0.4, however it applies to newer versions as well.

If your databases are communicating with each other through database links, then most likely the SCN numbers are very close to each other.

Just for fun, check your environment for SCN numbers, you might be surprised how close they are!