Alhamdulillah, sharing another real troubleshooting session — this one from an EBS 12.2 database tier clone on Oracle Linux 8.10, where adcfgclone.pl dbTechStack failed and the on-screen error pointed in completely the wrong direction.
Environment
Oracle EBS 12.2, Oracle Database 19c (19.21) multitenant (CDB/PDB), Oracle Linux 8.10, target server testserver01.
The Symptom
perl adcfgclone.pl dbTechStack /u01/EBSDB/19.0.0/appsutil/EBSDB_testserver01.xml
RC-00110: Fatal: Error occurred while relinking of ApplyDBTechStack
ERROR while running Apply...
ERROR: Failed to execute /u01/EBSDB/19.0.0/appsutil/clone/bin/adclone.pl
The first instinct with RC-00110 is to suspect a relink problem — missing OS packages, a corrupt backup, bad extraction. We verified all of that and ruled it out. The real lesson of this post: the RC error on screen is generic. Follow the log chain.
The Log Chain
Step 1 — The ApplyDBTechStack log showed the relink script actually passed:
adlnkoh.sh completed sucessfully
but home registration failed:
Finished OUI CLI cloning for s_db_oh with return code: 1
ouicli.pl INSTE8_APPLY 1
RC-50013: Fatal: Instantiate driver did not complete successfully.
Step 2 — ohclone.log pointed one level deeper:
OUI runinstaller log file - /u01/EBSDB/oraInventory/logs/InstallActions<timestamp>/installActions<timestamp>.log
Found the INFO: Exit Status is -1 in runInstaller log.
OUI CLI cloning returned non-zero.
Note the timing: runInstaller started and exited within the same second. An installer that dies instantly is not failing a task — it is failing a pre-check.
Step 3 — The installActions log had the true error:
[WARNING] [INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'
SUMMARY: - java.lang.NullPointerException
Root Cause
The 19c ORACLE_HOME delivered inside the EBS Rapid Clone stage carries the 19.3 base installer (year 2019). Its OS certification table ends at Oracle Linux 7. On OL8/RHEL8, the lookup for the distribution ID returns null and the installer crashes with a NullPointerException before doing any work — surfacing back up the chain as RC-50013 and RC-00110.
The Fix
Set CV_ASSUME_DISTID in the same shell session before rerunning the clone:
export CV_ASSUME_DISTID=OEL7.8
echo $CV_ASSUME_DISTID
cd /u01/EBSDB/19.0.0/appsutil/clone/bin
perl adcfgclone.pl dbTechStack /u01/EBSDB/19.0.0/appsutil/EBSDB_testserver01.xml
Result — the rerun completed cleanly, confirmed by these lines in the new ApplyDBTechStack log:
Finished OUI CLI cloning for s_db_oh with return code: 0
Completed home registration for s_db_oh
Completed Apply...
ApplyDBTechStack Completed Successfully.
Important: this variable does NOT change your OS or the installed software. It only tells the old installer which certification profile to use for its checks. The value must be one the 19.3-base installer recognizes — OEL7.8 is the value documented in Oracle’s Linux 8 release notes. Setting it to your actual OS version (8.10) defeats the purpose.
Permanent Fix
The workaround can also live inside the ORACLE_HOME, in $ORACLE_HOME/cv/admin/cvu_config:
# Fallback to this distribution id
CV_ASSUME_DISTID=OEL7.8 <-- uncomment and set
This also explains a common confusion: “my previous clone worked without this!” If the earlier backup was taken from a home where cvu_config already had this set, the fix travelled inside the backup. A backup from an untouched home reintroduces the failure. Check with:
After the fix, you will still see FATAL errors like “DB Connection failed” and “Invalid APPS database user credentials” from the AutoConfig phase, plus DB-ETCC connectivity warnings — these are expected at this stage because the database is not restored and opened yet. They resolve once you restore the database and rerun AutoConfig and ETCC.
Also refresh ETCC from patch 17537119 — the bundled bugfix XML goes stale after 30 days, and older versions do not recognize newer Release Updates such as 19.21.
Key Takeaways
RC-00110/RC-50013 are wrappers, not root causes — always walk ApplyDBTechStack log → ohclone.log → installActions log. A runInstaller that exits in under a second failed a pre-check, not the work itself. And on OL8/RHEL8 with any 19.3-base home, CV_ASSUME_DISTID=OEL7.8 belongs in your clone runbook.
Disclaimer: The views expressed on this blog are my own and do not reflect the views of my employer or any client. All environment names, hostnames, and identifiers used in this post are anonymized. Always test in a non-production environment before applying any change to production.
Alhamdulillah, another interesting production incident to share. This one is a classic example of how a small code change — a single commented-out COMMIT — can stay silent for weeks until a new concurrent workload exposes it as an Oracle deadlock in production. If you are searching for Oracle deadlock troubleshooting or ORA-00060 trace file analysis in an Oracle EBS environment, this walkthrough covers the full investigation from symptom to fix.
Quick Summary
Issue: ORA-00060 deadlocks after deployment of a new APEX DBMS_SCHEDULER job.
Impact: Oracle Workflow Background Process intermittently failed, delaying order processing.
Root Cause: A previously removed COMMIT caused row locks to be held far longer than intended, allowing concurrent sessions to deadlock.
Resolution: Restored the correct transaction boundary, adjusted scheduler timing, and verified with Oracle Support that no product-side issue was involved.
Result: No recurrence after deployment.
The Symptom
On a production EBS environment (EBS 12.1, Database 12.1.0.2), we started receiving alerts for ORA-00060 in the database alert log:
ORA-00060: Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_12345.trc
At the same time, the business reported sales order lines not progressing. The Oracle Workflow Background Process for the OM Order Line workflow (ONT) was erroring intermittently — a visible impact on order processing and a classic Oracle EBS performance troubleshooting scenario.
A quick note on deadlock behavior: Oracle detects deadlocks automatically and resolves them by rolling back one of the participating statements — the victim — allowing the remaining transaction to continue. Unlike simple blocking, only the offending statement is rolled back, not the victim’s entire transaction. The victim’s session receives ORA-00060 and a trace file is written to disk. That is why the application does not hang forever; instead, you see intermittent errors and trace files accumulating.
Step 1 – Read the Deadlock Trace
The trace file is always the starting point of ORA-00060 trace file analysis. The deadlock graph showed two sessions blocking each other on TX enqueues in row-exclusive mode:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000a0015-0003c2d1 45 312 X 38 127 X
TX-0008001f-0004a1b2 38 127 X 45 312 X
Both sessions were waiting on enq: TX - row lock contention, each holding a row the other wanted — textbook Oracle row lock contention forming a deadlock cycle. The trace file also identifies which session was chosen as the victim and the exact SQL statement that was rolled back, which makes it the definitive evidence for the investigation.
Conceptually, the cycle looked like this:
Workflow Background Process
│
│ Locks Row A
▼
Reservation Row A
│
│ Waiting for Row B
▼
Reservation Row B
▲
│ Locked by
│
APEX Scheduler Job
Workflow waits for APEX.
APEX waits for Workflow.
→ Oracle detects a deadlock (ORA-00060)
Step 2 – Identify the Two Sessions
SET LINES 200 PAGES 100
COL SID FORMAT 99999
COL SERIAL# FORMAT 999999
COL USERNAME FORMAT A12
COL PROGRAM FORMAT A28
COL MODULE FORMAT A28
COL ACTION FORMAT A20
COL SQL_ID FORMAT A14
SELECT s.sid, s.serial#, s.username,
NVL(s.program,'N/A') program,
NVL(s.module,'N/A') module,
NVL(s.action,'N/A') action,
NVL(s.sql_id,'N/A') sql_id
FROM v$session s
WHERE s.sid IN (312, 127);
Session 1 was the Workflow Background Process (FNDWFBG, ONT item type) — making this an Oracle Workflow deadlock scenario. Session 2 was more interesting — a DBMS_SCHEDULER job session:
COL JOB_NAME FORMAT A25
COL OWNER FORMAT A12
COL STATE FORMAT A12
COL REPEAT_INTERVAL FORMAT A40
SELECT owner, job_name, state, repeat_interval
FROM dba_scheduler_jobs
WHERE job_name = 'APEX_ORDER_RESERVE';
A newly deployed APEX-driven scheduler job, running every few minutes, calling a custom reservation package — the second half of an Oracle DBMS_SCHEDULER deadlock pattern.
Step 3 – Which Rows Were They Fighting Over?
From the trace file, the “Rows waited on” section gives the object number:
COL OWNER FORMAT A10
COL OBJECT_NAME FORMAT A30
COL OBJECT_TYPE FORMAT A12
SELECT owner, object_name, object_type
FROM dba_objects
WHERE object_id = &object_id_from_trace;
Both sessions were colliding on reservation rows — the Workflow process updating them as part of order line progression, and the APEX job updating the same rows through the custom package.
Step 4 – The Root Cause
Reviewing the custom reservation package source, we found this:
UPDATE xx_order_reservations
SET status = 'RESERVED'
WHERE header_id = p_header_id;
-- COMMIT; <== commented out during a previous change
The investigation identified that the custom package was holding row locks far longer than intended, because a COMMIT had been removed in an earlier code change. Instead of releasing locks per iteration, the scheduler job session held all of its row locks across the entire loop over order headers. It is worth being precise here: a missing COMMIT by itself does not cause a deadlock — a deadlock requires two sessions acquiring locks on overlapping rows in conflicting order. What the missing COMMIT did was dramatically widen the lock-holding window, so when the new APEX scheduler job started running concurrently with the Workflow Background Process against the same reservation rows, the probability of the two sessions interleaving into a deadlock cycle went from negligible to near-certain.
This also explains why the package ran in production for weeks without issue — until the APEX job was deployed, nothing else contended for those rows at that frequency. It also explains why testing never caught it: lower environments rarely generate the same level of concurrent activity as production, making lock-contention issues extremely difficult to reproduce before go-live. The extended lock duration was always present; the new concurrent workload exposed it.
The Fix
Restored the COMMIT at the correct transactional boundary in the custom package (per-iteration, after each header’s reservation update), shrinking the lock-holding window.
Redeployed the package during an approved change window.
Rescheduled the APEX job to avoid peak Workflow Background Process cycles as an additional safety margin.
Raised an SR with Oracle Support to confirm no product-side involvement — confirmed clean; purely custom code.
No ORA-00060 recurrence since the fix, ما شاء الله.
Lessons Learned
An Oracle deadlock almost always has two contributors: the locking pattern AND the concurrency pattern. Fixing either breaks the cycle, but fix the code defect, not just the schedule.
Commented-out COMMITs are silent time bombs. Code review for custom PL/SQL touching EBS transactional tables must treat transaction boundaries as seriously as the DML itself.
New scheduler jobs (APEX, DBMS_SCHEDULER, concurrent programs) should be assessed for row-level contention with existing Oracle Workflow and concurrent processing before go-live.
The deadlock trace file gives you everything: the sessions, the victim, the SQL, the rows. Start there, not with guesswork.
Conclusion
Production incidents often reveal issues that remain hidden during testing, because realistic concurrency is difficult to reproduce in lower environments. This incident reinforced a simple discipline: whenever a new workload is introduced — an APEX scheduler job, a concurrent program, or an integration — review the transaction boundaries of every custom PL/SQL object it touches, and ask what else updates those same rows.
In Oracle, deadlocks are rarely caused by a single statement — they are caused by the interaction of multiple sessions under concurrency. Understanding transaction boundaries is often the key to solving them.
Disclaimer: All environment names, hostnames, and identifiers in this post are anonymized. The views expressed are my own.
Disclaimer: This article is based on field experience and publicly shareable troubleshooting steps. Oracle MOS notes are referenced for further reading. Customers with active Oracle Support should review the referenced MOS documents for complete guidance.
Category: Oracle EBS R12.2 | Cloning | Troubleshooting Level: Intermediate to Advanced Reference: MOS Doc ID 454427.1
Introduction
Cloning an Oracle E-Business Suite R12.2 application tier is a common activity — whether for setting up a DEV, TEST, or UAT environment. While the process is well-documented, real-world scenarios often throw up issues that aren’t immediately obvious from the error messages alone.
In this post, I walk through two common issues encountered during an EBS R12.2 app tier clone using adcfgclone.pl:
RC-50221: Port Pool Not Free — CloneContext failing due to an already-occupied port pool
FRM-92101: Forms Session Failed During Startup — Forms not launching due to missing library dependencies or invalid symlinks
Both issues are real, well-known, and worth documenting together since they often appear in sequence during a clone.
Environment Details
Oracle EBS Version: R12.2.x
OS: Oracle Linux 7 (OL7) / RHEL 7
Clone type: Application Tier only (adcfgclone.pl appsTier)
When running adcfgclone.pl appsTier, the CloneContext step prompts for a Target System Port Pool and throws the following warning/error:
Checking the port pool 0
RC-50221: Warning: Port Pool 0 is not free.
Please check logfile $COMMON_TOP/clone/bin/CloneContext_<timestamp>.log for conflicts.
RC-00201: Error: Not a valid port pool number
ERROR: Context creation not completed successfully.
CloneContext Log Location
$COMMON_TOP/clone/bin/CloneContext_<timestamp>.log
# Or typically:
/u01/APPLTOP/EBSapps/comn/clone/bin/CloneContext_<timestamp>.log
Root Cause
Port pool 0 is already in use by another EBS instance running on the same server, causing CloneContext validation to fail. Port pool 0 generally corresponds to the initially configured EBS port set for that environment. Actual port assignments vary by release, topology, and previous configuration changes.
Review /tmp/portcheck.txt for any conflicts before selecting a port pool number.
Fix
Target System Port Pool [0-99] : 1
Checking the port pool 1
done: Port Pool 1 is free
Important Note: Using a different port pool causes Oracle EBS to calculate a different set of ports based on the predefined port pool mapping. The resulting ports are not always a simple increment of one and should be verified in the generated context file after CloneContext completes.
Always verify the actual assigned ports: grep -i "port" $CONTEXT_FILE | grep -v "#" cat $INST_TOP/admin/out/portpool.lst
If your firewall rules, load balancer, or application configuration depend on specific port numbers, plan accordingly or stop the existing instance first to free pool 0.
Patch File System Port Pool
For R12.2 dual file system architecture, the Patch file system must use a different port pool than the Run file system. AutoConfig and cloning utilities will calculate a separate set of ports accordingly.
Patch file system should have different port pool than Run file system.
Target System Port Pool [0-99] : 2
Issue 2: FRM-92101 — Forms Session Failed During Startup
Symptom
FRM-92101: There was a failure in the Forms Server during startup.
Please look into the web-server log file for details.
Java Exception:
oracle.forms.net.ConnectionException: Forms session <1> failed during startup:
no response from runtime process
What This Means
The WebLogic Forms server (forms_server1) is running, but the underlying Forms runtime process (frmweb) is failing to start. WebLogic receives no response from it, and the session times out. FRM-92101 can have more than one root cause — the key is to run frmweb directly to identify which one applies.
The output will indicate which root cause applies:
Undefined symbol errors (e.g. Symbol nnftboot was referenced...not found) → Root Cause 1: Invalid ldflags symlink
Missing shared library error (e.g. libXm.so.2: cannot open shared object file) → Root Cause 2: Missing OpenMotif symlink
Root Cause 1: Invalid ldflags Symlink
Symptom
Running frmweb directly produces undefined symbol errors:
rtld: 0712-001 Symbol nnftboot was referenced from module frmweb(), but a runtime definition of the symbol was not found.
rtld: 0712-001 Symbol ldap_search_s was referenced from module frmweb(), but a runtime definition of the symbol was not found.
Root Cause
Investigation revealed that the ldflags symbolic link under $ORACLE_HOME/lib32 was missing or pointing to an incorrect location, causing the Forms executable to fail during startup. This is a known issue after fresh installations or clones and is referenced in MOS Doc ID 454427.1.
Verify ldflags
ls -la $ORACLE_HOME/lib32/ldflags
If the symlink is missing or pointing to a non-existent path, it needs to be corrected to point to $ORACLE_HOME/lib/ldflags.
Fix
After correcting the ldflags symbolic link and relinking the Forms components, frmweb started successfully. The relinking is performed using the Forms makefile under the 10.1.2 Oracle Home. Refer to MOS Doc ID 454427.1 for the complete steps applicable to your environment.
Root Cause 2: Missing OpenMotif Library Symlink
Symptom
Running frmweb directly produces a missing shared library error:
error while loading shared libraries:
libXm.so.2: cannot open shared object file: No such file or directory
Root Cause
The Oracle Forms runtime binary frmweb is compiled against libXm.so.2 (OpenMotif 2.x). On newer Linux versions (OL7/OL8/RHEL7/RHEL8), the installed OpenMotif package provides libXm.so.4, but the compatibility symlink libXm.so.2 → libXm.so.4 is missing.
Verify with ldd
ldd $ORACLE_HOME/bin/frmweb | grep Xm
# Before fix:
libXm.so.2 => not found
# After fix:
libXm.so.2 => /usr/lib/libXm.so.4 (0x...)
Option A: Create the Missing Symlink (Quick Fix)
📌 Note on 32-bit vs 64-bit Libraries: On some Linux platforms the required library may reside under /usr/lib64 rather than /usr/lib. Always verify the actual location before creating symbolic links.
# Step 1: Find the actual location of libXm.so.4
ls -l /usr/lib/libXm.so*
ls -l /usr/lib64/libXm.so*
# Step 2: Create the symlink (run as root)
# If library is in /usr/lib:
ln -s /usr/lib/libXm.so.4 /usr/lib/libXm.so.2
# If library is in /usr/lib64:
ln -s /usr/lib64/libXm.so.4 /usr/lib64/libXm.so.2
# Step 3: Verify
ldd $ORACLE_HOME/bin/frmweb | grep Xm
# Expected: libXm.so.2 => /usr/lib/libXm.so.4 (or /usr/lib64/...)
cd $ADMIN_SCRIPTS_HOME
./adstpall.sh apps/<apps_password>
./adstrtal.sh apps/<apps_password>
# Or start Forms server specifically:
./admanagedsrvctl.sh start forms_server1
After starting the services, verify that frmweb runtime processes are spawning successfully:
ps -ef | grep frmweb
ps -ef | grep forms
Then launch a Forms-based responsibility or function to confirm successful startup.
Conclusion
Both RC-50221 and FRM-92101 are common issues that can delay Oracle EBS R12.2 cloning activities. While the error messages may initially appear unrelated, a structured troubleshooting approach focusing on log analysis, port validation, and runtime dependency checks can quickly identify the root cause and reduce downtime.
Summary
Issue
Root Cause
Fix
RC-50221: Port Pool Not Free
Another EBS instance occupying port pool 0
Use port pool 1 (or stop existing instance first)
FRM-92101: ldflags issue
ldflags symlink missing or pointing to wrong location
Fix ldflags symlink and relink Forms components (MOS Doc ID 454427.1)
FRM-92101: OpenMotif issue
libXm.so.2 symlink missing after clone
ln -s /usr/lib[64]/libXm.so.4 /usr/lib[64]/libXm.so.2 or install openmotif21
Key Diagnostic Commands Cheat Sheet
# Check which port pools are in use
grep -i "port_pool" $CONTEXT_FILE
cat $INST_TOP/admin/out/portpool.lst
# Validate port availability
perl $AD_TOP/bin/txkValidateRollupPorts.pl -contextfile=$CONTEXT_FILE -outfile=/tmp/portcheck.txt
# Check running WebLogic processes
ps -ef | grep weblogic | grep -v grep
# Run frmweb manually to see actual error
$ORACLE_HOME/bin/frmweb
# Check frmweb library dependencies
ldd $ORACLE_HOME/bin/frmweb | grep Xm
# Check ldflags symlink
ls -la $ORACLE_HOME/lib32/ldflags
# Check OpenMotif installation
rpm -qa | grep motif
# Check Forms runtime processes
ps -ef | grep frmweb
# Check Forms server log
tail -100 $EBS_DOMAIN_HOME/servers/forms_server1/logs/forms_server1.log
Lessons Learned
FRM-92101 is often a symptom, not the root cause. The WebLogic log may show the Java exception, but the actual failure is in the native frmweb binary. Always run frmweb directly to see the real error.
FRM-92101 has multiple root causes. Check the actual error output from frmweb to distinguish between an ldflags relinking issue and a missing OpenMotif library symlink.
Use ldd to diagnose missing shared library dependencies. It quickly identifies unresolved libraries without having to dig through multiple log files.
Verify port pool availability before starting a clone. Stop existing instances or choose a different pool to avoid RC-50221 errors.
Always validate the resulting ports in the generated context file after CloneContext completes. Port pool mapping is predefined and the assigned ports should be confirmed before proceeding.
Newer Linux builds may include libXm.so.4 but not the compatibility symlink required by Oracle Forms. OL7/OL8 installations do not always create libXm.so.2 automatically.
Document port assignments after every clone to avoid firewall and load balancer configuration issues down the line.
Keywords
Oracle EBS R12.2 Clone
Oracle EBS R12.2 Forms Error
RC-50221 Port Pool Not Free
RC-50221 CloneContext Failed
FRM-92101 Forms Session Failed During Startup
FRM-92101 No Response From Runtime Process
ldflags symlink Oracle Forms
libXm.so.2 not found
Oracle Forms Runtime Process
adcfgclone.pl appsTier
adcfgclone.pl appsTier Error
Oracle EBS Clone Troubleshooting
Oracle Linux 7 OpenMotif Issue
OpenMotif
frmweb
CloneContext
References
MOS Doc ID 454427.1 — FRM-92101: There was a failure in the Forms Server during startup
A critical alert was triggered in production showing UNDO tablespace usage at 97% with near-zero free space. At first glance, this looked like an immediate outage risk. However, deeper analysis revealed a completely different story.
Initial Observations
Standard checks showed Tablespace UNDOTBS1 with a total size of 16 GB and only ~2 MB free space. This strongly suggested the UNDO tablespace was nearly full. However, deeper analysis revealed a different story.
The Turning Point
Instead of relying on DBA_FREE_SPACE, we analyzed UNDO internals using extent status:
SELECT status,
ROUND(SUM(bytes)/1024/1024,2) MB
FROM dba_undo_extents
GROUP BY status;
EXPIRED UNDO extents are fully reusable by Oracle. This means ~55% of the tablespace was immediately reusable and no actual space pressure existed.
Real Usage Calculation
SELECT ROUND(
SUM(CASE WHEN status IN ('ACTIVE','UNEXPIRED') THEN bytes ELSE 0 END)
/ SUM(bytes) * 100, 2) actual_used_pct
FROM dba_undo_extents;
Actual usage was only ~45% — not 97%.
Root Cause of the False Alert
The monitoring system was using DBA_FREE_SPACE which does not include reusable EXPIRED extents. This is incorrect for UNDO tablespaces and leads to false critical alerts, unnecessary escalations, and wasted DBA effort.
The Correct Monitoring Approach
SELECT tablespace_name,
ROUND(SUM(CASE WHEN status IN ('ACTIVE','UNEXPIRED') THEN bytes ELSE 0 END)
/ SUM(bytes) * 100, 2) actual_used_pct
FROM dba_undo_extents
GROUP BY tablespace_name;
A production alert indicating UNDO tablespace usage at ~99% is typically treated as a critical issue. However, in Oracle Multitenant environments with local UNDO enabled, this metric can be misleading when evaluated using traditional tablespace monitoring techniques.
This article presents a real-world scenario involving the PDB-level UNDO tablespace APPS_UNDOTS1, where utilization reached ~99%, yet no actual resource contention existed.
This issue occurred in a Multitenant environment with Local UNDO enabled:
UNDO tablespace: APPS_UNDOTS1
Scope: PDB-level
Each PDB maintains its own UNDO
UNDO behavior must always be analyzed at the PDB level, not just at the CDB level.
Investigation Approach
Rather than relying on % used, a state-based analysis was performed.
Step 1: Active Transactions
SELECT COUNT(*) FROM v$transaction;
Observation:
Minimal active transactions
No ongoing workload pressure
Step 2: UNDO Extent Analysis
SELECT status, SUM(bytes)/1024/1024 MB FROM dba_undo_extents GROUP BY status;
Key Observation
STATUS MB PCT ----------- -------- ------- ACTIVE ~3 MB ~0.01% UNEXPIRED ~51 MB ~0.18% EXPIRED ~28 GB ~99.81%
💡 Critical Insight
~99.81% of UNDO (APPS_UNDOTS1) was EXPIRED and fully reusable
This means:
No real space pressure
No transaction risk
Tablespace is effectively available
Understanding UNDO Behavior
UNDO extents follow a lifecycle:
ACTIVE → UNEXPIRED → EXPIRED → REUSED
State
Description
Reusable
ACTIVE
Used by active transactions
❌
UNEXPIRED
Retained for consistency
⚠️
EXPIRED
No longer needed
✅
UNDO extents are not physically freed but logically reused by Oracle based on demand.
Why the Alert Was Misleading
The alert was based on:
SELECT * FROM dba_free_space;
Limitation
Shows only physically free space
Does not reflect reusable UNDO
Ignores Oracle’s internal reuse mechanism
Root Cause
A long-running concurrent request:
Ran for ~30 hours
Performed heavy DELETE operations
Generated significant UNDO
During execution:
UNDO reached ~99% → real pressure
After completion:
UNDO became EXPIRED
Space became reusable
Alert persisted → false positive
When UNDO Is Actually a Problem
ACTIVE > 20–30% UNEXPIRED very high EXPIRED very low
UNDO becomes a real issue only when both EXPIRED and UNEXPIRED extents are exhausted and no reusable space remains.
Recommended Monitoring Approach
WITH undo AS ( SELECT status, SUM(bytes)/1024/1024 mb FROM dba_undo_extents GROUP BY status ), total AS ( SELECT SUM(mb) total_mb FROM undo ) SELECT u.status, ROUND(u.mb,2) AS mb, ROUND((u.mb / t.total_mb) * 100, 2) AS pct FROM undo u, total t;
A tablespace showing 99% utilization can still be completely healthy if most of its extents are reusable.
Level Insight
“UNDO is not a storage problem — it is a lifecycle problem. The difference between false alarms and accurate diagnosis lies in understanding how Oracle transitions undo extents.”
In Oracle E-Business Suite (EBS) environments, performance issues are often attributed to high workload or system resource constraints. However, some of the most critical slowdowns originate from less obvious sources — inactive sessions holding uncommitted transactions. This post walks through a real-world production incident where an inactive Oracle Forms session caused cascading blocking across multiple users due to TX row-level locks.
Observed Symptoms
Oracle Forms screens becoming unresponsive in Order Management and Shipping modules
Analysis of v$session, v$transaction, and dba_wait_chains revealed a single inactive Oracle Forms session (frmweb) holding an active transaction with multiple downstream sessions waiting on TX row lock contention.
-- Identify blocking sessions
SELECT blocking_session, sid, wait_class, event
FROM v$session
WHERE blocking_session IS NOT NULL;
-- Detect inactive sessions with active transactions
SELECT s.sid, s.serial#, s.status, s.program,
s.username, t.start_time,
ROUND(s.last_call_et/3600,2) hrs_inactive
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
AND s.status = 'INACTIVE'
ORDER BY hrs_inactive DESC;
-- Analyze full wait chain
SELECT * FROM dba_wait_chains;
The root blocking session showed STATUS = INACTIVE and EVENT = 'SQL*Net message from client' but had an active transaction in v$transaction — confirming it was idle at the application level but actively holding locks at the database level.
Root Cause
An Oracle Forms session executed a SELECT ... FOR UPDATE NOWAIT on WSH_DELIVERY_DETAILS, then became idle without committing or rolling back. This held exclusive row locks that blocked other sessions attempting to access the same rows, creating a cascading blocking chain.
-- The problematic SQL pattern
SELECT *
FROM WSH_DELIVERY_DETAILS
WHERE ROWID = :B1
FOR UPDATE NOWAIT;
Resolution
The root blocking session was identified, verified to have no active business transactions, approvals were obtained, and the session was terminated:
-- Kill blocking session (only after full validation and approval)
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
Locks were released immediately, the blocking chain resolved, and application responsiveness was restored.
Preventive Measures
Implement idle session timeout policies
Educate users on proper transaction handling in Oracle Forms
Review custom code using FOR UPDATE — keep transactions short and commit promptly
Monitor long-running and idle transactions proactively
Key Takeaways
An inactive session can still hold active transactions and critical locks
Always identify the root blocker — intermediate sessions are symptoms, not the cause
Application-level inactivity does not mean database-level inactivity
In Oracle EBS, the most disruptive issues are often caused by inactive sessions holding uncommitted transactions
Written by Syed Anwar Ahmed — Oracle Apps DBA with 11 years of production experience. Connect: sdanwarahmed@gmail.com | LinkedIn
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
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
Metric
Observation
Average Switch Rate
5-7 per hour
Peak Rate
8-10 per hour during business hours
Off-Peak Rate
1-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
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.
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.
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
