Troubleshooting ORA-01555 Snapshot Too Old Errors in Oracle EBS

One of our finance users came to me today complaining that a critical end-of-month report kept failing halfway through. Looking at the request log, the culprit was obvious: ORA-01555: snapshot too old: rollback segment number with name... too small.

1. Understanding the Failure

This error simply means a long-running query needed to see old data blocks for consistency, but those blocks were overwritten in the Undo tablespace before the query could finish. To find out exactly how long the query was running and check our baseline retention times, I ran this diagnostic check:

SELECT tuned_undoretention, maxquerylen, undoblks FROM v$undostat;

2. The Live Production Fix

The maximum query length was heavily exceeding our default undo retention window parameters. To stop this from killing long finance reports, I dynamically extended our data parameters and scaled up our space bounds directly on the live database server node:

ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH;
ALTER DATABASE DATAFILE '/u01/oradata/prod/undo01.dbf' RESIZE 10G;

Setting `undo_retention` to 10800 forces Oracle to hold onto historical database undo blocks for a minimum of 3 hours. The user re-ran the processing transaction, and it completed successfully without a single snapshot dropout. Keep an eye on your monthly undo sizing!

How I Fixed a Critical ORA-04031 Error on EBS R12.2 Production

I ran into a brutal production cluster lockup on our Oracle E-Business Suite R12.2 environment today. Users were reporting sudden connection dropouts.

1. Root Cause Identification

I jumped onto the database node and checked the main alert log at:

$DIAG_HOME/diag/rdbms/ebsprod/ebsprod/trace/alert_ebsprod.log

The log was filled with a critical error: ORA-04031: unable to allocate 4096 bytes of shared memory. To verify the space fragmentation inside the reserved memory pool, my team ran this query:

SELECT name, free_space, request_failures FROM v$shared_pool_reserved;

2. Emergency Temporary Patch

The request failures counter was climbing rapidly. I applied a temporary patch to clean out the memory fragmentation:

ALTER SYSTEM FLUSH SHARED_POOL;

Warning: Doing this causes a brief, noticeable CPU spike for a few minutes while the system re-parses incoming SQL commands.

3. Long-Term Prevention Parameters

To fix this permanently, we updated our initialization parameter profile bounds to scale up the allocations safely:

ALTER SYSTEM SET shared_pool_size=4G SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_reserved_size=512M SCOPE=SPFILE;

Monitor your allocations closely!

Fixing Concurrent Manager Crashes After EBS R12.2 Cloning

We finished a rapid clone of our Oracle EBS R12.2 instance last night, but the Concurrent Managers refused to come up. Every time we kicked off the startup scripts, the ICM (Internal Concurrent Manager) immediately went into a deactivated status.

1. Checking the Real Logs

Instead of guessing, I went straight to the application tier diagnostic logs. The ICM log layout is found under your specific log directories:

$APPLCSF/$APPLLOG/NAME_MMDD.mgr

Inside, the log clearly stated that it could not initialize due to old node configurations stuck in the database layout tables. To clear out the stale configuration entries from the previous environment, I logged into SQL*Plus as the APPS user and ran the clean scripts:

EXEC FND_CONC_CLONE.SETUP_CLEAN;

2. Regenerating the Environment

Once the setup cleanup command executed successfully, I had to run Autoconfig to completely rebuild the system profile values and directory layouts:

sh $ADMIN_SCRIPTS_HOME/adautocfg.sh

After Autoconfig finished with a successful status code 0, I brought the managers back online using the standard control utility line script:

sh $ADMIN_SCRIPTS_HOME/adcmctl.sh start apps/apps_password

The Internal Concurrent Manager caught the correct database tables instantly and stayed up. Always remember to clear out stale configuration contexts post-clone!