Ambiente primary:
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CORTEX READ WRITE PRIMARY
Logical Standby:
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CORTEXDR READ WRITE LOGICAL STANDBY
Iniciando processos de SQL Apply:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
Checando o número de sequence do primary:
SQL> SELECT THREAD#,SEQUENCE#, STATUS FROM V$LOG;
THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 193 CURRENT
1 191 INACTIVE
1 192 INACTIVE
Vemos que os archives foram enviados para o Logical:
2021-04-19T04:29:03.468370-03:00
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 192, +DG_RECO/cortexdr/archivelog/cortex1_192_1039033628.arc
2021-04-19T04:29:03.469272-03:00
LOGMINER: End mining logfile for session 1 thread 1 sequence 192, +DG_RECO/cortexdr/archivelog/cortex1_192_1039033628.arc
Criando mais archive no primary e acompanhando o alert do standby:
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
2021-04-19T04:32:23.234798-03:00
rfs (PID:4037): Selected LNO:4 for T-1.S-194 dbid 548968087 branch 1039033628
2021-04-19T04:32:23.903474-03:00
rfs (PID:4451): Selected LNO:5 for T-1.S-193 dbid 548968087 branch 1039033628
2021-04-19T04:32:25.622455-03:00
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 193, +DG_FRA/CORTEXDR/ONLINELOG/group_5.267.1069046195
2021-04-19T04:32:26.012330-03:00
RFS LogMiner: Registered logfile [+DG_RECO/cortexdr/archivelog/cortex1_193_1039033628.arc] to LogMiner session id [1]
2021-04-19T04:32:27.258796-03:00
LOGMINER: End mining logfile for session 1 thread 1 sequence 193, +DG_FRA/CORTEXDR/ONLINELOG/group_5.267.1069046195
2021-04-19T04:32:27.331397-03:00
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 194, +DG_FRA/CORTEXDR/ONLINELOG/group_4.269.1069046175
Em um nível mais granular, podemos checar o gap de SCN comparando o valor das duas colunas abaixo, no standby:
SQL> SELECT MINING_SCN,LATEST_SCN FROM V$LOGSTDBY_PROGRESS;
MINING_SCN LATEST_SCN
---------- ----------
6544296 6544300
Quando isso ocorre, podemos ver o status atual no SQL Apply no standby:
SQL> SELECT STATUS FROM V$LOGSTDBY_PROCESS WHERE TYPE='READER';
STATUS
--------------------------------------------------------------------------------
ORA-16242: Processing log file (thread# 1, sequence# 194)
No ambiente primary, já podemos disparar o comando abaixo de “prepare” do ambiente para a operação de switchover. Podemos observar a mudança de valor da coluna “switchover status” do banco:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;
Database altered.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER
Realizando a mesma atividade no atual standby:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER
Neste momento, podemos cancelar a operação de switchover caso seja necessário, emitindo o seguinte comando (que não será neste artigo):
ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;
Disparando processo no primary:
SQL> alter database commit to switchover to logical standby;
Database altered.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
LOGICAL STANDBY NOT ALLOWED
Alert do primary antigo:
2021-04-19T05:10:56.047327-03:00
NET (PID:4009): Archived Log entry 363 added for T-1.S-200 ID 0x20b8e097 LAD:1
2021-04-19T05:10:56.048266-03:00
LOGSTDBY: Switchover complete (cortex)
LOGSTDBY: enabling scheduler job queue processes.
2021-04-19T05:10:56.048356-03:00
JOBQ: re-enabling CJQ0
Completed: alter database commit to switchover to logical standby
Rodando processo no standby antigo:
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY LOG SWITCH GAP
Alert do antigo standby:
2021-04-19T05:14:57.368964-03:00
LOGSTDBY: Database guard disabled. User transactions are now permitted.
LOGSTDBY: enabling scheduler job queue processes.
2021-04-19T05:14:57.371749-03:00
JOBQ: re-enabling CJQ0
Completed: alter database commit to switchover to primary
Porém 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)” (Link AQUI) recomanda que façamos a criação de alguns archives no novo primary para normalizar:
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY LOG SWITCH GAP
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> /
System altered.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
Por fim, podemos iniciar o SQL Apply no novo standby:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
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