Insufficient SRLs reported by DGMGRL

This is a short blog post about a small issue with the reporting of Standby Redo Logs in DGMGRL during the validation of a database.

Table of Contents

1 Standby Redo Logs
2 Validation of a Database in DGMGRL
3 Conclusion

Standby Redo Logs

In a Data Guard environment, Standby Redo Logs are used to allow real-time apply. With real-time apply, redo of the primary is written by an RFS process to a standby redo log in the standby database and immediately applied.

For single-instance systems, very often the THREAD parameter is omitted.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 SIZE 100M;

But for RAC databases it is required to create Standby Redo Logs for all existing redo threads.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 SIZE 100M; 
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 11 SIZE 100M;

Validation of a Database in DGMGRL

DGMGRL provides the functionality to validate a database, e.g. before a switchover. This validation checks a lot of things, including the existing Standby Redo Logs.

DGMGRL> VALIDATE DATABASE VERBOSE 'DB_SITE2';
...
  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (DB_SITE1)             (DB_SITE2)
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (DB_SITE2)             (DB_SITE1)
    1         3                       0                       Insufficient SRLs

As you can see DGMGRL complains about insufficient Standby Redo Logs in the standby and the primary database. But on both sites, 4 groups were created.

-- Primary
SQL> SELECT group#, thread#, sequence#, status FROM v$standby_log;

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------
        11          0          0 UNASSIGNED
        12          0          0 UNASSIGNED
        13          0          0 UNASSIGNED
        14          0          0 UNASSIGNED

-- Standby
SQL> SELECT group#, thread#, sequence#, status FROM v$standby_log;

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------
        11          1       4313 ACTIVE
        12          1          0 UNASSIGNED
        13          0          0 UNASSIGNED
        14          0          0 UNASSIGNED

From the output you can see, that thread number 0 is used. But why? The reason for this is, when you create a Standby Redo Log without the THREAD parameter, thread 0 is assigned to the group.

But when a Standby Redo Log is required, it switches to thread 1 in my case. Please have a look at group 11, which is currently active. So from a Data Guard point-of-view, we have no problem.

Conclusion

Because of the misleading output of DGMGRL, it is sometimes hard to identify “real issues”. In this case, I recommend creating Standby Redo Log always with the THREAD parameter, even when only one thread exists.

To change the thread, the group must be recreated.

SQL> ALTER DATABASE DROP LOGFILE GROUP 11;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 SIZE 100M;