Syed Anwar Ahmed – Oracle DBA Blog

Category: Troubleshooting

Oracle Database and EBS real-world troubleshooting guides and RCA

  • 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

  • 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