Verificando condições do ambiente primary e standby:
[oracle@fornix1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 22 04:48:31 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 NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
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 46 CURRENT
1 44 INACTIVE
1 45 INACTIVE
SQL>
Standby:
[oracle@fornix2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 22 04:49:31 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 NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CORTEX MOUNTED PHYSICAL STANDBY
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;
FLASHBACK_ON
------------------
YES
SQL> SELECT CLIENT_PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
CLIENT_P SEQUENCE# STATUS
-------- ---------- ------------
ARCH 0 CONNECTED
N/A 0 ALLOCATED
N/A 0 ALLOCATED
ARCH 0 CONNECTED
ARCH 45 CLOSING
ARCH 0 CONNECTED
Archival 0 IDLE
LGWR 46 IDLE
UNKNOWN 0 IDLE
9 rows selected.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
SQL> SELECT CLIENT_PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
CLIENT_P SEQUENCE# STATUS
-------- ---------- ------------
ARCH 0 CONNECTED
N/A 0 ALLOCATED
N/A 0 ALLOCATED
ARCH 0 CONNECTED
ARCH 45 CLOSING
ARCH 0 CONNECTED
Archival 0 IDLE
LGWR 46 IDLE
UNKNOWN 0 IDLE
N/A 46 APPLYING_LOG
10 rows selected.
Simulando uma indisponibilidade no ambiente primary, realizando um shutdown abort:
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CORTEX READ WRITE PRIMARY
SQL> SHU ABORT;
ORACLE instance shut down.
SQL> !ps -ef | grep pmon
grid 3325 1 0 04:41 ? 00:00:00 asm_pmon_+ASM
oracle 4689 4329 0 04:54 pts/0 00:00:00 /bin/bash -c ps -ef | grep pmon
oracle 4691 4689 0 04:54 pts/0 00:00:00 grep pmon
Nessa etapa, vamos parar o processo de Redo Apply no standby:
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
CORTEX MOUNTED PHYSICAL STANDBY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
Agora temos condições de realizar no standby a operação de Failover, para convertê-lo em primary:
SQL> ALTER DATABASE FAILOVER TO CORTEXDR;
Database altered.
Abrindo o novo primary:
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
------------------------------ -------------------- ----------------
cortexDR READ WRITE PRIMARY
No alert do novo primary temos os detalhes das operações:
2021-04-22 04:56:52.082000 -03:00
ALTER DATABASE FAILOVER TO CORTEXDR
.... (PID:4137): The Time Management Interface (TMI) is being enabled for role transition
.... (PID:4137): information. This will result in messages beingoutput to the alert log
.... (PID:4137): file with the prefix 'TMI: '. This is being enabled to make the timing of
.... (PID:4137): the various stages of the role transition available for diagnostic purposes.
.... (PID:4137): This output will end when the role transition is complete.
TMI: dbsdrv failover to target BEGIN 2021-04-22 04:56:52.082876
Terminal Recovery requested in process 4137
TMI: adbdrv termRecovery BEGIN 2021-04-22 04:56:52.084076
Attempt to do a Terminal Recovery (CORTEXDR)
TMI: adbdrv termRecovery END 2021-04-22 04:56:52.089336
Media Recovery Start: Managed Standby Recovery (CORTEXDR)
Serial Media Recovery started
NET (PID:4137): Managed Standby Recovery not using Real Time Apply
stopping change tracking
NET (PID:4137): Begin: SRL archival
NET (PID:4137): End: SRL archival
NET (PID:4137): Terminal Recovery timestamp is '04/22/2021 04:56:52'
NET (PID:4137): Terminal Recovery: applying standby redo logs.
NET (PID:4137): Terminal Recovery: thread 1 seq# 46 redo required
NET (PID:4137): Terminal Recovery:
Recovery of Online Redo Log: Thread 1 Group 4 Seq 46 Reading mem 0
Mem# 0: +DG_FRA/CORTEXDR/ONLINELOG/group_4.264.1066479889
Incomplete Recovery applied until change 4943531 time 04/22/2021 04:53:52
Media Recovery Complete (CORTEXDR)
Terminal Recovery: successful completion
NET (PID:4137): Forcing ARSCN to IRSCN for TR SCN:0x00000000004b6eab
NET (PID:4137): Attempt to set limbo arscn SCN:0x00000000004b6eab irscn SCN:0x00000000004b6eab
NET (PID:4137): Resetting standby activation ID 548987031 (0x20b8e097)
stopping change tracking
ALTER DATABASE SWITCHOVER TO PRIMARY (CORTEXDR)
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2021-04-22 04:56:52.862642
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2021-04-22 04:56:52.862705
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2021-04-22 04:56:52.863273
Backup controlfile written to trace file /oracle/19.3.0/base/diag/rdbms/cortexdr/CORTEXDR/trace/CORTEXDR_ora_4137.trc
Standby terminal recovery start SCN: 4943530
RESETLOGS after incomplete recovery UNTIL CHANGE 4943531 time 04/22/2021 04:53:52
NET (PID:4137): ORL pre-clearing operation disabled by switchover
Online log +DG_FRA/CORTEXDR/ONLINELOG/group_1.261.1066479861: Thread 1 Group 1 was previously cleared
Online log +DG_DATA/CORTEXDR/ONLINELOG/group_1.263.1066479867: Thread 1 Group 1 was previously cleared
Online log +DG_FRA/CORTEXDR/ONLINELOG/group_2.262.1066479871: Thread 1 Group 2 was previously cleared
Online log +DG_DATA/CORTEXDR/ONLINELOG/group_2.262.1066479877: Thread 1 Group 2 was previously cleared
Online log +DG_FRA/CORTEXDR/ONLINELOG/group_3.263.1066479879: Thread 1 Group 3 was previously cleared
Online log +DG_DATA/CORTEXDR/ONLINELOG/group_3.261.1066479885: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 4943529
Setting recovery target incarnation to 3
NET (PID:4137): RT: Role transition work is not done
NET (PID:4137): The Time Management Interface (TMI) is being enabled for role transition
NET (PID:4137): information. This will result in messages beingoutput to the alert log
NET (PID:4137): file with the prefix 'TMI: '. This is being enabled to make the timing of
NET (PID:4137): the various stages of the role transition available for diagnostic purposes.
NET (PID:4137): This output will end when the role transition is complete.
NET (PID:4137): Redo network throttle feature is disabled at mount time
NET (PID:4137): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]
Switchover: Complete - Database mounted as primary
Reinstate the Primary Database after Failover to Physical Standby Database
Esta etapa consiste em converter o antigo primary em um novo Standby, quando o mesmo voltar a estar disponível após sua falha original. Caso tenhamos habilitado o recurso de Flashback Database (demonstrado no início deste artigo), esse processo é simples e rápido. Caso contrário, será necessário a recriação do standby.
Foi possível vermos no Alert exposto acima o número do SCN no qual o antigo standby se tornou primary (“Standby became primary SCN: 4943529”), mas também podemos coletar essa informação rodando o seguinte comando no novo primary:
SQL> SELECT DB_UNIQUE_NAME,TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
DB_UNIQUE_NAME TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
------------------------------ ----------------------------------------
cortexDR 4943529
Montando o novo standby e realizando o Flashback usando este SCN:
[oracle@fornix1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 22 05:09:52 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 553648128 bytes
Database Buffers 2013265920 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 4943529;
Flashback complete.
Convertendo o menino para Physical Standby:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
Ligando processo de Redo Apply:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
Gerando archive no primary e checando se o mesmo é aplicado no standby:
SQL> SELECT DB_UNIQUE_NAME FROM V$DATABASE;
DB_UNIQUE_NAME
------------------------------
cortexDR
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> SELECT THREAD#,SEQUENCE#, STATUS FROM V$LOG;
THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 4 CURRENT
1 2 ACTIVE
1 3 ACTIVE
SQL> SELECT CLIENT_PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
CLIENT_P SEQUENCE# STATUS
-------- ---------- ------------
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 3 CLOSING
N/A 0 ALLOCATED
N/A 0 ALLOCATED
Archival 0 IDLE
LGWR 4 IDLE
UNKNOWN 0 IDLE
UNKNOWN 0 IDLE
UNKNOWN 0 IDLE
CLIENT_P SEQUENCE# STATUS
-------- ---------- ------------
N/A 4 APPLYING_LOG
12 rows selected.
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