{"id":4295,"date":"2025-06-17T21:28:22","date_gmt":"2025-06-17T21:28:22","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4295"},"modified":"2025-06-17T21:28:23","modified_gmt":"2025-06-17T21:28:23","slug":"oracle-alert-log-monitoring-with-ora-error-notifications","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/shell-scripts\/oracle-alert-log-monitoring-with-ora-error-notifications\/","title":{"rendered":"Oracle Alert Log Monitoring with ORA- Error Notifications"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Oracle alert logs contain critical information, including internal errors, startup\/shutdown events, and ORA- errors. Monitoring these logs in real-time ensures issues are detected and addressed before they escalate.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This script uses Oracle&#8217;s <strong>ADRCI utility<\/strong> to automatically scan alert logs of all registered databases every 15 minutes for any <code>ORA-<\/code> errors, and sends an email to the DBA team if such errors are found.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This solution is compatible with Oracle 11g and above.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Shell Script: <code>Adrci_alert_log.ksh<\/code><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\n\n################################################################################\n# Title   : Oracle Alert Log Monitoring Script\n# Script  : adrci_alert_log.sh\n# Purpose : Monitor all Oracle alert logs using ADRCI and notify if ORA- errors are found\n#\n# Features:\n#   - Scans alert logs for all ADR homes (RDBMS)\n#   - Checks for ORA- errors in the last 15 minutes\n#   - Sends detailed alert log output via email\n#\n# Author  : W3Buddy\n# Version : 1.0\n################################################################################\n\nexport LOG_DIR=\"\/u01\/app\/oracle\/scripts\/logs\"\nmkdir -p \"$LOG_DIR\"\nexport LOG_FILE=\"$LOG_DIR\/alert_log_check_$(date +%Y%m%d%H%M).log\"\n\necho \"##############################\" > \"$LOG_FILE\"\necho \"###### ALERT LOG ORA- ERRORS (Last 15 Minutes) ######\" >> \"$LOG_FILE\"\necho \"##############################\" >> \"$LOG_FILE\"\n\n# Get list of RDBMS ADR homes\nadrci_homes=( $(adrci exec=\"show homes\" | grep -E '^diag\/rdbms') )\n\nfor adrci_home in \"${adrci_homes&#91;@]}\"; do\n  echo -e \"\\n########################################\" >> \"$LOG_FILE\"\n  echo \"ADR Home: $adrci_home\" >> \"$LOG_FILE\"\n  adrci exec=\"set home ${adrci_home}; show alert -p \\\"message_text like '%ORA-%' and originating_timestamp > systimestamp-1\/96\\\"\" -term >> \"$LOG_FILE\"\ndone\n\n# Count number of ORA- errors\nnum_errors=$(grep -c 'ORA-' \"$LOG_FILE\")\n\nif &#91; \"$num_errors\" -gt 0 ]; then\n  mailx -s \"\u26a0\ufe0f ORA- Error Detected in Alert Logs on $(hostname)\" info.w3buddy@gmail.com &lt; \"$LOG_FILE\"\nfi<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\u2699\ufe0f Setup Instructions<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code># ----------------------------------------------\n# 1. Create the script directory (if not already)\n# ----------------------------------------------\nmkdir -p \/u01\/app\/oracle\/scripts\/logs\n\n# ----------------------------------------------\n# 2. Save the script\n# ----------------------------------------------\nvi \/u01\/app\/oracle\/scripts\/adrci_alert_log.sh\n\n# (Paste the script above and save)\n\n# ----------------------------------------------\n# 3. Make it executable\n# ----------------------------------------------\nchmod +x \/u01\/app\/oracle\/scripts\/adrci_alert_log.sh\n\n# ----------------------------------------------\n# 4. Test manually\n# ----------------------------------------------\n\/u01\/app\/oracle\/scripts\/adrci_alert_log.sh\n\n# ----------------------------------------------\n# 5. Configure in crontab to run every 15 mins\n# ----------------------------------------------\ncrontab -e\n\n# Add the following line:\n0,15,30,45 * * * * \/u01\/app\/oracle\/scripts\/adrci_alert_log.sh >> \/tmp\/adrci_monitor.log 2>&amp;1\n\n# ----------------------------------------------\n# 6. Confirm the cron job\n# ----------------------------------------------\ncrontab -l<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 Notes &amp; Best Practices<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>You can modify the time window by changing <code>systimestamp-1\/96<\/code> (15 minutes) to another interval like <code>1\/48<\/code> (30 minutes).<\/li>\n\n\n\n<li>This script works on all Oracle databases using ADR, starting from version 11g.<\/li>\n\n\n\n<li>Ensure <code>mailx<\/code> is configured and functional on your server.<\/li>\n\n\n\n<li>Compatible with both RAC and Single-instance environments using shared ADR base.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Oracle alert logs contain critical information, including internal errors, startup\/shutdown events, and ORA- errors. Monitoring these logs in real-time ensures issues are detected and addressed before they escalate. This script uses Oracle&#8217;s ADRCI utility to automatically scan alert logs of all registered databases every 15 minutes for any ORA- errors, and sends an email to [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,990],"class_list":["post-4295","cposts","type-cposts","status-publish","hentry","category-notes","category-shell-scripts"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4295","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}