MV Refresh Group(s)

Job details

Name:	MV Refresh Group(s)
Platform:	Oracle
Category:	Cluster and Replication
Description:	Checks refresh date of scheduled jobs in refresh group(s).
Long description:	Task checks refresh date of all scheduled jobs in refresh group(s).
Version:	1.3
Default schedule:	* * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘10%’	version like ‘11%’	version like ‘8%’	version like ‘9%’)]

Parameters

Name	Default value	Description
schemas (refresh jobs)	null	Names of schemas for which the refresh jobs are checked.
warning threshold	10	The maximum number of minutes the LAST REFRESH date value, for a refresh group,
alarm threshold	60	The maximum number of minutes the LAST REFRESH date value, for a materialized view,
excl. job ID	null	List of job ID excluded from being checked.

Job Summary

Purpose

The purpose of this job is to monitor refresh dates of all scheduled jobs within refresh groups in Oracle databases, specifically targeting compatibility with Oracle versions like ‘10%’, ‘11%’, ‘8%’, and ‘9%’.
This is crucial for ensuring that data in materialized views (MVs) is up-to-date, which is vital for maintaining data integrity and performance in applications relying on these views.

Why

This job is important to proactive system management by:
- Ensuring that the materialized views are refreshed regularly and are not stale.
- Helping quickly identify potential issues with scheduled refresh jobs, including checking for any broken jobs.
- Monitoring helps in avoiding performance degradation or incorrect data consumption by applications that depend on these materialized views.
If the refresh dates fall behind the defined thresholds (warning at 10 minutes and alarm at 60 minutes), it can indicate underlying problems such as job failures, system issues, or need for optimization.

Manual Checking

To manually check the status of the refresh groups and jobs, the following SQL commands can be issued:

select job "Job", log_user "Log user", schema_user "Schema user", broken "Broken" from dba_jobs where job in (select job from dba_refresh) order by job asc;

Table: Refresh Groups Overview

Owner	Refresh Group Name	Job	Next Refresh
Rowner	Rname	Job	Next Refresh

Table: Materialized Views Refresh Status

Owner	MV Name	Last Refresh	Next Refresh
Owner	Table Name	Last Refresh	Start With

Data Sources and Logic

This monitoring task retrieves data from Oracle’s internal views such as dba_refresh, dba_jobs, and dba_snapshots to compile a comprehensive report about:
- Current refresh jobs and their state.
- Scheduling details for these jobs.
- Specific exclusions and thresholds as set by the user parameters.

Procedure and Processing

The job includes an Oracle procedure ‘mv_refresh_group_check’ that:
- Runs checks against all refresh groups specified for the schemas included in parameters.
- Evaluates each refresh group against warning and alarm thresholds for last refresh time, and logs entries for required actions or alerts.
- Includes mechanisms to exclude certain job IDs from checks as specified.
- Has error handling constructs to log exceptions, ensuring any issues during the checks are captured for troubleshooting.

Output Generation

The job provides formatted output and logs that detail:
- Groups and status verified.
- Any refresh groups crossing the warning or alarm thresholds.
- Recommendations or direct actions (like enabling a broken job using DBMS_JOB package commands).

Further Actions

For any warnings or alarms, it might be necessary to:
- Check the underlying cause for delayed refreshes.
- Resolve any issues with the database environment or job configuration.
- Consider optimizing the refresh strategy or infrastructure if frequent delays or problems are detected.

This comprehensive approach ensures active monitoring and management of Oracle materialized views refresh jobs, aiding in maintaining optimal system performance and data accuracy.