Syed Anwar Ahmed – Oracle DBA Blog

Author: SYED ANWAR AHMED

  • Oracle Database Session Spike Mystery: When Connection Pools Collide

    In a production Oracle database environment, a sudden spike in session count exceeding 1000+ sessions triggered alerts and concern. Interestingly, the system recovered automatically without any database-level intervention. At first glance this appeared to be a database issue — but deeper analysis revealed a different story.

    The Incident

    An automated alert reported session count exceeding threshold (1000+), with the majority in INACTIVE state from middleware connection pool accounts. Despite the spike there were no blocking sessions, no performance degradation, and no database errors.

    -- Quick session count check
SELECT COUNT(*) FROM gv$session;
-- Result: 216 (already returning to normal)

-- Session breakdown by status
SELECT status, COUNT(*) cnt
FROM gv$session
GROUP BY status
ORDER BY cnt DESC;

    Root Cause

    Multiple production mid-tier servers simultaneously created new connection pools at the same time window. New pools created new database sessions while existing pools kept their sessions alive (INACTIVE) pending graceful termination — resulting in a temporary overlap:

    Old Sessions (Inactive) + New Sessions (Active) = Session Surge

    As older pools were cleaned up, inactive sessions terminated automatically and the count returned to baseline. This was not a database problem — it was connection pool lifecycle behavior in the mid-tier layer.

    Recommendations

    • Stagger connection pool refresh across mid-tier servers to avoid simultaneous spikes
    • Monitor inactive session trends to detect abnormal accumulation early
    • Configure appropriate idle timeout, maximum pool size, and session reuse settings

    Key Takeaways

    • Not all session spikes are database problems — check middleware behavior first
    • High session count does not necessarily indicate database stress
    • Transient issues still require analysis as they reveal architectural inefficiencies
    • Database alerts can originate from upstream connection management behavior

    Written by Syed Anwar Ahmed — Oracle Apps DBA with 11 years of production experience.
    Connect: sdanwarahmed@gmail.com  |  LinkedIn

  • Oracle Alert Log Deep Dive: Interpreting ORA-00031 and Redo Log Pressure Without Production Changes

    Production alert logs often contain messages that appear critical but are, in reality, indicators of normal database behavior under load. This article presents a real-world Oracle database investigation where repeated ORA-00031: session marked for kill messages and redo log allocation waits were observed. Using read-only analysis techniques, we demonstrate how to distinguish between expected behavior and actionable signals without performing any intrusive changes.

    Observed Symptoms

    ORA-00031: session marked for kill
Thread 1 cannot allocate new log
Private strand flush not complete

    Phase 1: Interpreting ORA-00031 Correctly

    ORA-00031 is generated when sessions are terminated using ALTER SYSTEM KILL SESSION. Oracle marks the session for cleanup and handles it asynchronously via background processes. This is not an error — it is a confirmation of successful session termination.

    Phase 2: Identifying the True Performance Signal

    The more critical messages were Thread 1 cannot allocate new log and Private strand flush not complete. These occur when LGWR attempts a redo log switch but active redo strands are still flushing. Oracle briefly delays the log switch until consistency is ensured — this is a redo allocation wait, typically seen under sustained transactional load.

    Phase 3: Evidence-Based Analysis (Read-Only)

    Redo switch frequency was analyzed to validate system behavior:

    SELECT
    TO_CHAR(TRUNC(first_time, 'HH24'), 'YYYY-MM-DD HH24:MI') AS switch_hour,
    COUNT(*) AS switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TRUNC(first_time, 'HH24')
ORDER BY 1;

    Findings

    MetricObservation
    Average Switch Rate5-7 per hour
    Peak Rate8-10 per hour during business hours
    Off-Peak Rate1-3 per hour

    A direct correlation was observed between log switch spikes and high DML activity, confirming a cause-effect relationship rather than random errors.

    Why No Changes Were Made

    In this scenario, production environment restrictions were in place, no user impact was observed, and the behavior was transient and self-resolving. A monitoring-first approach was adopted instead of immediate tuning.

    Recommendations

    • Continuously monitor redo switch frequency during peak windows
    • Use collected data to justify future redo log sizing via change management
    • Avoid unnecessary intervention when behavior is transient and non-impacting
    • Distinguish informational alert log messages from actionable errors

    Key Takeaways

    • ORA-00031 is expected and harmless — it confirms session termination
    • Redo allocation waits are transient under sustained load
    • Proper analysis prevents unnecessary production intervention
    • Not all alert log warnings indicate failure — some are early signals of workload growth
    • The goal is not to eliminate every alert, but to understand which ones matter

    Written by Syed Anwar Ahmed — Oracle Apps DBA with 11 years of production experience.
    Connect: sdanwarahmed@gmail.com  |  LinkedIn

  • Resolving Critical FRA Space Issue Using RMAN Optimization (Real-World DBA Scenario)

    During routine monitoring, a critical issue was identified where the Fast Recovery Area (FRA) utilization reached 95%, putting the database at risk of ORA-19809, archiver process failure, and potential database hang. Despite having an RMAN retention policy configured, the FRA continued to grow uncontrollably.

    Investigation

    -- Check FRA usage
SELECT name, space_limit/1024/1024/1024 limit_gb,
       space_used/1024/1024/1024 used_gb,
       ROUND(space_used/space_limit*100,2) pct_used
FROM v$recovery_file_dest;

-- Check what is consuming space
SELECT file_type, percent_space_used, number_of_files
FROM v$flash_recovery_area_usage
ORDER BY percent_space_used DESC;

-- Review RMAN configuration
SHOW ALL;

    Findings: backupsets were consuming the majority of FRA space. RMAN had a 7-day retention policy configured but backup optimization was disabled and archivelog deletion policy was not set.

    Root Cause

    The retention policy was defined but obsolete backups were not being automatically deleted. There was no scheduled execution of DELETE OBSOLETE, backup optimization was disabled causing redundant data, and archivelog lifecycle was not controlled. This led to continuous accumulation of backupsets and archivelogs.

    Immediate Fix

    -- Run RMAN cleanup
RMAN> CROSSCHECK BACKUP;
RMAN> DELETE EXPIRED BACKUP;
RMAN> DELETE OBSOLETE;

-- Verify FRA usage after cleanup
SELECT ROUND(space_used/space_limit*100,2) pct_used
FROM v$recovery_file_dest;

    FRA utilization reduced from 95% to 83% after cleanup.

    Configuration Fixes

    -- Enable backup optimization
CONFIGURE BACKUP OPTIMIZATION ON;

-- Set archivelog deletion policy
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

-- Enable compressed backups
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

    Automation

    Scheduled daily execution of DELETE OBSOLETE via cron:

    -- Add to RMAN backup script
DELETE NOPROMPT OBSOLETE;

# Crontab entry for daily cleanup
0 2 * * * /path/to/rman_cleanup.sh >> /tmp/rman_cleanup.log 2>&1

    Key Takeaways

    • RMAN retention policy does not enforce deletion automatically — you must schedule DELETE OBSOLETE
    • Backup lifecycle management must include automation
    • Monitor FRA usage proactively to prevent saturation
    • Enable backup optimization to avoid redundant data
    • Set archivelog deletion policy to control archivelog lifecycle

    Written by Syed Anwar Ahmed — Oracle Apps DBA with 11 years of production experience.
    Connect: sdanwarahmed@gmail.com  |  LinkedIn

  • Oracle EBS Login Issue: Real-Time Production Incident and Fix (OACORE + WebLogic Filter)

    📌 Introduction

    In Oracle E-Business Suite (EBS) environments, login failures are often perceived as simple application issues. However, in complex architectures, they can originate from multiple interacting layers across the application and middleware stack.

    In this blog, I’ll walk through a real-world production incident where an OACORE JVM issue combined with WebLogic security filtering resulted in complete login inaccessibility.

    This case highlights the importance of analyzing both performance and security layers together when troubleshooting critical application outages.

    ⚠️ Issue Summary

    • Users were unable to access the EBS login page
    • Pages were hanging or not loading
    • WebLogic console reported:
    Connection rejected, filter blocked Socket

    🔍 Initial Observation

    From the application server:

    • Load Average: ~10+

    👉 This indicated:

    • High CPU utilization
    • System under heavy stress
    • Potential JVM thread contention

    🔬 Detailed Analysis

    • One of the OACORE managed server JVMs became unresponsive
    • Long-running threads caused thread pool exhaustion
    • Incoming user requests began queueing

    At the same time:

    • WebLogic connection filter was actively enforcing access rules
    • Legitimate requests were being rejected under stressed conditions

    🧠 Understanding the Components

    OACORE (Application Layer)

    Handles:

    • Login requests
    • Forms processing
    • Core application logic

    If JVM threads are exhausted:
    👉 Requests queue → login hangs

    Oracle WebLogic Server Connection Filter

    EBS environments may use:

    oracle.apps.ad.tools.configuration.wls.filter.EBSConnectionFilterImpl

    This filter:

    • Enforces IP-based access control
    • Overrides default allow rules

    If misconfigured or stressed:
    👉 Legitimate traffic may be blocked

    🎯 Root Cause Analysis (RCA)

    The login issue was not caused by a single failure point, but by a combination of application tier resource exhaustion and restrictive middleware-level access control.

    • High CPU utilization and long-running threads caused one OACORE JVM to become unresponsive
    • Thread pool exhaustion led to request queuing, preventing new login requests from being processed

    Simultaneously:

    • The WebLogic connection filter (EBSConnectionFilterImpl) enforced strict access control policies
    • Under high load conditions, legitimate client requests were rejected with “filter blocked Socket”

    This interaction between performance degradation and security enforcement amplified the impact, resulting in complete login inaccessibility despite partial system availability.

    🛠️ Resolution Approach (Controlled & Safe)

    The resolution approach focused on stabilizing the JVM layer while validating and correcting middleware-level access controls in a controlled manner.

    🔹 Step 1: Identify Unresponsive JVM

    ps -ef | grep oacore

    ✔ Identify JVM with abnormal CPU or stuck behavior

    🔹 Step 2: Handle Stuck JVM (Controlled Action)

    ⚠️ Important Note:

    Forcefully terminating JVM processes should NOT be performed without validation.

    ✔ Recommended Approach:

    • Confirm the process is unresponsive
    • Ensure no critical transactions are running
    • Prefer controlled shutdown where possible

    ✔ Example (Only if fully unresponsive and approved):

    kill -9 <PID>

    👉 Node Manager can restart the JVM automatically after termination

    🔹 Step 3: Rolling Restart of OACORE

    admanagedsrvctl.sh stop oacore_server1
    admanagedsrvctl.sh start oacore_server1

    ✔ Ensures clean JVM state
    ✔ Restores thread pool balance

    🔹 Step 4: Validate WebLogic Connection Filter

    ⚠️ Important Note:

    The WebLogic connection filter is a security control and should NOT be disabled permanently.

    ✔ In this case:

    • Filter behavior was validated as part of troubleshooting
    • Temporary relaxation was used to confirm impact

    ✔ Recommended Approach:

    • Review allowed IP ranges
    • Validate filter configuration
    • Re-enable filter after correction

    The filter was re-enabled after validation and correction of configuration.

    🔹 Step 5: Restart Admin Server (If Required)

    adadminsrvctl.sh stop
    adadminsrvctl.sh start

    ✔ Ensures configuration changes are applied

    ✅ Final Outcome

    • System load reduced from ~10 → ~1.5
    • OACORE JVMs stabilized
    • Login page restored
    • Users successfully accessed the application

    📊 Key Learnings

    1. OACORE JVM issues can manifest as complete application outages due to thread exhaustion and request queuing.
    2. WebLogic connection filters can unintentionally block legitimate traffic, especially under high load conditions.
    3. Multi-layer failures (application + middleware + security) significantly amplify incident impact.
    4. System load metrics (CPU, load average) provide early indicators of JVM stress and should be monitored proactively.

    🧩 Preventive Measures

    • Monitor JVM thread utilization proactively
    • Review connection filter configurations periodically
    • Avoid peak concurrent load spikes
    • Implement alerting for high CPU / load conditions
    • Validate session and request handling patterns

    🔐 Governance Considerations

    • Avoid forceful JVM termination without proper validation
    • Do not disable security controls without understanding impact
    • Follow change management procedures in production
    • Coordinate with application and security teams before changes

    🏁 Conclusion

    This incident demonstrates that login failures in Oracle EBS are not always isolated to a single component but can result from complex interactions across application performance and middleware security layers.

    The combination of JVM resource exhaustion and connection filtering behavior created a compounded failure scenario, leading to complete login disruption.

    A structured, multi-layer troubleshooting approach—focused on performance, configuration, and governance—enabled effective resolution while minimizing risk.

    This reinforces the importance of analyzing both system behavior and security controls together when addressing critical production incidents.

    💡 Pro Tip

    When troubleshooting Oracle EBS login issues, always validate both:

    • JVM health (thread utilization, CPU load)
    • Middleware controls (connection filters, access rules)

    Ignoring either layer can lead to incomplete or misleading diagnosis.

  • Oracle EBS OACORE Server in FAILED_NOT_RESTARTABLE State: Real-Time Issue, RCA and Fix

    In Oracle E-Business Suite (EBS) environments, application tier stability is critical to ensure seamless user experience. However, there are scenarios where managed servers behave unexpectedly and require manual intervention. This post walks through a real-world production issue where an OACORE managed server entered a FAILED_NOT_RESTARTABLE state, its impact, root cause analysis, and how it was resolved.

    Environment Details

    • Oracle E-Business Suite: R12.2.x
    • Application Tier: WebLogic Managed Servers
    • Component Impacted: OACORE Server (oacore_server1)
    • Environment Type: Production

    Problem Statement

    An alert was received indicating oacore_server1 was in FAILED_NOT_RESTARTABLE state. Upon verification, the server was running but Node Manager could not auto-restart it.

    Key Observations

    Despite the OACORE server being in a failed state, the application remained accessible and functional — traffic was being handled by other OACORE servers. This is due to the multi-OACORE architecture with load balancing via OHS/Web tier. However, this creates a hidden risk: load redistribution increases pressure on remaining servers and can lead to cascading failures if not addressed promptly.

    Detailed Analysis

    • Managed server restart attempts failed during initialization
    • Bulk concurrent requests were actively running
    • CPU utilization spiked on the application tier
    • JVM resources were under pressure

    Understanding FAILED_NOT_RESTARTABLE

    In Oracle WebLogic Server, a managed server is marked as FAILED_NOT_RESTARTABLE after repeated unsuccessful restart attempts. This is a protective mechanism designed to prevent unstable restart loops when the server cannot recover successfully.

    Root Cause Analysis

    The OACORE managed server entered FAILED_NOT_RESTARTABLE state due to repeated startup failures following an unclean or resource-constrained shutdown. High CPU utilization and heavy concurrent workload placed JVM resources under pressure, preventing a clean restart cycle. Residual runtime artifacts (such as incomplete shutdown state or resource locks) prevented successful reinitialization, causing WebLogic to mark the server as FAILED_NOT_RESTARTABLE.

    Resolution

    cd $ADMIN_SCRIPTS_HOME
./admanagedsrvctl.sh stop oacore_server1
./admanagedsrvctl.sh start oacore_server1

    After the controlled restart, the server returned to RUNNING state with all deployments active and the application stable.

    Identify Inactive Forms Sessions

    Inactive sessions holding resources can contribute to JVM pressure. Use this query to identify them safely — do not terminate without proper validation and approvals:

    SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.program,
       s.machine,
       ROUND(s.last_call_et/3600,2) AS hours_inactive
FROM v$session s
WHERE s.status = 'INACTIVE'
AND s.username = 'APPS'
AND s.program LIKE 'frmweb%'
AND s.last_call_et > 28800   -- 8 hours
ORDER BY hours_inactive DESC;

    Reference only — do NOT execute without validation:

    ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

    Automate Session Monitoring

    Use this script to monitor inactive sessions every 8 hours via cron:

    #!/bin/bash
export ORACLE_SID=your_sid
export ORACLE_HOME=/path/to/oracle_home
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s / as sysdba <<EOF
SET LINES 200
SET PAGES 200
SELECT COUNT(*) AS inactive_sessions
FROM v\$session
WHERE status='INACTIVE'
AND username='APPS'
AND program LIKE 'frmweb%'
AND last_call_et > 28800;
EXIT;
EOF
    # Crontab entry - every 8 hours
0 */8 * * * /path/to/inactive_sessions.sh >> /tmp/inactive_sessions.log

    DBA Quick Commands

    -- Check system load
top
uptime
ps -ef | grep oacore

-- Check running concurrent requests
SELECT request_id, phase_code, status_code
FROM fnd_concurrent_requests
WHERE phase_code = 'R';

    Key Takeaways

    • Application may appear healthy even when an OACORE server fails due to load balancing
    • FAILED_NOT_RESTARTABLE is a protective mechanism, not the root cause itself
    • Resource pressure and restart failures must be analyzed together
    • Controlled and governed actions are critical in production environments
    • Proactive session monitoring via automation helps prevent recurrence

    Have questions or faced a similar issue? Reach out at sdanwarahmed@gmail.com.

  • Oracle DB Health Check Scripts: Real-World DBA Monitoring Guide

    In day-to-day Oracle Database and E-Business Suite (EBS) administration, proactive monitoring is critical to ensure system stability, performance, and availability. As part of my real-world DBA experience, I created a set of health check scripts to quickly assess the status of key database components.

    Objective

    The goal of these scripts is to provide a quick and efficient health check covering database status, instance status, tablespace usage, invalid objects, and RMAN backup status — all from a single SQL*Plus session.

    Solution Overview

    I developed a SQL-based script (hc.sql) that gathers essential health metrics from Oracle dynamic views and DBA tables. The scripts are designed to be lightweight, fast, and provide immediate actionable insights.

    GitHub Repository

    The complete script is available on GitHub:
    https://github.com/Syedanwarahmed/scripts_for_healthcheck

    Key Features

    • Database and instance status check
    • Tablespace usage monitoring
    • Active session tracking
    • Invalid object detection
    • RMAN backup job verification
    • Easy execution using SQL*Plus

    How to Use

    sqlplus / as sysdba
@hc.sql

    Sample Checks Included

    Database Status

    SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

    Tablespace Usage

    SELECT tablespace_name,
       ROUND(used_space * 8192 / 1024 / 1024, 2) used_mb,
       ROUND(tablespace_size * 8192 / 1024 / 1024, 2) total_mb,
       ROUND(used_percent, 2) pct_used
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

    Active Sessions

    SELECT COUNT(*) active_sessions FROM V$SESSION WHERE STATUS='ACTIVE';

-- Session breakdown by status
SELECT status, COUNT(*) cnt
FROM v$session
GROUP BY status
ORDER BY cnt DESC;

    Invalid Objects

    SELECT owner, object_type, COUNT(*) cnt
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY cnt DESC;

    RMAN Backup Status

    SELECT status, start_time, end_time,
       ROUND((output_bytes/1024/1024/1024),2) output_gb
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY start_time DESC
FETCH FIRST 5 ROWS ONLY;

    Real-World Value

    These scripts are derived from real-time production support scenarios where quick diagnosis is required during high CPU issues, backup failures, tablespace alerts, and performance degradation. Having a single script to validate system health saves valuable time during critical situations.

    Future Enhancements

    I plan to enhance this repository further by adding shell automation scripts, alerting mechanisms, integration with monitoring tools, and additional EBS-specific checks.

    Conclusion

    A well-designed health check script is an essential tool for every DBA. It not only helps in proactive monitoring but also ensures faster troubleshooting and improved system reliability. Feel free to explore the repository, use the scripts, and share your feedback.

    Written by Syed Anwar Ahmed — Oracle Apps DBA with 11 years of production experience.
    Connect: sdanwarahmed@gmail.com  |  LinkedIn  |  GitHub

  • Real-World Oracle Database and EBS Troubleshooting Guides

    A collection of real-world Oracle Database and EBS troubleshooting scenarios from production environments. These guides cover common issues encountered during day-to-day DBA operations.

    Resolving RMAN ORA-19502 Backup Failure Due to Disk Full

    RMAN backup job failed with the following errors:

    ORA-19502: write error on file
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device

    Environment

    Oracle Database 19c, RMAN Disk Backup, Linux Environment

    Diagnosis

    Checked RMAN logs and identified the backup destination filesystem was full:

    -- Check filesystem usage
df -h

-- Check FRA usage from database
SELECT name, space_limit/1024/1024/1024 limit_gb,
       space_used/1024/1024/1024 used_gb,
       ROUND(space_used/space_limit*100,2) pct_used
FROM v$recovery_file_dest;

    The backup mount point reached 100% utilization.

    Resolution

    Cleaned older backup files after validating they were no longer needed, then reran the backup job:

    -- Use RMAN to safely clean up obsolete backups (preferred over OS delete)
RMAN> CROSSCHECK BACKUP;
RMAN> DELETE EXPIRED BACKUP;
RMAN> DELETE OBSOLETE;

    After freeing space, the RMAN backup completed successfully.

    Key Lesson

    Always monitor backup destinations proactively. Schedule DELETE OBSOLETE as part of your regular RMAN maintenance to prevent FRA saturation.

    Written by Syed Anwar Ahmed — Oracle Apps DBA with 11 years of production experience.
    Connect: sdanwarahmed@gmail.com  |  LinkedIn