Data Guard Archive Status

Job details

Name:	Data Guard Archive Status
Platform:	Oracle
Category:	Availability
Description:	Checks that the standby database is receiving archive files from master database.
Long description:	Task checks that the standby database is receiving archive files from the master database.
Version:	3.0
Default schedule:	10,40 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & _priv_read_v_instance = ‘1′ & _priv_read_v_database = ‘1′ & _priv_read_v_archived_log = ‘1′]

Parameters

Name	Default value	Description
warning threshold	5	This parameter is used to control the gap between ARCHIVED_SEQ# values for Master and Standby databases (V$ARCHIVE_DEST_STATUS dictionary performance view).
alarm threshold	20	This parameter is used to control the gap between ARCHIVED_SEQ# values for Master and Standby databases (V$ARCHIVE_DEST_STATUS dictionary performance view).
local dest ID	1	This parameter refers to DEST_ID value (V$ARCHIVE_DEST_STATUS dictionary performance view) for local archive destination.
remote dest ID	2	This parameter refers to DEST_ID value (V$ARCHIVE_DEST_STATUS dictionary performance view) for remote archive destination.

Job Summary

Purpose: This job, titled “Data Guard Archive Status,” is essential for monitoring the status of Oracle Database Data Guard, ensuring that the standby database is consistently receiving archive logs from the primary database.
Why: This monitoring job is critical to confirm the high availability and disaster recovery aspects of database architectures using Oracle Data Guard. It helps detect any discrepancies or delays in log transfer, which can be vital for maintaining the integrity and availability of the standby database.
Manual checking: To manually check the status, the following SQL command can be issued against the database:

SELECT DB_NAME, HOST_NAME, DB_UNIQUE_NAME, REMOTE_HOST, LOG_ARCHIVED, LOG_APPLIED, APPLIED_TIME, LOG_GAP FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = [desired ID];

Job Description

The job runs at the schedule of every hour at minutes 10 and 40 (defined by the setting ‘10,40 * * * *’).
It operates by executing a stored procedure named “Dataguard_Arch_Check,” which gathers data from several dynamic performance views like V$ARCHIVE_DEST_STATUS, V$DATABASE, and V$INSTANCE.
The process primarily focuses on checking the sequence numbers of archived logs and their application on the standby versus the primary, and computes the latency or log gap to identify delays.

Report Details

Report Title: Data Guard Archive Status
Contents: The report generated by dbWatch Control Center from this job will display various pieces of crucial information:
- Primary and Standby Database Names
- Hostnames for the Primary and Standby Databases
- Sequence numbers of logs that have been archived
- Sequence numbers of logs that have been applied
- The application timestamps for the logs
- Current log gaps indicative of any delays or issues

Table Display (Reported Information in dbWatch)

Primary Database	Primary Hostname	Standby Database	Standby Hostname	Log Archived	Log Applied	Applied Time	Current Log Gap
Database Name 1	Hostname 1	Standby DB Name 1	Standby Hostname 1	Number	Number	Timestamp	Number

Operational Logic

The job uses parameters like ‘warning threshold’ and ‘alarm threshold’ to define acceptable and critical levels of log gap respectively, indicating how behind the standby database can be in applying logs.
Alerts could be triggered based on these thresholds if the log gap exceeds predefined limits, enabling proactive measures and troubleshooting.

Dependencies and Implementations

The job depends on its own successful execution data and is aligned to work strictly with Oracle Database instances. It checks privileges on reading certain dynamic views like V$INSTANCE and V$DATABASE which are essential for gathering the required data.

By creating a continuous check loop and generating benchmark reports, this job in dbWatch Control Center effectively ensures the optimal operation and reliability of Oracle Data Guard setups, protecting the databases against potential failures due to unsynchronized data.