Listener log check

Job details

Name:	Listener log check
Platform:	Oracle
Category:	Availability
Description:	This checks and reads errors from the database listener log. Using Oracle native code to read.
Long description:	This checks and reads errors from the database listener log. Using Oracle native code to read.
Version:	1.9
Default schedule:	7,27,47 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘9%’) & _priv_read_dba_directories = ‘1′ & can_get_grant_create_any_directory=‘1′]

Parameters

Name	Default value	Description
listener log name	listener.log	The name of the Listener log file.
listener log path	null	The path name of the Listener log file which is used to create directory object.
listener log directory name	DBW_LISTENER_DIR	The name of the directory object created with the DDL – “create directory”.
listener log error text	TNS-	Defines which strings (errors) the Check must look for. Values for this parameter must be
listener log line HWM	1	Each time the Check read the listener log, it registers how many rows have been checked.
listener log max lines	200000	If the listener log has more lines than the value of this parameter, then the Check terminates
listener log max elapsed time	60	Define the maximum elaps time the Check can execute (values are in seconds). If the execution ‘
listener log error text allowed	TNS-12500	Specifies the error strings which are excluded.

Job Summary

Purpose: This monitoring job is designed to ensure the health and availability of Oracle database listeners by examining their log files for errors.
Why: Monitoring listener logs is crucial for detecting potential connectivity and configuration issues that can impact database operations. Errors in listener logs can indicate problems that may affect user connections and overall database performance.
Manual checking: You can manually check listener logs in an Oracle database using the SQL command below:

SELECT text FROM listener_log WHERE status = 'ERROR';

Job Details

Name: Listener Log Check
Description: This job involves reading errors from the database listener log using Oracle native code.
Object: dbw_listener_log_#instance~id#
Category: Availability
Company: dbwatch.com
Version: 1.9
Default Schedule: Executes every 20 minutes (at 7, 27, and 47 minutes past the hour).

Main Functionalities

Reads and checks errors in the listener log.
Searches for specific error texts defined by the user, monitoring the log for any defined exceptions.
Manages and tracks the history of checked log lines to optimize performance and avoid redundancy.
Evaluates if the log exceeds specified thresholds such as the maximum number of lines (200,000 by default) and maximum elapsed time (60 seconds by default), warning the user in such cases.

Dependencies

Object	Object Type	Description	Cleanup On Fail
dbw_listener_log_#instance~id#	2	Self dependency	true
check_filelog	4	Pertains to alert_log check	false
get_filelog_status	4	Associated with alert_log status check	false

Parameters

Name	Default Value	Description
listener log name	listener.log	The name of the Listener log file.
listener log path	null	The path name of the Listener log file which is used to create the directory object.
listener log directory name	DBW_LISTENER_DIR	The name of the directory object created with DDL “create directory”, should be the path to the listener.log file.
listener log error text	TNS-	Defines which error strings the check must look for, separated by commas.
listener log line HWM	1	Records how many rows have been checked. The check browses past these rows in subsequent runs.
listener log max lines	200000	If the listener log exceeds this line count, the check terminates and returns WARNING status.
listener log max elapsed time	60	Maximum execution time for the check in seconds; if exceeded, the check returns WARNING status.
listener log error text allowed	TNS-12500	Specifies excluded error strings.

Implementation

This job includes an Oracle procedure that is automatically replaced on installation, ensuring the current procedure logic is executed according to the latest configuration and parameters specified. The procedure involves:

Creation or re-validation of the necessary directory object for accessing the log.
Checking the listener log for errors using the defined parameters, such as log name, path, identifiable error texts, and exclusions.
Handling threshold behaviors, updating log check statuses, and managing the log line high watermark for performance.

Reporting

Report Title: Listener Log
Description: Produces a report based on the Listener log check, displaying information pertinent to the configuration and status of the Listener log file.

Overall, this monitoring job is essential for proactive database management, ensuring database listeners operate optimally and preventing potential issues from escalating by providing timely warnings and detailed logs of detected errors.