FSFO (Fast-Start Failover Operation) requested by DBMS_DG.INITIATE_FS_FAILOVER

High Availability / 14/07/2022

A package DBMS_DG permite que as aplicações notifiquem o primary database ou o fast-start failover target a iniciar a operação de Failover, quando a aplicação se depara com alguma condição que o justifique. Nesse artigo vamos simular essa operação, tomando como referência o ambiente configurado neste meu último post.

Validando condições do nosso ambiente Data Guard:

[oracle@fornix1 ~]$ dgmgrl sys/oracle@CORTEX
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 27 04:44:22 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: Enabled in Potential Data Loss Mode
 
Configuration Status:
SUCCESS   (status updated 39 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: 36.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    CORTEXDR
 
Database Status:
SUCCESS
 
DGMGRL>

Em paralelo podemos ficar “observando” o Observer:

DGMGRL> EDIT DATABASE CORTEXDR SET STATE=APPLY-ON;
Succeeded.
DGMGRL> START OBSERVER FILE='/home/oracle/CORTEXFSFO.dat';
[W000 2021-04-27T04:43:09.600-03:00] FSFO target standby is cortexdr
Observer 'fornix2' started
[W000 2021-04-27T04:43:09.665-03:00] Observer trace level is set to USER
[W000 2021-04-27T04:43:09.665-03:00] Try to connect to the primary.
[W000 2021-04-27T04:43:09.665-03:00] Try to connect to the primary cortex.
[W000 2021-04-27T04:43:09.878-03:00] The standby cortexdr is ready to be a FSFO target
[W000 2021-04-27T04:43:09.878-03:00] Connection to the primary restored!
[W000 2021-04-27T04:43:11.879-03:00] Disconnecting from database cortex.

Para simular a solicitação de Failover por parte da aplicação, podemos executar o bloco PL/SQL abaixo, que faz a requisição e recebe um retorno em código sobre o efeito da execução (os detalhes podem ser vistos na documentação oficial AQUI):

SET SERVEROUTPUT ON
DECLARE
 v_status INTEGER;
 v_error  VARCHAR2(300);
BEGIN
  v_status := dbms_dg.initiate_fs_failover('Application Failover Requested');
  SELECT 
     DECODE(v_status,0,'normal, successful completion',
               16646,'Fast-Start Failover is disabled',
               16666,'unable to initiate Fast-Start Failover on a standby database',
               16817,'unsynchronized Fast-Start Failover configuration',
               16819,'Fast-Start Failover observer not started',
               16820,'Fast-Start Failover observer is no longer observing this database',
               16829,'lagging Fast-Start Failover') INTO v_error
   FROM dual; 
  dbms_output.put_line('Actual Status = ORA-' ||v_status||' : '||v_error);
END;
/

Log da execução:

[oracle@fornix1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 27 04:48:51 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> SET SERVEROUTPUT ON
DECLARE
 v_status INTEGER;
 v_error  VARCHAR2(300);
BEGIN
  v_status := dbms_dg.initiate_fs_failover('Application Failover Requested');
  SELECT
     DECODE(v_status,0,'normal, successful completion',
               16646,'Fast-Start Failover is disabled',
               16666,'unable to initiate Fast-Start Failover on a standby database',
               16817,'unsynchronized Fast-Start Failover configuration',
               16819,'Fast-Start Failover observer not started',
               16820,'Fast-Start Failover observer is no longer observing this database',
               16829,'lagging Fast-Start Failover') INTO v_error
   FROM dual;
  dbms_output.put_line('Actual Status = ORA-' ||v_status||' : '||v_error);
END;
/SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
Actual Status = ORA-0 : normal, successful completion
 
PL/SQL procedure successfully completed.
 
SQL>

Eis que já podemos ver no Observer que a operação de Failover foi disparada:

2021-04-27T04:49:36.826-03:00
Initiating Fast-Start Failover to database "cortexdr"...
[S002 2021-04-27T04:49:36.826-03:00] Initiating Fast-start Failover.
Performing failover NOW, please wait...

Failover executado com sucesso:

2021-04-27T04:49:36.826-03:00
Initiating Fast-Start Failover to database "cortexdr"...
[S002 2021-04-27T04:49:36.826-03:00] Initiating Fast-start Failover.
Performing failover NOW, please wait...
Failover succeeded, new primary is "cortexdr"
2021-04-27T04:50:19.630-03:00
[S002 2021-04-27T04:50:19.630-03:00] Fast-Start Failover finished...
[W000 2021-04-27T04:50:19.630-03:00] Failover succeeded. Restart pinging.
[W000 2021-04-27T04:50:20.050-03:00] Primary database has changed to cortexdr.
[W000 2021-04-27T04:50:20.050-03:00] New standby database will not be reinstated.
[W000 2021-04-27T04:50:20.051-03:00] Try to connect to the primary.
[W000 2021-04-27T04:50:20.051-03:00] Try to connect to the primary cortexdr.
[W000 2021-04-27T04:50:20.371-03:00] The standby cortex needs to be reinstated
[W000 2021-04-27T04:50:20.371-03:00] Try to connect to the new standby cortex.
[W000 2021-04-27T04:50:20.371-03:00] Connection to the primary restored!
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
 
Unable to connect to database using cortex
[W000 2021-04-27T04:50:22.373-03:00] Disconnecting from database cortexdr.
[W000 2021-04-27T04:50:50.431-03:00] Try to connect to the new standby cortex.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Montando o novo standby:

[oracle@fornix1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 27 04:56:29 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>

No FSFO, seria normal após disponibilizar o novo standby, já ser executado automaticamente o reinstate. Porém percebi que não é esse o comportamento:

Unable to connect to database using cortex
[W000 2021-04-27T04:56:50.979-03:00] Try to connect to the new standby cortex.
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
 
Unable to connect to database using cortex
[W000 2021-04-27T04:57:21.029-03:00] Try to connect to the new standby cortex.
[W000 2021-04-27T04:57:22.038-03:00] Connection to the new standby restored!
[W000 2021-04-27T04:57:24.038-03:00] Try to connect to the new standby cortex.
[W000 2021-04-27T04:57:26.043-03:00] Try to connect to the new standby cortex.
[W000 2021-04-27T04:57:29.046-03:00] Try to connect to the new standby cortex.

Desse modo faremos o reinstate na mão:

[oracle@fornix1 admin]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Apr 27 05:02:16 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
DGMGRL>

Processo reportado também no Observer:

2021-04-27T05:03:21.184-03:00
[W000 2021-04-27T05:03:22.183-03:00] Successfully reinstated database cortex.
[W000 2021-04-27T05:03:24.189-03:00] The standby cortex needs to be reinstated
[W000 2021-04-27T05:03:24.190-03:00] Try to connect to the new standby cortex.
[W000 2021-04-27T05:03:27.191-03:00] Try to connect to the primary cortexdr.
[W000 2021-04-27T05:03:27.195-03:00] The standby cortex has been reinstated.

Configuração atual do Data Guard:

DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxPerformance
  Members:
  cortexdr - Primary database
    cortex   - (*) Physical standby database
 
Fast-Start Failover: Enabled in Potential Data Loss Mode
 
Configuration Status:
SUCCESS   (status updated 23 seconds ago)

Realizando switchover para voltar o ambiente às condições originais:

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 "cortexDR"
Connected to "cortexDR"
Switchover succeeded, new primary is "cortex"
DGMGRL>

DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxPerformance
  Members:
  cortex   - Primary database
    cortexdr - (*) Physical standby database
 
Fast-Start Failover: Enabled in Potential Data Loss Mode
 
Configuration Status:
SUCCESS   (status updated 24 seconds 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.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish