Instance alert log 11

Job details

Name:	Instance alert log 11
Platform:	Oracle
Category:	Availability
Description:	Reads and checks errors in the X$DBGALERTEXT 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 X$DBGALERTEXT performance view which contains the XML decoded version of the XML version of the Alert log file.
Version:	5.22
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’ & has_x_dbgalertext=‘1′]/.[maj_version = ‘11′	maj_min_version = ‘12.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 how many rows have been checked. The next time the Check executes, it browses pass the rows already checked. The value of how many rows of the X$DBGALERTEXT view have already been checked are registered by this parameter.
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 X$DBGALERTEXT view before we ask to delete files on disk

Job Summary

Purpose: The purpose of this job is to monitor and detect errors in the X$DBGALERTEXT performance view for Oracle databases, specifically for versions 11 and 12.1. This view holds the XML decoded version of the Alert log files, which are crucial for understanding the health and error conditions within the database instance.
Why: This job is essential to maintain the reliability and availability of Oracle database instances by early detection of errors and potential issues. Alert logs are critical for database administrators as they provide insight into internal database errors and other significant events that could affect database performance and stability. By automating the monitoring of these logs, the job helps prevent the escalation of unnoticed errors.
Manual Checking: Database administrators can manually check the alert logs by querying the X$DBGALERTEXT view in the Oracle database:

select record_id, histr_date as "Date", message_text as "Message" from db_alert_err_tab_histr_11_#instance~id# order by histr_date desc;

Job Configuration and Logic

The job configuration specifies that it should execute every five minutes, which provides frequent and near real-time monitoring of the database logs. It checks for specific error strings (defaults to any string starting with ‘ORA-’), excluding certain strings specified by the “error text allowed” parameter.

The job internally uses a cursor to fetch new entries from the X$DBGALERTEXT based on a “High Water Mark” (HWM) that tracks the last processed record, ensuring each message is only processed once. If an error string is found and not in the allowed list, the entry is logged into a history table for further review.

Details of Database Modifications and Scripts

The scripts included with the job create necessary objects in the database such as a history table and a procedure on the dbWatch schema to read and process the alert logs:

Create Table Script: A table (db_alert_err_tab_histr_11_#instance~id#) is created to store identified errors from the alert logs along with their detection time.
Procedure Script: A procedure (db_alert_log_11_#instance~id#) encapsulates the logic to read from the X$DBGALERTEXT, detect errors as per configured parameters, and log them into the history table.

Dependencies and Cleanup

There are set dependencies such as the correct execution and functioning of other related objects and procedures. On failure, cleanup operations are triggered to ensure system integrity and cleanliness.

Scheduled Report Generation

The job includes a report template that generates reports based on the collected data:

Presentation Title	Data Coverage	Description
Last errors in alert log	Show last detected errors from the log	Provides a quick view of recent errors, assisting in rapid diagnostic efforts.
Last records from X$DBGALERTEXT view	Detailed historical error analysis	Displays a detailed timeline of error messages, helping in troubleshooting and analysis over a longer period.

Conclusion

This dbWatch job is a critical component of the database monitoring infrastructure, focusing on proactive error detection and logging within Oracle database environments. By consistently monitoring and reporting on database errors, it supports database administrators in maintaining system health and performance.