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:
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
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.