Oracle Apps DBA stuff
Tuesday, December 9, 2025
monitor
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/&/\&/g; s/</\</g; s/>/\>/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
ghost
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_idis 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.
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:
- First run (before reboot): Captures current mount points
- 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
- Save as
check_missing_mounts_with_email.sh chmod +x check_missing_mounts_with_email.sh- Run before patching → snapshot + email
- 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);