{"id":555,"date":"2023-02-24T16:57:39","date_gmt":"2023-02-24T15:57:39","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/?p=555"},"modified":"2023-02-24T19:13:55","modified_gmt":"2023-02-24T18:13:55","slug":"testing-client-failover-with-data-guard-2-switchover","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2023\/02\/testing-client-failover-with-data-guard-2-switchover\/","title":{"rendered":"Testing client failover with Data Guard 2\/switchover"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">All posts in this series<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/ilmarkerm.eu\/blog\/2023\/02\/testing-client-failover-with-data-guard-fast-start-failover-1-setup\/\" data-type=\"URL\" data-id=\"https:\/\/ilmarkerm.eu\/blog\/2023\/02\/testing-client-failover-with-data-guard-fast-start-failover-1-setup\/\">Part 1: Setup<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/ilmarkerm.eu\/blog\/2023\/02\/testing-client-failover-with-data-guard-2-switchover\/\" data-type=\"URL\" data-id=\"https:\/\/ilmarkerm.eu\/blog\/2023\/02\/testing-client-failover-with-data-guard-2-switchover\/\">Part 2: Switchover<\/a><\/li>\n\n\n\n<li>Part 3: Failover<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Switchover<\/h2>\n\n\n\n<p>Planned maintenance, need to take primary server down for maintenance. The obvious Data Guard command for role change in this case is SWITCHOVER, that changes database roles in a nice coordinated way and no standby requires reinstatement (flashback) after the operation.<\/p>\n\n\n\n<p>Lets test it from the client perspective.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Scenario: Only local SYNC standby &#8211; no ADG<\/h2>\n\n\n\n<p>First I disable and remove failtesti3 instance (remote standby) from data guard configuration, so I only have two local database instances in the configuration, in SYNC mode. Physical standby is in MOUNT mode.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DGMGRL&gt; show configuration verbose;\n\nConfiguration - failtest\n\n  Protection Mode: MaxAvailability\n  Members:\n  failtesti1 - Primary database\n    failtesti2 - (*) Physical standby database \n\n  (*) Fast-Start Failover target\n\n  Properties:\n    FastStartFailoverThreshold      = '25'\n    OperationTimeout                = '30'\n    TraceLevel                      = 'USER'\n    FastStartFailoverLagLimit       = '30'\n    CommunicationTimeout            = '180'\n    ObserverReconnect               = '1800'\n    FastStartFailoverAutoReinstate  = 'TRUE'\n    FastStartFailoverPmyShutdown    = 'TRUE'\n    BystandersFollowRoleChange      = 'ALL'\n    ObserverOverride                = 'FALSE'\n    ExternalDestination1            = ''\n    ExternalDestination2            = ''\n    PrimaryLostWriteAction          = 'FAILOVER'\n    ConfigurationWideServiceName    = 'QKNPUXMZ_CFG'\n\nFast-Start Failover: Enabled in Zero Data Loss Mode\n  Lag Limit:          30 seconds (not in use)\n  Threshold:          25 seconds\n  Active Target:      failtesti2\n  Potential Targets:  \"failtesti2\"\n    failtesti2 valid\n  Observer:           failtest-observer\n  Shutdown Primary:   TRUE\n  Auto-reinstate:     TRUE\n  Observer Reconnect: 1800 seconds\n  Observer Override:  FALSE\n\nConfiguration Status:\nSUCCESS<\/pre>\n\n\n\n<p>To execute DGMGRL commands I log remotely in using username and password.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">dgmgrl \"c##dataguard\/sysdgpassword@connstr\" \"show configuration\"<\/pre>\n\n\n\n<p>Start swingbench and start the testing program.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ charbench ...\n$ python failtest.py switchover_local_only_mount<\/code><\/pre>\n\n\n\n<p>All working well, now execute switchover<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>date -Iseconds ; dgmgrl \"c##dataguard\/sysdgpassword@connstr\" \"switchover to failtesti2\" ; date -Iseconds\n\n2023-02-14T10:23:27+00:00\nDGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 7 09:29:43 2023\nVersion 19.16.2.0.0\n\nConnected to \"failtesti1\"\nConnected as SYSDG.\nPerforming switchover NOW, please wait...\nOperation requires a connection to database \"failtesti2\"\nConnecting ...\nConnected to \"failtesti2\"\nConnected as SYSDG.\nNew primary database \"failtesti2\" is opening...\nOperation requires start up of instance \"failtesti1\" on database \"failtesti1\"\nStarting instance \"failtesti1\"...\n\nPlease complete the following steps to finish switchover:\n        start up and mount instance \"failtesti1\" of database \"failtesti1\"\n\n2023-02-14T10:24:44+00:00<\/code><\/pre>\n\n\n\n<p>Alert.log from new primary failtesti2 (cleaned)<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">2023-02-14T10:23:35.264043+00:00\nALTER DATABASE SWITCHOVER TO PRIMARY (failtesti2)\n2023-02-14T10:23:36.544532+00:00\nBackground Media Recovery process shutdown (failtesti2)\n2023-02-14T10:23:39.430461+00:00\n rmi (PID:3337550): Database role cleared from PHYSICAL STANDBY [kcvs.c:1114]\nSwitchover: Complete - Database mounted as primary\n2023-02-14T10:23:41.542728+00:00\nALTER DATABASE OPEN\n2023-02-14T10:23:46.293995+00:00\nCompleted: ALTER DATABASE OPEN\nALTER PLUGGABLE DATABASE ALL OPEN\n2023-02-14T10:23:48.984619+00:00\nSOEFAIL(3):Started service soe\/soe\/soe.prod1.dbs\n2023-02-14T10:23:48.985369+00:00\nPluggable database SOEFAIL opened read write\nCompleted: ALTER PLUGGABLE DATABASE ALL OPEN<\/pre>\n\n\n\n<p>Alert.log from old primary failtesti1<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">2023-02-14T10:23:31.344766+00:00\nALTER DATABASE SWITCHOVER TO 'failtesti2'\n2023-02-14T10:23:32.055470+00:00\nALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 2418351] (failtesti1)\n2023-02-14T10:23:33.067341+00:00\nPrimary will check for some target standby to have received all redo\nRSM0 (PID:2418351): Waiting for target standby to apply all redo\n2023-02-14T10:23:39.432887+00:00\nRSM0 (PID:2418351): Switchover complete. Database shutdown required\nTMI: dbsdrv switchover to target END 2023-02-14 10:23:39.432896\nCompleted: ALTER DATABASE SWITCHOVER TO 'failtesti2'<\/pre>\n\n\n\n<p>Looking at the alert.log I see that 2023-02-14T10:23:31.344766+00:00 old primary database initiated the switchover procedure and 2023-02-14T10:23:48.985369+00:00 the new primary database was ready to accept connections. Taking 17.64s.<\/p>\n\n\n\n<p>Lets see what client saw at the same time.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-10.59.48.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"654\" src=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-10.59.48-1024x654.png\" alt=\"\" class=\"wp-image-572\" srcset=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-10.59.48-1024x654.png 1024w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-10.59.48-300x192.png 300w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-10.59.48-768x491.png 768w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-10.59.48-1536x981.png 1536w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-10.59.48.png 1550w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>What I can see from it is that reads are allowed for a few seconds after switchover was initiated, making total downtime 14 seconds. Writes are closed immediately, so downtime for the client 17s. I actually expeted reads to be allowed for 4 more seconds, right until 2023-02-14T10:23:39 when alert.log reports Switchover complete. Something is not right, must retest this.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Scenario: Only local SYNC standby &#8211; with ADG<\/h2>\n\n\n\n<p>Lets repeat the test, but now the target standby CDB is open in READ ONLY mode. But pluggable databases are in MOUNTED mode (to prevent the service starting). So Active Data Guard is activated (LICENSE WARNING!).<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SQL&gt;sho pdbs\n\n    CON_ID CON_NAME                       OPEN MODE  RESTRICTED\n---------- ------------------------------ ---------- ----------\n         2 PDB$SEED                       READ ONLY  NO\n         3 SOEFAIL                        MOUNTED<\/pre>\n\n\n\n<p>Data Guard status<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Configuration - failtest\n\n  Protection Mode: MaxAvailability\n  Members:\n  failtesti2 - Primary database\n    failtesti1 - (*) Physical standby database \n\nFast-Start Failover: Enabled in Zero Data Loss Mode\n\nConfiguration Status:\nSUCCESS   (status updated 29 seconds ago)<\/pre>\n\n\n\n<p>Executing &#8220;switchover to failtesti1&#8221;<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">date -Iseconds ; dgmgrl \"c##dataguard\/sysdgpassword@connstr\" \"switchover to failtesti1\" ; date -Iseconds\n\n2023-02-14T10:39:13+00:00\n\nConnected to \"failtesti1\"\nConnected as SYSDG.\nPerforming switchover NOW, please wait...\nNew primary database \"failtesti1\" is opening...\nOperation requires start up of instance \"failtesti2\" on database \"failtesti2\"\nStarting instance \"failtesti2\"...<\/pre>\n\n\n\n<p>Alert.log from new primary failtesti1<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">2023-02-14T10:39:19.896716+00:00\nALTER DATABASE SWITCHOVER TO PRIMARY (failtesti1)\n2023-02-14T10:39:21.177020+00:00\nBackground Media Recovery process shutdown (failtesti1)\n2023-02-14T10:39:27.007310+00:00\nCompleted: alter pluggable database all close immediate\n2023-02-14T10:39:32.184760+00:00\nCLOSE: all sessions shutdown successfully.\nCompleted: alter pluggable database all close immediate\n2023-02-14T10:39:33.404812+00:00\nSwitchover: Complete - Database mounted as primary\nTMI: kcv_commit_to_so_to_primary Switchover from physical END 2023-02-14 10:39:33.532617\nSWITCHOVER: completed request from primary database.\n2023-02-14T10:39:37.033131+00:00\nALTER DATABASE OPEN\n2023-02-14T10:39:43.467549+00:00\nSOEFAIL(3):Started service soe\/soe\/soe.prod1.dbs\n2023-02-14T10:39:43.468294+00:00\nPluggable database SOEFAIL opened read write\n2023-02-14T10:39:43.694199+00:00\nCompleted: ALTER DATABASE OPEN\nALTER PLUGGABLE DATABASE ALL OPEN\nCompleted: ALTER PLUGGABLE DATABASE ALL OPEN\n<\/pre>\n\n\n\n<p>Alert.log from old primary failtesti2<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">2023-02-14T10:39:16.016238+00:00\nALTER DATABASE SWITCHOVER TO 'failtesti1'\n2023-02-14T10:39:16.711884+00:00\nALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 2441633] (failtesti2)\n2023-02-14T10:39:17.723971+00:00\nRSM0 (PID:2441633): Waiting for target standby to apply all redo\n2023-02-14T10:39:19.547345+00:00\nSwitchover: Complete - Database shutdown required\nTMI: kcv_switchover_to_target convert to physical END 2023-02-14 10:39:19.567773\nRSM0 (PID:2441633): Sending request(convert to primary database) to switchover target failtesti1\n2023-02-14T10:39:33.534253+00:00\nRSM0 (PID:2441633): Switchover complete. Database shutdown required<\/pre>\n\n\n\n<p>According to alert.log switchover took from 2023-02-14T10:39:16.016238+00:00 to 2023-02-14T10:39:43.467549+00:00 (when service opened), so 27.45s&#8230; surprise, did not expect this.<\/p>\n\n\n\n<p>And now the client perspective.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-11.39.56.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"786\" src=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-11.39.56-1024x786.png\" alt=\"\" class=\"wp-image-575\" srcset=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-11.39.56-1024x786.png 1024w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-11.39.56-300x230.png 300w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-11.39.56-768x590.png 768w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-11.39.56-1536x1179.png 1536w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-11.39.56.png 1688w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>Same story as before, but having the target database open (Active Data Guard) adds about 9-10s to the downtime. From alert.log it looks like it needs to close all read only pluggable databases before switchover completes.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Scenario: Local SYNC standby and remote ASYNC standby &#8211; no ADG<\/h2>\n\n\n\n<p>Now in addition to local SYNC standby, I&#8217;ll add a remote ASYNC standby failtesti3 in the configuration, which is 53ms away (ping time). But I&#8217;m still ony interested in the role change between failtesti1 and failtesti2.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Configuration - failtest\n\n  Protection Mode: MaxAvailability\n  Members:\n  failtesti1 - Primary database\n    failtesti2 - (*) Physical standby database \n    failtesti3 - Physical standby database \n\nFast-Start Failover: Enabled in Zero Data Loss Mode\n\nConfiguration Status:\nSUCCESS   (status updated 31 seconds ago)<\/pre>\n\n\n\n<p>Start the tests and do switchover.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">date -Iseconds ; dgmgrl \"c##dataguard\/sysdgpassword@connstr\" \"switchover to failtesti2\" ; date -Iseconds\n\n2023-02-24T10:18:13+00:00\n\nConnected to \"failtesti1\"\nConnected as SYSDG.\nPerforming switchover NOW, please wait...\nOperation requires a connection to database \"failtesti2\"\nConnecting ...\nConnected to \"failtesti2\"\nConnected as SYSDG.\nNew primary database \"failtesti2\" is opening...\nOperation requires start up of instance \"failtesti1\" on database \"failtesti1\"\nStarting instance \"failtesti1\"...\n\n2023-02-24T10:19:35+00:00<\/pre>\n\n\n\n<p>From client perspective<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-12.29.59.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"667\" src=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-12.29.59-1024x667.png\" alt=\"\" class=\"wp-image-577\" srcset=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-12.29.59-1024x667.png 1024w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-12.29.59-300x195.png 300w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-12.29.59-768x500.png 768w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-12.29.59-1536x1000.png 1536w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-12.29.59.png 1690w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>The remote standby made little difference. It added a few seconds to the switchover time, during which the old primary was still readable. During switchover Data Guard Broker must sync the configuration to all standbys.<\/p>\n\n\n\n<p>Alert log from old primary failtest1<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">2023-02-24T10:18:18.128228+00:00\nALTER DATABASE SWITCHOVER TO 'failtesti2'\n2023-02-24T10:18:18.827865+00:00\nALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 3284632] (failtesti1)\n...\n2023-02-24T10:18:25.855408+00:00\nSwitchover: Complete - Database shutdown required\n2023-02-24T10:18:30.367681+00:00\nRSM0 (PID:3284632): Switchover complete. Database shutdown required\nCompleted: ALTER DATABASE SWITCHOVER TO 'failtesti2'<\/pre>\n\n\n\n<p>I see that reads on the old primary were allowed right until switchover was complete. Writes and disabled immediately. This is the behaviour I expect.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Scenario: Local SYNC standby and remote standby with lagging apply<\/h2>\n\n\n\n<p>Here my goal is to see what happens when the remote standby is lagging behind. Will it affect the local switchover? To simulate that I&#8217;ll stop apply on failtesti3 and let swingbench run for a longer time before initiating switchover.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DGMGRL&gt; edit database failtesti3 set state='apply-off';\n-- run swingbenh for a few hours\nDGMGRL&gt; edit database failtesti3 set state='apply-on';\n-- immediately run switchover<\/code><\/pre>\n\n\n\n<p>Before the test lets check how big the lag is on the remote standby. Before switchover I have over 3h lag, with over 40GB logs to apply on failtesti3.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DGMGRL&gt; show configuration;\n\nConfiguration - failtest\n\n  Protection Mode: MaxAvailability\n  Members:\n  failtesti2 - Primary database\n    failtesti1 - (*) Physical standby database \n    failtesti3 - Physical standby database \n\nFast-Start Failover: Enabled in Zero Data Loss Mode\n\nConfiguration Status:\nSUCCESS   (status updated 37 seconds ago)\n\nDGMGRL&gt; edit database failtesti3 set state='apply-on';\nSucceeded.\nDGMGRL&gt; show database failtesti3;\n\nDatabase - failtesti3\n\n  Role:               PHYSICAL STANDBY\n  Intended State:     APPLY-ON\n  Transport Lag:      0 seconds (computed 0 seconds ago)\n  Apply Lag:          3 hours 7 minutes 36 seconds (computed 0 seconds ago)\n  Average Apply Rate: 88.35 MByte\/s\n  Real Time Query:    ON\n  Instance(s):\n    failtesti3\n\n  Database Warning(s):\n    ORA-16853: apply lag has exceeded specified threshold\n\nDatabase Status:\nWARNING\n\n-- switchover\n2023-02-24T13:37:45+00:00\nConnected to \"failtesti2\"\nConnected as SYSDG.\nPerforming switchover NOW, please wait...\nOperation requires a connection to database \"failtesti1\"\nConnecting ...\nConnected to \"failtesti1\"\nConnected as SYSDG.\nNew primary database \"failtesti1\" is opening...\nOperation requires start up of instance \"failtesti2\" on database \"failtesti2\"\nStarting instance \"failtesti2\"...\n2023-02-24T13:39:06+00:00\n\nDGMGRL&gt; show database failtesti3;\n\nDatabase - failtesti3\n\n  Role:               PHYSICAL STANDBY\n  Intended State:     APPLY-ON\n  Transport Lag:      0 seconds (computed 0 seconds ago)\n  Apply Lag:          2 hours 28 minutes 47 seconds (computed 0 seconds ago)\n  Average Apply Rate: 110.65 MByte\/s\n  Real Time Query:    ON\n  Instance(s):\n    failtesti3<\/pre>\n\n\n\n<p>After the switchover I still have plenty of lag in failtesti3 to apply.<\/p>\n\n\n\n<p>What did the client see?<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-15.45.39.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"640\" src=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-15.45.39-1024x640.png\" alt=\"\" class=\"wp-image-579\" srcset=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-15.45.39-1024x640.png 1024w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-15.45.39-300x187.png 300w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-15.45.39-768x480.png 768w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-15.45.39-1536x959.png 1536w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-15.45.39.png 1678w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>I don&#8217;t see any real difference to the test without the lag.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Scenario: Local SYNC standby and remote standby with lagging transport<\/h2>\n\n\n\n<p>Lets try again, but now I&#8217;ll change transport to remote failtesti3 standby to ARCH mode, expecting that during switchover it would need to transport a few GB.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DGMGRL&gt; edit database failtesti3 set property logxptmode='arch';\n-- run swingbench, so it has quite a few GB of redo collected before logswitch\n\n2023-02-24T14:14:43+00:00\nDGMGRL&gt; switchover to failtesti2;\nConnected to \"failtesti1\"\nConnected as SYSDG.\nPerforming switchover NOW, please wait...\nOperation requires a connection to database \"failtesti2\"\nConnecting ...\nConnected to \"failtesti2\"\nConnected as SYSDG.\nNew primary database \"failtesti2\" is opening...\nOperation requires start up of instance \"failtesti1\" on database \"failtesti1\"\nStarting instance \"failtesti1\"...\n2023-02-24T14:19:49+00:00<\/code><\/pre>\n\n\n\n<p>Finally&#8230; significantly slower switchover, taking minutes. How did the client see it?<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.23.19.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"664\" src=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.23.19-1024x664.png\" alt=\"\" class=\"wp-image-581\" srcset=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.23.19-1024x664.png 1024w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.23.19-300x195.png 300w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.23.19-768x498.png 768w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.23.19.png 1532w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>And it continues this way for minutes&#8230; until<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.24.55.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"730\" src=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.24.55-1024x730.png\" alt=\"\" class=\"wp-image-582\" srcset=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.24.55-1024x730.png 1024w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.24.55-300x214.png 300w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.24.55-768x548.png 768w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.24.55-1536x1095.png 1536w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.24.55.png 1554w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>Read queries were still only down for about 13s, but writes were down for 4min 3s! That is the time it took to send the archived logfile to the remote standby. In my test the logfile was 3GB is size.<\/p>\n\n\n\n<p>Lets look at the old primary alert.log<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">2023-02-24T14:14:48.009629+00:00\nALTER DATABASE SWITCHOVER TO 'failtesti2'\n2023-02-24T14:14:48.714313+00:00\nALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 2332229] (failtesti1)\n2023-02-24T14:14:49.727293+00:00\nRSM0 (PID:2332229): Generating and shipping final logs to target standby\nSwitchover End-Of-Redo Log thread 1 sequence 858 has been fixed\nSwitchover: Primary highest seen SCN set to 0x0000000017d7d99f\nRSM0 (PID:2332229): Noswitch archival of T-1.S-858\nRSM0 (PID:2332229): End-Of-Redo Branch archival of T-1.S-858\nRSM0 (PID:2332229): LGWR is actively archiving to LAD:2\n2023-02-24T14:18:38.393413+00:00\nRSM0 (PID:2332229): Archiving is disabled due to current logfile archival\nPrimary will check for some target standby to have received all redo\nRSM0 (PID:2332229): Waiting for target standby to apply all redo\n2023-02-24T14:18:43.333433+00:00\nRSM0 (PID:2332229): Switchover complete. Database shutdown required<\/pre>\n\n\n\n<p>I don&#8217;t really see any log entry that specifically says that it is waiting for the remote standby to receive the latest redo, although this is what it must be waiting on.<\/p>\n\n\n\n<p>Anyway, good thing to know in case of performing switchovers. Make sure ALL standbys in configuration have minimal amount of redo to ship during switchover.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">RETEST! Only local SYNC standby &#8211; no ADG<\/h2>\n\n\n\n<p>Don&#8217;t really understand why in my first test without any remote long distance standby, reads were stopped too early. I expected the old standby to be readable up until the very last moment of the switchover. Lets try again.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DGMGRL&gt; remove database failtesti3;\nRemoved database \"failtesti3\" from the configuration\n\nDGMGRL&gt; show configuration;\n\nConfiguration - failtest\n\n  Protection Mode: MaxAvailability\n  Members:\n  failtesti2 - Primary database\n    failtesti1 - (*) Physical standby database \n\nFast-Start Failover: Enabled in Zero Data Loss Mode\n\nConfiguration Status:\nSUCCESS   (status updated 11 seconds ago)\n\n2023-02-24T14:53:48+00:00\nDGMGRL&gt; switchover to failtest1;\nConnected to \"failtesti2\"\nConnected as SYSDG.\nPerforming switchover NOW, please wait...\nOperation requires a connection to database \"failtesti1\"\nConnecting ...\nConnected to \"failtesti1\"\nConnected as SYSDG.\nNew primary database \"failtesti1\" is opening...\nOperation requires start up of instance \"failtesti2\" on database \"failtesti2\"\nStarting instance \"failtesti2\"...\n2023-02-24T14:55:06+00:00<\/pre>\n\n\n\n<p>What did the client see?<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.57.08.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"508\" src=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.57.08-1024x508.png\" alt=\"\" class=\"wp-image-584\" srcset=\"https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.57.08-1024x508.png 1024w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.57.08-300x149.png 300w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.57.08-768x381.png 768w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.57.08-1536x763.png 1536w, https:\/\/ilmarkerm.eu\/blog\/wp-content\/uploads\/2023\/02\/Screenshot-2023-02-24-at-16.57.08.png 1696w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><\/figure>\n\n\n\n<p>A little bit faster this time. 12s downtime for reads, 15s downtime for writes.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Take-aways<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>During switchover reads are permitted as long as possible, writes are blocked immediately when switchover starts with ORA-16456.<\/li>\n\n\n\n<li>Having Active Data Guard database as the switchover target adds about 10s to the switchover time.<\/li>\n\n\n\n<li>Having far away ASYNC standby databases will add a few seconds to the switchover time. Minimal.<\/li>\n\n\n\n<li>But if one of the BYSTANDER remote standby databases is having a transport lag, during switchover writes will be blocked for quite a long time, depending on how long it will take to transport the missing redo. Apply lag did not have the same effect.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Next&#8230;<\/h2>\n\n\n\n<p>Failover methods<\/p>\n","protected":false},"excerpt":{"rendered":"<p>All posts in this series Switchover Planned maintenance, need to take primary server down for maintenance. The obvious Data Guard command for role change in this case is SWITCHOVER, that changes database roles in a nice coordinated way and no standby requires reinstatement (flashback) after the operation. Lets test it from the client perspective. Scenario: [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-555","post","type-post","status-publish","format-standard","hentry","category-blog-entry"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/555","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=555"}],"version-history":[{"count":17,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/555\/revisions"}],"predecessor-version":[{"id":595,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/555\/revisions\/595"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=555"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}