Failover to a Logical Standby using SQL *Plus

Verificando as condições do ambiente primary e standby:

[oracle@fornix1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 24 04:21:27 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> SELECT DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
 
DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
cortex                         READ WRITE           PRIMARY
 
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
 
FLASHBACK_ON
------------------
YES
 
SQL> SELECT THREAD#,SEQUENCE#, STATUS FROM V$LOG;
 
   THREAD#  SEQUENCE# STATUS
---------- ---------- ----------------
         1        193 CURRENT
         1        191 INACTIVE
         1        192 INACTIVE

Standby:

[oracle@fornix2 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 24 04:24:25 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> SELECT DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
 
DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
cortexDR                       READ WRITE           LOGICAL STANDBY
 
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
 
Database altered.
 
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
 
FLASHBACK_ON
------------------
YES
 
SQL> SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS F_SCN#,
NEXT_CHANGE# AS N_SCN#, TIMESTAMP,
DICT_BEGIN, DICT_END,
THREAD# AS THR#, APPLIED
FROM DBA_LOGSTDBY_LOG
ORDER BY SEQUENCE#;  2    3    4    5    6
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP           DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------- --- --- ---------- --------
+DG_RECO/cortexdr/archivelog/cortex1_187_1039033628.arc
       187    6329394    6433563 2021-04-08:04:15:47 NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_188_1039033628.arc
       188    6433563    6435070 2021-04-08:04:15:46 NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_189_1039033628.arc
       189    6435070    6439330 2021-04-08:04:24:46 NO  NO           1 YES
 
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP           DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------- --- --- ---------- --------
+DG_RECO/cortexdr/archivelog/cortex1_190_1039033628.arc
       190    6439330    6439337 2021-04-08:04:24:46 NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_191_1039033628.arc
       191    6439337    6439342 2021-04-08:04:24:48 NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_192_1039033628.arc
       192    6439342    6540301 2021-04-24:04:24:27 NO  NO           1 YES
 
 
6 rows selected.

Gerando novo archive no primary:

SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.

Que foi aplicado com sucesso no standby (sequence 193):

SQL> /
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP           DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------- --- --- ---------- --------
+DG_RECO/cortexdr/archivelog/cortex1_187_1039033628.arc
       187    6329394    6433563 2021-04-08:04:15:47 NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_188_1039033628.arc
       188    6433563    6435070 2021-04-08:04:15:46 NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_189_1039033628.arc
       189    6435070    6439330 2021-04-08:04:24:46 NO  NO           1 YES
 
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP           DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------- --- --- ---------- --------
+DG_RECO/cortexdr/archivelog/cortex1_190_1039033628.arc
       190    6439330    6439337 2021-04-08:04:24:46 NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_191_1039033628.arc
       191    6439337    6439342 2021-04-08:04:24:48 NO  NO           1 YES
 
+DG_RECO/cortexdr/archivelog/cortex1_192_1039033628.arc
       192    6439342    6540301 2021-04-24:04:24:27 NO  NO           1 YES
 
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP           DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------- --- --- ---------- --------
+DG_RECO/cortexdr/archivelog/cortex1_193_1039033628.arc
       193    6540301    6542996 2021-04-24:04:26:41 NO  NO           1 YES
 
 
7 rows selected.

Simulando uma indisponibilidade no ambiente primary, realizando um shutdown abort:

SQL> SHU ABORT;
ORACLE instance shut down.
SQL> !ps -ef | grep pmon
grid      3310     1  0 04:15 ?        00:00:00 asm_pmon_+ASM
oracle    5519  4548  0 04:29 pts/0    00:00:00 /bin/bash -c ps -ef | grep pmon
oracle    5521  5519  0 04:29 pts/0    00:00:00 grep pmon

A operação de Failover para o Logical Standby consiste na execução do comando abaixo:

SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;
 
Database altered.
 
SQL> SELECT DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
 
DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
cortexDR                       READ WRITE           PRIMARY

No alert do novo primary temos o processo de failover sendo detalhado:

2021-04-24T04:31:10.773409-03:00
ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY (CORTEXDR)
2021-04-24T04:31:10.775639-03:00
LOGSTDBY: Logical ACTIVATE state
2021-04-24T04:31:10.826360-03:00
LOGSTDBY: terminating active RFS connections for role change
2021-04-24T04:31:10.832661-03:00
LOGMINER: session# 1 begin Terminal Apply mode
2021-04-24T04:31:11.878120-03:00
LOGMINER: Terminal Apply complete with thread 1 seq 194 scn 0x000000000063db83 session# 1
2021-04-24T04:31:11.878455-03:00
LOGMINER: WARNING: session# 1 failover completed with potential data loss
2021-04-24T04:31:11.878572-03:00
LOGMINER: thread merge scn 0x000000000063db83
2021-04-24T04:31:11.880366-03:00
LOGSTDBY: STANDBY_BECAME_PRIMARY_SCN established at 6544258 [0x000000000063db82]
2021-04-24T04:31:11.882473-03:00
LOGSTDBY Apply process AS01 server id=1 pid=87 OS id=4473 stopped
2021-04-24T04:31:11.883063-03:00
LOGSTDBY Apply process AS03 server id=3 pid=89 OS id=4477 stopped
2021-04-24T04:31:11.883915-03:00
LOGSTDBY Apply process AS04 server id=4 pid=90 OS id=4479 stopped
2021-04-24T04:31:11.885878-03:00
LOGSTDBY Apply process AS05 server id=5 pid=91 OS id=4481 stopped
2021-04-24T04:31:11.888140-03:00
LOGSTDBY Apply process AS02 server id=2 pid=88 OS id=4475 stopped
2021-04-24T04:31:11.889941-03:00
LOGSTDBY Analyzer process AS00 server id=0 pid=86 OS id=4471 stopped
2021-04-24T04:31:11.892214-03:00
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=85 OS id=4468 sid=115 stopped
2021-04-24T04:31:11.902819-03:00
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=83 OS id=4464 sid=127 stopped
2021-04-24T04:31:11.934742-03:00
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=84 OS id=4466 sid=129 stopped
2021-04-24T04:31:11.939616-03:00
LOGSTDBY status: ORA-16128: User initiated stop apply successfully completed
2021-04-24T04:31:12.254182-03:00
Sat Apr 24 04:31:12 2021
Logminer Bld: Lockdown Complete. UnwindToSCN is 6580502
LOGSTDBY: Starting SCN of new stream from recent lockdown [0x0000000000646916]
2021-04-24T04:31:12.289014-03:00
.... (PID:4434): Database role cleared from LOGICAL STANDBY [dglc.c:1943]
Starting background process LSP1
2021-04-24T04:31:12.308860-03:00
LSP1 started with pid=51, OS id=4894
2021-04-24T04:31:12.309484-03:00
LOGSTDBY: (LSP1) LogMiner Dictionary Build Process Created
2021-04-24T04:31:12.310562-03:00
LOGSTDBY: enabling scheduler job queue processes.
2021-04-24T04:31:12.310602-03:00
JOBQ: re-enabling CJQ0
2021-04-24T04:31:12.320958-03:00
LOGSTDBY: (LSP1) Archiving online logs as a primary database
Completed: ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY

Reinstate the Primary Database after Failover to Logical Standby Database

Coletando no novo primary o Flashback_SCN que será usado como referência na operação de reinstate, com o seguinte comando:

[oracle@fornix2 trace]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 24 04:38:40 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> SELECT MERGE_CHANGE# AS FLASHBACK_SCN, PROCESSED_CHANGE# AS RECOVERY_SCN FROM DBA_LOGSTDBY_HISTORY
WHERE STREAM_SEQUENCE# = (SELECT MAX(STREAM_SEQUENCE#) -1 FROM DBA_LOGSTDBY_HISTORY);  2
 
FLASHBACK_SCN RECOVERY_SCN
------------- ------------
      6544258      6544260

Realizando o Flashback database no primary antigo:

[oracle@fornix1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 24 04:44:48 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area 2583690520 bytes
Fixed Size                  8899864 bytes
Variable Size             570425344 bytes
Database Buffers         1996488704 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 6544258;
 
Flashback complete.

Convertendo o primary antigo para physical standby:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
 
Database altered.
 
SQL> SHU IMMEDIATE;
ORA-01109: database not open
 
 
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
 
Total System Global Area 2583690520 bytes
Fixed Size                  8899864 bytes
Variable Size             570425344 bytes
Database Buffers         1996488704 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> SELECT DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
 
DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
cortex                         MOUNTED              PHYSICAL STANDBY

Confirmando que o parâmetro FAL_SERVER esteja devidamente preenchido no novo standby:

SQL> SHO PARAMETER FAL_SERVER
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_server                           string      cortexDR
SQL>

Removendo archive divergentes no novo standby caso os mesmos existam:

[oracle@fornix1 ~]$ rman target /
 
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Apr 24 04:51:47 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087, not open)
 
RMAN> DELETE FORCE ARCHIVELOG FROM SCN 6544258;
 
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=54 device type=DISK
specification does not match any archived log in the repository

Realizando recover no novo standby utilizando o SCN coletado no novo primary:

[oracle@fornix1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 24 05:02:56 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> RECOVER MANAGED STANDBY DATABASE UNTIL CHANGE 6544260;
Media recovery complete.

Habilitando o Database Guard:

SQL> ALTER DATABASE GUARD ALL;
 
Database altered.

Ativando o physical standby para o mesmo se tornar o primary database:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
 
Database altered.
 
SQL> ALTER DATABASE OPEN;
 
Database altered.
 
SQL> SELECT DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
 
DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
cortex                         READ WRITE           PRIMARY

No alert temos os detalhes desse processo:

2021-04-24T05:09:22.309939-03:00
ALTER DATABASE ACTIVATE STANDBY DATABASE
2021-04-24T05:09:22.310009-03:00
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE [Process Id: 8887] (cortex)
2021-04-24T05:09:22.311485-03:00
NET  (PID:8887): Begin: SRL archival
NET  (PID:8887): End: SRL archival
RESETLOGS after incomplete recovery UNTIL CHANGE 6544260 time 04/24/2021 04:31:25
2021-04-24T05:09:22.727364-03:00
NET  (PID:8887): Waiting for all non-current ORLs to be archived
2021-04-24T05:09:22.727413-03:00
NET  (PID:8887): All non-current ORLs have been archived
NET  (PID:8887): Clearing online redo logfile 1 +DG_DATA/CORTEX/ONLINELOG/group_1.261.1039033629
NET  (PID:8887): Clearing online redo logfile 2 +DG_DATA/CORTEX/ONLINELOG/group_2.262.1039033629
NET  (PID:8887): Clearing online redo logfile 3 +DG_DATA/CORTEX/ONLINELOG/group_3.263.1039033647
Clearing online log 1 of thread 1 sequence number 193
Clearing online log 2 of thread 1 sequence number 194
Clearing online log 3 of thread 1 sequence number 192
2021-04-24T05:09:45.365212-03:00
NET  (PID:8887): Clearing online redo logfile 1 complete
NET  (PID:8887): Clearing online redo logfile 2 complete
NET  (PID:8887): Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 548987031 (0x20b8e097)
Online log +DG_DATA/CORTEX/ONLINELOG/group_1.261.1039033629: Thread 1 Group 1 was previously cleared
Online log +DG_FRA/CORTEX/ONLINELOG/group_1.257.1039033637: Thread 1 Group 1 was previously cleared
Online log +DG_DATA/CORTEX/ONLINELOG/group_2.262.1039033629: Thread 1 Group 2 was previously cleared
Online log +DG_FRA/CORTEX/ONLINELOG/group_2.258.1039033637: Thread 1 Group 2 was previously cleared
Online log +DG_DATA/CORTEX/ONLINELOG/group_3.263.1039033647: Thread 1 Group 3 was previously cleared
Online log +DG_FRA/CORTEX/ONLINELOG/group_3.259.1039033651: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 6544258

Após isso, podemos criar um DB_LINK apontando para o primary que está em operação e finalizar o processo conforme abaixo, ou seja, normalizando o novo ambiente logical standby:

[oracle@fornix1 trace]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 24 05:49:40 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> SELECT DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
 
DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
cortex                         READ WRITE           PRIMARY
 
SQL> CREATE PUBLIC DATABASE LINK BSS CONNECT TO system IDENTIFIED BY oracle USING 'CORTEXDR';
 
Database link created.
 
SQL> SELECT * FROM DUAL@BSS;
 
D
-
X
 
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY BSS;
 
Database altered.
 
SQL> SELECT DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
 
DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
cortex                         READ WRITE           LOGICAL STANDBY

No alert podemos observar os detalhes da operação:

2021-04-24T05:50:16.440372-03:00
ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY BSS
2021-04-24T05:50:16.540033-03:00
ALTER DATABASE START LOGICAL STANDBY APPLY (cortex)
2021-04-24T05:50:16.540106-03:00
with optional part
NEW PRIMARY BSS.LOCALDOMAIN
LOGSTDBY: End stream parameters:
LOGSTDBY: Local  dbid          [548968087]
LOGSTDBY: Local  first_change# [0]
LOGSTDBY: Local  last_change#  [6541881]
LOGSTDBY: Remote dbid          [548968087]
LOGSTDBY: Remote first_change# [0]
LOGSTDBY: Remote last_change#  [6541881]
LOGSTDBY: Remote sbp_change#   [0]
LOGSTDBY: End stream permitted -- apply state in synch with primary dbid [548968087].
LOGSTDBY: End stream complete.
Attempt to start background Logical Standby process
Starting background process LSP0
2021-04-24T05:50:17.130313-03:00
LSP0 started with pid=65, OS id=12195
2021-04-24T05:50:17.644726-03:00
.... (PID:12145): Database role changed from PRIMARY to LOGICAL STANDBY [dglc.c:1992]
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY BSS

Gerando novo archive no primary:

[oracle@fornix2 trace]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 24 05:51:02 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.
 
SQL> SELECT THREAD#,SEQUENCE#, STATUS FROM V$LOG;
 
   THREAD#  SEQUENCE# STATUS
---------- ---------- ----------------
         1         10 ACTIVE
         1         11 CURRENT
         1          9 INACTIVE

Após alguns minutos (até observando o alert do novo standby), podemos ver que o SQL Apply está habilitado e fazendo seu trabalho:

[oracle@fornix1 trace]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 24 05:55:26 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS F_SCN#,
NEXT_CHANGE# AS N_SCN#, TIMESTAMP,
DICT_BEGIN, DICT_END,
THREAD# AS THR#, APPLIED
FROM DBA_LOGSTDBY_LOG
ORDER BY SEQUENCE#;  2    3    4    5    6
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP           DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------- --- --- ---------- --------
+DG_RECO/CORTEX/ARCHIVELOG/2021_04_24/thread_1_seq_6.450.1070690071
         6    6576069    6580523 2021-04-24:05:54:34 YES NO           1 YES
 
+DG_RECO/CORTEX/ARCHIVELOG/2021_04_24/thread_1_seq_7.451.1070690071
         7    6580523    6580898 2021-04-24:05:54:34 NO  YES          1 YES
 
+DG_RECO/CORTEX/ARCHIVELOG/2021_04_24/thread_1_seq_8.449.1070690073
         8    6580898    6587939 2021-04-24:05:54:34 NO  NO           1 YES
 
 
FILE_NAME
--------------------------------------------------------------------------------
      SEQ#     F_SCN#     N_SCN# TIMESTAMP           DIC DIC       THR# APPLIED
---------- ---------- ---------- ------------------- --- --- ---------- --------
+DG_RECO/CORTEX/ARCHIVELOG/2021_04_24/thread_1_seq_9.453.1070690075
         9    6587939    6592023 2021-04-24:05:54:34 NO  NO           1 YES
 
+DG_RECO/CORTEX/ARCHIVELOG/2021_04_24/thread_1_seq_10.452.1070690075
        10    6592023    6598117 2021-04-24:05:54:34 NO  NO           1 CURRENT
 
 
SQL>

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment

Your email address will not be published.