{"id":3932,"date":"2021-04-19T08:24:48","date_gmt":"2021-04-19T08:24:48","guid":{"rendered":"https:\/\/swiv.com.br\/switchover-to-a-logical-standby-using-sql-plus\/"},"modified":"2026-05-27T20:02:32","modified_gmt":"2026-05-27T19:02:32","slug":"switchover-to-a-logical-standby-using-sql-plus","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/04\/19\/switchover-to-a-logical-standby-using-sql-plus\/","title":{"rendered":"Switchover to a Logical Standby using SQL *Plus"},"content":{"rendered":"\n

Ambiente primary:<\/p>\n\n\n

\nSQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME      OPEN_MODE            DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEX    READ WRITE           PRIMARY\n<\/pre><\/div>\n\n\n
Logical Standby:<\/p>\n\n\n
\nSQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;\n \nNAME      OPEN_MODE            DATABASE_ROLE\n--------- -------------------- ----------------\nCORTEXDR  READ WRITE           LOGICAL STANDBY\n<\/pre><\/div>\n\n\n
Iniciando processos de SQL Apply:<\/p>\n\n\n
\nSQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
Checando o n\u00famero de sequence do primary:<\/p>\n\n\n
\nSQL> SELECT THREAD#,SEQUENCE#, STATUS FROM V$LOG;\n \n   THREAD#  SEQUENCE# STATUS\n---------- ---------- ----------------\n         1        193 CURRENT\n         1        191 INACTIVE\n         1        192 INACTIVE\n<\/pre><\/div>\n\n\n
Vemos que os archives foram enviados para o Logical:<\/p>\n\n\n
\n2021-04-19T04:29:03.468370-03:00\nLOGMINER: Begin mining logfile for session 1 thread 1 sequence 192, +DG_RECO\/cortexdr\/archivelog\/cortex1_192_1039033628.arc\n2021-04-19T04:29:03.469272-03:00\nLOGMINER: End   mining logfile for session 1 thread 1 sequence 192, +DG_RECO\/cortexdr\/archivelog\/cortex1_192_1039033628.arc\n<\/pre><\/div>\n\n\n
Criando mais archive no primary e acompanhando o alert do standby:<\/p>\n\n\n
\nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n<\/pre><\/div>\n\n
\n2021-04-19T04:32:23.234798-03:00\n rfs (PID:4037): Selected LNO:4 for T-1.S-194 dbid 548968087 branch 1039033628\n2021-04-19T04:32:23.903474-03:00\n rfs (PID:4451): Selected LNO:5 for T-1.S-193 dbid 548968087 branch 1039033628\n2021-04-19T04:32:25.622455-03:00\nLOGMINER: Begin mining logfile for session 1 thread 1 sequence 193, +DG_FRA\/CORTEXDR\/ONLINELOG\/group_5.267.1069046195\n2021-04-19T04:32:26.012330-03:00\nRFS LogMiner: Registered logfile [+DG_RECO\/cortexdr\/archivelog\/cortex1_193_1039033628.arc] to LogMiner session id [1]\n2021-04-19T04:32:27.258796-03:00\nLOGMINER: End   mining logfile for session 1 thread 1 sequence 193, +DG_FRA\/CORTEXDR\/ONLINELOG\/group_5.267.1069046195\n2021-04-19T04:32:27.331397-03:00\nLOGMINER: Begin mining logfile for session 1 thread 1 sequence 194, +DG_FRA\/CORTEXDR\/ONLINELOG\/group_4.269.1069046175\n<\/pre><\/div>\n\n\n
Em um n\u00edvel mais granular, podemos checar o gap de SCN comparando o valor das duas colunas abaixo, no standby:<\/p>\n\n\n
\nSQL> SELECT MINING_SCN,LATEST_SCN FROM V$LOGSTDBY_PROGRESS;\n \nMINING_SCN LATEST_SCN\n---------- ----------\n   6544296    6544300\n<\/pre><\/div>\n\n\n
Quando isso ocorre, podemos ver o status atual no SQL Apply no standby:<\/p>\n\n\n
\nSQL> SELECT STATUS FROM V$LOGSTDBY_PROCESS WHERE TYPE='READER';\n \nSTATUS\n--------------------------------------------------------------------------------\nORA-16242: Processing log file (thread# 1, sequence# 194)\n<\/pre><\/div>\n\n\n
No ambiente primary, j\u00e1 podemos disparar o comando abaixo de “prepare” do ambiente para a opera\u00e7\u00e3o de switchover. Podemos observar a mudan\u00e7a de valor da coluna “switchover status” do banco:<\/p>\n\n\n
\nSQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;\n \nSWITCHOVER_STATUS\n--------------------\nTO STANDBY\n \nSQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;\n \nDatabase altered.\n \nSQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;\n \nSWITCHOVER_STATUS\n--------------------\nPREPARING SWITCHOVER\n<\/pre><\/div>\n\n\n
Realizando a mesma atividade no atual standby:<\/p>\n\n\n
\nSQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;\n \nSWITCHOVER_STATUS\n--------------------\nNOT ALLOWED\n \nSQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;\n \nDatabase altered.\n \nSQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;\n \nSWITCHOVER_STATUS\n--------------------\nPREPARING SWITCHOVER\n<\/pre><\/div>\n\n\n
Neste momento, podemos cancelar a opera\u00e7\u00e3o de switchover caso seja necess\u00e1rio, emitindo o seguinte comando (que n\u00e3o ser\u00e1 neste artigo):<\/p>\n\n\n
\nALTER DATABASE PREPARE TO SWITCHOVER CANCEL;\n<\/pre><\/div>\n\n\n
Disparando processo no primary:<\/p>\n\n\n
\nSQL> alter database commit to switchover to logical standby;\n \nDatabase altered.\n \nSQL> select database_role,switchover_status from v$database;\n \nDATABASE_ROLE    SWITCHOVER_STATUS\n---------------- --------------------\nLOGICAL STANDBY  NOT ALLOWED\n<\/pre><\/div>\n\n\n
Alert do primary antigo:<\/p>\n\n\n
\n2021-04-19T05:10:56.047327-03:00\nNET  (PID:4009): Archived Log entry 363 added for T-1.S-200 ID 0x20b8e097 LAD:1\n2021-04-19T05:10:56.048266-03:00\nLOGSTDBY: Switchover complete (cortex)\nLOGSTDBY: enabling scheduler job queue processes.\n2021-04-19T05:10:56.048356-03:00\nJOBQ: re-enabling CJQ0\nCompleted: alter database commit to switchover to logical standby\n<\/pre><\/div>\n\n\n
Rodando processo no standby antigo:<\/p>\n\n\n
\nSQL> alter database commit to switchover to primary;\n \nDatabase altered.\n \nSQL> select database_role,switchover_status from v$database;\n \nDATABASE_ROLE    SWITCHOVER_STATUS\n---------------- --------------------\nPRIMARY          LOG SWITCH GAP\n<\/pre><\/div>\n\n\n
Alert do antigo standby:<\/p>\n\n\n
\n2021-04-19T05:14:57.368964-03:00\nLOGSTDBY: Database guard disabled.  User transactions are now permitted.\nLOGSTDBY: enabling scheduler job queue processes.\n2021-04-19T05:14:57.371749-03:00\nJOBQ: re-enabling CJQ0\nCompleted: alter database commit to switchover to primary\n<\/pre><\/div>\n\n
Por\u00e9m pudemos notar um status de Log Switch Gap. Desse modo, o Oracle Note “Step by Step How to Do Swithcover\/Failover on Logical Standby Environment (Doc ID 2535950.1)<\/strong>” (Link AQUI<\/a>) recomanda que fa\u00e7amos a cria\u00e7\u00e3o de alguns archives no novo primary para normalizar:<\/p>\n\n
\nSQL> select database_role,switchover_status from v$database;\n \nDATABASE_ROLE    SWITCHOVER_STATUS\n---------------- --------------------\nPRIMARY          LOG SWITCH GAP\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> \/\n \nSystem altered.\n \nSQL> select database_role,switchover_status from v$database;\n \nDATABASE_ROLE    SWITCHOVER_STATUS\n---------------- --------------------\nPRIMARY          TO STANDBY\n<\/pre><\/div>\n\n\n
Por fim, podemos iniciar o SQL Apply no novo standby:<\/p>\n\n\n
\nSQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
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":"
Ambiente primary: Logical Standby: Iniciando processos de SQL Apply: Checando o n\u00famero de sequence do primary: Vemos que os archives foram enviados para o Logical: Criando mais archive no primary e acompanhando o alert do standby: Em um n\u00edvel mais granular, podemos checar o gap de SCN comparando o valor das duas colunas abaixo, no […]<\/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-3932","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\/3932","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=3932"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3932\/revisions"}],"predecessor-version":[{"id":9164,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3932\/revisions\/9164"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3932"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3932"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3932"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}