How To Check Data Guard Status

We will check data guard status in SQL Plus in this article in many ways.

Check 1:

Check for LAG from V$DATAGUARD_STATS View.

Check 2:

It is also possible to see whether Redo Logs are sent and applied to Standby correctly or not, by following the Sequence numbers.

Check 3:

The query below shows the latest archives on Primary and Standby. It shows the last archive produced in Primary and the last archive applied to Standby.

Check 4:

It can be seen if there is an error in the Alert Log from V$DATAGUARD_STATUS View. An alarm can be created by selecting the “Error” column in the Severity column here.

Check 5:

The status of Redo Apply and Redo Transport services can be seen from V$MANAGED_STANDBY View.

NOTE: If you notice, there is no LSP process that performs Redo-Apply job in Logical Standby Databases as a result of the query. The reason is that v$managed_standby view is valid for Physical Standby Database.

Check 6:

The information of the transactions currently processed by SQL APPLY can be seen from the V$LOGSTDBY_TRANSACTION View.

Check 7:

It can be seen which archives are transmitted to the standby when.

Check 8:

You can get information about Standby Redo Logs from V$STANDBY_LOG View by using the following queries.

The combinations of the ARCHIVED and STATUS columns in the above query mean the following;

ARC    STATUS

NO      UNASSIGNED Standby Redo Log has been archived and is suitable for reuse.
YES     UNASSIGNED Standby Redo Log has never been used and is ready for use.
NO     ACTIVE Shows that writing to Standby Redo Log is finished and Standby Redo Log is expected to be archived.
YES    ACTIVE Shows that Standby Redo Log is actively used and is not yet archived.

Check 9:

In cases where Standby Database does not receive Redos, we should check V$ARCHIVE_DEST.

Query ARCHIVE DESTINATION.

Values and meanings of the VALID_NOW column in this View are as follows;

YES This value shows that the Archive log destination has been defined and is valid for the current database role. Usually LOG_ARCHIVE_DEST_1 is defined this way.
WRONG VALID_TYPE This value shows that the Archive log destination has been defined, but there are no Standby Logs to use when in Standby Role.
WRONG VALID_ROLE This value shows that the Archive log destination is incorrectly defined. Online Logs cannot be read while in Standby Role.
UNKNOWN This value shows that the Archive Log destination is not defined.

Check 10:

If there are no problems in the destinations, errors are checked.

Check 11:

If there is no error, the following are checked.

  • TNSNAMES.ORA file,
  • LOG_ARCHIVE_DEST_n parameters,
  • LOG_ARCHIVE_DEST_STATE_n parameters,
  • LISTENER.ORA file,
  • Whether Listener runs on Standby,
  • Whether instances are running is checked on standby.

Queries to Check Standby