Alert log check

Job details

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

Parameters

Name	Default value	Description
alert log name	alert_ORACLE_SID.log	The name of the Oracle alert log.
alert log directory name	DBW_DUMP_DEST	The name of the directory object created with the DDL – “create directory”.
alert log error text	ORA-	Defines which strings (errors) the Check must look for. Values must be separated with commas (,).
alert log line HWM	1	Each time the Check reads the alert log, it registers how many rows have been checked. The next time
alert log max lines	200000	If the alert log has more lines than the value of this parameter, then the Check terminates and
alert log max elapsed time	60	Defines the maximum time the Check can execute (values are in seconds). If the execution time reaches
alert log error text allowed	NULL	Specifies the error strings which are excluded.
loop time	180	The number of seconds the “Alert log check” will continue to try to open the alert log file if an exception occurs during opening of the file. This parameter was introduced as a workaround when the alert log file is locked by other process.
pause time	20	The number of seconds the check will wait before trying to reopen the alert log file when an exception occurred during opening of that file.
max exception time	40	The maximum number of minutes the check will try to reopen the alert log file. If the check is not able to open the file for this amount of time (in minutes), it will return a warning.
last exception	null	The timestamp (hh24:mi dd.mm.yyyy) when an exception occurred trying to open the alert log file. If null, the opening of the alert log file is successful.

Job Summary

Purpose: The purpose of this job is to monitor and check for errors in the Oracle alert log for databases running Oracle 10g. This check is specifically designed to ensure system stability by early detection of potential issues indicated by error logs.
Why: This job is crucial for maintaining database health and performance. It helps in identifying errors early, potentially avoiding severe issues that could lead to downtime or significant performance degradation. It is specially formulated for Oracle 10g instances.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

SELECT log_text FROM alert_log_#instance~id# ORDER BY log_date DESC;

Job Details

Task Execution

The monitoring job operates by reading the database alert log using Oracle internal routines to parse log files.
This job is configured to trigger checks every 10 minutes (as per the default schedule: 0,10,20,30,40,50 * * * *).

Parameters and Filters:

This job reads the Oracle alert log files, looking for predefined error text strings (default ‘ORA-’). Any encountered error strings trigger further action or logging.
The alert log is also checked for its size and the total execution time of the check, specified by the ‘alert log max lines’ and ‘alert log max elapsed time’ parameters.
Exclusion filters are applied to ignore specific error text defined in ‘alert log error text allowed’.
Additional parameters guide the behavior during anomalies, like when the alert log file is temporarily inaccessible due to being locked by another process.

Dependencies and Grant Requirements:

The job uses several objects and procedures within dbWatch, needing appropriate permissions set for the database instance where it is deployed.
Pre-implementation grants include privileges to CREATE ANY DIRECTORY and EXECUTE privileges on SYS.UTL_FILE and SYS.DBMS_LOCK for the dbWatch schema.

Installation and Automated Controls:

This job includes a force-install attribute which suggests that it will ensure necessary environment setups (like directory creation and privilege granting) are enforced upon job installation.
The setup processes parameters and conditions based on the database instance specifics (like version and existing configuration).

Report and Monitoring

A report template is provided within the job configuration detailing how the collected data will be presented. This includes listings of the detected alert logs and directory objects.
The report updates at the frequency specified in its default schedule (every hour), summarizing the alert logs for administrative review.

Conclusion

The dbWatch job serves as a proactive monitoring tool specifically designed for Oracle 10g databases to ensure operational health through constant surveillance of alert logs, providing administrators with immediate reports on potential issues and facilitating quick response times.