Changing Data Guard Protection Mode using Data Broker

High Availability / 14/07/2022

Verificando que o Redo Apply está OFF:

[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 04:27:05 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 DATABASE VERBOSE CORTEXDR;
 
Database - cortexdr
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-OFF
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Active Apply Rate:  (unknown)
  Maximum Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    CORTEXDR
...

Inicializando o apply:

DGMGRL> EDIT DATABASE CORTEXDR SET STATE=APPLY-ON;
Succeeded.
DGMGRL> SHOW DATABASE VERBOSE CORTEXDR;
 
Database - cortexdr
 
  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: 685.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    CORTEXDR
...

No ambiente primary, vamos criar a procedure abaixo para podermos realizar um teste durante o artigo:

SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE HR.TEST_UPDATE( N NUMBER )
IS
 /* This procedure performs sequential update on HR.EMPLOYEES table for testing
    purposes. It doesn’t make any real changes on data but real transactions
    will be generated
 */
 T1 DATE;
 TYPE EMP_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 EMPS EMP_TABLE;
BEGIN
 IF N IS NULL OR N < 1 THEN
    DBMS_OUTPUT.PUT_LINE('INVALID VALUE OF PASSED PARAMETER.');
    RETURN;
 END IF;
 SELECT SYSDATE INTO T1 FROM DUAL;
 SELECT EMPLOYEE_ID
    BULK COLLECT
    INTO EMPS
    FROM HR.EMPLOYEES
    ORDER BY 1;
 
 FOR I IN 1..N LOOP
    FOR J IN 1..EMPS.COUNT LOOP
     UPDATE HR.EMPLOYEES SET SALARY = SALARY * 1 WHERE EMPLOYEE_ID=EMPS(J) ;
     COMMIT;
    END LOOP;
  END LOOP;
 DBMS_OUTPUT.PUT_LINE(TO_CHAR( (SYSDATE-T1)*24*60*60 , '999,999.99') || ' s');
END TEST_UPDATE;
/

[oracle@fornix1 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 23 04:41:43 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> SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE HR.TEST_UPDATE( N NUMBER )
IS
 /* This procedure performs sequential update on HR.EMPLOYEES table for testing
        purposes. It doesn’t make any real changes on data but real transactions
        will be generated
 */
 T1 DATE;
 TYPE EMP_TABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 EMPS EMP_TABLE;
BEGIN
 IF N IS NULL OR N < 1 THEN
        DBMS_OUTPUT.PUT_LINE('INVALID VALUE OF PASSED PARAMETER.');
        RETURN;
 END IF;
 SELECT SYSDATE INTO T1 FROM DUAL;
 SELECT EMPLOYEE_ID
        BULK COLLECT
        INTO EMPS
        FROM HR.EMPLOYEES
        ORDER BY 1;
 
 FOR I IN 1..N LOOP
        FOR J IN 1..EMPS.COUNT LOOP
         UPDATE HR.EMPLOYEES SET SALARY = SALARY * 1 WHERE EMPLOYEE_ID=EMPS(J) ;
         COMMIT;
        END LOOP;
  END LOOP;
 DBMS_OUTPUT.PUT_LINE(TO_CHAR( (SYSDATE-T1)*24*60*60 , '999,999.99') || ' s');
END TEST_UPDATE;
/SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30
 
Procedure created.

Como podemos ver, o modo vigente é o Maximum Performance (que é o valor padrão):

[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 04:47:14 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:
  cortex   - Primary database
    cortexdr - Physical standby database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 45 seconds ago)

Antes de iniciar os testes, vamos definir os parâmetros “NetTimeout”, “ReopenSecs” e “LogXptMode”:

DGMGRL> EDIT DATABASE CORTEXDR SET PROPERTY NetTimeout=20;
Property "nettimeout" updated
DGMGRL> EDIT DATABASE CORTEXDR SET PROPERTY ReopenSecs=20;
Property "reopensecs" updated
DGMGRL> EDIT DATABASE CORTEXDR SET PROPERTY LogXptMode='SYNC';
Property "logxptmode" updated

Já é possível ver a mudança de valor do parâmetro também no primary:

SQL> SHO PARAMETER ARCHIVE_DEST_2
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service="cortexdr", SYNC AFFIR
                                                 M delay=0 optional compression
                                                 =disable max_failure=0 reopen=
                                                 20 db_unique_name="cortexdr" n
                                                 et_timeout=20, valid_for=(onli
                                                 ne_logfile,all_roles)

Finalmente, alterando o Protection Mode para Maximum Availability no Standby:

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxAvailability
  Members:
  cortex   - Primary database
    cortexdr - Physical standby database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 23 seconds ago)

Agora vamos simular o comportamento do Data Guard quando o Standby ficar indisponível. Executando a procedure que criamos no Primary, e analisando o seu tempo de execução (49 segundos):

SQL> set serveroutput on
SQL> exec HR.TEST_UPDATE(1000);
49.00 s
 
PL/SQL procedure successfully completed.

Executando novamente a procedure no primary, mas após o seu disparo, realizando um abort no ambiente standby:

SQL> exec HR.TEST_UPDATE(1000);

SQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;
 
INSTANCE_NAME    STATUS
---------------- ------------
CORTEXDR         MOUNTED
 
SQL> SHU ABORT;
ORACLE instance shut down.

Percebemos que o processo continuou no primary e finalizou com sucesso:

SQL> exec HR.TEST_UPDATE(1000);
42.00 s
 
PL/SQL procedure successfully completed.

Enquanto isso, no alert do Data Broker, temos o report de que o standby está indisponível:

[oracle@fornix1 trace]$ cd /oracle/19.3.0/base/diag/rdbms/cortex/cortex/trace
[oracle@fornix1 trace]$ tail -1000f drccortex.log
 
...
 
2021-03-23T05:03:58.284-03:00
Error: The actual protection level 'Resynchronization' is different from the configured protection mode 'MaxAvailability'.
Redo transport problem detected: redo transport to database cortexdr has the following error:
  ORA-01034: ORACLE not available
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               cortex                            Warning  ORA-16607: one or more members have failed
  Primary Database            cortex                              Error  ORA-16810: multiple errors or warnings detected for the member
  Physical Standby Database   cortexdr                            Error  ORA-01034: ORACLE not available

No configuration do Data Broker também é reportado o problema:

DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxAvailability
  Members:
  cortex   - Primary database
    Error: ORA-16810: multiple errors or warnings detected for the member
 
    cortexdr - Physical standby database
      Error: ORA-1034: ORACLE not available
 
Fast-Start Failover:  Disabled
 
Configuration Status:
ERROR   (status updated 8 seconds ago)

Montando o standby:

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>

Após 20 segundos (que foi o valor definido por nós), a comunicação é restabelecida:

DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxAvailability
  Members:
  cortex   - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode
 
    cortexdr - Physical standby database
      Error: ORA-16525: The Oracle Data Guard broker is not yet available.
 
Fast-Start Failover:  Disabled
 
Configuration Status:
ERROR   (status updated 50 seconds ago)
 
DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxAvailability
  Members:
  cortex   - Primary database
    cortexdr - Physical standby database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 8 seconds ago)

Alterando o Standby para MAXPROTECTION:

[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 05:16:43 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> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPROTECTION;
Succeeded.
DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxProtection
  Members:
  cortex   - Primary database
    cortexdr - Physical standby database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 19 seconds ago)

Executando novamente a procedure no primary, e logo em seguida realizando o abort do Standby:

SQL> set serveroutput on
SQL> exec HR.TEST_UPDATE(1000);

SQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;
 
INSTANCE_NAME    STATUS
---------------- ------------
CORTEXDR         MOUNTED
 
SQL> SHU ABORT;
ORACLE instance shut down.

No alert do primary já é possível ver o problema sendo reportado:

2021-03-23T05:26:50.793285-03:00
LGWR (PID:3541): LAD:2 is UNSYNCHRONIZED
LGWR (PID:3541): All standby destinations have failed
LGWR (PID:3541): *************************************************************************
LGWR (PID:3541): WARN: All standby database destinations have failed
LGWR (PID:3541): WARN: Instance shutdown required to protect primary
LGWR (PID:3541): *************************************************************************
LGWR (ospid: ): terminating the instance due to ORA error

E a execução, que até o momento estava “travada”, finalmente reporta erro (que era o comportamento esperado):

SQL> set serveroutput on
SQL> exec HR.TEST_UPDATE(1000);
 
BEGIN HR.TEST_UPDATE(1000); END;
 
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 7976
Session ID: 98 Serial number: 35155

Podemos ver que o ambiente primary também ficou indisponível:

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@fornix1 trace]$
[oracle@fornix1 trace]$
[oracle@fornix1 trace]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 23 05:29:10 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> !ps -ef | grep pmon
grid      3380     1  0 04:18 ?        00:00:00 asm_pmon_+ASM
oracle    8673  8657  0 05:29 pts/0    00:00:00 /bin/bash -c ps -ef | grep pmon
oracle    8675  8673  0 05:29 pts/0    00:00:00 grep pmon

Montando o standby:

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.

Subindo o primary:

SQL> STARTUP;
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.
Database opened.

Validando se está tudo OK:

[oracle@fornix1 trace]$ dgmgrl sys/oracle@CORTEX
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 05:33:07 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 VERBOSE;
 
Configuration - cortex
 
  Protection Mode: MaxProtection
  Members:
  cortex   - Primary database
    cortexdr - Physical standby database
 
  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '0'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'cortex_CFG'
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS

Voltando o Data Guard ao modo original do ambiente (MAXPERFORMANCE):

[oracle@fornix2 ~]$ dgmgrl sys/oracle@CORTEXDR
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Mar 23 05:35:46 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> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;
Succeeded.
DGMGRL> EDIT DATABASE CORTEXDR SET PROPERTY LogXptMode='ASYNC';
Property "logxptmode" updated
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 48 seconds ago)
 
DGMGRL>

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