{"id":9800,"date":"2017-02-21T15:13:36","date_gmt":"2017-02-21T14:13:36","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/"},"modified":"2023-06-08T16:18:20","modified_gmt":"2023-06-08T14:18:20","slug":"dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/","title":{"rendered":"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition"},"content":{"rendered":"

By Mouhamadou Diaw<\/strong><\/p>\n

As of Oracle Database 12c Release 2 (12.2.0.1), when a physical standby database is converted into a primary you have the option to keep any sessions connected to the physical standby, without disruption, during the switchover\/failover. When the database is reopened as the primary, the suspended sessions resume their operations as if nothing had happened. If the database (or an individual PDB) is not opened in the primary role, the sessions will be terminated.
\nTo enable this feature, the STANDBY_DB_PRESERVE_STATES initialization parameter in the standby side is used. This parameter can have following values:
\nNONE \u2014 No sessions on the standby are retained during a switchover\/failover.
\nSESSION or ALL \u2014 User sessions are retained during switchover\/failover.
\nThis parameter is only meaningful on a physical standby database that is open in real-time query mode<\/span>. This needs Active dataguard option
\nIn this blog we are going \u00a0to do a demonstration of this new feature. First we present below our configuration
\n
\nDGMGRL> show configuration;
\nConfiguration - ORCL_DR
\nProtection Mode: MaxProtection
\nMembers:
\nORCL_SITE - Primary database
\nORCL_SITE1 - Physical standby database
\nORCL_SITE2 - Physical standby database
\nFast-Start Failover: DISABLED
\nConfiguration Status:
\nSUCCESS (status updated 32 seconds ago)
\nDGMGRL>
\n<\/code>
\nNow let\u2019s connect to the standby ORCL_SITE1 and let\u2019s note our session\u2019s info (sid, serial#)
\nSQL>
\nselect username,sid, serial# from v$session where sid=SYS_CONTEXT('USERENV','SID');
\nUSERNAME SID SERIAL#
\n--------------- ---------- ----------
\nSYSTEM 65 2869
\n<\/code>
\n
\nSQL> show parameter db_unique_name
\nNAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\ndb_unique_name string ORCL_SITE1
\n<\/code>
\n
\nSQL> select open_mode from v$database;
\nOPEN_MODE
\n--------------------
\nREAD ONLY WITH APPLY
\n<\/code>
\nWith the default value NONE for the parameter standby_db_preserve_states on ORCL_SITE1 let\u2019s do a switchover to ORCL_SITE1.
\nSQL>
\nshow parameter standby_db_preserve_states;
\nNAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\nstandby_db_preserve_states string NONE
\nSQL>
\n<\/code>
\n
\nDGMGRL> switchover to 'ORCL_SITE1';
\nPerforming switchover NOW, please wait...
\nOperation requires a connection to database \"ORCL_SITE1\"
\nConnecting ...
\nConnected to \"ORCL_SITE1\"
\nConnected as SYSDBA.
\nNew primary database \"ORCL_SITE1\" is opening...
\nOperation requires start up of instance \"ORCL\" on database \"ORCL_SITE\"
\nStarting instance \"ORCL\"...
\nORACLE instance started.
\nDatabase mounted.
\nDatabase opened.
\nConnected to \"ORCL_SITE\"
\nSwitchover succeeded, new primary is \"ORCL_SITE1\"
\n<\/code>
\nWhile the switchover going on, let\u2019s start a query on ORCL_SITE1. As expected we get an error, the session was disconnected
\n
\nSQL> select * from dba_objects;
\nselect * from dba_objects
\n*
\nERROR at line 1:
\nORA-03113: end-of-file on communication channel
\nProcess ID: 1915
\nSession ID: 65 Serial number: 2869
\nSQL>
\n<\/code>
\nOur new configuration is now like this
\n
\nDGMGRL> show configuration;
\nConfiguration - ORCL_DR
\nProtection Mode: MaxProtection
\nMembers:
\nORCL_SITE1 - Primary database
\nORCL_SITE - Physical standby database
\nORCL_SITE2 - Physical standby database
\nFast-Start Failover: DISABLED
\nConfiguration Status:
\nSUCCESS (status updated 58 seconds ago)
\nDGMGRL>
\n<\/code>
\nNow let\u2019s connect to the standby ORCL_SITE with the standby_db_preserve_states set to ALL
\n
\nSQL> select username,sid, serial# from v$session where sid=SYS_CONTEXT('USERENV','SID');
\nUSERNAME SID SERIAL#
\n--------------- ---------- ----------
\nSYSTEM 58 58847
\n<\/code>
\n
\nSQL> show parameter db_unique_name
\nNAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\ndb_unique_name string ORCL_SITE
\n<\/code>
\n
\nSQL> show parameter standby_db_preserve_states
\nNAME TYPE VALUE
\n------------------------------------ ----------- ------------------------------
\nstandby_db_preserve_states string ALL
\n<\/code>
\nNow let\u2019s do a switchover back to SITE_ORCL and let\u2019s monitor the connection.
\n
\nDGMGRL> switchover to 'ORCL_SITE';
\nPerforming switchover NOW, please wait...
\nOperation requires a connection to database \"ORCL_SITE\"
\nConnecting ...
\nConnected to \"ORCL_SITE\"
\nConnected as SYSDBA.
\nNew primary database \"ORCL_SITE\" is opening...
\nOperation requires start up of instance \"ORCL\" on database \"ORCL_SITE1\"
\nStarting instance \"ORCL\"...
\nORACLE instance started.
\nDatabase mounted.
\nDatabase opened.
\nConnected to \"ORCL_SITE1\"
\nSwitchover succeeded, new primary is \"ORCL_SITE\"
\nDGMGRL>
\n<\/code>
\nAs expected, after the switchover I see that my session is still connected with the same SID and SERIAL#. Indeed user sessions are retained and when the database is reopened as the primary, the suspended sessions resume their operations as if nothing had happened.
\n
\nSQL> select username,sid, serial# from v$session where sid=SYS_CONTEXT('USERENV','SID');
\nUSERNAME SID SERIAL#
\n--------------- ---------- ----------
\nSYSTEM 58 58847
\n<\/code>
\nJust in the documentation it is mentioned that \u201cSessions that have long running queries or are using database links will not be retained regardless of the setting of this parameter\u201d.<\/p>\n","protected":false},"excerpt":{"rendered":"

By Mouhamadou Diaw As of Oracle Database 12c Release 2 (12.2.0.1), when a physical standby database is converted into a primary you have the option to keep any sessions connected to the physical standby, without disruption, during the switchover\/failover. When the database is reopened as the primary, the suspended sessions resume their operations as if […]<\/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],"tags":[1037,96,1025],"type_dbi":[],"class_list":["post-9800","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-datguard","tag-oracle","tag-oracle-12cr2"],"acf":[],"yoast_head":"\nDataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition - dbi Blog<\/title>\n<meta name=\"description\" content=\"oracle 12c R2, dataguard\" \/>\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\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition\" \/>\n<meta property=\"og:description\" content=\"oracle 12c R2, dataguard\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-02-21T14:13:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-08T14:18:20+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=\"3 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\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition\",\"datePublished\":\"2017-02-21T14:13:36+00:00\",\"dateModified\":\"2023-06-08T14:18:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/\"},\"wordCount\":353,\"commentCount\":0,\"keywords\":[\"datguard\",\"Oracle\",\"Oracle 12cR2\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/\",\"name\":\"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2017-02-21T14:13:36+00:00\",\"dateModified\":\"2023-06-08T14:18:20+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"oracle 12c R2, dataguard\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition\"}]},{\"@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":"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition - dbi Blog","description":"oracle 12c R2, dataguard","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\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/","og_locale":"en_US","og_type":"article","og_title":"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition","og_description":"oracle 12c R2, dataguard","og_url":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/","og_site_name":"dbi Blog","article_published_time":"2017-02-21T14:13:36+00:00","article_modified_time":"2023-06-08T14:18:20+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition","datePublished":"2017-02-21T14:13:36+00:00","dateModified":"2023-06-08T14:18:20+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/"},"wordCount":353,"commentCount":0,"keywords":["datguard","Oracle","Oracle 12cR2"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/","url":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/","name":"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-02-21T14:13:36+00:00","dateModified":"2023-06-08T14:18:20+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"oracle 12c R2, dataguard","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/dataguard-oracle-12-2-keeping-physical-standby-sessions-connected-during-role-transition\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Dataguard Oracle 12.2: Keeping Physical Standby Sessions Connected During Role Transition"}]},{"@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\/9800","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=9800"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9800\/revisions"}],"predecessor-version":[{"id":25693,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9800\/revisions\/25693"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9800"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9800"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9800"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9800"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}