Instance alert log

Job details

Name:	Instance alert log
Platform:	Oracle
Category:	Availability
Description:	Reads and checks errors in the V$DIAG_ALERT_EXT performance view contained in the XML decoded version of the XML version of Alert log file.
Long description:	Task reads and checks errors in the V$DIAG_ALERT_EXT performance view which contains the XML decoded version of the XML version of the Alert log file.
Version:	5.4
Default schedule:	0,5,10,15,20,25,30,35,40,45,50,55 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & twelve_and_above=‘1′ & maj_min_version != ‘12.1′ & has_x_dbgalertext=‘1′]

Parameters

Name	Default value	Description
error text	ORA-	Defines which strings (errors) the Check must look for. Values must be separated with commas (,).
HWM	1	Each time the Check read the X$DBGALERTEXT view, it registers the last record_id of the row that have been checked. The next time the Check executes, it browses pass the rows already checked.
error text allowed	ORA-0	Specifies the error strings which are excluded.
seperator	,	Specifies separator sign between text strings or lines
history threshold	1000	The maximum numbers of error messages history this table will keep (in the db_alert_err_tab_histr table).
max lines	500000	The maximum numbers of lines we want to have in V$DIAG_ALERT_EXT view before we ask to delete files on disk

Job Summary

Purpose: The purpose of this monitoring job is to check the Oracle instance alert log for errors by tracking entries in the V$DIAG_ALERT_EXT performance view.
Why: This job is critically important for detecting any anomalies or issues such as errors and warnings that are logged in the Oracle database’s alert log. Early identification of such errors can prevent significant database issues and ensure continuous database health and availability.
Manual checking: You can check this manually in the database by issuing these SQL commands:

SELECT component_id, message_level, message_text FROM V$DIAG_ALERT_EXT WHERE record_id > :hwm ORDER BY record_id DESC;

Job Implementation Details

The monitoring job is implemented to run a procedure which scours through the alert log entries given certain conditions. It identifies the first occurrence of new entries beyond a high water mark (HWM) and checks for specific error strings (noted by parameter ‘error text’), while ignoring others determined by ‘error text allowed’.
The content of the checked log entries is stored in a history table (db_alert_err_tab_histr_#instance~id#) for maintaining a record of identified errors.
If the total number of rows exceeds a certain limit as dictated by the ‘max lines’ parameter, an alert is raised suggesting manual intervention to clear space.

Dependency Details

This job has key dependencies on certain database views and custom tables for storing log entries and errors. The procedure depends on being able to query and insert into these Oracle views and tables smoothly in order to operate without interruption.

Error Handling

Throughout the monitoring job, various checkpoints are placed to ensure that any exceptions caught during the execution are recorded properly along with an appropriate error message detailing the position and nature of the error.

Upgrade and Pre-Implementation

Prior to full implementation, necessary views are created or replaced, and checks are performed to ensure the necessary privileges are in place.
During an upgrade, procedures and parameters may be recalibrated—such as adjusting the description of existing parameters or deleting outdated parameters based on changes in the Oracle system version or database strategy.

Report Specifications

The dbWatch job includes functionalities to automatically generate reports exhibiting recent errors caught by this monitor. The report consists of tables showing recent errors fully qualified with details like the originating timestamp, error text, etc.
A custom SQL query directly pulls logs from the db_alert_err_tab_histr_#instance~id# table, showcasing the most recent error message trends.

Optimization and Performance

This job has been optimized to manage high volumes of log data by setting thresholds on historical data storage and limiting the read volume per cycle using a high water mark (HWM).
The default schedule of this job ensures that checks are frequent enough to catch issues in a timely manner but not so frequent as to overload the system resources.

In summary, this dbWatch monitoring job serves as an essential tool in a database administrator’s kit, enabling proactive management of Oracle database health by closely monitoring the instance alert logs for potential issues well before they escalate into significant problems.