how to find out which process cause the n-file to cross the limit

[caj]

Hi all


We have a production system which has the n-file parameter reached the threshold .The server is having oracle DB . Is it possible to know which process is causing the n-file parameter bump

15:55:22 text-sz ov proc-sz ov inod-sz ov file-sz ov
15:55:25 N/A N/A 485/16404 0 6148/19012 0 30563/30870 0
15:55:28 N/A N/A 485/16404 0 6148/19012 0 30566/30870 0
1# sar -v 2 2


Thanks in advance

Ajeesh

[James R. Ferguson · Accepted solution]

Hi:

I suppose that you could craft a C program using 'pstat(2)' to ascertain each process's contribution to the overall count.

However, why bother? Increasing 'nfile' doesn't consume much memory. The parameter controls the maximum number of slots in the process table at minimal memory cost.

The *default* value of 'nfile' is 64K on systems with 1GB memory or more. I would simply set your value much higher -- at least double what you currently have.

Regards!

...JRF...

[TwoProc]

Look in the alert file for the Oracle database. If there is a file it can't open due to an nfile constraint error - it will show up in the alert log.

[caj]

Thanks JRF for your quick response .

Question here is the server has some critical production job running .hence i won't able to bumb the parameter .I would like to know which is the process holding more memory so that i can free sme n-file .so that my production job keep running with out issue .
Also i have rp8400 server running 12 GB and 12 CPU .Can i bubm to 100000.

Thanks
ajeesh

[TwoProc]

Attacking your problem from the standpoint of which program is holding the most memory isn't going to get you the largest number of free file handles at all. Besides - the thing that's holding up the most memory in your server is problably the Oracle server itself, and you don't want to kill that off anyway.

Why not just kill off all other reports, database connections, etc. that are not critical to the system? Do that in order of least importance of what you're killing off until you're back down below the nfile threshold. For example a user that is looking to see if his expense report from last week has been approved is (at least at this moment) less important than a sales or customer-facing user. Likewise attendance reports, quality reports, etc are more-killable than your critical job, and could be killed off.

But going after a criteria of "memory footprint" isn't going to get you were you want to be.

[caj]

All ,

finaly the Database crashed and we have rebooted the server and restarted the DB .now everything looks ok .Now i need to increase the n-file parameter on this server .Is there any way/formula (i mean the value of maximum n file can be configured on the server based on the configuration ) .I have an rp8400 with 12 GB ram and 12 CPU and oracle 8i is running on this.Could you pls suggest the maximum value of n-file that i can bump to with out having other issue (performance ,etc).

Thanks
Ajeesh

[James R. Ferguson]

Hi (again) Ajeesh:

First, disread any formula used with 'nfile' and simply set it to the value you want.

The manpages below come from 11.23 but will be appropriate information for 11.11 too. You have an upper limit of 2^31. As noted, make sure that you follow this constraint, too:

nfile >= 2*maxfiles_lim

http://www.docs.hp.com/en/B2355-60105/nfile.5.html

Regards!

...JRF...

[A. Clay Stephenson]

... and rather than worrying about which process took you over the limit -- which is a pointless exercise, a real sysadmin would have had monitors in place that would have warned of the impending event so that action could have been taken before the limit was reached. In any event, bump nfile up by 50% or so and declare victory.

[okyour]

Nfile------

Max Number of simultaneously Open files system-wide at any given time. Total number of slots it the file descriptor table
For Oracle installations with a high number of data files this might be not enough, than use the >= ((number of oracle processes) * (number of Oracle data files) + 2048)

[Bill Hassell]

There is no way to sort processes by the number of files they open at the same time. As mentioned, your production machine is badly, badly overloaded so you have to stop new jobs from running. Now it is possible that some user is creating a stupid program that opens a network port hundreds of times, or creates thousands of dummy files, but a good sysadmin would never allow developers to write or run such programs.

The fatc that it is a production machine means that hitting a limit like nfiles means a mandatory reboot to double nfiles to 60000. Otherwise, you'll just have endless problems. You should never allow the system to use more that 85% of a critical parameter like nfiles without scheduling a reboot.