Cronjob:
0 8 * * * /path/to/your/Monitoring.sh
Save the below file with name Monitoring.sh
chmod +x script_name.sh
#!/bin/bash
# Database credentials
DB_USERNAME="your_db_username"
DB_PASSWORD="your_db_password"
DB_SID="your_db_sid"
# Email configuration
MAIL_SERVER="your_mail_server"
MAIL_PORT="your_mail_port"
MAIL_USERNAME="your_mail_username"
MAIL_PASSWORD="your_mail_password"
MAIL_FROM="your_email_address"
MAIL_TO="recipient_email_address"
# Path to the alert log file
ALERT_LOG_FILE="/path/to/alert.log"
# Function to send an email
send_email() {
SUBJECT="Database Alert Log Error"
MESSAGE="An error was found in the database alert log. Please check the log file for details."
echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \
openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \
-CApath /etc/ssl/certs/ -ign_eof \
-auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \
-to "${MAIL_TO}" -from "${MAIL_FROM}"
}
# Check the alert log for errors
check_alert_log() {
ERROR_COUNT=$(grep -i "ORA-" "${ALERT_LOG_FILE}" | wc -l)
if [ $ERROR_COUNT -gt 0 ]; then
send_email
fi
}
# Main script execution
check_alert_log
****Try this**************************************************
#!/bin/bash
# Database credentials
DB_USERNAME="your_db_username"
DB_PASSWORD="your_db_password"
DB_SID="your_db_sid"
# Email configuration
MAIL_SERVER="your_mail_server"
MAIL_PORT="your_mail_port"
MAIL_USERNAME="your_mail_username"
MAIL_PASSWORD="your_mail_password"
MAIL_FROM="your_email_address"
MAIL_TO="recipient_email_address"
# Path to the alert log file
ALERT_LOG_FILE="/path/to/alert.log"
# Function to send an email
send_email() {
SUBJECT="Database Alert Log Error"
MESSAGE="An error was found in the database alert log. Please check the log file for details."
echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \
openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \
-CApath /etc/ssl/certs/ -ign_eof \
-auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \
-to "${MAIL_TO}" -from "${MAIL_FROM}"
}
# Check the alert log for errors
check_alert_log() {
ERROR_COUNT=$(awk '/ORA-/ {count++} END {print count}' "${ALERT_LOG_FILE}")
if [ $ERROR_COUNT -gt 0 ]; then
send_email
fi
}
# Main script execution
check_alert_log
***********************************************************
Here's a script that checks if the database and listener are down and sends an email notification if either of them is not running:
#!/bin/bash
# Database credentials
DB_USERNAME="your_db_username"
DB_PASSWORD="your_db_password"
DB_SID="your_db_sid"
# Listener configuration
LISTENER_NAME="your_listener_name"
# Email configuration
MAIL_SERVER="your_mail_server"
MAIL_PORT="your_mail_port"
MAIL_USERNAME="your_mail_username"
MAIL_PASSWORD="your_mail_password"
MAIL_FROM="your_email_address"
MAIL_TO="recipient_email_address"
# Function to send an email
send_email() {
SUBJECT="Database or Listener Down"
MESSAGE="The database or listener is not running. Please check the status."
echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \
openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \
-CApath /etc/ssl/certs/ -ign_eof \
-auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \
-to "${MAIL_TO}" -from "${MAIL_FROM}"
}
# Check if the database is running
check_database() {
DB_STATUS=$(echo "SELECT 'Database Open' FROM dual;" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Database Open")
if [ $DB_STATUS -eq 0 ]; then
send_email
fi
}
# Check if the listener is running
check_listener() {
LISTENER_STATUS=$(lsnrctl status ${LISTENER_NAME} | grep -c "The listener supports no services")
if [ $LISTENER_STATUS -eq 1 ]; then
send_email
fi
}
# Main script execution
check_database
check_listener
***********************************************************
Here's a script that checks if there are any RMAN backups, export backups, or statistics jobs running and sends an email notification if any of them are in progress:
#!/bin/bash
# Database credentials
DB_USERNAME="your_db_username"
DB_PASSWORD="your_db_password"
DB_SID="your_db_sid"
# Email configuration
MAIL_SERVER="your_mail_server"
MAIL_PORT="your_mail_port"
MAIL_USERNAME="your_mail_username"
MAIL_PASSWORD="your_mail_password"
MAIL_FROM="your_email_address"
MAIL_TO="recipient_email_address"
# Function to send an email
send_email() {
SUBJECT="Database Backup or Job Running"
MESSAGE="There is a database backup or job running. Please check the status."
echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \
openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \
-CApath /etc/ssl/certs/ -ign_eof \
-auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \
-to "${MAIL_TO}" -from "${MAIL_FROM}"
}
# Check if there are any RMAN backups running
check_rman_backup() {
RMAN_STATUS=$(echo "SELECT 'RMAN Backup Running' FROM v\$rman_status WHERE status = 'RUNNING';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "RMAN Backup Running")
if [ $RMAN_STATUS -gt 0 ]; then
send_email
fi
}
# Check if there are any export backups running
check_export_backup() {
EXPORT_STATUS=$(echo "SELECT 'Export Backup Running' FROM v\$session WHERE program LIKE 'expdp%';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Export Backup Running")
if [ $EXPORT_STATUS -gt 0 ]; then
send_email
fi
}
# Check if there are any statistics jobs running
check_stats_job() {
STATS_STATUS=$(echo "SELECT 'Statistics Job Running' FROM dba_scheduler_running_jobs WHERE job_name LIKE '%GATHER_STATS_JOB%';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Statistics Job Running")
if [ $STATS_STATUS -gt 0 ]; then
send_email
fi
}
# Main script execution
check_rman_backup
check_export_backup
check_stats_job
***********************************************************
Here's a script that performs a basic health check on the database and sends an email notification if the database performance is below a certain threshold
#!/bin/bash
# Database credentials
DB_USERNAME="your_db_username"
DB_PASSWORD="your_db_password"
DB_SID="your_db_sid"
# Email configuration
MAIL_SERVER="your_mail_server"
MAIL_PORT="your_mail_port"
MAIL_USERNAME="your_mail_username"
MAIL_PASSWORD="your_mail_password"
MAIL_FROM="your_email_address"
MAIL_TO="recipient_email_address"
# Threshold values (you can adjust these values as per your requirements)
CPU_THRESHOLD=80
MEMORY_THRESHOLD=80
# Function to send an email
send_email() {
SUBJECT="Database Health Check Alert"
MESSAGE="The database performance is below the acceptable threshold. Please investigate the issue."
echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \
openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \
-CApath /etc/ssl/certs/ -ign_eof \
-auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \
-to "${MAIL_TO}" -from "${MAIL_FROM}"
}
# Check CPU usage
check_cpu_usage() {
CPU_USAGE=$(top -b -n 1 | grep "%Cpu(s)" | awk '{print $2}' | cut -d. -f1)
if [ $CPU_USAGE -ge $CPU_THRESHOLD ]; then
send_email
fi
}
# Check memory usage
check_memory_usage() {
MEMORY_USAGE=$(free | awk '/Mem/ { printf("%.2f"), $3/$2 * 100 }')
if (( $(echo "$MEMORY_USAGE >= $MEMORY_THRESHOLD" | bc -l) )); then
send_email
fi
}
# Main script execution
check_cpu_usage
check_memory_usage
***********************************************************
Here's a script that checks if a tablespace has less space available than a specified threshold and sends an email notification immediately
#!/bin/bash
# Database credentials
DB_USERNAME="your_db_username"
DB_PASSWORD="your_db_password"
DB_SID="your_db_sid"
# Email configuration
MAIL_SERVER="your_mail_server"
MAIL_PORT="your_mail_port"
MAIL_USERNAME="your_mail_username"
MAIL_PASSWORD="your_mail_password"
MAIL_FROM="your_email_address"
MAIL_TO="recipient_email_address"
# Tablespace and threshold configuration
TABLESPACE_NAME="your_tablespace_name"
THRESHOLD_PERCENTAGE=80
# Function to send an email
send_email() {
SUBJECT="Tablespace Space Alert"
MESSAGE="The tablespace '${TABLESPACE_NAME}' has less space available. Please check and take appropriate actions."
echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \
openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \
-CApath /etc/ssl/certs/ -ign_eof \
-auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \
-to "${MAIL_TO}" -from "${MAIL_FROM}"
}
# Check tablespace space usage
check_tablespace_space() {
SPACE_USAGE=$(echo "SELECT ROUND((1 - (df.totalspace - fs.freespace) / df.totalspace) * 100, 2) AS used_percentage FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS totalspace FROM dba_data_files WHERE tablespace_name = '${TABLESPACE_NAME}' GROUP BY tablespace_name) df, (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS freespace FROM dba_free_space WHERE tablespace_name = '${TABLESPACE_NAME}' GROUP BY tablespace_name) fs WHERE df.tablespace_name = fs.tablespace_name;" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | awk '{print $1}')
if (( $(echo "$SPACE_USAGE >= $THRESHOLD_PERCENTAGE" | bc -l) )); then
send_email
fi
}
# Main script execution
check_tablespace_space
*******************************************************
Here's a script that checks if there are any failed RMAN backups, export backups, or statistics jobs, and sends an email notification immediately
#!/bin/bash
# Database credentials
DB_USERNAME="your_db_username"
DB_PASSWORD="your_db_password"
DB_SID="your_db_sid"
# Email configuration
MAIL_SERVER="your_mail_server"
MAIL_PORT="your_mail_port"
MAIL_USERNAME="your_mail_username"
MAIL_PASSWORD="your_mail_password"
MAIL_FROM="your_email_address"
MAIL_TO="recipient_email_address"
# Function to send an email
send_email() {
SUBJECT="Backup or Job Failure Alert"
MESSAGE="There is a failure in one or more backups or jobs. Please check the status."
echo -e "Subject:${SUBJECT}\n${MESSAGE}" | \
openssl s_client -quiet -connect "${MAIL_SERVER}:${MAIL_PORT}" -starttls smtp -crlf \
-CApath /etc/ssl/certs/ -ign_eof \
-auth login -user "${MAIL_USERNAME}" -password "${MAIL_PASSWORD}" \
-to "${MAIL_TO}" -from "${MAIL_FROM}"
}
# Check for failed RMAN backups
check_failed_rman_backups() {
FAILED_BACKUPS=$(echo "SELECT 'RMAN Backup Failed' FROM v\$rman_backup_job_details WHERE status = 'FAILED';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "RMAN Backup Failed")
if [ $FAILED_BACKUPS -gt 0 ]; then
send_email
fi
}
# Check for failed export backups
check_failed_export_backups() {
FAILED_EXPORTS=$(echo "SELECT 'Export Backup Failed' FROM dba_datapump_jobs WHERE state = 'NOT RUNNING' AND operation = 'EXPORT' AND status = 'EXCEPTION';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Export Backup Failed")
if [ $FAILED_EXPORTS -gt 0 ]; then
send_email
fi
}
# Check for failed statistics jobs
check_failed_stats_jobs() {
FAILED_JOBS=$(echo "SELECT 'Statistics Job Failed' FROM dba_scheduler_job_run_details WHERE status = 'FAILED' AND job_name LIKE '%GATHER_STATS_JOB%';" | sqlplus -s "${DB_USERNAME}/${DB_PASSWORD}@${DB_SID}" | grep -c "Statistics Job Failed")
if [ $FAILED_JOBS -gt 0 ]; then
send_email
fi
}
# Main script execution
check_failed_rman_backups
check_failed_export_backups
check_failed_stats_jobs
Enjoy the work and do like me if you like this article!!!