Data Guard Insufficient SRLs – …::: César DBA :::…

Hoje foi me solicitado que fizesse a avaliação de um ambiente Oracle Data Guard 19c single instance para agendamento de um switch, ao realizar a verificação no broker, o status estava com Insufficient SRLs (Standby Redo Logs), abaixo verificação e correção:

Ambientes:

Primary Database:  CDBPRD01
Physical standby database: CDBPRS01

Informações do Data Guard physical standby:

set linesize 300 
SELECT NAME,OPEN_MODE,DB_UNIQUE_NAME,DATABASE_ROLE,PROTECTION_MODE FROM GV$DATABASE;

SQL>

NAME      | OPEN_MODE            | DB_UNIQUE_NAME                 | DATABASE_ROLE    | PROTECTION_MODE
--------- | -------------------- | ------------------------------ | ---------------- | --------------------
CDBPRD01  | MOUNTED              | CDBPRS01                       | PHYSICAL STANDBY | MAXIMUM PERFORMANCE

Validação do physical standby database CDBPRS01 utilizando o broker, recomendo sempre realizar esta validação antes de realizar um switch no Data Guard:

dgmgrl /
dgmgrl> validate database verbose CDBPRS01;


  Database Role:     Physical standby database
  Primary Database:  CDBPRD01

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    CDBPRD01:  On
    CDBPRS01:  On

  Capacity Information:
    Database  Instances        Threads
    CDBPRD01  1                1
    CDBPRS01  1                1

  Managed by Clusterware:
    CDBPRD01:  YES
    CDBPRS01:  YES

  Temporary Tablespace File Information:
    CDBPRD01 TEMP Files:  3
    CDBPRS01 TEMP Files:  2

  Data file Online Move in Progress:
    CDBPRD01:  No
    CDBPRS01:  No

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 0 seconds ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:  Yes
    Gap Status:    No Gap
    Transport Lag:  0 seconds (computed 0 seconds ago)
    Transport Status:  Success

  Log Files Cleared:
    CDBPRD01 Standby Redo Log Files:  Cleared
    CDBPRS01 Online Redo Log Files:   Cleared
    CDBPRS01 Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (CDBPRD01)              (CDBPRS01)
    1         7                       5                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (CDBPRS01)              (CDBPRD01)
    1         7                       1                       Insufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (CDBPRD01)                (CDBPRS01)
    1          256 MBytes                256 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (CDBPRS01)                (CDBPRD01)
    1          256 MBytes                256 MBytes

  Apply-Related Property Settings:
    Property                        CDBPRD01 Value           CDBPRS01 Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     10
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        CDBPRD01 Value           CDBPRS01 Value
    LogShipping                     ON                       ON
    LogXptMode                      ASYNC                    ASYNC
    Dependency                      <empty>                  <empty>
    DelayMins                       0                        0
    Binding                         optional                 optional
    MaxFailure                      0                        0
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE

Como pude ver, o status do validate era de Insufficient SRLs e a quantidade de Standby Redo estavam incorretos:

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (CDBPRD01)              (CDBPRS01)
    1         7                       5                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (CDBPRS01)              (CDBPRD01)
    1         7                       1                       Insufficient SRLs

Ao verificar os erros no Oracle Support, encontrei o Doc ID 1956103.1 informando que a causa do problema é que os grupos de redo standby, foram criados sem nenhuma thread especificada.

Ao verificar os grupos de redo em produção (primario), todos os grupos estavam com a thread 1 corretamente:

SET LINESIZE 180 
set colsep   " | "
SET PAGESIZE  9999
COLUMN member FORMAT A55
COLUMN first_change# FORMAT 99999999999999999999
COLUMN next_change# FORMAT 99999999999999999999
SELECT l.thread#,
       lf.group#,
       lf.member,
       TRUNC(l.bytes/1024/1024) AS size_mb,
       l.status,
	   lf.type  
FROM   gv$logfile lf
       JOIN gv$log l ON l.group# = lf.group#
ORDER BY l.thread#,lf.group#;
SET LINESIZE 150 


   THREAD# |     GROUP# | MEMBER                                                  |    SIZE_MB | STATUS           | TYPE
---------- | ---------- | ------------------------------------------------------- | ---------- | ---------------- | -------
         1 |          1 | +DG_REDO_1/CDBPRD01/ONLINELOG/group_1.263.1091279913    |        256 | INACTIVE         | ONLINE
         1 |          1 | +DG_REDO_2/CDBPRD01/ONLINELOG/group_1.263.1091279915    |        256 | INACTIVE         | ONLINE
         1 |          2 | +DG_REDO_1/CDBPRD01/ONLINELOG/group_2.257.1091279913    |        256 | INACTIVE         | ONLINE
         1 |          2 | +DG_REDO_2/CDBPRD01/ONLINELOG/group_2.257.1091279915    |        256 | INACTIVE         | ONLINE
         1 |          3 | +DG_REDO_1/CDBPRD01/ONLINELOG/group_3.258.1091279913    |        256 | INACTIVE         | ONLINE
         1 |          3 | +DG_REDO_2/CDBPRD01/ONLINELOG/group_3.258.1091279915    |        256 | INACTIVE         | ONLINE
         1 |          4 | +DG_REDO_1/CDBPRD01/ONLINELOG/group_4.259.1091279913    |        256 | INACTIVE         | ONLINE
         1 |          4 | +DG_REDO_2/CDBPRD01/ONLINELOG/group_4.262.1091279915    |        256 | INACTIVE         | ONLINE
         1 |          5 | +DG_REDO_1/CDBPRD01/ONLINELOG/group_5.260.1091279913    |        256 | INACTIVE         | ONLINE
         1 |          5 | +DG_REDO_2/CDBPRD01/ONLINELOG/group_5.259.1091279915    |        256 | INACTIVE         | ONLINE
         1 |          6 | +DG_REDO_1/CDBPRD01/ONLINELOG/group_6.261.1091279913    |        256 | ACTIVE           | ONLINE
         1 |          6 | +DG_REDO_2/CDBPRD01/ONLINELOG/group_6.260.1091279915    |        256 | ACTIVE           | ONLINE
         1 |          7 | +DG_REDO_1/CDBPRD01/ONLINELOG/group_7.262.1091279913    |        256 | CURRENT          | ONLINE
         1 |          7 | +DG_REDO_2/CDBPRD01/ONLINELOG/group_7.261.1091279915    |        256 | CURRENT          | ONLINE

Ao verificar os grupos de redo standby em produção (primario), verifiquei que os grupos 21, 22, 23, 24, 25, 26 e 27 estavam com a thread igual a 0 conforme o Doc ID 1956103.1, abaixo marquei os grupos com problemas com uma seta para facilitar a visualização:

SET LINESIZE 180 
set colsep   " | "
SET PAGESIZE  9999
COLUMN member FORMAT A70
COLUMN first_change# FORMAT 99999999999999999999
COLUMN next_change# FORMAT 99999999999999999999
SELECT l.thread#,
       lf.group#,
       lf.member,
	   l.bytes size_bytes,
       TRUNC(l.bytes/1024/1024) AS size_mb,
	   --TRUNC(l.bytes/1024/1024/1024) AS size_gb,
       l.status,
       l.archived,
       lf.type
FROM   gv$logfile lf
       JOIN gv$STANDBY_LOG l ON l.group# = lf.group#
GROUP BY l.thread#,
       lf.group#,
       lf.member,
	   l.bytes,
	   l.status,
       l.archived,
       lf.type
ORDER BY l.thread#,lf.group#;
SET LINESIZE 150 


         THREAD# |     GROUP# | MEMBER                                                  |    SIZE_MB | STATUS     | ARC | TYPE
      ---------- | ---------- | ------------------------------------------------------- | ---------- | ---------- | --- | -------
==>         0 |         21    | +DG_REDO_1/CDBPRD01/ONLINELOG/group_21.265.1099478785   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         21    | +DG_REDO_2/CDBPRD01/ONLINELOG/group_21.265.1099478785   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         22    | +DG_REDO_1/CDBPRD01/ONLINELOG/group_22.266.1099478785   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         22    | +DG_REDO_2/CDBPRD01/ONLINELOG/group_22.266.1099478785   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         23    | +DG_REDO_1/CDBPRD01/ONLINELOG/group_23.267.1099478785   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         23    | +DG_REDO_2/CDBPRD01/ONLINELOG/group_23.267.1099478785   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         24    | +DG_REDO_1/CDBPRD01/ONLINELOG/group_24.268.1099478785   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         24    | +DG_REDO_2/CDBPRD01/ONLINELOG/group_24.268.1099478787   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         25    | +DG_REDO_1/CDBPRD01/ONLINELOG/group_25.269.1099478787   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         25    | +DG_REDO_2/CDBPRD01/ONLINELOG/group_25.269.1099478787   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         26    | +DG_REDO_1/CDBPRD01/ONLINELOG/group_26.270.1099478787   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         26    | +DG_REDO_2/CDBPRD01/ONLINELOG/group_26.270.1099478787   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         27    | +DG_REDO_1/CDBPRD01/ONLINELOG/group_27.271.1099478787   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         27    | +DG_REDO_2/CDBPRD01/ONLINELOG/group_27.271.1099478787   |        256 | UNASSIGNED | YES | STANDBY
            1 |         20    | +DG_REDO_1/CDBPRD01/ONLINELOG/group_20.264.1099478775   |        256 | UNASSIGNED | YES | STANDBY
            1 |         20    | +DG_REDO_2/CDBPRD01/ONLINELOG/group_20.264.1099478775   |        256 | UNASSIGNED | YES | STANDBY

Ao verificar os grupos de redo no physical standby, todos os grupos estavam com a thread 1 corretamente:

   THREAD# |     GROUP# | MEMBER                                                  |    SIZE_MB | STATUS           | TYPE
---------- | ---------- | ------------------------------------------------------- | ---------- | ---------------- | -------
         1 |          1 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_1.265.1100191299    |        256 | UNUSED           | ONLINE
         1 |          1 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_1.265.1100191299    |        256 | UNUSED           | ONLINE
         1 |          2 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_2.266.1100191299    |        256 | UNUSED           | ONLINE
         1 |          2 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_2.266.1100191301    |        256 | UNUSED           | ONLINE
         1 |          3 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_3.267.1100191301    |        256 | UNUSED           | ONLINE
         1 |          3 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_3.267.1100191301    |        256 | UNUSED           | ONLINE
         1 |          4 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_4.268.1100191301    |        256 | UNUSED           | ONLINE
         1 |          4 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_4.268.1100191301    |        256 | UNUSED           | ONLINE
         1 |          5 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_5.269.1100191303    |        256 | UNUSED           | ONLINE
         1 |          5 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_5.269.1100191303    |        256 | UNUSED           | ONLINE
         1 |          6 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_6.270.1100191303    |        256 | UNUSED           | ONLINE
         1 |          6 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_6.270.1100191303    |        256 | UNUSED           | ONLINE
         1 |          7 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_7.271.1100191305    |        256 | UNUSED           | ONLINE
         1 |          7 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_7.271.1100191305    |        256 | UNUSED           | ONLINE

Ao verificar os grupos de redo standby no physical standby, verifiquei que os grupos 25, 26 e 27 estavam com a thread igual a 0 conforme o Doc ID 1956103.1:

      THREAD# |     GROUP# | MEMBER                                                  |    SIZE_MB | STATUS     | ARC | TYPE
   ---------- | ---------- | ------------------------------------------------------- | ---------- | ---------- | --- | -------
==>         0 |         25 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_25.262.1100189765   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         25 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_25.262.1100189765   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         26 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_26.263.1100189765   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         26 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_26.263.1100189765   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         27 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_27.264.1100189765   |        256 | UNASSIGNED | YES | STANDBY
==>         0 |         27 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_27.264.1100189767   |        256 | UNASSIGNED | YES | STANDBY
            1 |         20 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_20.257.1100189759   |        256 | UNASSIGNED | NO  | STANDBY
            1 |         20 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_20.257.1100189761   |        256 | UNASSIGNED | NO  | STANDBY
            1 |         21 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_21.258.1100189761   |        256 | ACTIVE     | YES | STANDBY
            1 |         21 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_21.258.1100189761   |        256 | ACTIVE     | YES | STANDBY
            1 |         22 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_22.259.1100189761   |        256 | UNASSIGNED | NO  | STANDBY
            1 |         22 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_22.259.1100189763   |        256 | UNASSIGNED | NO  | STANDBY
            1 |         23 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_23.260.1100189763   |        256 | UNASSIGNED | NO  | STANDBY
            1 |         23 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_23.260.1100189763   |        256 | UNASSIGNED | NO  | STANDBY
            1 |         24 | +DG_REDO_1/CDBPRS01/ONLINELOG/group_24.261.1100189763   |        256 | UNASSIGNED | NO  | STANDBY
            1 |         24 | +DG_REDO_2/CDBPRS01/ONLINELOG/group_24.261.1100189763   |        256 | UNASSIGNED | NO  | STANDBY

Agora que ja verifiquei qual é o problema, irei recriar os grupos de standby redo com problemas, tanto em produção quanto no physical standby, abaixo segue procedimento:

Desativei a aplicação e sincronismo do Data Guard no broker e alterei o parâmetro STANDBY_FILE_MANAGEMENT para manual:

Produção:

dgmgrl /
DGMGRL> show configuration;
DGMGRL> EDIT DATABASE CDBPRS01 SET STATE='APPLY-OFF';

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=both sid='*';

No Data Guard physical standby, alterei o parâmetro STANDBY_FILE_MANAGEMENT para manual e cancelei o recover:

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL' scope=both sid='*';
SQL> alter database recover managed standby database cancel;

Apaguei os grupos 21, 22, 23, 24, 25, 26 e 27 de standby redo em produção, e recriei multiplexando com a thread correta igual a 1:

alter database drop standby logfile group 21;
alter database drop standby logfile group 22;
alter database drop standby logfile group 23;
alter database drop standby logfile group 24;
alter database drop standby logfile group 25;
alter database drop standby logfile group 26;
alter database drop standby logfile group 27;

alter database add standby logfile thread 1
group 21 ('+DG_REDO_1','+DG_REDO_2') size 256M,
group 22 ('+DG_REDO_1','+DG_REDO_2') size 256M, 
group 23 ('+DG_REDO_1','+DG_REDO_2') size 256M, 
group 24 ('+DG_REDO_1','+DG_REDO_2') size 256M,
group 25 ('+DG_REDO_1','+DG_REDO_2') size 256M,
group 26 ('+DG_REDO_1','+DG_REDO_2') size 256M,
group 27 ('+DG_REDO_1','+DG_REDO_2') size 256M;

Apaguei os grupos 25, 26 e 27 de standby redo no Data Guard physical standby, e recriei multiplexando com a thread corrta igual a 1:

alter database drop standby logfile group 25;
alter database drop standby logfile group 26;
alter database drop standby logfile group 27;

alter database add standby logfile thread 1
group 25 ('+DG_REDO_1','+DG_REDO_2') size 256M,
group 26 ('+DG_REDO_1','+DG_REDO_2') size 256M,
group 27 ('+DG_REDO_1','+DG_REDO_2') size 256M;

Na produção e no Data Guard physical standby, alterei o parâmetro STANDBY_FILE_MANAGEMENT para automatico:

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=both sid='*';

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' scope=both sid='*';

Habilitei a aplicação e sincronismo do Data Guard no broker em produção:

dgmgrl /
DGMGRL> EDIT DATABASE CDBPRS01 SET STATE='APPLY-ON';

Agora que ativei a aplicação para sincronismo, realizei alguns switch para verificar se o o Data Guard physical standby iria receber as novas sequências para aplicação:

alter system switch all logfile;
alter system switch all logfile;
alter system switch all logfile;
alter system switch all logfile;
alter system switch all logfile;

Verificando gaps no Data Guard physical standby:

set colsep   " | "
SET LINESIZE  300
SET PAGESIZE  9999
SET VERIFY    off
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
SELECT a.thread#,
       b. last_seq "LAST RECEIVED" ,
       a.applied_seq "LAST APPLIED",
       a. last_app_timestamp,
       b.last_seq - a.applied_seq ARC_DIFF
FROM   (SELECT thread#,
               Max(sequence#) applied_seq,
               Max(next_time) last_app_timestamp
        FROM   gv$archived_log
        WHERE  applied = 'YES'
        GROUP  BY thread#) a,
       (SELECT thread#,
               Max (sequence#) last_seq
        FROM   gv$archived_log
        GROUP  BY thread#) b
WHERE  a.thread# = b.thread#;

Após a correção, realizei a validação novamente do physical standby no broker, desta vez o status foi Sufficient SRLs e a quantidade de Standby Redo estavam corretos:

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (CDBPRD01)              (CDBPRS01)
    1         7                       8                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (CDBPRS01)              (CDBPRD01)
    1         7                       8                       Sufficient SRLs

Espero que este post possa te ajudar a solucionar o problema com Insufficient SRLs no Data Guard.