Monitor SYS.AUD$ table size

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor the size of the Oracle SYS.AUD$ table, which contains audit trail records. The job checks if the size of the table exceeds predefined warning and alarm thresholds in megabytes (MB).
• Why: Monitoring the SYS.AUD$ table size is critical because an excessively large audit table can impact database performance and availability by consuming significant disk space. It also helps in ensuring that the database remains compliant with audit policies and can efficiently handle auditing without impacting overall performance.
• Manual checking: You can check the size of the SYS.AUD$ table manually in the database by issuing the following SQL command:

select round(bytes/1048576,1) as "Table Size in MB" from dba_segments where segment_name='AUD$' and owner='SYS';

Essential Details

• Version: 1.5
• Company: dbwatch.com
• Default Schedule: Every 15 minutes

Implementation

The following SQL procedures and tables are created to support this monitoring job:

CREATE table dbw_aud$_size_tab(histr_date date, tab_size number, rec_count number)
CREATE table dbw_aud$_size_histr_tab(histr_date date, tab_size number, rec_count number)
CREATE or replace PROCEDURE dbw_aud$_table_size_proc (taskid in number) ...

This procedure checks the table size and inserts records into history tables to track size changes over time. It conditionally updates a monitoring table based on the current size relative to the threshold settings and records audit trail information.

Dependencies

• Job Dependency: Self-referential (depends on its own procedure).
• Histories: It depends on history tables for size and record count which include “dbw_aud$_table_size_proc,” “dbw_aud$_size_histr_tab,” and “dbw_aud$_size_tab.”

Job Parameters

Reporting

This monitoring job generates reports containing the following details:

• “AUD$ table size check” indicating the current size and record count for audit trails.
• Information on the number of audit records for each type of audit action.
• Size and record count history with statistical charts.

Particularly, there are tables and charts generated as part of the report showing audit-related statistics and history. This comprehensive reporting helps in the analytical review of how the audit table grows over time, enabling DBAs to make informed decisions on capacity management and performance tuning tailored to audit storage.

Privileges and Security Checks

• The job is subject to prerequisite checks for necessary Oracle privileges such as reading from system views (`dba_segments`, `sys.aud$`, `v$parameter`) which are essential to compute table size and ensure successful execution of the job. These privileges checks help in maintaining the security and integrity of the database management operations.

Cleanup and Error Handling

• In case of failure, the job includes mechanisms to clean up erroneous records and data entries from the monitoring tables and history tables to maintain data accuracy and system stability.

This monitoring job is vital for maintaining optimal database performance and ensuring compliance with data governance standards through effective audit log management.