Snapshot Log(s) size
Job details
Name: | Snapshot Log(s) size | ||
Platform: | Oracle | ||
Category: | Cluster and Replication | ||
Description: | Checks size of all snapshot logs. | ||
Long description: | Task checks the size of all snapshot logs. | ||
Version: | 2.3 | ||
Default schedule: | 10 6,12,18 * * | ||
Requires engine install: | Yes | ||
Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘9%’ | version like ‘10%’ | version like ‘11%’)] |
Parameters
Name | Default value | Description |
---|---|---|
schemas (owners of snapshot logs) | null | Names of schemas for which the snapshot-logs are checked. |
warning threshold | 300 | The maximum size (in MB) of all snapshot-logs for a selected schema. |
alarm threshold | 500 | The maximum size (in MB) of all snapshot-logs for a selected schema. |
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.