{"id":2091,"date":"2022-04-07T12:59:37","date_gmt":"2022-04-07T10:59:37","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=2091"},"modified":"2022-04-07T13:00:20","modified_gmt":"2022-04-07T11:00:20","slug":"far-sync-alternate-sync","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/far-sync-alternate-sync\/","title":{"rendered":"Can a physical standby database receive the redo SYNC if the Far Sync instance fails?"},"content":{"rendered":"

\"\"<\/a>The answer is YES<\/strong>.<\/p>\n

In the following configuration, cdgsima_lhr1pq<\/strong> (primary) sends synchronously<\/strong> to cdgsima_farsync1<\/strong> (far sync), which forwards the redo stream asynchronously<\/strong> to cdgsima_lhr1bm<\/strong> (physical standby):<\/p>\n

DGMGRL> show configuration verbose\r\n\r\nConfiguration - cdgsima\r\n\r\n  Protection Mode: MaxPerformance\r\n  Members:\r\n  cdgsima_lhr1pq   - Primary database\r\n    cdgsima_farsync1 - Far sync instance\r\n      cdgsima_lhr1bm   - Physical standby database\r\n    cdgsima_lhr1bm   - Physical standby database (alternate of cdgsima_farsync1)\r\n\r\n  Members Not Receiving Redo:\r\n  cdgsima_farsync2 - Far sync instance\r\n<\/pre>\n
But if cdgsima_farsync1<\/strong> is not available, I want the primary to send synchronously to the physical standby database<\/strong>. I accept a performance penalty, but I do not want to compromise my data protection.<\/p>\n
I just need to set up the Redoroutes as follows:<\/p>\n
-- when primary is cdgsima_lhr1pq \r\nEDIT DATABASE 'cdgsima_lhr1pq' SET PROPERTY 'RedoRoutes' = '(LOCAL : (cdgsima_farsync1 SYNC PRIORITY=1, cdgsima_lhr1bm SYNC PRIORITY=2 ))';\r\nEDIT FAR_SYNC 'cdgsima_farsync1' SET PROPERTY 'RedoRoutes' = '(cdgsima_lhr1pq : cdgsima_lhr1bm ASYNC)';\r\n\r\n-- when primary is cdgsima_lhr1bm\r\nEDIT DATABASE 'cdgsima_lhr1bm' SET PROPERTY 'RedoRoutes' = '(LOCAL : (cdgsima_farsync2 SYNC PRIORITY=1, cdgsima_lhr1pq SYNC PRIORITY=2 ))';\r\nEDIT FAR_SYNC 'cdgsima_farsync2' SET PROPERTY 'RedoRoutes' = '(cdgsima_lhr1bm : cdgsima_lhr1pq ASYNC)';<\/pre>\n
This is defined the second part of the RedoRoutes rules:<\/p>\n
cdgsima_lhr1bm SYNC PRIORITY=2<\/pre>\n
Let’s test. If I shutdown abort the farsync instance:<\/p>\n
$ rlwrap sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 26 10:55:31 2022\r\nVersion 19.13.0.0.0\r\n\r\nCopyright (c) 1982, 2021, Oracle.  All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production\r\nVersion 19.13.0.0.0\r\n\r\nSQL> shutdown abort\r\nORACLE instance shut down.\r\nSQL> \r\n<\/pre>\n
I can see the new SYNC destination being open almost instantaneously (because the old destination fails immediately with ORA-03113):<\/p>\n
2022-03-26T10:55:35.581460+00:00\r\nLGWR (PID:42101): Attempting LAD:2 network reconnect (3113)\r\nLGWR (PID:42101): LAD:2 network reconnect abandoned\r\n2022-03-26T10:55:35.602542+00:00\r\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/cdgsima_lhr1pq\/cdgsima\/trace\/cdgsima_lgwr_42101.trc:\r\nORA-03113: end-of-file on communication channel\r\nLGWR (PID:42101): Error 3113 for LNO:3 to 'dgsima1.dbdgsima.misclabs.oraclevcn.com:1521\/cdgsima_farsync1.dbdgsima.misclabs.oraclevcn.com'\r\n2022-03-26T10:55:35.608691+00:00\r\nLGWR (PID:42101): LAD:2 is UNSYNCHRONIZED\r\n2022-03-26T10:55:36.610098+00:00\r\nLGWR (PID:42101): Failed to archive LNO:3 T-1.S-141, error=3113\r\nLGWR (PID:42101): Error 1041 disconnecting from LAD:2 standby host 'dgsima1.dbdgsima.misclabs.oraclevcn.com:1521\/cdgsima_farsync1.dbdgsima.misclabs.oraclevcn.com'\r\n2022-03-26T10:55:37.143448+00:00\r\nLGWR (PID:42101): LAD:3 is UNSYNCHRONIZED\r\n2022-03-26T10:55:37.143569+00:00\r\nLGWR (PID:42101): LAD:2 no longer supports SYNCHRONIZATION\r\nStarting background process NSS3\r\n2022-03-26T10:55:37.227954+00:00\r\nNSS3 started with pid=38, OS id=78251\r\n2022-03-26T10:55:40.733905+00:00\r\nThread 1 advanced to log sequence 142 (LGWR switch),  current SCN: 8068734\r\n  Current log# 1 seq# 142 mem# 0: \/u03\/app\/oracle\/redo\/CDGSIMA_LHR1PQ\/onlinelog\/o1_mf_1_k251hfvk_.log\r\n2022-03-26T10:55:40.781499+00:00\r\nARC0 (PID:42266): Archived Log entry 220 added for T-1.S-141 ID 0x9eb046ef LAD:1\r\n2022-03-26T10:55:41.606175+00:00\r\nALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=MEMORY SID='*';\r\n2022-03-26T10:55:43.747483+00:00\r\nLGWR (PID:42101): LAD:3 is SYNCHRONIZED\r\n2022-03-26T10:55:43.816978+00:00\r\nThread 1 advanced to log sequence 143 (LGWR switch),  current SCN: 8068743\r\n  Current log# 2 seq# 143 mem# 0: \/u03\/app\/oracle\/redo\/CDGSIMA_LHR1PQ\/onlinelog\/o1_mf_2_k251hfwz_.log\r\n<\/pre>\n
Indeed, I can see the new NSS process (synchronous redo transport) spawn at that time:<\/p>\n
SQL> r\r\n  1  select NAME\r\n  2  ,PID\r\n  3  ,TYPE\r\n  4  ,ROLE ACTION\r\n  5  ,CLIENT_PID\r\n  6  ,CLIENT_ROLE\r\n  7  ,GROUP#\r\n  8  ,RESETLOG_ID\r\n  9  ,THREAD#\r\n 10  ,SEQUENCE#\r\n 11  ,BLOCK#\r\n 12* from v$dataguard_process where name like 'NSS%'\r\n\r\nNAME  PID                      TYP ACTION                   CLIENT_PID CLIENT_ROLE          GROUP# RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#\r\n----- ------------------------ --- ------------------------ ---------- ---------------- ---------- ----------- ---------- ---------- ----------\r\nNSS2  54961                    KSB sync                              0 none                      0           0          0          0          0\r\nNSS3  78251                    KSB sync                              0 none                      0           0          0          0          0\r\n\r\nSQL> !ps -eaf | grep ora_nss\r\noracle   54961     1  0 Mar10 ?        00:00:55 ora_nss2_cdgsima\r\noracle   78251     1  0 10:55 ?        00:00:00 ora_nss3_cdgsima\r\n<\/pre>\n
—<\/p>\n
Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"
The answer is YES. In the following configuration, cdgsima_lhr1pq (primary) sends synchronously to cdgsima_farsync1 (far sync), which forwards the redo stream asynchronously to cdgsima_lhr1bm (physical standby): DGMGRL> show configuration verbose Configuration – cdgsima Protection Mode: MaxPerformance Members: cdgsima_lhr1pq – Primary … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[329],"tags":[202,292,351,352],"class_list":["post-2091","post","type-post","status-publish","format-standard","hentry","category-oracle-dg","tag-active-data-guard","tag-data-guard","tag-far-sync","tag-redoroutes"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/2091","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=2091"}],"version-history":[{"count":2,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/2091\/revisions"}],"predecessor-version":[{"id":2095,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/2091\/revisions\/2095"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=2091"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=2091"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=2091"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}