.
\nYou 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.
\n<\/p>\n
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).
\nWhen 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.
\nIf you do that from the application using different connections, then you have to manage the synchronization, or accept inconsistencies.
\nIf 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.<\/p>\n
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).
\nHere is the setup in local database:<\/p>\n
\n07:31:03 SQL> connect demo\/demo@\/\/dbi-cdb\/pdb1.dbitrial1.oraclecloud.internal\nConnected.\n07:31:04 SQL> create table T1 as select * from dual;\nTable created.\n07:31:04 SQL> create database link REMOTEDB connect to demo identified by demo using '\/\/dbi-cdb\/SLOB';\nDatabase link created.\n07:31:04 SQL> select current_scn from v$database;\nCURRENT_SCN\n-----------\n 13044181\n<\/code><\/pre>\nand in remote<\/p>\n
\n07:31:04 SQL> connect demo\/demo@\/\/dbi-cdb\/SLOB\nConnected.\n07:31:06 SQL> drop table T2;\nTable dropped.\n07:31:10 SQL> create table T2 as select * from dual;\nTable created.\n07:31:12 SQL> select current_scn from v$database;\nCURRENT_SCN\n-----------\n 6479885\n<\/code><\/pre>\nTwo 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.<\/p>\n
I check the SCN and current timestamp from the local database:<\/p>\n
\n07:31:12 SQL> connect demo\/demo@\/\/dbi-cdb\/SLOB\nConnected.\n07:31:13 SQL> column GET_SYSTEM_CHANGE_NUMBER new_value remote_scn\n07:31:13 SQL> select instance_name,current_timestamp,dbms_flashback.get_system_change_number from v$instance;\n \nINSTANCE_NAME CURRENT_TIMESTAMP GET_SYSTEM_CHANGE_NUMBER\n------------- ---------------------------------------- ------------------------\nSLOB 27-AUG-15 07.31.13.432962 AM +00:00 6480023\n<\/code><\/pre>\nI check the same in the remote database:<\/p>\n
\n07:31:13 SQL> connect demo\/demo@\/\/dbi-cdb\/pdb1.dbitrial1.oraclecloud.internal\nConnected.\n07:31:13 SQL> column GET_SYSTEM_CHANGE_NUMBER new_value local_scn\n07:31:13 SQL> select instance_name,current_timestamp,dbms_flashback.get_system_change_number from v$instance;\n \nINSTANCE_NAME CURRENT_TIMESTAMP GET_SYSTEM_CHANGE_NUMBER\n------------- ---------------------------------------- ------------------------\nCDB 27-AUG-15 07.31.13.879165 AM +00:00 13045154\n<\/code><\/pre>\nIf I want to know which timestamp the local SCN coressponds to in the remote database, it’s a totally different time:<\/p>\n
\n07:31:13 SQL> select instance_name,scn_to_timestamp(&remote_scn) from v$instance;\nold 1: select instance_name,scn_to_timestamp(&remote_scn) from v$instance\nnew 1: select instance_name,scn_to_timestamp( 6480023) from v$instance\n \nINSTANCE_NAME SCN_TO_TIMESTAMP(6480023)\n------------- ---------------------------------------------------------------------------\nCDB 26-AUG-15 09.27.41.000000000 PM\n<\/code><\/pre>\nNot 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.<\/p>\n
Distributed transaction<\/h1>\n
I’ll do a transaction involving local and remote database<\/p>\n
\n07:31:17 SQL> select * from T1;\n \nD\n-\nX\n \n07:31:17 SQL> select * from T2@REMOTEDB;\n \nD\n-\nX\n \n07:31:17 SQL> commit;\nCommit complete.\n<\/code><\/pre>\nDo 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. <\/p>\n
SCN<\/h1>\n
Now let’s check the SCNs in the same way as before.<\/p>\n
\n07:31:17 SQL> connect demo\/demo@\/\/dbi-cdb\/SLOB\nConnected.\n07:31:18 SQL> column GET_SYSTEM_CHANGE_NUMBER new_value remote_scn\n07:31:18 SQL> select instance_name,current_timestamp,dbms_flashback.get_system_change_number from v$instance;\n \nINSTANCE_NAME CURRENT_TIMESTAMP GET_SYSTEM_CHANGE_NUMBER\n------------- ---------------------------------------- ------------------------\nSLOB 27-AUG-15 07.31.18.378737 AM +00:00 13045598\n \n07:31:18 SQL> connect demo\/demo@\/\/dbi-cdb\/pdb1.dbitrial1.oraclecloud.internal\nConnected.\n07:31:18 SQL> column GET_SYSTEM_CHANGE_NUMBER new_value local_scn\n07:31:18 SQL> select instance_name,current_timestamp,dbms_flashback.get_system_change_number from v$instance;\n \nINSTANCE_NAME CURRENT_TIMESTAMP GET_SYSTEM_CHANGE_NUMBER\n------------- ---------------------------------------- ------------------------\nCDB 27-AUG-15 07.31.18.794605 AM +00:00 13045600\n<\/code><\/pre>\nThe SCN numbers are now very close. Let’s see the meaning of the local SCN in the remote database:<\/p>\n
\n07:31:18 SQL> select instance_name,scn_to_timestamp(&remote_scn) from v$instance;\nold 1: select instance_name,scn_to_timestamp(&remote_scn) from v$instance\nnew 1: select instance_name,scn_to_timestamp( 13045598) from v$instance\n \nINSTANCE_NAME SCN_TO_TIMESTAMP(13045598)\n------------- ---------------------------------------------------------------------------\nCDB 27-AUG-15 07:31:16.000000000 AM\n<\/code><\/pre>\nThe remote SCN (13045598) at 07.31.18 corresponds to 07.31.16 in the local database.
\nYou 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.
\nIf we do the opposite check in the remote database:<\/p>\n
\nSQL> select instance_name,scn_to_timestamp(&remote_scn) from v$instance;\n \nINSTANCE_NAME SCN_TO_TIMESTAMP(13045600)\n------------- ---------------------------------------------------------------------------\nSLOB 27-AUG-15 07.31.18.000000000 AM\n<\/code><\/pre>\nThe local SCN (13045600) at 07:31:18 corresponds to the same timestamp in the remote database.<\/p>\n
So what?<\/h1>\n
There are several reasons to know that behavior.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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. <\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[619,620,96,621],"type_dbi":[],"class_list":["post-5298","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-db-link","tag-distributed-transaction","tag-oracle","tag-scn_to_timestamp"],"acf":[],"yoast_head":"\n
SCN synchronization in distributed transactions - dbi Blog<\/title>\n<meta name=\"description\" content=\"SCN synchronisation in distributed transactions can be used to check db link usage\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SCN synchronization in distributed transactions\" \/>\n<meta property=\"og:description\" content=\"SCN synchronisation in distributed transactions can be used to check db link usage\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-08-27T15:47:52+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"SCN synchronization in distributed transactions\",\"datePublished\":\"2015-08-27T15:47:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/\"},\"wordCount\":817,\"commentCount\":0,\"keywords\":[\"db link\",\"distributed transaction\",\"Oracle\",\"scn_to_timestamp\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/\",\"name\":\"SCN synchronization in distributed transactions - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-08-27T15:47:52+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"SCN synchronisation in distributed transactions can be used to check db link usage\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SCN synchronization in distributed transactions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SCN synchronization in distributed transactions - dbi Blog","description":"SCN synchronisation in distributed transactions can be used to check db link usage","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/","og_locale":"en_US","og_type":"article","og_title":"SCN synchronization in distributed transactions","og_description":"SCN synchronisation in distributed transactions can be used to check db link usage","og_url":"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/","og_site_name":"dbi Blog","article_published_time":"2015-08-27T15:47:52+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"SCN synchronization in distributed transactions","datePublished":"2015-08-27T15:47:52+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/"},"wordCount":817,"commentCount":0,"keywords":["db link","distributed transaction","Oracle","scn_to_timestamp"],"articleSection":["Database Administration & Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/","url":"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/","name":"SCN synchronization in distributed transactions - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-08-27T15:47:52+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"SCN synchronisation in distributed transactions can be used to check db link usage","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/scn-synchronization-in-distributed-transactions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SCN synchronization in distributed transactions"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5298"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5298\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5298"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5298"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}