Alert log check (java)

Job details

Name:	Alert log check (java)
Platform:	Oracle
Category:	Availability
Description:	This check reads and looks for errors in the database alert log. Uses embedded java to read files.
Long description:	This check reads and looks for errors in the database alert log. Uses embedded java to read files.
Version:	2.7
Default schedule:	0,10,20,30,40,50 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & eleven_and_above = ‘1′ & enable_java = ‘1′]

Parameters

Name	Default value	Description
alertlog name	AUTO	The name of the Oracle alert log. If set to AUTO, we will try to figure out the alert log file name ourself.
alertlog directory name	AUTO	The name of the directory we are looking for the alert log in. If set to AUTO, we will try to figure out where the alert log file is located.
alertlog error text	ORA-	Defines which strings (errors) the Check must look for. Values must be separated with commas (,). Text is case and space sensitive, so “ ORA-, Error” is looking for “ ORA-” and “ Error”, different from “ORA-,Error” which looks for “ORA-” and “Error”.
alertlog line HWM	0	Each time the Check read the alert log, it registers how many rows have been checked. The next time
alertlog read lines	4000	Number of lines to maximum read on each run.
alertlog error text allowed		Specifies the error strings which are excluded. Values must be separated with commas (,). Text is case and space sensitive, so “ ORA-, Error” is looking for “ ORA-” and “ Error”, different from “ORA-,Error” which looks for “ORA-” and “Error”. This list is filtering away errors found by “alertlog error text”, so if “ORA-” is searched for in “alertlog error text” parameter, and “ORA-600″ is in “alertlog error text allowed” parameter, then “ORA-600″ messages are not reported on.

Job Summary

Purpose: The purpose of this job is to monitor the Oracle database alert log for specific errors using embedded Java.
Why: This job is important to check and report errors found in the Oracle alert log, which can help in proactive database management and error resolution. Monitoring these logs helps in identifying and reacting to issues that could potentially affect the database’s availability and performance.
Manual checking: You can check this manually in the database by issuing the SQL command to query the “dbw_alert_error_history” table:

SELECT readdate, line FROM dbw_alert_error_history_#instance~id# ORDER BY readdate DESC;

Job Configuration & Execution Flow

The monitoring job is designed for Oracle databases (11g and above) that have Java enabled.
The job automatically determines the alert log file and directory if not specified.
It looks for specific error strings in the alert log. If no errors are specified, it defaults to looking for “ORA-” string errors.
Detected errors are filtered against an allowlist which excludes them from reporting, based on the settings defined by ‘alertlog error text allowed’.
Error detection and history are managed by inserting relevant entries into custom tables like “dbw_alert_error_history_#instance~id#”.

Technical Implementation

Java Source and Procedures: Java sources read the alert log files and Java stored procedures are used to manage file read operations within the Oracle DB.
Tables: Several support tables are created and used, such as:
- “dbw_alert_data_#instance~id#”: for storing lines from the alert log.

Dependencies

Dependency	Description
dbw_alert_log	Primary procedure for checking alert logs
dbw_alert_data	Stores alert log data
dbw_alert_error_history	Maintains history of errors found
dbw_alert_config	Configuration settings for the alert log file
dbw_alert_err_text	Stores error strings to look for
dbw_alert_good_text	Stores strings which denote exceptions to error alerts

Reporting

The job includes a reporting template for displaying data about detected errors and their counts over time, as well as historical error data.
Reports include a category chart for visual representation and a detailed list table for historical error review.

By employing an automated approach using Java embedded in the database, this job helps in monitoring and quickly pinning down potential issues indicated by Oracle alert logs. This proactive monitoring ensures database health and operational stability.