ORA-27041 SVR4 Error:24: Too many open files

Recently this error appeared in forms while compiling flexfields:


While compiling the flexfields, the procedure wf_event.raise failed with following parameters:

event_name = oracle.apps.fnd.flex.dff.compiled
event_key = ONT.OE_HEADER_ATTRIBUTES

The error raised is:

ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 959
ORA-01110: data file 959: $ORACLE_HOME/data/exampledbf.dbf
ORA-27041: unable to open file
SVR4 Error: 24: Too many open files
Additional information: 3

This error occurs when the open file descriptors is set low. We can check it with this command:

ulimit -n
256

256 is the default value if nothing is set specifically in the kernel parameters.

To increase the value without rebooting you should do the following

Shutdown all app services and DB services.

Login as root
ulimit -n 8192
su - applmgr
Start all app services
exit
su - oracle
Start all DB services
exit

The following script measures open files at the OS-level :

lsof | perl -lane '$x{"$F[0]:$F[1]"}++;END { print "$x{$_}\t$_" for sort {$x{$a}<=>$x{$b}} keys %x}'

Make sure lsof is in your PATH
Perl can be used from opatch installation.

I am revisiting this article today on August 6, 2009 where this was reported again. We have since moved to Solaris 10 where lsof doesn't work the same way it did in Solaris 8. However this time it was not due to a low ulimit. Solaris 10 employs the mechanism of /etc/project file to control resource allocation based on user and group. You can verify if an entry for your oracle user exists correctly on your box by typing this command:

projects -l

# projects -l

system

projid : 0

comment: ""

users : (none)

groups : (none)

attribs:

user.root

projid : 1

comment: ""

users : (none)

groups : (none)

attribs:

noproject

projid : 2

comment: ""

users : (none)

groups : (none)

attribs:

default

projid : 3

comment: ""

users : (none)

groups : (none)

attribs:

group.staff

projid : 10

comment: ""

users : (none)

groups : (none)

attribs:

user.oracle

projid : 100

comment: "Oracle Database"

users : oracle

groups : (none)

attribs: process.max-file-descriptor=(basic,2048,deny)

project.max-sem-ids=(priv,4098,deny)

project.max-shm-ids=(priv,2048,deny)

project.max-shm-memory=(priv,51539607552,deny)

The error was coming again while compiling flexfields. I checked the project file and found that the entry read like this:

user.oracle11i

projid : 100

comment: "Oracle Database"

users : oracle11i

groups : (none)

attribs: process.max-file-descriptor=(basic,2048,deny)

project.max-sem-ids=(priv,4098,deny)

project.max-shm-ids=(priv,2048,deny)

project.max-shm-memory=(priv,51539607552,deny)

However we did not have a user called oracle11i. The oracle user was called oracle, but the instance name was oracle11i. I took help of Unix team to get the /etc/project file corrected.

The actual line in /etc/project looks very different than the clean output of projects -l command:

user.oracle:118:Oracle Database:oracle::process.max-file-descriptor=(basic,2048,deny);project.max-sem-ids=(priv,4098,deny);project.max-shm-ids=(priv,2048,deny);project.max-shm-memory=(priv,51539607552,deny)

Once it was corrected it looked like this in projects -l:

user.oracle

projid : 100

comment: "Oracle Database"

users : oracle

groups : (none)

attribs: process.max-file-descriptor=(basic,2048,deny)

project.max-sem-ids=(priv,4098,deny)

project.max-shm-ids=(priv,2048,deny)

project.max-shm-memory=(priv,51539607552,deny)

We bounced the database and all services of E-Business Suite. The users retested and confirmed that they were not facing the issue now.