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.8
Default schedule:	7,27,47 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & twelve_and_above = ‘1′ & _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: The purpose of this job is to monitor the Oracle database Listener log for any errors and ensure the stability and availability of database services.
Why: Monitoring the Listener log is crucial as it can provide early warnings about network or configuration issues that affect database connectivity. Detecting errors early helps in maintaining the overall health and performance of the database system.
Manual checking: You can check the Listener log manually in the database by using SQL commands or directly accessing the log file. A typical SQL command to check for errors might look like:
bc. SELECT error_message FROM listener_log WHERE log_date >= sysdate – 1/24;

Details of the Monitoring Job

Listener Log Monitoring: This job involves reading the Listener log using Oracle native processes to identify specified errors.
Errors Checked: The job highlights specific error strings configured in the “listener log error text” parameter, while allowing to exclude certain warnings or errors using the “listener log error text allowed” parameter.
Automated Handling: This monitoring job includes automated procedures to manage the checks, including creating necessary directory objects if not present and handling logs that exceed predefined limits in size or runtime.

Implementation Logic

Creation and Validation: Handles creating or recreating directory objects if needed and validates paths dynamically.
Log Checking Routine: Uses the “check_filelog” procedure to read and analyze logs based on conditions set by multiple parameters like “listener log name”, “listener log path”, and thresholds like “listener log max lines” and “listener log max elapsed time”.
Error Handling: Implements robust error handling to track and record exceptions that might affect the monitoring, ensuring errors are logged and categorized properly.

Dependencies and Pre-requisite Checks

Object	Type	Description	Cleanup on Fail
check_filelog	Procedure	Alert_log check-dependent procedure	false
get_filelog_status	Procedure	Fetches the last know status of the file log check	false
dbw_listener_log_#instance~id#	Self-reference	Indicates dependency on its own artifacts for execution	true

Adjustment and Maintenance Best Practices

Parameter Adjustments: All crucial parameters like log paths, maximum line thresholds, and error text are configurable, allowing adjustments based on specific requirements and log behavior.
Regular Reviews: Regularly review the thresholds and excluded errors to ensure that the monitoring is tuned to capture relevant and significant error messages.

Reporting

Report Included: The job configuration includes a report template that consolidates and presents the data collected from the Listener log checks.
Information Presented: The report provides detailed information on error logs and directory definitions, helping in quick diagnostics and administrative oversight.

Overall, this dbWatch Control Center job provides an essential function by continuously monitoring critical Oracle database logs to ensure connectivity and operational stability, backed by automated procedures and comprehensive reporting.