A operação de Failover em um ambiente Data Guard é a resposta a um evento de falha, ou seja, uma situação não planejada, ocorrendo devido indisponibilidade do Primary. Como consequência, o Standby passa a operar como Primary (no modo maximum performance), e caso queiramos mudar esse modo para outro valor, é necessário antes realizar o “reinstate” do primary antigo, ou criar um novo standby database.
Para esse artigo, vamos simular de maneira controlada esse evento. Checando o ambiente primary,standby e que o Redo Apply (por ser um physical standby) está operando normalmente:
[oracle@fornix1 ~]$ dgmgrl sys/oracle@CORTEX
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 20:30:39 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cortex"
Connected as SYSDBA.
DGMGRL> SHOW CONFIGURATION;
Configuration - cortex
Protection Mode: MaxPerformance
Members:
cortex - Primary database
cortexdr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 12 seconds ago)
DGMGRL> SHOW DATABASE CORTEX
Database - cortex
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
cortex
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE CORTEXDR;
Database - cortexdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: OFF
Instance(s):
CORTEXDR
Database Status:
SUCCESS
DGMGRL>
Para simular o evento de falha, vamos logar no banco primary e realizar um shutdown abort:
[oracle@fornix1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 20:34: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 INSTANCE_NAME,STATUS FROM V$INSTANCE;
INSTANCE_NAME STATUS
---------------- ------------
cortex OPEN
SQL> SHU ABORT;
ORACLE instance shut down.
SQL> !ps -ef | grep pmon
grid 3398 1 0 04:45 ? 00:00:02 asm_pmon_+ASM
oracle 31763 31728 0 20:34 pts/1 00:00:00 /bin/bash -c ps -ef | grep pmon
oracle 31765 31763 0 20:34 pts/1 00:00:00 grep pmon
Uma vez identificado a falha, e antes de realizar o failover, podemos verificar se o standby está apto para esta operação com o comando abaixo:
[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 20:35:59 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cortexDR"
Connected as SYSDBA.
DGMGRL> VALIDATE DATABASE VERBOSE CORTEXDR;
Database Role: Physical standby database
Primary Database: cortex
Warning: primary database was not reachable
Ready for Switchover: No
Ready for Failover: Yes (Primary Not Running)
Flashback Database Status:
cortex : Unknown
cortexdr: On
Capacity Information:
Database Instances Threads
cortex Unknown Unknown
cortexdr 1 1
Managed by Clusterware:
cortex : Unknown
cortexdr: YES
Validating static connect identifier for the primary database cortex...
ORA-01017: invalid username/password; logon denied
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
Temporary Tablespace File Information:
cortex TEMP Files: Unknown
cortexdr TEMP Files: 1
Data file Online Move in Progress:
cortex: Unknown
cortexdr: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 91 seconds ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: No
Gap Status: Unknown
Transport Lag: 0 seconds (computed 91 seconds ago)
Transport Status: Success
Log Files Cleared:
cortex Standby Redo Log Files: Unknown
cortexdr Online Redo Log Files: Unknown
cortexdr Standby Redo Log Files: Unknown
Apply-Related Property Settings:
Property cortex Value cortexdr Value
DelayMins 0 0
ApplyParallel AUTO AUTO
ApplyInstances 0 0
Transport-Related Property Settings:
Property cortex Value cortexdr Value
LogShipping ON ON
LogXptMode ASYNC ASYNC
Dependency <empty> <empty>
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
ReopenSecs 300 20
NetTimeout 30 20
RedoCompression DISABLE DISABLE
Uma vez pronto, podemos realizar o failover:
DGMGRL> FAILOVER TO CORTEXDR;
Performing failover NOW, please wait...
Failover succeeded, new primary is "cortexdr"
Checando nova configuração:
[oracle@fornix2 trace]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 20:44:10 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "CORTEXDR"
Connected as SYSDBA.
DGMGRL> SHOW CONFIGURATION;
Configuration - cortex
Protection Mode: MaxPerformance
Members:
cortexdr - Primary database
cortex - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 19 seconds ago)
DGMGRL> SHOW DATABASE CORTEX;
Database - cortex
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
cortex
Database Status:
DISABLED - ORA-16661: the standby database needs to be reinstated
DGMGRL> SHOW DATABASE CORTEXDR;
Database - cortexdr
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
CORTEXDR
Database Status:
SUCCESS
Performing Primary Database Reinstate
Montando o primary database antigo:
[oracle@fornix1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 20 20:51:39 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>
Conectando no novo primary database, e realizando a operação de reinstate do primary antigo:
[oracle@fornix2 trace]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 20 20:55:27 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "CORTEXDR"
Connected as SYSDBA.
DGMGRL> REINSTATE DATABASE CORTEX;
Reinstating database "cortex", please wait...
Reinstatement of database "cortex" succeeded
Checando nova configuração:
DGMGRL> SHOW CONFIGURATION;
Configuration - cortex
Protection Mode: MaxPerformance
Members:
cortexdr - Primary database
cortex - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 36 seconds ago)
DGMGRL> SHOW DATABASE CORTEX;
Database - cortex
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 44.00 KByte/s
Real Time Query: OFF
Instance(s):
cortex
Database Status:
SUCCESS
DGMGRL> SHOW DATABASE CORTEXDR;
Database - cortexdr
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
CORTEXDR
Database Status:
SUCCESS
A partir daqui, se quisermos voltar o ambiente na condição original, podemos realizar um switchover conforme abaixo:
DGMGRL> VALIDATE DATABASE CORTEX;
Database Role: Physical standby database
Primary Database: cortexdr
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Managed by Clusterware:
cortexdr: YES
cortex : YES
Log Files Cleared:
cortexdr Standby Redo Log Files: Cleared
cortex Online Redo Log Files: Not Cleared
cortex Standby Redo Log Files: Available
Transport-Related Property Settings:
Property cortexdr Value cortex Value
ReopenSecs 20 300
NetTimeout 20 30
DGMGRL> SWITCHOVER TO CORTEX;
Performing switchover NOW, please wait...
Operation requires a connection to database "cortex"
Connecting ...
Connected to "cortex"
Connected as SYSDBA.
New primary database "cortex" is opening...
Oracle Clusterware is restarting database "cortexdr" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "cortexDR"
Connected to "cortexDR"
Switchover succeeded, new primary is "cortex"
DGMGRL>
Ambiente operando nas condições originais:
DGMGRL> SHOW CONFIGURATION;
Configuration - cortex
Protection Mode: MaxPerformance
Members:
cortex - Primary database
cortexdr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 2 second ago)
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