Snapshot Log(s) size

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and check the size of all snapshot logs in the database, ensuring they remain within acceptable limits.
• Why: This job is critical to ensure that the sizes of the snapshot logs do not grow excessively, which might affect the performance or storage capacity of the Oracle database servers.
• Manual checking: This can be manually checked in the database through specific SQL commands, focusing on segments related to snapshot logs:

SELECT owner, COUNT(*) AS snap_count, ROUND(SUM(bytes)/1048576) AS size_MB FROM dba_segments WHERE segment_name LIKE 'MLOG$_%' GROUP BY owner;

Job Details

• Description: The job automates the process of monitoring snapshot log sizes by analyzing all relevant logs across specified schemas, evaluating their total size, and comparing it with predefined alarm and warning thresholds.
• Version: 2.3
• Company: dbwatch.com
• Default Schedule: Scheduled to run three times a day at 6 AM, 12 PM, and 6 PM.

Job Actions

• Upon detection of sizes exceeding the warning threshold but staying below the alarm threshold, the job marks the check status accordingly.
• If the size breaches the alarm threshold, further actions or notifications are triggered to handle this exceptional case.
• The job creates and updates an historical record (dbw_snapshotlogs_size_histr) to track changes over time in snapshot log sizes.

Dependencies and Implementations

The job has two main dependencies:

• Internal dependency: It relies on its procedure to execute the checks (snapshot_log_size_check).
• External dependency: It utilizes a history table (dbw_snapshotlogs_size_histr) where each execution’s data regarding the snapshot logs are stored.

The primary function of the job is defined in the Oracle PL/SQL procedure snippet provided within the code section of the implementation:

<< SQL and PL/SQL implementation details as described >>

Reporting

• Overview: The job provides reports that detail current and historical data on snapshot log sizes for database schemas configured within the job parameters.
• Reports include:
  • Snapshot Log(s) Size Check detailed by owner, count, and total size.
  • Historical view charting snapshot log sizes over time.
  • A focused report on the top 10 largest snapshot logs by size.

Configuration

Given its crucial role, table creation for logging historical details and the procedure for the check are automatically handled upon job installation, with a setting to enforce installation irrespective of existing configurations (force-install).

In summary, this monitoring job plays an integral role in proactive database management by keeping snapshot log sizes under check to prevent potential storage and performance issues within an Oracle database environment.