{"id":5953,"date":"2015-10-21T21:05:39","date_gmt":"2015-10-21T19:05:39","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/"},"modified":"2015-10-21T21:05:39","modified_gmt":"2015-10-21T19:05:39","slug":"sql-alter-database-switchover-to","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/","title":{"rendered":"SQL&gt; alter database switchover to &#8230;"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nIn 12c the manual switchover to physical standby requires only one command (ALTER DATABASE SWITCHOVER) instead of two (ALTER DATABASE COMMIT TO SWITCHOVER on each database). We usually recommend to use the broker and I wanted to see if the broker uses that new command or not. Let&#8217;s compare the alert.log for both manual and broker switchover.<\/p>\n<h3>One simple command<\/h3>\n<p>I have the broker configured, but try a manual switchover with that simple command:<\/p>\n<pre><code>\nSQL&gt;  alter database switchover to demo12;\nDatabase altered.\n<\/code><\/pre>\n<p>then let&#8217;s look at he alert.log on primary and standby. I put only the minimum I want to show here.<\/p>\n<h3>alert.log on primary<\/h3>\n<p>Here is the initialisation (I&#8217;ve run the ALTER DATABASE SWITCHOVER on the primary):<\/p>\n<pre><code>\nWed Oct 21 22:57:10 2015\n alter database switchover to demo12\nWed Oct 21 22:57:10 2015\nStarting switchover [Process ID: 24349]\nWed Oct 21 22:57:10 2015\nALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 24349] (DEMO11)\nWaiting for target standby to receive all redo\n<\/code><\/pre>\n<p>then after a few seconds:<\/p>\n<pre><code>\nSwitchover: Primary controlfile converted to standby controlfile succesfully.\nSwitchover: Complete - Database shutdown required\nSending request(convert to primary database) to switchover target DEMO12\nSwitchover complete. Database shutdown required\nUSER (ospid: 24349): terminating the instance\nWed Oct 21 22:57:17 2015\nInstance terminated by USER, pid = 24349\nCompleted:  alter database switchover to demo12\nShutting down instance (abort)\nLicense high water mark = 9\nWed Oct 21 22:57:17 2015\nInstance shutdown complete\n<\/code><\/pre>\n<p>The minimum is done there on the primary: role is changed to STANDBY and instance is terminated (shutdown abort)<\/p>\n<h3>alert.log on standby<\/h3>\n<p>Nothing to run on the second database, the switchover request has been sent to it:<\/p>\n<pre><code>\nWed Oct 21 22:57:13 2015\nSWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.\nWed Oct 21 22:57:13 2015\nALTER DATABASE SWITCHOVER TO PRIMARY (DEMO12)\n<\/code><\/pre>\n<p>(the typo in DTABASE  is not mine)<\/p>\n<p>and the role is changed to PRIMARY:<\/p>\n<pre><code>\nCLOSE: killing server sessions.\nCLOSE: all sessions shutdown successfully.\nSwitchOver after complete recovery through change 6571015\nStandby became primary SCN: 6571013\nSwitchover: Complete - Database mounted as primary\nSWITCHOVER: completed request from primary database.\n<\/code><\/pre>\n<p>At that point, the primary is available, but it is not protected anymore: the new standby is down and the log shipping is not configured.<\/p>\n<h3>But if you used the broker<\/h3>\n<p>So I did a manual switchover but I had a broker configuration, so it is totally messed up. Let&#8217;s check it.<\/p>\n<pre><code>\n[oracle@VM111 trace]$ dgmgrl sys\/oracle\nDGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production\n&nbsp;\nCopyright (c) 2000, 2013, Oracle. All rights reserved.\n&nbsp;\nWelcome to DGMGRL, type \"help\" for information.\nConnected as SYSDG.\nDGMGRL&gt; show configuration\nORA-01034: ORACLE not available\nProcess ID: 0\nSession ID: 0 Serial number: 0\n&nbsp;\nConfiguration details cannot be determined by DGMGRL\n<\/code><\/pre>\n<p>Yes, of course, the new standby has to be restarted. In 12c I can do it from dgmgrl:<\/p>\n<pre><code>\nDGMGRL&gt; startup\nORACLE instance started.\nDatabase mounted.\nDatabase opened.\nDGMGRL&gt;\n<\/code><\/pre>\n<p>then check the configuration again:<\/p>\n<pre><code>\nDGMGRL&gt; show configuration\n&nbsp;\nConfiguration - demo11\n&nbsp;\n  Protection Mode: MaxPerformance\n  Members:\n  demo11 - Primary database\n    demo12 - Physical standby database\n&nbsp;\nFast-Start Failover: DISABLED\n&nbsp;\nConfiguration Status:\nSUCCESS   (status updated 12 seconds ago)\n<\/code><\/pre>\n<p>Humm&#8230; SUCESS, but totally messed-up. The broker still think that DEMO11 is the primary and DEMO12 the standby&#8230;<\/p>\n<pre><code>\nDGMGRL&gt; show database demo12\n&nbsp;\nDatabase - demo12\n&nbsp;\n  Role:               PHYSICAL STANDBY\n  Intended State:     APPLY-ON\n  Transport Lag:      (unknown)\n  Apply Lag:          (unknown)\n  Average Apply Rate: 6.00 KByte\/s\n  Real Time Query:    OFF\n  Instance(s):\n    DEMO12\n&nbsp;\n  Database Error(s):\n    ORA-16816: incorrect database role\n&nbsp;\nDatabase Status:\nERROR\n<\/code><\/pre>\n<p>When connecting to what is supposed to be the standby, the broker sees it&#8217;s actually a primary, this the &#8216;incorrect role&#8217; error.<\/p>\n<p>Two solutions to that: recreate the broker configuration, or switchover to the other way.<br \/>\nBut for the seconds solution, you need to finish some configuration:<\/p>\n<pre><code>\nSQL&gt; alter database switchover to demo11;\nalter database switchover to demo11\n*\nERROR at line 1:\nORA-16474: target_db_name not found in the LOG_ARCHIVE_DEST_n parameter\n<\/code><\/pre>\n<p>don&#8217;t think you can do it with the old syntax:<\/p>\n<pre><code>\nSQL&gt;  ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;\n ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY\n*\nERROR at line 1:\nORA-16416: No viable Physical Standby switchover targets available\n<\/code><\/pre>\n<p>You need to set LOG_ARCHIVE_DEST_n or the instance does not know where the current standby is.<\/p>\n<h3>what does the broker in addition to that<\/h3>\n<p>In order to compare, I did before a switchover with the broker, and I&#8217;ll show what differs in the alert logs between broker and manual switchover.<\/p>\n<p>Before the switchover, the broker does some verifications.<br \/>\nOn the primary:<\/p>\n<pre><code>\nSWITCHOVER VERIFY: Send VERIFY request to switchover target demo12\nSWITCHOVER VERIFY COMPLETE\n<\/code><\/pre>\n<p>On the standby:<\/p>\n<pre><code>\nSWITCHOVER VERIFY BEGIN\nSWITCHOVER VERIFY COMPLETE\nSWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.\n<\/code><\/pre>\n<p>After the switchover (&#8216;Completed: ALTER DATABASE SWITCHOVER&#8217;) the primary is only stopped when switchover is initiated from sqlplus:<\/p>\n<pre><code>\nShutting down instance (abort)\nInstance shutdown complete\n<\/code><\/pre>\n<p>but now the broker restarts it :<\/p>\n<pre><code>\nData Guard Broker operation requires restart; shutting down instance now\nStarting ORACLE instance (normal) (OS id: 24088)\nPhysical Standby Database mounted.\nCompleted: alter database mount\nalter database open\nData Guard Broker initializing...\nBeginning Standby Crash Recovery.\nCompleted Standby Crash Recovery.\n<\/code><\/pre>\n<p>and then the properties of the new primary are applied (no log shipping here as it is the standby): <\/p>\n<pre><code>\nALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;\nALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;\nALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='DEMO11';\nALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='DEMO11';\n<\/code><\/pre>\n<p>and it enters recovery mode:<\/p>\n<pre><code>\nALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY\n<\/code><\/pre>\n<p>On the new primary, the broker set all the properties for log shipping: ALTER SYSTEM SET&#8230;<\/p>\n<h3>Conclusion<\/h3>\n<p>What I wanted to show you here is that even if the new 12c syntax is very similar to the dgmgrl command, it does nothing more than before.<br \/>\nExcept if you want to make things complex (and who want to do that on a HA configuration?) you should use the broker.<br \/>\nWhen all the properties are set in the broker, and once you&#8217;ve validated it by a switchover test, you can rely on it with no stress.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . In 12c the manual switchover to physical standby requires only one command (ALTER DATABASE SWITCHOVER) instead of two (ALTER DATABASE COMMIT TO SWITCHOVER on each database). We usually recommend to use the broker and I wanted to see if the broker uses that new command or not. Let&#8217;s compare the alert.log [&hellip;]<\/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":[221,96,209],"type_dbi":[],"class_list":["post-5953","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-data-guard","tag-oracle","tag-oracle-12c"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL&gt; alter database switchover to ... - dbi Blog<\/title>\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\/sql-alter-database-switchover-to\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL&gt; alter database switchover to ...\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In 12c the manual switchover to physical standby requires only one command (ALTER DATABASE SWITCHOVER) instead of two (ALTER DATABASE COMMIT TO SWITCHOVER on each database). We usually recommend to use the broker and I wanted to see if the broker uses that new command or not. Let&#8217;s compare the alert.log [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-10-21T19:05:39+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=\"5 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\/sql-alter-database-switchover-to\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"SQL&gt; alter database switchover to &#8230;\",\"datePublished\":\"2015-10-21T19:05:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/\"},\"wordCount\":546,\"commentCount\":0,\"keywords\":[\"Data Guard\",\"Oracle\",\"Oracle 12c\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/\",\"name\":\"SQL&gt; alter database switchover to ... - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-10-21T19:05:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL&gt; alter database switchover to &#8230;\"}]},{\"@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":"SQL&gt; alter database switchover to ... - dbi Blog","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\/sql-alter-database-switchover-to\/","og_locale":"en_US","og_type":"article","og_title":"SQL&gt; alter database switchover to ...","og_description":"By Franck Pachot . In 12c the manual switchover to physical standby requires only one command (ALTER DATABASE SWITCHOVER) instead of two (ALTER DATABASE COMMIT TO SWITCHOVER on each database). We usually recommend to use the broker and I wanted to see if the broker uses that new command or not. Let&#8217;s compare the alert.log [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/","og_site_name":"dbi Blog","article_published_time":"2015-10-21T19:05:39+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"SQL&gt; alter database switchover to &#8230;","datePublished":"2015-10-21T19:05:39+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/"},"wordCount":546,"commentCount":0,"keywords":["Data Guard","Oracle","Oracle 12c"],"articleSection":["Database Administration &amp; Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/","name":"SQL&gt; alter database switchover to ... - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-10-21T19:05:39+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-alter-database-switchover-to\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL&gt; alter database switchover to &#8230;"}]},{"@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\/5953","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=5953"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5953\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5953"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}