Infrastructure at your Service

Oracle Team

SCN synchronization in distributed transactions

By Franck Pachot

.
You have lot of db links between two databases, you think they are not used but want to be sure. It’s not difficult. When doing distributed transactions (which means running a transaction that involves remote databases through db links) the SCN of the database is synchronized. Comparing them can help. But let’s explain that.

SCN synchronization

Why is SCN synchronized? The SCN (System Change number) is there to identify a specific point-in-time for the version of the data. It is incremented at each commit so that it can identify exactly a version of your data. Oracle does not use a timestamp for several reasons. First, because you can have very frequent commits, so the timestamp precision may not identify those changes. And timestamp is subject to system time changes (daylight saving for example).
When you restore your database to a specific point in time, you restore and recover it to get the data at a specific SCN, for the whole database, so that everything is consistent. But if your application is doing changes in several databases you should consider all databases. If you restore one you must restore the others to the same point-in-time.
If you do that from the application using different connections, then you have to manage the synchronization, or accept inconsistencies.
If you do that – distributed transactions – from the same session using db links, then Oracle manages that itself: it synchronizes the SCN of the databases at each commit by advancing the smallest ones to the highest one.

example

I have two databases. the local one (called CDB) has a database link (called REMOTEDB) to the remote one (called SLOB – don’t ask why).
Here is the setup in local database:

07:31:03 SQL> connect demo/[email protected]//dbi-cdb/pdb1.dbitrial1.oraclecloud.internal
Connected.
07:31:04 SQL> create table T1 as select * from dual;
Table created.
07:31:04 SQL> create database link REMOTEDB connect to demo identified by demo using '//dbi-cdb/SLOB';
Database link created.
07:31:04 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
13044181

and in remote

07:31:04 SQL> connect demo/[email protected]//dbi-cdb/SLOB
Connected.
07:31:06 SQL> drop table T2;
Table dropped.
07:31:10 SQL> create table T2 as select * from dual;
Table created.
07:31:12 SQL> select current_scn from v$database;
CURRENT_SCN
-----------
6479885

Two databases with very different SCN (they had their own life). Table T2 in remote, table T2 in local, and a db link REMOTEDB to remote.

I check the SCN and current timestamp from the local database:

07:31:12 SQL> connect demo/[email protected]//dbi-cdb/SLOB
Connected.
07:31:13 SQL> column GET_SYSTEM_CHANGE_NUMBER new_value remote_scn
07:31:13 SQL> select instance_name,current_timestamp,dbms_flashback.get_system_change_number from v$instance;
 
INSTANCE_NAME CURRENT_TIMESTAMP GET_SYSTEM_CHANGE_NUMBER
------------- ---------------------------------------- ------------------------
SLOB 27-AUG-15 07.31.13.432962 AM +00:00 6480023

I check the same in the remote database:

07:31:13 SQL> connect demo/[email protected]//dbi-cdb/pdb1.dbitrial1.oraclecloud.internal
Connected.
07:31:13 SQL> column GET_SYSTEM_CHANGE_NUMBER new_value local_scn
07:31:13 SQL> select instance_name,current_timestamp,dbms_flashback.get_system_change_number from v$instance;
 
INSTANCE_NAME CURRENT_TIMESTAMP GET_SYSTEM_CHANGE_NUMBER
------------- ---------------------------------------- ------------------------
CDB 27-AUG-15 07.31.13.879165 AM +00:00 13045154

If I want to know which timestamp the local SCN coressponds to in the remote database, it’s a totally different time:

07:31:13 SQL> select instance_name,scn_to_timestamp(&remote_scn) from v$instance;
old 1: select instance_name,scn_to_timestamp(&remote_scn) from v$instance
new 1: select instance_name,scn_to_timestamp( 6480023) from v$instance
 
INSTANCE_NAME SCN_TO_TIMESTAMP(6480023)
------------- ---------------------------------------------------------------------------
CDB 26-AUG-15 09.27.41.000000000 PM

Not so far here because I tested that the day before. You may also get a ‘ORA-08181: specified number is not a valid system change number’ because the SCN for one database has no meaning in the other one.

Distributed transaction

I’ll do a transaction involving local and remote database

07:31:17 SQL> select * from T1;
 
D
-
X
 
07:31:17 SQL> select * from [email protected];
 
D
-
X
 
07:31:17 SQL> commit;
Commit complete.

Do you think it’s not a transaction because it’s only select? Well, that’s true and false. There is no changes, so no need for a transaction. But if we do some DML later we will need a transaction. In the local session, the transaction starts there. But in the remote session, a transaction is started from the first remote access even if it is a select.

SCN

Now let’s check the SCNs in the same way as before.

07:31:17 SQL> connect demo/[email protected]//dbi-cdb/SLOB
Connected.
07:31:18 SQL> column GET_SYSTEM_CHANGE_NUMBER new_value remote_scn
07:31:18 SQL> select instance_name,current_timestamp,dbms_flashback.get_system_change_number from v$instance;
 
INSTANCE_NAME CURRENT_TIMESTAMP GET_SYSTEM_CHANGE_NUMBER
------------- ---------------------------------------- ------------------------
SLOB 27-AUG-15 07.31.18.378737 AM +00:00 13045598
 
07:31:18 SQL> connect demo/[email protected]//dbi-cdb/pdb1.dbitrial1.oraclecloud.internal
Connected.
07:31:18 SQL> column GET_SYSTEM_CHANGE_NUMBER new_value local_scn
07:31:18 SQL> select instance_name,current_timestamp,dbms_flashback.get_system_change_number from v$instance;
 
INSTANCE_NAME CURRENT_TIMESTAMP GET_SYSTEM_CHANGE_NUMBER
------------- ---------------------------------------- ------------------------
CDB 27-AUG-15 07.31.18.794605 AM +00:00 13045600

The SCN numbers are now very close. Let’s see the meaning of the local SCN in the remote database:

07:31:18 SQL> select instance_name,scn_to_timestamp(&remote_scn) from v$instance;
old 1: select instance_name,scn_to_timestamp(&remote_scn) from v$instance
new 1: select instance_name,scn_to_timestamp( 13045598) from v$instance
 
INSTANCE_NAME SCN_TO_TIMESTAMP(13045598)
------------- ---------------------------------------------------------------------------
CDB 27-AUG-15 07:31:16.000000000 AM

The remote SCN (13045598) at 07.31.18 corresponds to 07.31.16 in the local database.
You should remember that there can be a 3 seconds gap when using SCN_TO_TIMESTAMP because not all SCN are recorded in SMON_SCN_TIME table.
If we do the opposite check in the remote database:

SQL> select instance_name,scn_to_timestamp(&remote_scn) from v$instance;
 
INSTANCE_NAME SCN_TO_TIMESTAMP(13045600)
------------- ---------------------------------------------------------------------------
SLOB 27-AUG-15 07.31.18.000000000 AM

The local SCN (13045600) at 07:31:18 corresponds to the same timestamp in the remote database.

So what?

There are several reasons to know that behavior.

First, its a good way to verify if databases that are linked by db links are actually using them or not. If the SCN gap is huge (in the number, and the timestamp correspondence in the other database), then you can be confident that there were no distributed transactions for a long time.

Second, it’s good to know that the synchronization happens even when you commit a transaction that has not changed anything. When you are sure that your transaction will read only, the it’s better to set the transaction read only.

Third, that synchronization is a good thing: you can do a point-in-time restore or duplicate of several databases and get a consistent state for the whole. You should use distributed transactions (for example pl/sql call through db link) when one database has to synchronize with an other one.

But there are cases where you don’t want that synchronization. In the past, I had to check if an electronic voting machine keeps privacy of votes or not. You have to store the vote choice, and the voting people in different databases, so that it’s impossible to mach a vote with a name. But if you do that with distributed transaction from oracle then, because of the SCN synchronization, it’s easy to use log miner and join the redo from both databases.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team