SCN Synchronization when using a Database Link

 In my previous blog post about querying across a Database Link, I had stated "when running a query (just a SELECT statement) requires the two databases to synchronise SCN.  Whichever is the database with the lower SCN has to increment its SCN to the other database's (higher) SCN "

Here is a quick demonstration :

-- First I verify the current SCN in two databases "ORCLCDB" and "CDB2"
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB   07-SEP-22 10.10.49.643026 PM +08:00     19617903

ORCLCDB SQL>

NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
CDB2      07-SEP-22 10.10.52.378969 PM +08:00     18562711

CDB2 SQL>
-- there is a wide gap between the two -- with ORCLCDB being ahead by approximately 1million
============================================================================================



-- now I connect a client to a PDB in ORCLCDB
-- and run a SELECT query that queries across a DBLink a table in a PDB in CDB2
-- this query is NOT a user transaction, only a SELECT statement
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> set time on
22:11:33 SQL>
22:11:48 SQL> select count(*) from tmp_data@pdbtmp_data;

  COUNT(*)
----------
     73181

22:12:05 SQL>


-- Now I check the SCNs in the two databases
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB   07-SEP-22 10.12.19.955477 PM +08:00     19620089

ORCLCDB SQL>

CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
CDB2      07-SEP-22 10.12.23.499409 PM +08:00     19620089

CDB2 SQL>
-- I can see that CDB2's SCN has "mysteriously" caught up with ORCLCDB 
-- in fact they are exactly the same
-- in the real world there would still be some difference by the time I query again
-- because other transactions in either or both the databases would have incremented the SCN(s)
================================================================================================



-- now I disconnect from the client
-- this *closes* the database link
22:12:58 SQL> disconnect
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

-- and reconnect to the client
22:13:04 SQL> connect hemant/hemant@orclpdb1
Connected.
22:13:14 SQL>

-- I compare the SCNs in the two databases
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB   07-SEP-22 10.13.22.738662 PM +08:00     19620223

ORCLCDB SQL>

CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
CDB2      07-SEP-22 10.13.30.874874 PM +08:00     19620229

CDB2 SQL>
-- the gap is very small
-- in fact some internal operations in CDB2 have slightly incremented it ahead
===============================================================================



-- I run a PLSQL procedure in my client that does 1million COMMITs in the PDB
-- to increase the SCN only in ORCLCDB
22:14:06 SQL> execute onemillioncommits;

PL/SQL procedure successfully completed.

22:15:03 SQL>
22:15:07 SQL>


-- I now compare the two SCNs
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB   07-SEP-22 10.15.21.713518 PM +08:00     20654338

ORCLCDB SQL>
CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
CDB2      07-SEP-22 10.15.23.779855 PM +08:00     19620296

CDB2 SQL>
-- and find that there is again a huge gap between the two databases, with ORCLCDB ahead again
==============================================================================================



-- I query across the Database Link again from my client session
22:15:46 SQL>  select count(*) from tmp_data@pdbtmp_data;

  COUNT(*)
----------
     73181

22:15:50 SQL>


-- and now compare the two SCNs
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;
NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB   07-SEP-22 10.16.02.362146 PM +08:00     20654373

ORCLCDB SQL>
CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME      SYSTIMESTAMP                         CURRENT_SCN
--------- ------------------------------------ -----------
CDB2      07-SEP-22 10.16.14.122201 PM +08:00     20654453

CDB2 SQL>
-- now the gap is very small
-- some other operations in ORCLCDB have already incremented the SCN after the query
====================================================================================

So, simply running a SELECT statement (not even a User-Initiated COMMIT causes the two databases to synchronise SCNs.  The database with the lower SCN "inherits" the higher SCN.

So, the next time you see database SCNs mysteriously jump (e.g. in V$DATABASE.CURRENT_SCN  or in V$ARCHIVED_LOG.FIRST_CHANGE#  or in V$DATAFILE.CHECKPOINT_SCN) very significantly, check to see if you have queries to/from the database that use one or more Database Links.