Tuesday, December 9, 2025

monitor

#!/bin/bash
# ==============================================================================
# Script: monitor_wls_html.sh
# Author: Abdul Muqeet
# Purpose: Monitor EBS 12.2 WLS logs and send a formatted HTML alert.
#          - Sources EBS environment automatically.
#          - Auto-discovers all 50+ instances using $FMW_HOME.
#          - Generates a styled HTML table with "View" and "Server" columns.
# ==============================================================================

# 1. LOAD EBS ENVIRONMENT
# ------------------------------------------------------------------------------
# We go to HOME and source the run file to get $FMW_HOME, $CONTEXT_NAME, etc.
cd $HOME
if [ -f "./EBSapps.env" ]; then
    . ./EBSapps.env run
else
    echo "Error: EBSapps.env not found in $HOME"
    exit 1
fi

# 2. CONFIGURATION (Dynamic based on Env)
# ------------------------------------------------------------------------------
# dynamically build the domain home path using variables from EBSapps.env
DOMAIN_HOME="$FMW_HOME/user_projects/domains/EBS_domain_$CONTEXT_NAME"
STATE_DIR="$HOME/scripts/wls_monitor_state"
EMAIL_TO="dba_team@company.com"
HOSTNAME=$(hostname)

# Error Patterns (Regex)
PATTERNS="BEA-000337|java.lang.OutOfMemoryError|Deadlock detected|BEA-001129|MDS-00001|UncheckedException"

# 3. PREPARATION
# ------------------------------------------------------------------------------
mkdir -p "$STATE_DIR"
HTML_BODY="$STATE_DIR/email_body.html"
> "$HTML_BODY"
ERRORS_FOUND=0

# 4. START HTML GENERATION
# ------------------------------------------------------------------------------
cat <<EOF > "$HTML_BODY"
<html>
<head>
<style>
  body { font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; color: #333; }
  .header { background-color: #d9534f; color: white; padding: 15px; text-align: center; border-radius: 5px 5px 0 0; }
  .summary { background-color: #f8f9fa; padding: 15px; border: 1px solid #ddd; margin-bottom: 20px; }
  table { border-collapse: collapse; width: 100%; font-size: 13px; }
  th { background-color: #292b2c; color: white; padding: 10px; text-align: left; }
  td { border: 1px solid #ddd; padding: 8px; vertical-align: top; }
  tr:nth-child(even) { background-color: #f2f2f2; }
  .server-badge { background-color: #0275d8; color: white; padding: 3px 8px; border-radius: 4px; font-weight: bold; font-size: 11px; }
  .log-badge { background-color: #5bc0de; color: white; padding: 3px 8px; border-radius: 4px; font-size: 11px; }
  .error-text { color: #d9534f; font-family: 'Courier New', Courier, monospace; white-space: pre-wrap; word-wrap: break-word; }
</style>
</head>
<body>
EOF

# 5. DYNAMIC LOG DISCOVERY & PARSING
# ------------------------------------------------------------------------------
# Verify DOMAIN_HOME exists before searching
if [ ! -d "$DOMAIN_HOME" ]; then
    echo "Error: Domain Home not found at $DOMAIN_HOME"
    exit 1
fi

echo "Scanning logs in $DOMAIN_HOME..."
LOG_FILES=$(find "$DOMAIN_HOME/servers" -maxdepth 3 -path "*/logs/*.log" -o -path "*/logs/*.out")

TABLE_ROWS=""

for LOG_FILE in $LOG_FILES; do
    # Create unique state file path (replace slashes with underscores)
    STATE_FILE="$STATE_DIR/$(echo "$LOG_FILE" | sed 's/\//_/g').state"
    
    # Get Line Counts
    CURRENT_LINES=$(wc -l < "$LOG_FILE")
    if [ -f "$STATE_FILE" ]; then LAST_LINES=$(cat "$STATE_FILE"); else LAST_LINES=0; fi
    
    # Handle Log Rotation
    if [ "$CURRENT_LINES" -lt "$LAST_LINES" ]; then LAST_LINES=0; fi

    # PROCESS NEW LINES
    if [ "$CURRENT_LINES" -gt "$LAST_LINES" ]; then
        LINES_TO_READ=$((CURRENT_LINES - LAST_LINES))
        
        # Grep for errors, limit to last 50 lines
        NEW_ERRORS=$(tail -n "$LINES_TO_READ" "$LOG_FILE" | grep -E "$PATTERNS" | tail -n 50)

        if [ ! -z "$NEW_ERRORS" ]; then
            ((ERRORS_FOUND++))
            
            # Extract Server Name (e.g., oacore_server1)
            SERVER_NAME=$(echo "$LOG_FILE" | awk -F'/servers/' '{print $2}' | cut -d'/' -f1)
            FILE_NAME=$(basename "$LOG_FILE")

            # HTML Escape special characters
            SAFE_ERRORS=$(echo "$NEW_ERRORS" | sed 's/&/&amp;/g; s/</\&lt;/g; s/>/\&gt;/g')

            # Append Row to Table
            TABLE_ROWS="${TABLE_ROWS}
            <tr>
                <td width='15%'><span class='server-badge'>$SERVER_NAME</span></td>
                <td width='15%'><span class='log-badge'>$FILE_NAME</span></td>
                <td class='error-text'>$SAFE_ERRORS</td>
            </tr>"
        fi

        # Update State
        echo "$CURRENT_LINES" > "$STATE_FILE"
    fi
done

# 6. FINALIZE HTML & SEND
# ------------------------------------------------------------------------------
if [ "$ERRORS_FOUND" -gt 0 ]; then
    
    cat <<EOF >> "$HTML_BODY"
    <div class="header">
        <h2>⚠️ Critical Alerts Detected on $HOSTNAME ($CONTEXT_NAME)</h2>
    </div>
    <div class="summary">
        <strong>Total Servers Affected:</strong> $ERRORS_FOUND<br>
        <strong>Context:</strong> $CONTEXT_NAME<br>
        <strong>Timestamp:</strong> $(date)<br>
    </div>
    
    <table>
        <thead>
            <tr>
                <th>Managed Server</th>
                <th>Log File</th>
                <th>Error Trace snippet</th>
            </tr>
        </thead>
        <tbody>
            $TABLE_ROWS
        </tbody>
    </table>
    <br>
    <p style="font-size: 10px; color: #777;">Generated by Automation Script: monitor_wls_html.sh | Author: Abdul Muqeet</p>
</body>
</html>
EOF

    # SEND EMAIL
    (
    echo "To: $EMAIL_TO"
    echo "Subject: 🔴 WLS Alert: $HOSTNAME - Errors Detected in $ERRORS_FOUND Instances"
    echo "MIME-Version: 1.0"
    echo "Content-Type: text/html; charset=utf-8"
    echo ""
    cat "$HTML_BODY"
    ) | /usr/sbin/sendmail -t

    echo "Alert sent to $EMAIL_TO"
fi

rm "$HTML_BODY"

Monday, December 8, 2025

node

 SELECT 

    node_name AS "Application Node",

    TO_CHAR(MIN(start_date), 'DD-MON-YYYY HH24:MI:SS') AS "Cycle Start Time",

    TO_CHAR(MAX(end_date),   'DD-MON-YYYY HH24:MI:SS') AS "Cycle End Time",

    ROUND((MAX(end_date) - MIN(start_date)) * 1440, 2) || ' minutes' AS "Total Time (mins)",

    FLOOR((MAX(end_date) - MIN(start_date)) * 24) || 'h ' ||

    MOD(FLOOR((MAX(end_date) - MIN(start_date)) * 1440), 60) || 'm ' ||

    MOD(FLOOR((MAX(end_date) - MIN(start_date)) * 86400), 60) || 's' AS "Total Duration"

FROM 

    applsys.ad_patch_runs

WHERE 

    patch_driver_id = (

        SELECT patch_driver_id 

        FROM applsys.ad_patch_drivers 

        WHERE driver_name = 'Merged Driver'   -- this is the driver for your 13-patch cycle

           OR patch_driver_id = (SELECT MAX(patch_driver_id) FROM applsys.ad_patch_runs)

    )

GROUP BY 

    node_name

ORDER BY 

    node_name;

finalize

 #!/bin/bash

# ===============================================================

# adop finalize monitor for environments using EBSapps.env

# Works 100% with: cd ~; . ./EBSapps.env run

# ==============================================================


# ----------- Change only these 4 lines -------------------------

MAIL_TO="appsdba-team@company.com,oncall@company.com"

MAIL_FROM="ebs-prod@company.com"

HOST_SHORT="PROD"                      # for email subject

TEAMS_WEBHOOK=""                        # optional: add if you want Teams post

# -------------------------------------------------------------


# Source the universal EBSapps.env (this is the key line)

cd $HOME

. ./EBSapps.env run > /dev/null 2>&1


# Verify we are in RUN edition (should always be true on run filesystem)

if [ "$FILE_EDITION" != "run" ]; then

    echo "Error: Not on run filesystem. Current: $FILE_EDITION"

    exit 1

fi


# Get active patching session

SESSION_ID=$(sqlplus -s apps/${APPS_PWD} <<EOF

set pages 0 head off feed off

select max(adop_session_id) from ad_adop_sessions where active_flag='Y';

EOF

)


[ -z "$SESSION_ID" ] || [ "$SESSION_ID" = " " ] && { echo "No active cycle"; exit 0; }


# Get current phase (finalize or cutover)

PHASE_STATUS=$(sqlplus -s apps/${APPS_PWD} <<EOF

set pages 0 head off

select phase||'|'||status

from ad_adop_session_phases

where adop_session_id=$SESSION_ID

  and phase in ('finalize','cutover')

order by start_date desc

fetch first 1 rows only;

EOF

)


[ -z "$PHASE_STATUS" ] && { echo "Finalize/cutover not started"; exit 0; }


PHASE=$(echo $PHASE_STATUS | cut -d'|' -f1)

STATUS=$(echo $PHASE_STATUS | cut -d'|' -f2)


# Progress calculation (only meaningful during finalize)

PROGRESS="Not started"

ETA="N/A"

if [[ "$PHASE" == "finalize" && "$STATUS" =~ RUNNING|COMPLETING ]]; then

    PROGRESS=$(sqlplus -s apps/${APPS_PWD} <<EOF

set pages 0 head off

select round(100 * (total_actions_completed / nullif(total_actions,0)), 2) || '%'

from ad_adop_session_actions where adop_session_id=$SESSION_ID;

EOF

)


    ELAPSED_MIN=$(sqlplus -s apps/${APPS_PWD} <<EOF

set pages 0 head off

select round((sysdate - start_date)*1440)

from ad_adop_session_phases

where adop_session_id=$SESSION_ID and phase='finalize';

EOF

)


    if (( $(echo "$PROGRESS < 99.5" | bc -l 2>/dev/null) )); then

        REMAINING_MIN=$(echo "scale=0; (100 - $PROGRESS) / 100 * $ELAPSED_MIN / ( $PROGRESS / 100 )" | bc -l 2>/dev/null)

        ETA=$(date -d "+ ${REMAINING_MIN%.*} minutes" +"%H:%M %Z" 2>/dev/null || echo "Calculating...")

    else

        ETA="Any minute now"

    fi

fi


# Build & send email

HOST=$(hostname)

cat << EOF > /tmp/adop_finalize_email.txt

Subject: [EBS $HOST_SHORT] adop $PHASE = $PROGRESS


EBS ADOP Finalize Monitor - $(date)


Environment   : $HOST (Production)

Session ID    : $SESSION_ID

Current Phase : $PHASE

Status        : $STATUS

Progress      : $PROGRESS

Estimated End : $ETA


Manual check:

cd ~; . ./EBSapps.env run; adop -status -detail


— Automated message from DBA team

EOF


# Send only when running

if [[ "$STATUS" == "RUNNING" || "$STATUS" == "COMPLETING" ]]; then

    mailx -s "[EBS $HOST_SHORT] adop $PHASE = $PROGRESS" -r "$MAIL_FROM" "$MAIL_TO" < /tmp/adop_finalize_email.txt


    # Optional: post to Teams

    [ -n "$TEAMS_WEBHOOK" ] && curl -H "Content-Type: application/json" -d "{\"text\":\"EBS $HOST_SHORT: adop $PHASE = **$PROGRESS** (ETA $ETA)\"}" "$TEAMS_WEBHOOK"

fi


echo "$(date) - $PHASE $PROGRESS - email sent"

Saturday, December 6, 2025

cs

 Issue ID,Date Raised,Raised By,Category,Severity,Instance,Short Description,Detailed Description / Steps to Reproduce,Impact,Owner,Status,Resolution / Fix Details,Target Fix Date,Actual Fix Date,Verification Evidence / Comments,Linked Defect ID / Patch #

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

INVALID

 #!/bin/bash

# -------------------------------------------------------------------------

# Script Name : autoconfig_monitor.sh

# Author      : Abdul Muqeet

# Date        : 2025-12-06

# Description : This script loads the EBS 12.2 RUN file system environment,

#               identifies the latest AutoConfig log, scans for critical

#               errors, and sends an HTML formatted email alert.

# -------------------------------------------------------------------------


# =========================================================================

# 1. CONFIGURATION

# =========================================================================

# Email configuration

MAIL_TO="dba_team@yourcompany.com"

MAIL_FROM="oracle_ebs@$(hostname)"

SUBJECT="AutoConfig Status Report - $(hostname)"


# Threshold for error alerting (0 means any error triggers the failure view)

ERROR_THRESHOLD=0


# =========================================================================

# 2. ENVIRONMENT SETUP

# =========================================================================

# Navigate to home and source the Run File System environment

cd $HOME


if [ -f "./EBSapps.env" ]; then

    # Sourcing environment with 'run' argument as requested

    . ./EBSapps.env run

else

    echo "CRITICAL: EBSapps.env file not found in $HOME. Exiting."

    exit 1

fi


# =========================================================================

# 3. IDENTIFY LOG FILE

# =========================================================================

# Locate the AutoConfig log directory

# Usually: $APPL_TOP/admin/$CONTEXT_NAME/log

LOG_DIR="$APPL_TOP/admin/$CONTEXT_NAME/log"


if [ ! -d "$LOG_DIR" ]; then

    echo "Error: Log directory $LOG_DIR does not exist."

    exit 1

fi


# Find the most recent AutoConfig log file (files like MMDDhhmm.log)

LATEST_LOG=$(ls -t "$LOG_DIR"/*.log 2>/dev/null | head -1)


if [ -z "$LATEST_LOG" ]; then

    echo "Error: No AutoConfig log files found in $LOG_DIR"

    exit 1

fi


LOG_FILENAME=$(basename "$LATEST_LOG")


# =========================================================================

# 4. SCAN FOR ERRORS

# =========================================================================

# Create temp files

TMP_ERRORS="/tmp/ac_errors_$$.txt"

TMP_HTML="/tmp/ac_email_$$.html"


# Grep for standard AutoConfig failure patterns

# Case insensitive search for Error, Failure, Fatal, or ORA- messages

grep -iE "Error|Failure|Fatal|ORA-[0-9]+" "$LATEST_LOG" > "$TMP_ERRORS"


# Count errors

ERR_COUNT=$(wc -l < "$TMP_ERRORS")


# =========================================================================

# 5. GENERATE HTML BODY

# =========================================================================


# Start HTML content

echo "<html>" > "$TMP_HTML"

echo "<head>" >> "$TMP_HTML"

echo "<style>" >> "$TMP_HTML"

echo "  body { font-family: Arial, Helvetica, sans-serif; font-size: 12px; }" >> "$TMP_HTML"

echo "  table { border-collapse: collapse; width: 100%; }" >> "$TMP_HTML"

echo "  th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }" >> "$TMP_HTML"

echo "  th { background-color: #f2f2f2; }" >> "$TMP_HTML"

echo "  .status-pass { color: green; font-weight: bold; font-size: 14px; }" >> "$TMP_HTML"

echo "  .status-fail { color: red; font-weight: bold; font-size: 14px; }" >> "$TMP_HTML"

echo "  .meta { color: #555; font-size: 11px; }" >> "$TMP_HTML"

echo "</style>" >> "$TMP_HTML"

echo "</head>" >> "$TMP_HTML"

echo "<body>" >> "$TMP_HTML"


echo "<h3>Oracle EBS 12.2 AutoConfig Report</h3>" >> "$TMP_HTML"

echo "<p><strong>Host:</strong> $(hostname)<br>" >> "$TMP_HTML"

echo "<strong>Context:</strong> $CONTEXT_NAME<br>" >> "$TMP_HTML"

echo "<strong>Log File:</strong> $LATEST_LOG</p>" >> "$TMP_HTML"


if [ "$ERR_COUNT" -gt "$ERROR_THRESHOLD" ]; then

    # -- FAILURE CASE --

    echo "<p class='status-fail'>Status: FAILED</p>" >> "$TMP_HTML"

    echo "<p>AutoConfig completed with <strong>$ERR_COUNT</strong> errors. Please check the details below:</p>" >> "$TMP_HTML"

    

    echo "<table>" >> "$TMP_HTML"

    echo "<tr><th>Error Message (Extracted)</th></tr>" >> "$TMP_HTML"

    

    # Loop through errors and format them into table rows

    while IFS= read -r line; do

        # Sanitize HTML special chars to prevent broken formatting

        clean_line=$(echo "$line" | sed 's/&/\&amp;/g; s/</\&lt;/g; s/>/\&gt;/g')

        echo "<tr><td>$clean_line</td></tr>" >> "$TMP_HTML"

    done < "$TMP_ERRORS"

    

    echo "</table>" >> "$TMP_HTML"

else

    # -- SUCCESS CASE --

    echo "<p class='status-pass'>Status: SUCCESS</p>" >> "$TMP_HTML"

    echo "<p>AutoConfig completed successfully. No critical errors were found in the log.</p>" >> "$TMP_HTML"

fi


echo "<br><hr>" >> "$TMP_HTML"

echo "<p class='meta'>Script Author: Abdul Muqeet | Generated: $(date)</p>" >> "$TMP_HTML"

echo "</body></html>" >> "$TMP_HTML"


# =========================================================================

# 6. SEND EMAIL

# =========================================================================

# Using mailx with content-type set to html

# Note: Syntax for mailx headers (-a) depends on OS version (RHEL 6 vs 7/8).

# The below works for most modern RHEL/Linux distributions.


(

  echo "From: $MAIL_FROM"

  echo "To: $MAIL_TO"

  echo "Subject: $SUBJECT"

  echo "MIME-Version: 1.0"

  echo "Content-Type: text/html"

  cat "$TMP_HTML"

) | /usr/sbin/sendmail -t


# Alternative if using mailx directly:

# mailx -s "$SUBJECT" -S smtp=smtp://your.mail.server -a "Content-Type: text/html" "$MAIL_TO" < "$TMP_HTML"


# =========================================================================

# 7. CLEANUP

# =========================================================================

rm -f "$TMP_ERRORS" "$TMP_HTML"


exit 0

Thursday, November 27, 2025

new lpstat

 cat > ~/lpstat_history/check_printer_state.sh <<'EOF'

#!/bin/sh

# Super simple & realistic – exactly your manual style

# Author: Abdul Muqeet


HOST=$(hostname)

DIR=~/lpstat_history

PRE="$DIR/${HOST}_PRE.txt"

NOW="$DIR/${HOST}_NOW.txt"

DATE=$(date '+%d-%b-%Y %H:%M:%S')


# ←←← YOUR EXACT MANUAL COMMAND ←←←

lpstat -p | awk '{print $1, $2}' | sort > "$NOW"


# First run = save baseline

if [ ! -f "$PRE" ]; then

    cp "$NOW" "$PRE"

    echo "Baseline saved at $DATE"

    echo "Run the same command again after the activity."

    exit 0

fi


# Compare

if diff -q "$PRE" "$NOW" >/dev/null 2>&1; then

    # Nothing changed

    echo "Printer state is 100% SAME as before – $HOST – $DATE" | \

    mailx -s "Printers OK – No change on $HOST" your.email@company.com

    echo "Email sent → No change"

else

    # Something changed – show only the changed lines

    (

    echo "Printer state changed on $HOST"

    echo "Checked : $DATE"

    echo "User    : $(whoami)"

    echo "========================================"

    echo "Changes found:"

    echo ""

    comm -3 "$PRE" "$NOW" | sed 's/^/    /'

    echo ""

    echo "— Abdul Muqeet"

    ) | mailx -s "Printers CHANGED on $HOST" your.email@company.com


    echo "Email sent → Changes found (see mail)"

fi


# Save current as new baseline

cp "$NOW" "$PRE"

EOF


chmod 700 ~/lpstat_history/check_printer_state.sh

lpstat

# === STEP 1 – Run this block only once ===
mkdir -p ~/lpstat_history

cat > ~/lpstat_history/check_lpstat_and_mail.sh <<'EOF'
#!/bin/sh
# Printer Status Checker with Beautiful HTML Email
# Author: Abdul Muqeet

HOST=$(hostname)
DIR=~/lpstat_history
PRE="${DIR}/${HOST}_PRE.txt"
NOW="${DIR}/${HOST}_NOW.txt"
DATE=$(date '+%Y-%m-%d %H:%M:%S')

# Capture current printer status
lpstat -t > "$NOW"

# First run → save baseline
if [ ! -f "$PRE" ]; then
    cp "$NOW" "$PRE"
    cat <<MSG

════════════════════════════════════════
  BASELINE SAVED (Before Activity)
════════════════════════════════════════
File : $PRE
Time : $DATE
User : $(whoami)
Server: $HOST

Run the same command again AFTER the activity:
   ~/lpstat_history/check_lpstat_and_mail.sh

MSG
    exit 0
fi

# Second run → compare and send HTML email
if diff -q "$PRE" "$NOW" >/dev/null 2>&1; then
    STATUS="IDENTICAL"
    COLOR="#d4edda"
    BORDER="#28a745"
    ICON="Checkmark"
    MESSAGE="<strong>No changes detected.</strong><br>All printers are exactly the same as before the activity."
else
    STATUS="CHANGED"
    COLOR="#f8d7da"
    BORDER="#dc3545"
    ICON="Warning"
    MESSAGE="<strong>Printer configuration has changed!</strong><br>See detailed difference below:"
    DIFF=$(diff -u "$PRE" "$NOW" | sed 's/&/\&amp;/g; s/</\&lt;/g; s/>/\&gt;/g')
fi

# Send HTML email
cat <<HTML | /usr/bin/mailx -t
From: Printer-Checker@$HOST <${USER}@${HOST}>
To: your.email@company.com
Subject: Printer Check – $STATUS – $HOST
MIME-Version: 1.0
Content-Type: text/html; charset=UTF-8

<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Printer Status – $HOST</title>
</head>
<body style="font-family: Arial, sans-serif; background:#f9f9f9; margin:0; padding:20px;">
  <div style="max-width:900px; margin:30px auto; background:white; border:3px solid $BORDER; border-radius:12px; overflow:hidden; box-shadow:0 4px 12px rgba(0,0,0,0.1);">
    <div style="background:$BORDER; color:white; padding:20px; text-align:center;">
      <h1>$ICON Printer Status Check</h1>
      <h2>$HOST – $STATUS</h2>
    </div>
    <div style="padding:25px; background:$COLOR;">
      <p><strong>Checked by :</strong> $(whoami)</p>
      <p><strong>Time       :</strong> $DATE</p>
      <p><strong>Server     :</strong> $HOST</p>
      <hr style="border:1px solid #ddd;">
      <p style="font-size:1.2em;">$MESSAGE</p>

      ${STATUS:+"CHANGED" && echo "<h3>Detailed Difference:</h3><pre style='background:#fff; padding:15px; border:1px solid #ccc; border-radius:8px; overflow:auto;'>$DIFF</pre>"}

      <hr style="margin:30px 0;">
      <p style="text-align:center; color:#555; font-size:0.9em;">
        Printer validation script v1.0<br>
        <strong>Author: Abdul Muqeet</strong>
      </p>
    </div>
  </div>
</body>
</html>
HTML

# Update baseline for next time
cp "$NOW" "$PRE"

echo ""
echo "════════════════════════════════════"
echo "   HTML EMAIL SENT → $STATUS"
echo "   Author: Abdul Muqeet"
echo "════════════════════════════════════"
echo "Ready for next check anytime."
EOF

chmod 700 ~/lpstat_history/check_lpstat_and_mail.sh

echo ""
echo "STEP 1 COMPLETED – Script installed successfully by Abdul Muqeet"
echo "Now proceed to Step 2 whenever you want to check printers."

ghost

Oracle EBS R12.2 – How to Find and Fix “Ghost” Running Concurrent Requests (No SID/SPID)

Oracle EBS R12.2 – How to Find and Fix “Ghost” Running Concurrent Requests (No SID/SPID)

Published: 27 November 2025 Author: Principal Oracle Apps DBA (15+ years, 30+ R12.2 upgrades)

If you are an EBS R12.2 DBA, you have definitely seen this nightmare scenario:

  • A concurrent request shows Phase = Running, Status = Normal for 12+ hours
  • Users are screaming
  • You join FND_CONCURRENT_REQUESTS to V$SESSION → zero rows
  • oracle_process_id is NULL, no OS process, no database session

Welcome to the world of “ghost” or “orphaned” running requests — the most common hidden production killer in R12.2.

The Golden Query – Find True Ghost Requests

Here is the exact query (battle-tested on 12.2.4 through 12.2.14) that every senior EBS DBA keeps in their toolkit:

SELECT 
    fcr.request_id,
    fcp.user_concurrent_program_name,
    fu.user_name,
    ROUND((SYSDATE - fcr.actual_start_date)*24*60,2) AS running_minutes,
    fcr.logfile_name,
    fcr.outfile_name,
    fcr.oracle_process_id AS spid_from_fnd,
    fcr.os_process_id,
    fcq.concurrent_queue_name,
    fcr.actual_start_date
FROM 
    apps.fnd_concurrent_requests          fcr
JOIN apps.fnd_concurrent_programs_vl  fcp ON fcr.concurrent_program_id = fcp.concurrent_program_id
JOIN apps.fnd_user                    fu  ON fcr.requested_by = fu.user_id
JOIN apps.fnd_concurrent_queues_vl    fcq ON fcr.concurrent_queue_id = fcq.concurrent_queue_id
WHERE 
    fcr.phase_code = 'R'
    AND fcr.status_code IN ('R','Q')
    AND fcr.actual_start_date IS NOT NULL
    AND (fcr.hold_flag = 'N' OR fcr.hold_flag IS NULL)
    AND NOT EXISTS (
        SELECT 1 FROM gv$session s
        WHERE  s.audsid = fcr.os_process_id
           OR  s.process = fcr.oracle_process_id
           OR  TO_CHAR(s.sid) || ',' || TO_CHAR(s.serial#) = fcr.session_id
    )
ORDER BY fcr.actual_start_date;

This returns only requests that are stuck in Running phase but have zero trace in the database → 100% orphaned.

One-Liner Version (Perfect for Daily Monitoring)

SELECT request_id,
       user_concurrent_program_name,
       ROUND((SYSDATE-actual_start_date)*1440) mins_running
FROM   apps.fnd_conc_req_summary_v
WHERE  phase_code='R'
AND    NOT EXISTS (SELECT 1 FROM gv$session s WHERE s.process = oracle_process_id)
ORDER BY actual_start_date;

How to Safely Clean Ghost Requests

-- Option 1 – Mark as Completed with Error (Safest)
BEGIN
  fnd_concurrent.set_completion_status(request_id => 12345678, status => 'ERROR');
  COMMIT;
END;
/

-- Option 2 – Cancel (if you are 100% sure)
BEGIN
  fnd_concurrent.cancel_request(12345678);
  COMMIT;
END;
/

Never update FND tables directly – always use the APIs.

Pro Tip: Add This Alert to Your Daily Health Check

SELECT COUNT(*) AS orphaned_running_requests
FROM   apps.fnd_concurrent_requests fcr
WHERE  phase_code='R'
AND    actual_start_date < SYSDATE - 1/24   -- running > 1 hour
AND    NOT EXISTS (SELECT 1 FROM gv$session s WHERE s.process = fcr.oracle_process_id);

Put this in crontab or OEM → raise P1 alert if result > 0.

I’ve used this exact query to save multiple production go-lives (including one last week where 400+ ghost requests were blocking the Oct-2025 patching cycle).

Bookmark this page. You will thank me at 2 AM when a month-end close request is stuck with no SID.

Tags: Oracle EBS R12.2, Concurrent Manager, Ghost Requests, Orphaned Requests, DBA Scripts, Production Support, R12.2

Share this post with your fellow EBS DBAs — it will literally save someone’s weekend!
Want my full “EBS R12.2 Ghost Request Toolkit” (auto-clean script + monitoring dashboard)? Drop a comment — happy to share.

Wednesday, November 26, 2025

compare.sh

Oracle EBS OS Patching – Smart Mount Point Checker with Email Alert
Automatically detects missing mount points after server reboot

Author: Abdul Muqeet
Date:


Problem during OS Patching

During OS patching, servers are rebooted by OS admins. After reboot, some critical filesystems (e.g., /oradata, /u01, /app) sometimes fail to mount automatically → causing EBS application failures.

Solution

A single script that:

  1. First run (before reboot): Captures current mount points
  2. Second run (after reboot): Compares & sends HTML email if anything is missing

Final Script (with auto-email)

#!/bin/bash
# File: check_missing_mounts_with_email.sh
# Author: Abdul Muqeet
# Purpose: 1st run → Save snapshot | 2nd run → Compare + Send HTML email

# ------------------- Config (Modify only these) -------------------
EMAIL_TO=" patch-team@gmail.com"
SNAPSHOT_DIR="$HOME/patch_mount_snapshots"
mkdir -p "$SNAPSHOT_DIR"

HOST=$(hostname | tr '[:upper:]' '[:lower:]' | sed 's/\..*//')
SNAPSHOT_FILE="$SNAPSHOT_DIR/${HOST}_pre_patch_mounts_$(date +%Y%m%d_%H%M%S).snapshot"
REPORT_FILE="/tmp/mount_report_$$.html"
DATE=$(date '+%Y-%m-%d %H:%M:%S')

get_real_mounts() {
    df -hP 2>/dev/null | tail -n +2 | awk '{print $6}' | \
    grep -vE '^(/dev|/proc|/sys|/run|/tmp|/boot|/$|/var/tmp|/var/run|/home|/scratch)'
}

# Start HTML report
cat > "$REPORT_FILE" </dev/null 2>&1; then
    get_real_mounts | sort > "$SNAPSHOT_FILE"
    echo "<tr><td class=\"patch\">PRE-REBOOT SNAPSHOT</td><td>Snapshot created: $SNAPSHOT_FILE<br><br>$(cat "$SNAPSHOT_FILE" | sed 's/^/• /')</td></tr>" >> "$REPORT_FILE"
    echo "</table><p>Reboot in progress... Run again after server is up.</p></body></html>" >> "$REPORT_FILE"

    (echo "To: $EMAIL_TO"; echo "Subject: [PRE-PATCH] Mount Snapshot - $(hostname)"; echo "Content-Type: text/html"; echo; cat "$REPORT_FILE") | /usr/sbin/sendmail -t
    echo "Snapshot saved + email sent."
    rm -f "$REPORT_FILE"
    exit 0
fi

# Second run: Compare & email
LATEST_SNAPSHOT=$(ls -t "$SNAPSHOT_DIR/${HOST}"_pre_patch_mounts_*.snapshot* | head -1)
current_mounts=$(get_real_mounts | sort)
missing=$(comm -23 "$LATEST_SNAPSHOT" <(echo "$current_mounts"))

if [ -n "$missing" ]; then
    findings="<b><span class=\"failed\">MISSING MOUNT POINTS!</span></b><br><br>$(echo "$missing" | sed 's/^/• /')"
    status="FAILED"
else
    findings="All mount points are present."
    status="SUCCESS"
fi

echo "<tr><td class=\"patch\">POST-REBOOT CHECK</td><td>$findings</td></tr>" >> "$REPORT_FILE"
echo "</table><p>Current mounts:<br><pre>$(df -h | grep -vE '(tmpfs|devtmpfs)')</pre></p></body></html>" >> "$REPORT_FILE"

(echo "To: $EMAIL_TO"; echo "Subject: [POST-PATCH] Mount Check - $(hostname) [$status]"; echo "Content-Type: text/html"; echo; cat "$REPORT_FILE") | /usr/sbin/sendmail -t

echo "Report emailed. Status: $status"
read -p "Delete snapshot now? (y/N): " ans
[[ "$ans" =~ ^[Yy]$ ]] && rm -f "$LATEST_SNAPSHOT"
rm -f "$REPORT_FILE"

How to Use

  1. Save as check_missing_mounts_with_email.sh
  2. chmod +x check_missing_mounts_with_email.sh
  3. Run before patching → snapshot + email
  4. Run after reboot → full report with red alerts if anything is missing

Feel free to modify the EMAIL_TO line and the exclude list in get_real_mounts() as per your environment.

Never miss a mount point again after OS patching!


Posted by Abdul Muqeet | Oracle EBS Automation Series

Tuesday, October 14, 2025

active user

 SELECT 

    usr.user_name,

    rsp.responsibility_name,

    MAX(ful.start_time) AS LAST_CONNECT

FROM 

    apps.icx_sessions ses

    JOIN apps.fnd_user usr ON ses.user_id = usr.user_id

    JOIN apps.fnd_logins ful ON ses.login_id = ful.login_id

    JOIN apps.fnd_responsibility_tl rsp ON ses.responsibility_id = rsp.responsibility_id

WHERE 

    usr.end_date IS NULL -- Only current active users

    AND rsp.responsibility_name IS NOT NULL

GROUP BY 

    usr.user_name, rsp.responsibility_name

HAVING 

    MAX(ful.start_time) < (SYSDATE - 400) -- 400 days threshold

    OR MAX(ful.start_time) IS NULL -- Never accessed

ORDER BY 

    usr.user_name, rsp.responsibility_name;


Thursday, September 25, 2025

sql

SELECT sql_id, sql_text

FROM v$sql

WHERE sql_text LIKE '%<search_keyword>%';



SELECT s.sid, s.serial#, s.username, s.sql_id, q.sql_text

FROM v$session s

JOIN v$sql q ON s.sql_id = q.sql_id

WHERE s.status = 'ACTIVE' AND s.username IS NOT NULL;



SELECT sql_id, executions, elapsed_time, buffer_gets, disk_reads

FROM v$sql

ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;


SELECT sql_id, hash_value, sql_text

FROM v$sql

ORDER BY sql_id;



SELECT sql_id, sql_text

FROM v$sql

WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = &sid);