{"id":4010,"date":"2021-04-22T08:15:45","date_gmt":"2021-04-22T08:15:45","guid":{"rendered":"https:\/\/swiv.com.br\/failover-to-a-physical-standby-using-sql-plus\/"},"modified":"2026-05-27T20:02:32","modified_gmt":"2026-05-27T19:02:32","slug":"failover-to-a-physical-standby-using-sql-plus","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/04\/22\/failover-to-a-physical-standby-using-sql-plus\/","title":{"rendered":"Failover to a Physical Standby using SQL *Plus"},"content":{"rendered":"\n<p>Verificando condi\u00e7\u00f5es do ambiente primary e standby:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&#x5B;oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 22 04:48:31 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL&gt; SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME      OPEN_MODE            DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEX    READ WRITE           PRIMARY\n \nSQL&gt; SELECT FLASHBACK_ON FROM V$DATABASE;\n \nFLASHBACK_ON\n------------------\nYES\n \nSQL&gt; SELECT THREAD#,SEQUENCE#, STATUS FROM V$LOG;\n \n   THREAD#  SEQUENCE# STATUS\n---------- ---------- ----------------\n         1         46 CURRENT\n         1         44 INACTIVE\n         1         45 INACTIVE\n \nSQL&gt;\n<\/pre><\/div>\n\n\n<p>Standby:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&#x5B;oracle@fornix2 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 22 04:49:31 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL&gt; SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME      OPEN_MODE            DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEX    MOUNTED              PHYSICAL STANDBY\n \nSQL&gt; SELECT FLASHBACK_ON FROM V$DATABASE;\n \nFLASHBACK_ON\n------------------\nYES\n \nSQL&gt; SELECT CLIENT_PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;\n \nCLIENT_P  SEQUENCE# STATUS\n-------- ---------- ------------\nARCH              0 CONNECTED\nN\/A               0 ALLOCATED\nN\/A               0 ALLOCATED\nARCH              0 CONNECTED\nARCH             45 CLOSING\nARCH              0 CONNECTED\nArchival          0 IDLE\nLGWR             46 IDLE\nUNKNOWN           0 IDLE\n \n9 rows selected.\n \nSQL&gt; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\n \nDatabase altered.\n \nSQL&gt; SELECT CLIENT_PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;\n \nCLIENT_P  SEQUENCE# STATUS\n-------- ---------- ------------\nARCH              0 CONNECTED\nN\/A               0 ALLOCATED\nN\/A               0 ALLOCATED\nARCH              0 CONNECTED\nARCH             45 CLOSING\nARCH              0 CONNECTED\nArchival          0 IDLE\nLGWR             46 IDLE\nUNKNOWN           0 IDLE\nN\/A              46 APPLYING_LOG\n \n10 rows selected.\n<\/pre><\/div>\n\n\n<p>Simulando uma indisponibilidade no ambiente primary, realizando um shutdown abort:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME      OPEN_MODE            DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEX    READ WRITE           PRIMARY\n \nSQL&gt; SHU ABORT;\nORACLE instance shut down.\nSQL&gt; !ps -ef | grep pmon\ngrid      3325     1  0 04:41 ?        00:00:00 asm_pmon_+ASM\noracle    4689  4329  0 04:54 pts\/0    00:00:00 \/bin\/bash -c ps -ef | grep pmon\noracle    4691  4689  0 04:54 pts\/0    00:00:00 grep pmon\n<\/pre><\/div>\n\n\n<p>Nessa etapa, vamos parar o processo de Redo Apply no standby:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME      OPEN_MODE            DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEX    MOUNTED              PHYSICAL STANDBY\n \nSQL&gt; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n<p>Agora temos condi\u00e7\u00f5es de realizar no standby a opera\u00e7\u00e3o de Failover, para convert\u00ea-lo em primary:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER DATABASE FAILOVER TO CORTEXDR;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n<p>Abrindo o novo primary:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER DATABASE OPEN;\n \nDatabase altered.\n \nSQL&gt; SELECT DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nDB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE\n------------------------------ -------------------- ----------------\ncortexDR                       READ WRITE           PRIMARY\n<\/pre><\/div>\n\n\n<p>No alert do novo primary temos os detalhes das opera\u00e7\u00f5es:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n2021-04-22 04:56:52.082000 -03:00\nALTER DATABASE FAILOVER TO CORTEXDR\n.... (PID:4137): The Time Management Interface (TMI) is being enabled for role transition\n.... (PID:4137): information.  This will result in messages beingoutput to the alert log\n.... (PID:4137): file with the prefix &#039;TMI: &#039;.  This is being enabled to make the timing of\n.... (PID:4137): the various stages of the role transition available for diagnostic purposes.\n.... (PID:4137): This output will end when the role transition is complete.\nTMI: dbsdrv failover to target BEGIN 2021-04-22 04:56:52.082876\nTerminal Recovery requested in process 4137\nTMI: adbdrv termRecovery BEGIN 2021-04-22 04:56:52.084076\nAttempt to do a Terminal Recovery (CORTEXDR)\nTMI: adbdrv termRecovery END 2021-04-22 04:56:52.089336\nMedia Recovery Start: Managed Standby Recovery (CORTEXDR)\nSerial Media Recovery started\nNET  (PID:4137): Managed Standby Recovery not using Real Time Apply\nstopping change tracking\nNET  (PID:4137): Begin: SRL archival\nNET  (PID:4137): End: SRL archival\nNET  (PID:4137): Terminal Recovery timestamp is &#039;04\/22\/2021 04:56:52&#039;\nNET  (PID:4137): Terminal Recovery: applying standby redo logs.\nNET  (PID:4137): Terminal Recovery: thread 1 seq# 46 redo required\nNET  (PID:4137): Terminal Recovery:\nRecovery of Online Redo Log: Thread 1 Group 4 Seq 46 Reading mem 0\n  Mem# 0: +DG_FRA\/CORTEXDR\/ONLINELOG\/group_4.264.1066479889\nIncomplete Recovery applied until change 4943531 time 04\/22\/2021 04:53:52\nMedia Recovery Complete (CORTEXDR)\nTerminal Recovery: successful completion\nNET  (PID:4137): Forcing ARSCN to IRSCN for TR SCN:0x00000000004b6eab\nNET  (PID:4137): Attempt to set limbo arscn SCN:0x00000000004b6eab irscn SCN:0x00000000004b6eab\nNET  (PID:4137): Resetting standby activation ID 548987031 (0x20b8e097)\nstopping change tracking\nALTER DATABASE SWITCHOVER TO PRIMARY (CORTEXDR)\nMaximum wait for role transition is 15 minutes.\nTMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2021-04-22 04:56:52.862642\nTMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2021-04-22 04:56:52.862705\nTMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2021-04-22 04:56:52.863273\nBackup controlfile written to trace file \/oracle\/19.3.0\/base\/diag\/rdbms\/cortexdr\/CORTEXDR\/trace\/CORTEXDR_ora_4137.trc\nStandby terminal recovery start SCN: 4943530\nRESETLOGS after incomplete recovery UNTIL CHANGE 4943531 time 04\/22\/2021 04:53:52\nNET  (PID:4137): ORL pre-clearing operation disabled by switchover\nOnline log +DG_FRA\/CORTEXDR\/ONLINELOG\/group_1.261.1066479861: Thread 1 Group 1 was previously cleared\nOnline log +DG_DATA\/CORTEXDR\/ONLINELOG\/group_1.263.1066479867: Thread 1 Group 1 was previously cleared\nOnline log +DG_FRA\/CORTEXDR\/ONLINELOG\/group_2.262.1066479871: Thread 1 Group 2 was previously cleared\nOnline log +DG_DATA\/CORTEXDR\/ONLINELOG\/group_2.262.1066479877: Thread 1 Group 2 was previously cleared\nOnline log +DG_FRA\/CORTEXDR\/ONLINELOG\/group_3.263.1066479879: Thread 1 Group 3 was previously cleared\nOnline log +DG_DATA\/CORTEXDR\/ONLINELOG\/group_3.261.1066479885: Thread 1 Group 3 was previously cleared\nStandby became primary SCN: 4943529\nSetting recovery target incarnation to 3\nNET  (PID:4137): RT: Role transition work is not done\nNET  (PID:4137): The Time Management Interface (TMI) is being enabled for role transition\nNET  (PID:4137): information.  This will result in messages beingoutput to the alert log\nNET  (PID:4137): file with the prefix &#039;TMI: &#039;.  This is being enabled to make the timing of\nNET  (PID:4137): the various stages of the role transition available for diagnostic purposes.\nNET  (PID:4137): This output will end when the role transition is complete.\nNET  (PID:4137): Redo network throttle feature is disabled at mount time\nNET  (PID:4137): Database role cleared from PHYSICAL STANDBY &#x5B;kcvs.c:1030]\nSwitchover: Complete - Database mounted as primary\n<\/pre><\/div>\n\n\n<h2 class=\"wp-block-heading\">Reinstate the Primary Database after Failover to Physical Standby Database<\/h2>\n\n\n\n<p>Esta etapa consiste em converter o antigo primary em um novo Standby, quando o mesmo voltar a estar dispon\u00edvel ap\u00f3s sua falha original. Caso tenhamos habilitado o recurso de Flashback Database (demonstrado no in\u00edcio deste artigo), esse processo \u00e9 simples e r\u00e1pido. Caso contr\u00e1rio, ser\u00e1 necess\u00e1rio a recria\u00e7\u00e3o do standby.<\/p>\n\n\n\n<p>Foi poss\u00edvel vermos no Alert exposto acima o n\u00famero do SCN no qual o antigo standby se tornou primary (&#8220;Standby became primary SCN: 4943529&#8221;), mas tamb\u00e9m podemos coletar essa informa\u00e7\u00e3o rodando o seguinte comando no novo primary:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT DB_UNIQUE_NAME,TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;\n \nDB_UNIQUE_NAME                 TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)\n------------------------------ ----------------------------------------\ncortexDR                       4943529\n<\/pre><\/div>\n\n\n<p>Montando o novo standby e realizando o Flashback usando este SCN:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n&#x5B;oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 22 05:09:52 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \nConnected to an idle instance.\n \nSQL&gt; startup mount;\nORACLE instance started.\n \nTotal System Global Area 2583690520 bytes\nFixed Size                  8899864 bytes\nVariable Size             553648128 bytes\nDatabase Buffers         2013265920 bytes\nRedo Buffers                7876608 bytes\nDatabase mounted.\nSQL&gt; FLASHBACK DATABASE TO SCN 4943529;\n \nFlashback complete.\n<\/pre><\/div>\n\n\n<p>Convertendo o menino para Physical Standby:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER DATABASE CONVERT TO PHYSICAL STANDBY;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n<p>Ligando processo de Redo Apply:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n<p>Gerando archive no primary e checando se o mesmo \u00e9 aplicado no standby:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT DB_UNIQUE_NAME FROM V$DATABASE;\n \nDB_UNIQUE_NAME\n------------------------------\ncortexDR\n \nSQL&gt; ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL&gt; SELECT THREAD#,SEQUENCE#, STATUS FROM V$LOG;\n \n   THREAD#  SEQUENCE# STATUS\n---------- ---------- ----------------\n         1          4 CURRENT\n         1          2 ACTIVE\n         1          3 ACTIVE\n<\/pre><\/div>\n\n<div class=\"wp-block-syntaxhighlighter-code \\&quot;wp-block-syntaxhighlighter-code\\&quot;\"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; SELECT CLIENT_PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;\n \nCLIENT_P  SEQUENCE# STATUS\n-------- ---------- ------------\nARCH              0 CONNECTED\nARCH              0 CONNECTED\nARCH              0 CONNECTED\nARCH              3 CLOSING\nN\/A               0 ALLOCATED\nN\/A               0 ALLOCATED\nArchival          0 IDLE\nLGWR              4 IDLE\nUNKNOWN           0 IDLE\nUNKNOWN           0 IDLE\nUNKNOWN           0 IDLE\n \nCLIENT_P  SEQUENCE# STATUS\n-------- ---------- ------------\nN\/A               4 APPLYING_LOG\n \n12 rows selected.\n<\/pre><\/div>\n\n\n<p>Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Verificando condi\u00e7\u00f5es do ambiente primary e standby: Standby: Simulando uma indisponibilidade no ambiente primary, realizando um shutdown abort: Nessa etapa, vamos parar o processo de Redo Apply no standby: Agora temos condi\u00e7\u00f5es de realizar no standby a opera\u00e7\u00e3o de Failover, para convert\u00ea-lo em primary: Abrindo o novo primary: No alert do novo primary temos os [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-4010","post","type-post","status-publish","format-standard","hentry","category-high-availability"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4010","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=4010"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4010\/revisions"}],"predecessor-version":[{"id":9161,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/4010\/revisions\/9161"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=4010"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=4010"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=4010"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}