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 9i only). |
| Version: | 2.6 |
| Default schedule: | 0,10,20,30,40,50 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘9%’) & _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 read 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 | Define the maximum running time the Check can execute (values are in seconds). If the execution time reach |
| 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 the Oracle Database alert log for any errors.
- Why: This job is important as it helps in early detection of errors and unusual events in the database alert log, potentially preventing more severe issues by addressing them promptly. It specifically targets environments running Oracle 9i, ensuring compatibility with older database systems. Since the alert log contains critical diagnostic information, monitoring it helps maintain the database’s overall health and stability.
- Manual Checking: You can manually check the alert log by navigating to the appropriate directory and reviewing the contents of the log file specified.
Details of Monitoring
- Force Install: The job is set to force installation.
- comp-query: Filters applicable instances to those running Oracle with specific characteristics suited for the job’s execution.
- Default Schedule: Scheduled to run every ten minutes.
Dependencies
- Primary Dependency: The job primarily depends on the existence of the “alert_log_#instance~id#” object, which represents the specific procedure that performs the check.
- Supporting Dependencies:
- dbWatch Engine Functions: Functions like “get_filelog_status” and “check_filelog” ensure that the log file’s status is properly checked and maintained.
- Database Objects: Utilizes procedures and tables such as “DBW_FILELOGS“ and “dbw_filelogs_row_seq” within the dbWatch engine to support log file operations.
Pre-Implementation Steps
- The necessary privileges for creating directories and executing utility functions are granted to the designated schema or user, ensuring that all required database privileges are correctly assigned for the task’s execution.
Implementation Details
- Stored Procedure Logic:
- The implementation essentially revolves around a PL/SQL procedure which performs detailed checks on the alert log concerning errors, the number of lines read, maximum lines, and execution time limits. It checks for specific error strings and handles all exceptions appropriately, updating database parameters based on the outcomes.
- Handles exception scenarios where the alert log file is locked, attempting to reopen it based on predefined intervals.
Post-Implementation Checks
- Updates task-specific parameters based on the operational environment (like different Oracle versions), ensuring that settings like the alert log file name and directory path are correctly set as per the environment or instance specifics.
Report Template
- Provides a detailed report format ready to be used to display data collected during the Oracle alert log check, including specifics like log text and directory object information, further helping in analysis and review of the alert log’s status and contents.