Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, October 18, 2007

ORA-27041 SVR4 Error: 24: Too many open files after 10.2.0.3 upgrade and patch 5257698

As already described in my previous post, after an 11i upgrade, we came across:

ORA-01116: error in opening database file 79

ORA-01110: data file 123:'/example/dbfs/exampledata.dbf'

ORA-27041: unable to open file

SVR4 Error: 24: Too many open files

Additional information: 3

These errors came on running concurrent requests, opening forms, compiling flexfields. You name it. We increased the file descriptors from 256 to 4096 and then to 8192. However the error kept coming. On researching the problem we found bug 5257698 which matched our problem. But we had already applied patch 5257698 as part of the post steps of the upgrade, why did the error still crop up then ? Further research revealed that the patch readme has special instructions of re-running cr9idata.pl if the instance uses 9i NLS data. When I had analyzed this patch, I assumed that since Apps 11i was certified with 10.2.0.3, it won't need any old 9i nls data. I was incorrect in this assumption. According to Metalink Note 376460.1 and the bug 5257698, the old 9i NLS data is needed by Apps 11i:

DIAGNOSTIC ANALYSIS:

Bug is raised with APPS team bug 5248754 who confirmed that reverting to 9i settings is legitimate and necessary since Apps C code will break if we try to use the 10gR2 NLS data.

So we ran the following as per readme:

1. Shutdown the database
2. Re-run the script '$ORACLE_HOME/nls/data/old/cr9idata.pl' as the owner of the ORACLE_HOME directory
3. Set the ORA_NLS10 environment variable to the newly created $ORACLE_HOME/nls/data/9idata directory
4. Restart the database

We are hoping that the issue will be solved after this. However, I have an SR logged with Oracle for this issue, just in case the problem persists. Will update this thread with results in the coming days.

3 comments:

Jagdeep said...

Hi Vikram,

I also faced the same issue and applied the patch. In the test instance, before this patch was applied, the error was there. I applied the patch and the error went away. We moved the resolution to production. But again, after 3 days, the error has come back again. My database version is 10.2.0.2.0 as we are on 11.5.10.2 oracle applications. Please share your views on this.

Regards,

Jagdeep
jsethi3@yahoo.com

Vikram said...

Hi Jagdeep,

Did you guys do the post steps of the patch that is re run cr9idata.pl ?

We did not face the issue again after doing the post patch steps.

Our ulimit was set to 8192. To find out on unix level what is the number of open files at any time, you can give this command:

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.

Also you can upgrade to 10.2.0.3 as it is certified with 11.5.10.2

- Vikram

Jagdeep said...

Hi Vikram,

We had to increase the nofile to 12000 and the error went away. One module in our implementation is opening too many files. Probably that was causing the issue. Yes, i did to the post patch step of creating 9i data as suggested in patch.

Thanks for your support and response.

Regards,

Jagdeep
jsethi3@yahoo.com