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.
Portuguese
English
Spanish