Oracle AWR Report Analyze

Oracle AWR Report Analyzer & Comparison Tool (Client-Side)

Welcome to the free Oracle Automatic Workload Repository (AWR) analysis utility. When performance spikes, bottlenecks occur, or your database crawls, comparing a healthy baseline AWR report against an incident window report is the fastest way to find the root cause. This tool runs 100% locally in your browser to protect your sensitive enterprise data.

⚠️ Disclaimer: This tool is provided for general guidance, troubleshooting assistance, and diagnostic support only. Automated AWR parsing findings and recommendations may not cover all edge cases or environment-specific configurations. Always verify recommendations against official Oracle documentation and test changes in a non-production environment before applying them to live production systems.

---

Upload AWR Reports for Differential Analysis

1. Baseline Report (No Issue)

Upload HTML/Text AWR report from normal operations

2. Incident Report (Issue Time)

Upload HTML/Text AWR report when performance degraded

---

Additional Oracle DBA Resources & Troubleshooting Guides

Looking for more deep-dive technical solutions for common Oracle database errors and maintenance issues? Check out our detailed walkthrough on resolving control file locking issues: Fixing ORA-00240: Control File Enqueue Timeout & Troubleshooting Guide.

If You Run an RMAN Level 1 Backup First Without a Level 0

What Happens If You Run an RMAN Level 1 Backup First Without a Level 0?

We’ve all been there. You’re setting up a new automated backup schedule, tweaking your shell scripts, or dealing with a brand-new database environment, and you realize something: the automated script is about to trigger a Level 1 incremental backup, but nobody ever ran the baseline Level 0 backup.

Your first instinct might be panic. Is the job going to crash in the middle of the night? Will it throw a critical Oracle error and leave the system completely unprotected?

If you're running Oracle 11g, 12c, 19c, or upward, the short answer is no, it won’t fail. But what it actually does under the hood might surprise you—and it can definitely impact your storage and disk performance if you aren't prepared for it.

---

A Quick Reality Check: Level 0 vs. Level 1

Before looking at the fallback mechanics, let’s quickly clear up how RMAN normally views these two pieces:

• RMAN Level 0: This is your baseline parent backup. It grabs every single block currently holding data. Even though it acts exactly like a traditional "full database backup," RMAN tags it as a Level 0 so future incremental jobs know where to look for changes.
• RMAN Level 1: This is the delta backup. It's supposed to look back at the parent backup and only copy the data blocks that have been modified since. (Differential looks back at the closest Level 0 or 1; Cumulative looks strictly back to the last Level 0).

---

The First-Time Level 1 Behavioral Loop

If you fire off a Level 1 job on a clean database with zero backup history, Oracle doesn't just give up. Instead, it relies on a specific built-in safety logic that hinges on your database initialization parameters.

Modern Environments (Oracle 11g through 23c)

Assuming your database COMPATIBILITY parameter is set to 10.0.0 or higher—which is true for basically any production environment today—RMAN handles the missing baseline seamlessly:

• The Check: RMAN looks through the control file or your recovery catalog for an available Level 0 baseline. It finds nothing.
• The Pivot: Instead of throwing a tantrum, RMAN falls back to using the Datafile Creation SCN (System Change Number) as its starting line.
• The Heavy Lifting: Because it has to look all the way back to the moment the datafiles were first built, it scans everything and copies every single active data block in the entire database.
• The Identity Crisis: Even though it just performed a full-scale database backup in terms of time, performance, and file size, RMAN still registers the final file as a Level 1 backup in the metadata repository.

The Practical Reality: Your very first Level 1 backup will take just as long and use just as much disk space as a full database backup. It just won't be named Level 0.

What About Old Legacy Databases?

If you happen to be working on incredibly old legacy systems running on pre-10g compatibility rules, Oracle handles this differently. RMAN catches the missing baseline and automatically forces the generation of an actual, implicitly tagged Level 0 backup file right then and there.

---

What Happens the Next Day?

The good news is that once this massive first-time file is successfully written, the hard part is over. Your database now has a tracking baseline saved in its metadata.

When your automated scheduler triggers the exact same Level 1 script on Day 2:

1. RMAN checks the registry and sees the Level 1 file from yesterday.
2. It maps out the checkpoint SCN from that run.
3. It successfully executes a true incremental delta, grabbing only the minor block updates from the last 24 hours.

Pro Tip: If you want day-to-day incremental runs to finish in minutes rather than hours, make sure you enable Block Change Tracking (BCT). It keeps a small tracking file on disk so RMAN doesn't have to brute-force scan your entire database to find modified blocks.

---

Why You Still Shouldn't Rely on This Fallback

Even though Oracle handles this elegantly, building a production strategy around accidental Level 1 baselines isn't a great idea. Here is why you should always explicitly kick off a Level 0 first:

• Enterprise Backup Confusion: Media management software like NetBackup, Veeam, or Commvault can get incredibly confused when parsing retention windows if they don't see a standard Level 0 baseline. This can lead to your files being purged too early or kept indefinitely.
• Disaster Recovery Drills: If you are writing manual restoration scripts or practicing emergency point-in-time recovery, having your fundamental baseline tagged as a "Level 1" can break standard recovery scripts that explicitly look for a Level 0 tag to start the restore process.

A Cleaner Way to Handle Your Scripts

Instead of hoping for the best, you can write your RMAN scripts to safely handle incremental tasks while maintaining clean catalog tags:

RUN {
  # Open up multiple streams to speed things up
  ALLOCATE CHANNEL disk_ch1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL disk_ch2 DEVICE TYPE DISK;

  # Standardizing the daily incremental run
  # RMAN safely processes this, but keeping it explicit keeps your catalog clean
  BACKUP INCREMENTAL LEVEL 1 DATABASE 
  TAG 'DB_DAILY_DELTA'
  PLUS ARCHIVELOG;
  
  # Keep your disk healthy by cleaning up old copies
  DELETE OBSOLETE;
  RELEASE CHANNEL disk_ch1;
  RELEASE CHANNEL disk_ch2;
}

Bottom line: Oracle has your back if you accidentally run things out of order, but taking a few minutes to establish an explicit baseline will save you a massive headache down the line when it comes to catalog cleanups and audits.

Fixing ORA-00240: Control File Enqueue Deadlock (Real-World Triage)

 If your alert logs are spewing ORA-00240: control file enqueue deadlock held for more than N seconds, your database is effectively frozen. Session requests are queuing up, processes are hanging on control file sequential read or control file parallel write, and RMAN backups or Data Guard broker syncs are likely ground to a halt.

I usually run into this on heavily loaded 19c RAC nodes, ODA boxes during peak batch windows, or when automated RMAN script schedules overlap with database checkpoints and snapshot operations.

Here is how I clear the deadlock, pinpoint the blocking process, and stop it from recurring.

Step 1: Kill the Blocker (Emergency Triage)

When ORA-00240 hits, you don't have time to wait for a clean timeout. First, jump in via SQL*Plus (use a pre-prepped sqlplus -prelim / as sysdba connection if standard SYSDBA logins hang):

SQL

-- Find the session holding the CF (Control File) enqueue
SELECT 
    s.sid, s.serial#, s.username, s.program, s.event, s.seconds_in_wait
FROM v$session s, v$enqueue_lock e
WHERE e.type = 'CF' 
  AND e.id1 = 0 
  AND e.id2 = 0 
  AND s.sid = e.sid;

If that query hangs or takes too long, check OS-level processes holding file locks on the control files via lsof or fuser(Linux/Unix):

Bash

# Check OS processes touching control files directly
fuser -v /u01/app/oracle/oradata/*/control01.ctl

Once you have the offending SID,SERIAL# or OS PID, kill the blocker immediately to release the enqueue lock and unfreeze the database:

SQL

-- Kill at database level
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

(If the session hangs in KILLED status, kill -9 <spid> at the OS level is your best friend).

Step 2: The Root Causes & Permanent Fixes

Clearing the session gets you back online, but unless you fix the root trigger, it’s coming back. Here are the three usual suspects:

1. RMAN Overlap with Snapshot Control Files

If RMAN attempts to back up the control file while another process (like an automated OEM job or Data Guard sync) requests a lock, you get a collision.

• Fix: Move your RMAN control file autobackup off local spinning disks or slow NFS mounts onto high-speed flash storage/ASM diskgroups.

• Tweak: Ensure RMAN isn't backing up the control file concurrently across multiple channels.

2. Slow I/O on Control File Storage

Control file writes must be fast. If disk latency spikes on the mount point holding control01.ctl or control02.ctl, enqueue waits skyrocket.

• Run an AWR/ASH report for the window and check v$event_histogram for control file parallel write. If average wait time exceeds 10ms, your storage path is choked.

3. Data Guard Broker & Observer Contention

If you run Data Guard Fast-Start Failover (FSFO), the Observer process constantly pings the control file for heartbeat checks. During intermittent network hiccups, the broker can hold onto CF enqueue locks longer than expected.

• Ensure your DG_BROKER_TIMEOUT is tuned properly so the Observer doesn't lock resources aggressively during minor network jitters.

Step 3: Quick Shell Diagnostic Script

I keep this short check in my DBA utility kit to catch control file wait spikes before they trigger full ORA-00240deadlocks:

Bash

#!/bin/bash
# Monitor Control File Enqueue Waits
sqlplus -s / as sysdba << 'EOF'
SET HEADINGS OFF PAGESIZE 0
SELECT 'WARNING: CF Enqueue Wait Count = ' || COUNT(*) 
FROM v$session_wait 
WHERE event LIKE '%control file%';
EOF

Written by Sunny Thakur. I handle Oracle Database administration, Data Guard DR setups, ODA migrations, and RAC performance tuning.

• 📌 Need Remote DBA Support? Facing urgent database outages, persistent deadlocks, or performance bottlenecks? Drop a comment or reach out directly for consulting services.

How to Fix ORA-65114: Space Usage in Container Is Too High (Oracle 19c & 23ai Multi-Tenant)

When managing Oracle Multitenant databases on on-premises ODA (Oracle Database Appliance), Exadata, or Cloud infrastructure (OCI / AWS RDS / Azure), encountering ORA-65114: space usage in container is too high can bring critical pluggable database (PDB) operations to a sudden halt.

This error occurs when a Pluggable Database (PDB) exceeds its allocated storage quota defined by the MAX_PDB_STORAGEparameter or runs out of allocated MAX_SHARED_TEMP_SIZE space.

Below is the complete, production-tested DBA step-by-step resolution playbook to diagnose and resolve ORA-65114without downtime.

1. Quick Root Cause Identification

When ORA-65114 triggers, Oracle blocks any CREATE TABLESPACE, datafile auto-extend, or temporary space allocation inside the target PDB.

First, connect to your CDB or target PDB via SQL*Plus or SQLcl and run the following diagnostic script:

SQL

-- Connect to the target PDB or check from CDB$ROOT
ALTER SESSION SET CONTAINER = your_pdb_name;

-- Check current PDB storage limits vs actual size
SELECT 
    name AS pdb_name,
    open_mode,
    total_size/1024/1024 AS current_size_mb,
    max_size/1024/1024 AS max_size_mb
FROM v$pdbs;

If current_size_mb has reached or surpassed max_size_mb, the container limit has been hit.

2. Step-by-Step Resolution Methods

Method 1: Increase the Container MAXSIZE Limit (Recommended)

The fastest fix for production environments running out of allocated container ceiling space is to adjust the PDB storage attribute:

SQL

-- Connect to CDB$ROOT or directly to the PDB with SYSDBA / PDB_ADMIN privileges
ALTER SESSION SET CONTAINER = your_pdb_name;

-- Increase storage ceiling to 100 Gigabytes
ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 100G);

-- Alternatively, set to UNLIMITED if storage is governed at the ASM/DG level
ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Method 2: Adjust Shared Temporary Space Limits

If the error occurs during heavy batch processing or sorting, the issue may stem from temporary tablespace caps:

SQL

-- Set both MAXSIZE and SHARED TEMP limits concurrently
ALTER PLUGGABLE DATABASE STORAGE (MAXSIZE 200G MAX_SHARED_TEMP_SIZE 20G);

Method 3: Identify & Reclaim Wasted PDB Storage

If hardware or cloud storage limits prevent expanding the container size, reclaim unallocated space inside the PDB:

SQL

-- Find top space-consuming segments inside the PDB
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb 
FROM dba_segments 
WHERE bytes > 100*1024*1024 
ORDER BY bytes DESC 
FETCH FIRST 10 ROWS ONLY;

• Execute ALTER TABLE <table_name> SHRINK SPACE; on tables with high high-water marks.

• Purge recycled objects: PURGE TABLESPACE <tablespace_name>;

3. Automation Script: Prevent ORA-65114 Alerts

Add this Bash / Shell monitoring snippet to your cron jobs or enterprise OEM monitoring scripts to trigger an alert before the PDB hits 90% of its MAX_PDB_STORAGE:

Bash

#!/bin/bash
# PDB Storage Threshold Monitor
ORACLE_SID=your_cdb_sid
export ORACLE_SID

sqlplus -s / as sysdba << 'EOF'
SET HEADINGS OFF FEEDBACK OFF PAGESIZE 0
SELECT name || ' is at ' || ROUND((total_size/NULLIF(max_size,0))*100,2) || '% capacity'
FROM v$pdbs 
WHERE max_size > 0 AND (total_size/max_size) > 0.90;
EOF

🛠️ Need Remote Enterprise Oracle DBA Support?

Struggling with complex Data Guard failovers, RAC performance tuning, ODA migrations, or database emergency troubleshooting?

Contact us for Remote DBA Consulting & On-Call Engineering Services across Asia-Pacific (Hong Kong, Singapore, Australia), EMEA, and North America

ORA-04031: Unable to Allocate Shared Memory - Root Cause & Resolution Guide

The ORA-04031: unable to allocate %s bytes of shared memory error occurs when Oracle fails to allocate a contiguous block of memory in the Shared Pool or Large Pool within the System Global Area (SGA). In 90% of production environments, this error is caused by Shared Pool memory fragmentation resulting from literal SQL queries without bind variables, rather than an actual lack of total physical memory. Immediate resolution involves purging specific heavy cursors or flushing the Shared Pool, while long-term prevention requires enforcing bind variables, configuring CURSOR_SHARING = FORCE, or tuning SHARED_POOL_RESERVED_SIZE.

Environment & Prerequisites

This troubleshooting guide applies across all supported Oracle Database releases, including Single Instance, Real Application Clusters (RAC), and E-Business Suite (EBS R12.2) environments. Executing the diagnostic queries and resolution commands requires administrative privileges and direct access to dynamic performance views.

• Supported Versions: Oracle Database 11g, 12c, 18c, 19c, 21c, 23c/23ai (Linux x86-64/AIX/Solaris)

• Required Roles/Privileges: SYSDBA, ALTER SYSTEM, or EXECUTE privileges on DBMS_SHARED_POOL

• Target Dynamic Views: V$SGASTAT, V$SQLAREA, V$SQL, V$SHARED_POOL_RESERVED, V$PROCESS, V$SESSION

Problem Description & Exact Error Stack

An ORA-04031 error terminates SQL statement execution, PL/SQL package compilation, or background process tasks when an allocation request for a contiguous memory chunk fails after scanning free lists and attempting to free unpinned memory.

Command Line Execution Log

SQL

SQL> EXECUTE dbms_stats.gather_table_stats('APPS', 'OE_ORDER_HEADERS_ALL');
BEGIN dbms_stats.gather_table_stats('APPS', 'OE_ORDER_HEADERS_ALL'); END;
*
ERROR at line 1:
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","executor tree")

Database Alert Log Snippet

Plaintext

2026-10-01T14:22:05.123456+08:00
Errors in file /u01/app/oracle/diag/rdbms/prod/prod1/trace/prod1_ora_54210.trc:
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","SELECT /*+ ALL_ROWS */...","SQLA","tmp")

What Causes ORA-04031 in Oracle Databases?

ORA-04031 stems from contiguous memory allocation failures in the SGA, primarily driven by memory fragmentation, excessive SQL hard-parsing, or improper SGA subpool sizing. Distinguishing between memory exhaustion and memory fragmentation is critical to selecting the correct resolution path.

Cause Category	Primary Trigger	Diagnostic Indicator
Shared Pool Fragmentation	High rates of hard parsing from un-bound literal SQL queries	Thousands of rows in V$SQL with EXECUTIONS = 1
Insufficient Reserved Area	Large allocation requests (> 4,400 bytes) failing under load	REQUEST_FAILURES > 0 in V$SHARED_POOL_RESERVED
High Cursor Version Count	Parent cursors generating hundreds of child cursors	High VERSION_COUNT (> 100) in V$SQLAREA
Under-sized SGA / Shared Pool	Automatic Memory Management (ASMM) hitting SGA cap	V$SGASTAT showing zero free memory across subpools

How to Diagnose and Resolve ORA-04031 Step-by-Step

Resolving ORA-04031 requires identifying the affected subpool, pinpointing the SQL queries causing fragmentation, and applying targeted memory purging or initialization parameter adjustments. Follow these sequential steps to diagnose and clear the condition without restarting the database.

Step 1: Check Shared Pool Free Memory and Subpool Allocations

Execute the following query against V$SGASTAT to analyze memory distribution across Shared Pool components:

SQL

SELECT 
    subpool,
    name,
    ROUND(bytes/1024/1024, 2) AS size_mb
FROM v$sgastat
WHERE pool = 'shared pool'
  AND name IN ('free memory', 'SQLA', 'KGH: NO ACCESS', 'library cache', 'row cache')
ORDER BY bytes DESC;

Sample Output:

Plaintext

SUBPOOL     NAME               SIZE_MB
----------- ------------------ -------
subpool 1   SQLA                842.15
subpool 1   KGH: NO ACCESS      410.50
subpool 1   library cache       215.30
subpool 1   free memory           8.12

Note: If free memory shows available space (e.g., > 50 MB) while ORA-04031 occurs, the issue is fragmentation (lack of small contiguous free chunks) rather than total memory exhaustion.

Step 2: Identify Un-Bound Literal SQL Queries Causing Fragmentation

Run this query to locate un-bound SQL statements polluting the Library Cache with single-use cursors:

SQL

SELECT 
    SUBSTR(sql_text, 1, 60) AS sql_snippet,
    COUNT(*) AS literal_count,
    ROUND(SUM(sharable_mem)/1024/1024, 2) AS total_mem_mb
FROM v$sqlarea
WHERE executions = 1
GROUP BY SUBSTR(sql_text, 1, 60)
HAVING COUNT(*) > 50
ORDER BY COUNT(*) DESC;

Sample Output:

Plaintext

SQL_SNIPPET                                                  LITERAL_COUNT TOTAL_MEM_MB
------------------------------------------------------------ ------------- ------------
SELECT * FROM OE_ORDER_HEADERS_ALL WHERE HEADER_ID = 100231            4120       142.50
SELECT * FROM OE_ORDER_HEADERS_ALL WHERE HEADER_ID = 100232            3890       138.10

Step 3: Check Reserved Pool Failures (V$SHARED_POOL_RESERVED)

Oracle reserves a portion of the Shared Pool for large memory allocations (> 4,400 bytes). Check if requests are failing in this region:

SQL

SELECT 
    free_space,
    avg_free_size,
    used_space,
    request_failures,
    last_failure_size
FROM v$shared_pool_reserved;

Sample Output:

Plaintext

FREE_SPACE AVG_FREE_SIZE USED_SPACE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ---------------- -----------------
  12450816         14210   54210890              142              4120

Note: If REQUEST_FAILURES > 0 and LAST_FAILURE_SIZE matches your ORA-04031 error stack, SHARED_POOL_RESERVED_SIZE must be increased.

Step 4: Immediate Emergency Remediation (Non-Disruptive Memory Release)

Option A: Purge a Specific High-Memory SQL Cursor (Preferred)

Instead of flushing the entire Shared Pool, target and remove specific memory-heavy cursors using DBMS_SHARED_POOL.PURGE:

SQL

-- Find ADDRESS and HASH_VALUE for the offending SQL_ID
SELECT address, hash_value 
FROM v$sqlarea 
WHERE sql_id = '8f3a9bc11z0q2';

-- Output example: ADDRESS = 00000003ABC1234, HASH_VALUE = 1234567890

-- Purge the specific cursor from memory:
EXEC DBMS_SHARED_POOL.PURGE('00000003ABC1234, 1234567890', 'C');

Option B: Flush the Shared Pool (System-Wide Reset)

If system-wide fragmentation blocks database operations, clear all unpinned cursors from the Shared Pool:

SQL

ALTER SYSTEM FLUSH SHARED_POOL;

Warning: Flushing the Shared Pool causes a temporary CPU spike due to subsequent hard parsing across all active application sessions.

Step 5: Apply Permanent Fixes

Fix 1: Enforce Cursor Sharing for Un-bound Applications

If application code cannot be immediately modified to use bind variables, force Oracle to substitute literals with system bind variables dynamically:

SQL

ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE=BOTH;

Fix 2: Increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE

Increase the reserved area allocation to prevent large block allocation failures:

SQL

-- Set Reserved Pool to 15% of Shared Pool
ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE = 256M SCOPE=BOTH;

-- Expand total Shared Pool size
ALTER SYSTEM SET SHARED_POOL_SIZE = 2G SCOPE=BOTH;

How to Verify the ORA-04031 Fix?

Verification requires confirming that memory allocation requests succeed without raising errors and that V$SHARED_POOL_RESERVED stops accumulating failure counts.

Query V$SHARED_POOL_RESERVED to verify zero new request failures following the fix:

SQL

SELECT 
    request_failures, 
    request_misses, 
    free_space 
FROM v$shared_pool_reserved;

Sample Output:

Plaintext

REQUEST_FAILURES REQUEST_MISSES FREE_SPACE
---------------- -------------- ----------
               0              0   42108900

Verify that contiguous free memory is available in the target subpools:

SQL

SELECT 
    subpool, 
    bytes/1024/1024 AS free_mb 
FROM v$sgastat 
WHERE pool = 'shared pool' 
  AND name = 'free memory';

Sample Output:

Plaintext

SUBPOOL    FREE_MB
---------- -------
subpool 1   184.50
subpool 2   192.10

Prevention Best Practices & Sizing Formulas

Preventing future ORA-04031 occurrences relies on proactive memory sizing, enforcing bind variable standards in development, and pinning essential PL/SQL objects into memory during startup.

Reserved Pool Sizing Formula

Set SHARED_POOL_RESERVED_SIZE between 10% and 15% of the total SHARED_POOL_SIZE:

$$\text{SHARED\_POOL\_RESERVED\_SIZE}=\text{SHARED\_POOL\_SIZE}\times 0.10$$

Pin Critical Packages into Memory

Keep core application packages pinned in the Shared Pool to prevent them from aging out and causing memory fragmentation upon re-allocation:

SQL

-- Pin heavy packages on database startup
EXEC DBMS_SHARED_POOL.KEEP('APPS.OE_ORDER_GRP', 'P');
EXEC DBMS_SHARED_POOL.KEEP('DBMS_STANDARD', 'P');