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.