Dataguard standby archivelog gap
Job details
| Name: | Dataguard standby archivelog gap |
| Platform: | Oracle |
| Category: | Cluster and Replication |
| Description: | Checks refresh date of scheduled jobs in refresh group(s). |
| Long description: | |
| Version: | 1.0 |
| Default schedule: | 15m |
| Requires engine install: | No |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| replicationgroup | DGRAC | Replication group metadata tag to identify primary and standby instances that are connected |
| gap_alarm_threshold | 10 | Gap alarm threshold |
| gap_warning_threshold | 5 | Gap warning threshold |
Job Summary
- “Purpose”: The purpose of this job is to monitor and identify any gaps in the archivelog sequence numbers between primary and standby Oracle database instances within a specified replication group. This helps ensure that data is being replicated accurately and consistently across database instances, critical for disaster recovery and data integrity.
- “Why”: This job is crucial as it detects mismatches or delays in log sequence synchronization, which can signify potential issues in the database replication process. Quick identification and resolution of these gaps help maintain the health and performance of the databases, minimizing data loss risks during an unplanned failover or disaster recovery.
- “Manual checking”: You can check this manually in the database by issuing commands to view the redo log sequence numbers for primary and standby instances and comparing them to identify any significant gaps.
SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG ORDER BY THREAD#, SEQUENCE#;Implementation Details
The monitoring job defines several elements to perform its operations effectively:
- “Name and Version”: It is identified as “Oracle Dataguard gap noschema”, version 1.0.
- “Database Compatibility”: This job is specifically compatible with Oracle databases that operate as Physical Standby as part of a Dataguard configuration.
- “Execution Schedule”: The job is scheduled to run every 15 minutes, allowing timely detection of issues.
- “Parameters”: It expects parameters such as:
- replication group identifier (e.g., “DGRAC“)
- gap alarm threshold
- gap warning threshold
These parameters help in defining the specific replication group to monitor and the thresholds for triggering warnings or alarms based on the severity of the log sequence gap.
Job Functionality
The script performs the following steps:
- It initializes variables for tracking status and counts of logged sequences.
- Extracts archivelog sequence numbers for up to ten redo threads from the primary and standby database instances that match the specified replication group.
- Compares the sequence numbers across the primary and standby instances:
- If the sequences do not match up to the defined “warning” or “alarm” thresholds, it generates the appropriate alert message.
- It identifies gaps which exceed the thresholds and presents detailed discrepancies between expected and actual sequence numbers.
After running the necessary checks, it updates the job status based on the findings:
- Status 0: No gaps found; synchronization is healthy.
- Status 1: Warnings about potential discrepancies.
- Status 2: Alarms for significant discrepancies requiring immediate attention.
Reporting
The job outcome is visually presented in a report format under the job title “Dataguard standby archivelog gap status”. The report includes:
- A detailed log of any discrepancies found between thread sequences.
- A presentation table that lists details about the gaps, which can be critical for auditing and troubleshooting purposes.
The structured report ensures that database administrators can quickly understand and respond to synchronization issues within the Oracle Dataguard environment.