How to Use Oracle Alert Log Monitoring with Shell Script and Email Notification

How to Use Oracle Alert Log Monitoring with Shell Script and Email Notification

Introduction

Monitoring the Oracle alert log is one of the simplest yet most effective ways to catch early warnings of database issues—like ORA errors, corrupt blocks, or memory issues. Many DBAs rely on OEM or third-party tools, but what if you need a lightweight, script-based solution?

This blog will walk you through creating a shell script that monitors the Oracle alert log, detects new errors, and sends an email notification—all in under 15 minutes.

Why Monitor the Alert Log Manually?

• Lightweight and works even without monitoring tools.

• Can be customized for any Oracle version or error type.

• Works in secure environments with no internet access.

Step-by-Step: Build Your Alert Log Monitor

Pre-requisites

• Linux/Unix-based Oracle Server

• Mailx configured (for email notifications)

• Access to $ORACLE_BASE/diag/rdbms/.../trace/alert_<SID>.log

1. Create the Alert Log Monitor Script

#!/bin/bash

# Set Environment
ORACLE_SID=PROD
ORACLE_BASE=/u01/app/oracle
ALERT_LOG="$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_${ORACLE_SID}.log"
LAST_CHECK="/tmp/last_check_${ORACLE_SID}.txt"
EMAIL="dba_team@example.com"

# Initialize last check timestamp
if [ ! -f "$LAST_CHECK" ]; then
  date +"%b %d %H" > "$LAST_CHECK"
fi

# Read last timestamp
LAST_TIME=$(cat "$LAST_CHECK")

# Check for new ORA- errors
grep "$LAST_TIME" -A 500 "$ALERT_LOG" | grep "ORA-" > /tmp/errors_found.txt

if [ -s /tmp/errors_found.txt ]; then
  mailx -s "ALERT: Oracle DB $ORACLE_SID - Errors Detected" "$EMAIL" < /tmp/errors_found.txt
fi

# Update timestamp for next run
date +"%b %d %H" > "$LAST_CHECK"
 

2. Schedule with Cron

crontab -e

# Run every 15 minutes
*/15 * * * * /home/oracle/scripts/alert_log_monitor.sh

3. Example Email Notification

Subject: ALERT: Oracle DB PROD - Errors Detected

ORA-04031: unable to allocate bytes of shared memory
ORA-00600: internal error code, arguments: [13011]
 

Benefits of This Script

• Works even if Oracle Enterprise Manager is down.

• Easily extensible for other log files (listener, ASM, etc.)

• Great for non-production or DR environments.

  Conclusion

  In environments where simplicity, reliability, and customization matter, shell-based alert log monitoring can be a lifesaver. With just a few lines of code, you gain real-time insight into database issues—no OEM or third-party tool required.

  Start small. Tune it. And make it part of your DBA toolkit.

   

 

 

 

Understanding Oracle SQL Plan Baselines for Performance Stability

 Understanding Oracle SQL Plan Baselines for Performance Stability

Introduction

As Oracle DBAs, we often encounter unexpected SQL performance regressions after statistics collection, database upgrades, or plan changes. One of the lesser-used but powerful tools to combat such issues is the SQL Plan Baseline feature. This blog post aims to demystify SQL Plan Baselines—what they are, why they matter, and how to implement them in real-world scenarios.

What is a SQL Plan Baseline?

A SQL Plan Baseline is a set of accepted execution plans stored in the data dictionary that Oracle uses to ensure consistent query performance. If a SQL statement's newly generated plan differs from the baseline and isn't verified yet, Oracle will prefer the known "accepted" plan—thus avoiding sudden regressions.

Why Use SQL Plan Baselines?

• Prevents performance regressions after statistics gathering or DB upgrades.

• Maintains execution plan consistency for critical SQL statements.

• Allows safe testing of new plans before full adoption.

How SQL Plan Baselines Work

1. A SQL statement is executed.

2. If no baseline exists, Oracle uses the best cost-based plan and stores it as a "candidate".

3. A DBA can manually verify and accept the candidate plan.

4. Once accepted, Oracle uses the plan for future executions unless a better one is verified and promoted.

   Step-by-Step: How to Create a SQL Plan Baseline

   1. Capture the Plan Manually

   -- Enable plan baseline capture at session level
   ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
   
   -- Run your SQL
   SELECT /*+ gather_plan_statistics */ * FROM employees WHERE department_id = 10;
    

   2. Load Plans into Baseline from Cursor Cache 

   DECLARE
     l_plans_loaded PLS_INTEGER;
   BEGIN
     l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
       sql_id => 'abcd1234abcd1234'
     );
   END;
   /
   3. Verify Baseline Exists

   SELECT sql_handle, plan_name, enabled, accepted
   FROM dba_sql_plan_baselines
   WHERE sql_text LIKE '%employees%';
    

   Advanced Usage

Evolving Plans: You can allow Oracle to test better plans automatically using plan evolution.

Transporting Baselines: Use DBMS_SPM.CREATE_STGTAB_BASELINE to export/import across databases.

Disabling Bad Plans: If a baseline plan is no longer good, you can disable it without removing it.

Best Practices

• Regularly monitor baselines for outdated or rejected plans.

• Avoid accepting plans without testing in QA.

• Combine with SQL Plan Management (SPM) policies in critical production environments.

  Conclusion

  SQL Plan Baselines are a powerful tool for ensuring SQL performance stability across changes. They are often overlooked but can make a significant difference in environments where query performance is business-critical. Start with critical queries and evolve your baseline strategy to cover more ground as your database evolves.

   

 

 

PATCH IMPACT ANALYSIS IN ORACLE EBS 12.2

 

PATCH IMPACT ANALYSIS IN ORACLE EBS 12.2

 Step 1: Download the patch in MOS

 34436371

  

  

 

  Step 2- To check staging directory

 

Navigate to Sys administrator -> oracle application manager ->patching and utilities

 

 In select feature tab please select patch impact and click go to selected feature

  

 

 In that tab click the patch wizard preferences task icon

  

 

 In that pages its sows staging directory so we need to move that patch to this same location

  

 

 Step 3- Move that patch to staging directory location and unzip it

 

 Step 4- Choose recommended analysis task icon

 

 Step 5 - select the analysis specific patch and give the patch number and click ok button

 

  Step 6- click confirmation once done the request submitted please note the request ID

 

 

 

  259170061.

 To view the running status of the request please choose job staus option

 

 

Its shows running status

  

  After some time it will completed.

 

 We can check the request id running status via conc manager ->find request

 

 

Step 7 - Execute below query and mention the correct analysis patch number its give report .

 Export the file in excel format.

 select b.APPLICATION_NAME Application,s.directory,s.filename,

DECODE(s.typeid, 'upgrade', 'Changed File', 'not applied', 'Unchanged File', 'new', 'New File') "Impact Type",

s.NEW_VERSION "Version in Patch",

s.OLD_VERSION "Version in APPL_TOP",

s.FILES_AFFECTED "Objects Affected"

from apps.fnd_application_vl b,    

apps.FND_IMP_PSMaster2 s    

where s.app_short_name=b.APPLICATION_SHORT_NAME

and s.bug_no='34436371';

 

 

 

ORA-38824 for trigger FLOWS_FILES.wwv_biu_flow_file_objects.

 ORA-38824 for trigger FLOWS_FILES.wwv_biu_flow_file_objects.

 

Issue Faced:

After installing apex 20.2 some of the APEX functions were not working as expected. For example,we are not able to import applications using the APEX GUI.

Below image shows the error faced while importing applications.

 

 

 

Root Cause:

 

 After analyzing we found that the trigger WWV_BIU_FLOW_FILE_OBJECTS becomes INVALID since there is a old version of apex already existed and installation is considered as upgrade.

 

Solution:

 

1. As SYS, drop the FLOW_FILES trigger WWV_BIU_FLOW_FILE_OBJECTS.

 

Sql> drop trigger FLOWS_FILES.wwv_biu_flow_file_objects;

 

2. Go to the apex/core directory of the APEX   19.1 installation media.

 

    This script is version dependent and you must use the script for the version of APEX installed in the database. If necessary, download a new copy of the installation media to the server.

$cd /apex/core s

 

3. Start sqlplus as SYS. 

$sqlplus / as sysdba

 

4. Recreate the trigger using the file flows_files_new2.sql (found in apex/core).

Execute below script

 

@flows_files_new2.sql

This script create a new trigger.

 

5. Validate APEX using below statement: 

 

exec sys.validate_apex

 

To avoid this type for error kindly check is there any apex schemas available in that database if it is there drop that old schema and upgarade the apex and do the fresh installation

 

DROP USER APEX_050000 CASCADE;