Shell Script to Read Oracle Database Alert Log and Report ORA Errors

In this post, I will share shell script to check and read my alert log file based in timestamp and provide me ORA errors from last check timestamp. The script will capture data after last checked timestamp.

Assumption:

You need to know your alertlog file path

Script:

#!/bin/bash
#set -x
# Oracle environment variables (adjust as necessary)
#export ORACLE_SID=your_sid
#export ORACLE_HOME=/path/to/oracle_home
#export PATH=$ORACLE_HOME/bin:$PATH
. ~/.bash_profile
# Paths to alert log and timestamp file
ALERT_LOG="/u01/db/TRAIN/12.1.0/admin/TRAIN_ebstraining/diag/rdbms/train/TRAIN/trace/alert_TRAIN.log"
TIMESTAMP_FILE="/tmp/last_checked_timestamp.txt"
REPORT_FILE="health_check_report.html"

# Function to validate the database alert log and identify ORA error codes
check_alert_log_for_errors() {
echo "<h3>Database Alert Log Errors</h3>" >> $REPORT_FILE
echo "<table style=\"border-collapse: collapse; width: 100%;\" border=\"1\">" >> $REPORT_FILE
echo "<tr><th>Timestamp</th><th>Error Message</th></tr>" >> $REPORT_FILE

# Get the last checked timestamp
if [ -f "$TIMESTAMP_FILE" ]; then
last_checked=$(cat "$TIMESTAMP_FILE")
else
last_checked="Sat May 11 00:00:00 2024"
fi
# Update the timestamp file with the current time
current_time=$(date '+%a %b %d %H:%M:%S %Y')
echo "$current_time" > "$TIMESTAMP_FILE"

# Convert last checked timestamp to seconds since epoch for comparison
last_checked_epoch=$(date -d "$last_checked" '+%s')

# Initialize a variable to keep track of the current log timestamp
current_log_timestamp=""

# Search for ORA errors in the alert log since the last checked time
while IFS= read -r line; do
# Check if the line contains a timestamp
if [[ $line =~ ^[A-Za-z]{3}\ [A-Za-z]{3}\ [0-9]{2}\ [0-9]{2}:[0-9]{2}:[0-9]{2}\ [0-9]{4}$ ]]; then
current_log_timestamp=$line
current_log_epoch=$(date -d "$current_log_timestamp" '+%s')
fi

# If the current log entry is after the last checked timestamp, check for ORA errors
if [[ -n $current_log_timestamp && $current_log_epoch -ge $last_checked_epoch ]]; then
if [[ $line =~ ORA-[0-9]{5} ]]; then
error_message=$line
echo "<tr><td>$current_log_timestamp</td><td>$error_message</td></tr>" >> $REPORT_FILE
fi
fi
done < "$ALERT_LOG"
echo "</table>" >> $REPORT_FILE
}

# Main script execution
echo "<html><body>" > $REPORT_FILE
check_alert_log_for_errors
echo "</body></html>" >> $REPORT_FILE