Data Guard Archive Status Check
Job details
| Name: | Data Guard Archive Status Check | |
| 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: | 2.3 | |
| Default schedule: | 10,40 * * * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[version like ‘9%’ | version like ‘10%’]/.[hasengine=‘YES’ & _priv_read_v_archive_dest_status = ‘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: The job is designed to monitor the status of data guard archive logs transfer between the primary and the standby Oracle databases, ensuring the standby database is updated and consistent with the primary.
- Why: This monitoring is crucial for maintaining data availability and integrity in disaster recovery scenarios. Ineffective synchronization between primary and standby databases can lead to data loss or corruption in the event of primary database failure.
- Manual checking: You can check the archive log status manually on the Oracle database by running the following query:
SELECT dest_id, dest_name, database_mode, status, destination, archived_seq#, applied_seq#, error FROM V$ARCHIVE_DEST_STATUS ORDER BY 1;Details of the Monitoring Job
Category
- Availability – The job falls under the ‘Availability’ category indicating its role in ensuring the database’s operational integrity and readiness.
Job Operation
The monitoring job is configured to execute a procedural script that:
- Checks the sequence number difference for archived logs between the primary and standby databases.
- Raises warnings if the sequence number difference exceeds a configurable warning threshold but stays below an alarm threshold.
- Raises alarms when the difference meets or exceeds the alarm threshold.
- Reports back the database archive status, which includes:
- Mode and status of the remote database.
- Sequence numbers of archives.
- Any existing errors on both local and remote databases.
Parameters Monitored
This monitoring job involves key parameters (without descriptions as per request), such as:
- Warning threshold
- Alarm threshold
- Local and remote destination IDs
Scheduled Checks
- The job is scheduled to run periodically at the 10th and 40th minute of every hour, as specified in the setting ‘default-schedule’.
Dependency and Self-checks
- The job has a declared dependency on its own monitoring function and includes the stipulation for cleaning up on failures.
Implementation
The core logic implemented in an Oracle PL/SQL procedure checks the operational and synchronization status of Oracle Data Guard setups.
Report Generation
Data Guard Archive Status Report
- Title: “Data Guard Archive Status”
- Description: The report aims to provide insights into the synchronization state between the primary and standby Oracle databases.
Report Contents
- Archive Setup and Status: Includes information such as destination ID, destination name, database mode, status, destinations, sequence numbers, and errors from the dynamic performance view V$ARCHIVE_DEST_STATUS.
- Parameters related to archives and recovery: Detailed view from V$PARAMETERS reflecting settings associated with archive and recovery processes.
The job, through thorough monitoring and detailed reporting, aids database administrators in maintaining high availability and disaster recovery readiness of Oracle databases using Data Guard technology.