Snapshot Log(s) rows count

Job details

Name:	Snapshot Log(s) rows count
Platform:	Oracle
Category:	Cluster and Replication
Description:	Checks row count on all snapshot logs for given schema.
Long description:	Task checks the row count of all snapshot logs for a given schema.
Version:	1.3
Default schedule:	30 6,12,18 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘9%’	version like ‘10%’	version like ‘11%’) & _priv_read_dba_segments = ‘1′]

Parameters

Name	Default value	Description
schemas (owners of snapshot logs)	null	Names of schemas for which the snapshot-logs are checked.
warning threshold	100000	The maximum rows count for all snapshot-logs for a selected schema.
alarm threshold	1000000	The maximum rows count for all snapshot-logs for a selected schema.
history threshold	1000	Snapshot logs with rows count higher than the parameter value is send to history table.

Job Summary

Purpose: The purpose of this job is to monitor and manage the row count of snapshot logs across specified Oracle database schemas.
Why: This job is essential because monitoring row counts in snapshot logs can help in managing replication and data consistency issues efficiently. Excessive growth can potentially slow down the database and impact performance.
Manual checking: You can check this manually in the database using the following SQL commands:

select owner, segment_name, count(*) from dba_segments where owner = [schema name] and segment_name like 'MLOG$_%' group by owner, segment_name;

Job Configuration

Database Type: “Oracle”
Database Versions: Supports Oracle versions 9.x to 11.x
Default Execution Schedule: Runs at 6 AM, 12 PM, and 6 PM every day
Category: Cluster and Replication

Job Behavior

“Snapshot Log(s) rows count check” iteratively checks the row count of all snapshot logs for the given schemas.
Thresholds are established to trigger warnings and alarms based on the rows count:
- Warning Threshold: 100,000 rows
- Alarm Threshold: 1,000,000 rows
- Historical Data Threshold: 1,000 rows beyond which data is moved to a history table for tracking trends over time.

Dependencies

This job first verifies that the necessary Oracle segments are accessible.
It depends on its previous executions to gather statistics (dependency to itself).
It integrates with a history table “dbw_snapshotlogs_rows_histr” which collects statistics for snapshot logs over the threshold.

Implementation Details

A set of procedural implementations manipulate data:
- Oracle PL/SQL procedures are used for main checks and logging into history.
- A parameterized script dynamically adjusts to the input schemas ensuring the monitoring is adaptable.
- The procedure checks every snapshot log’s row count against defined thresholds, logs excessive counts, and updates a history-tracking system.

Reports

Table. Snapshot log details

Owner	Snapshot name	Rows Count	Date
Owner1	SNAPSHOT_ABC	450000	2023-01-15
Owner2	SNAPSHOT_DEF	1050000	2023-01-15

Additionally, the reports provide a visual representation of data trends and immediate data statistics which include the total counts and date-wise logging of rows in snapshot logs.

Additional Scripts and Automated Workflows

The automatic ‘cleanup’ mechanism deletes entries older than 30 days from the history table to conserve space and maintain relevant data records.
Alerts and warnings are processed based on the surpassing of threshold values, triggering notifications to administrators to take immediate action or review.

Conclusion

This dbWatch Control Center task is crucial for maintaining an efficient, performance-optimized Oracle database environment by actively managing and auditing the size and growth of snapshot logs.