HPUNIX调整文件打开数

1、HPUNIX调整文件打开数一天，在一个业务高峰期，某省的一个数据库突然宕机了，根据alertlog中发现的报错：Mon Jan 12 09:49:42 2009ARC0: Evaluating archive log 5 thread 1 sequence 142032 ARC0: Beginning to archive log 5 thread 1 sequence 142032 Creating archive destination LOG_ARCHIVE_DEST_1:/oraarchlog/arch_1_142032.arcMon Jan 12 09:49:56 2009ARC0:

2、 Completed archiving log 5 thread 1 sequence 142032 Mon Jan 12 09:57:57 2009Errors in file/oracle/app/oracle/admin/orasid/bdump/orasid_lgwr_8267.tr c:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1:/dev/vg_ora3/rredo_256m_21ORA-27041: unable to open file

3、HP-UX Error: 23: File table overflowAdditional information: 2ORA-00312: online log 1 thread 1:/dev/vg_ora2/rredo_256m_11ORA-27041: unable to open fileHP-UX Error: 23: File table overflowAdditional information: 2ORA-00312: online log 1 thread 1:/dev/vg_ora1/rredo_256m_01ORA-27041: unable to open file

4、HP-UX Error: 23: File table overflowAdditional information: 2Mon Jan 12 09:57:57 2009Errors in file/oracle/app/oracle/admin/orasid/bdump/orasid_lgwr_8267.tr c:ORA-00313: open failed for members of log group 1 of thread 1ORA-00312: online log 1 thread 1:/dev/vg_ora3/rredo_256m_21ORA-27041: unable to

5、open fileHP-UX Error: 23: File table overflowAdditional information: 2ORA-00312: online log 1 thread 1:/dev/vg_ora2/rredo_256m_11ORA-27041: unable to open fileHP-UX Error: 23: File table overflowAdditional information: 2看来是打开文件数太多，导致了此次数据库宕机。通过监控打开文件数发现，其实在前一天的时候，打开文件数已经到98%，在上午8点多的时候，打开文件数已经到99%，但是

6、一直都没有引起监控人员的注意，终于导致打开文件数被撑爆。我们通过sar -v可以发现打开文件数的总数：HP-UX my_db01 B.11.11 U 9000/800 01/17/0901:08:52 text-sz ov proc-sz ov inod-sz ov file-sz ov 01:08:54 N/A N/A 1139/4096 0 2678/348160 113494/200010 001:08:56 N/A N/A 1139/4096 0 2678/348160 113494/200010 001:08:58 N/A N/A 1135/4096 0 2675/348160 11

7、3487/200010 001:09:00 N/A N/A 1135/4096 0 2675/348160 113487/200010 001:09:02 N/A N/A 1135/4096 0 2675/348160 113487/200010 0其中的file-sz就是指打开文件数。其中20多万的那个值是打开文件数的阈值，前者是当前实际打开的文件数。这阈值是有核心参数nfile决定的。我们在设置nfile的时候，建议是(15 * NPROC + 2048)；提醒值是oracle的process数乘以datafile数再加2048；极限值，在存数据库环境应该是os的极限进程数乘以数据文件数，

8、即每个进程都打开所有的数据文件。NfileMax Number of simultaneously Openfiles system-wide at any given time. Totalnumber of slots it the file descriptor table,default=16*(nproc+16+maxusers)/10+32+ 2*(npty+nstrpty) recommended:to use default.= Oracle 9i = (15 * NPROC + 2048)For Oracle installations with a high number

9、of data files this might be not enough, than use the= (number of oracle processes) * (number of Oracle data files) + 2048)NprocMax Number of Processes that can existsimultaneously in the system,default=(20+8*MAXUSERS),influences ninode, nfile.recommended:= Oracle 9i = 4096根据计算，如果根据建议值，那么nfile将设置成634

10、88，如果根提醒值，将有141万，如果根据极限值，将有386万：SQL !oraclemy_db01:/oracle kmtune |grep nprocnproc4096 - 4096oraclemy_db01:/oracle exitSQL select15*4096+2048from dual;15*4096+2048-63488SQL show parameter processNAME TYPE VALUE - - -processes integer1500SQLSQLSQL select count(*)from v$datafile;COUNT(*)-944SQL select

11、1500*944+2048from dual;1500*944+2048-1418048SQL select4096*944from dual;4096*944-3866624SQL对于设置6万，肯定是完全不适合我们这个系统；如果设置141万或者设置386万，这似乎又太大了。到底是什么进程需要打开那么多文件数？在sar -v的命令中，我们只看到了一个总数，能具体的去看什么进程打开多少文件数吗？在这里，HP提供了一个工具：crashinfo。可以查看各个进程的打开文件数：crashinfo用法是：在root权限下：my_db01#/tmpchmod +x crashinfomy_db01#/tm

12、p./crashinfo -ofiles此时能看到各个进程的打开文件数情况：my_db01#/tmp ./crashinfo -ofilescrashinfo (4.74)Note: HP CONFIDENTIALlibp4 (9.305): Opening /stand/vmunix /dev/kmemLoading symbols from /stand/vmunixKernel TEXT pages not requested in crashconfWill use an artificial mapping from a.out TEXT pages Command line: ./

13、crashinfo -ofilescrashinfo (4.74)pid p_maxof ofiles p_comm- - - -1 40962 init2011 4096 4 getty94 4096 4 evmmirr62 4096 4 emcpdaemon63 4096 4 emcpProcd64 4096 4 emcpd65 4096 4 emcpdfd66 4096 4 emcpwdd67 4096 4 emcpstratd68 4096 4 MpAsyncIoDaemo69 4096 4 MpTestDaemon70 4096 4 MpPirpNotDoneD71 4096 4 M

14、pPeriodicDaem72 4096 4 MpDeferredDaem73 4096 4 MpGeneralReque74 4096 4 MpcAsyncIoDaem75 4096 4 MpcResumeIoDae76 4096 4 MpcPeriodicCal12986 4096 671 oracle13005 4096 671 oracle13291 4096 10 oracle12991 4096 671 oracle22466 4096 51 oracle12300 4096 847 oracle12898 4096 9 oracle12302 4096 979 oracle130

15、03 4096 671 oracle13009 4096 670 oracle13012 4096 459 oracle13014 4096 49 oracleTotal = 116775my_db01#/tmpps -ef |grep 12298oracle 12298 1 0 Jan 15 ? 9:31ora_dbw0_orasidroot 12469 11867 1 02:06:39 pts/te 0:00 grep 12298 my_db01#/tmpps -ef |grep 12300oracle 12300 1 0 Jan 15 ? 9:32ora_dbw1_orasidroot

16、13363 11867 0 02:16:09 pts/te 0:00 grep 12300 my_db01#/tmp通过ps -ef |grep 打开文件数最多的几个进程号，发现打开文件数最多的进程是dbwr，smon，lgwr，chkpt。每个进程基本上800900个打开文件数，且dbwr 进程有8个，打开文件数那就更加多了。其他打开文件数较多的进程是LOCAL=NO 的进程，通过观察spid关联sql_text，发现是对历史表的操作。历史表是大表，表空间有300多个数据文件，因此，这些进程的打开文件数，也在每个600左右。此时忽然记起，前段时间该省做优化时，当时DBA将dbwr进程数从2调

17、整到了8，dbwr数增加了4倍，导致打开文件数也相应的增加，这应该就是造成nfile 在近期慢慢增长到撑爆为止的原因了吧。找到了原因，问题就比较好解决了。找了一个停机的时间，把db_writer_processes调整回2，且为了以防万一，再将nfile值改为40万。附：crashinfo命令的其他用法：Usage: crashinfo options . coredir | kernel core Default: coredir=. if INDEX file present elsekernel=/stand/vmunix core=/dev/kmemOptions:-h | -help

18、 flag,flag.flags: detail-u | -update-v | -verbose-c | -continue-H | -Html-e | -email flags: file=from=callid=-t | -trace flag,flag.flags: argsregs (PA Only)Rregs (PA Only)locals (IA64 Only) frame (IA64 Only) mems (IA64 Only) bsp (IA64 Only) ss (IA64 Only) -s | -syscall-f | -full_comm-S | -Sleep-l |

19、-listonly flag,flag.flags: pid=-top count-p | -proctree flag,flag.flags: tids-i | -ioscan-ofiles pid,flag,flag. flags: tids-signals pid-seminfo-unlinked all-m | -msginfo flag,flag.flags: skipid=msg=type=-flocks flag,flag.flags: sleepvnode=inode=-sgfr version-fwlog cpu-vmtrace flag,flag.flags: bucket

20、=arena=count=leakcorlogparse如：my_db01#/tmpps -ef |grep ckptoracle 12304 1 0 Jan 15 ? 3:29ora_ckpt_orasidroot 13509 11867 0 02:18:35 pts/te 0:00 grep ckpt my_db01#/tmpmy_db01#/tmp./crashinfo -ofiles 12304crashinfo (4.74)Note: HP CONFIDENTIALlibp4 (9.305): Opening /stand/vmunix /dev/kmemLoading symbols from /stand/vmunixKernel TEXT pages not requested in crashconfWill use an artificial mapping from a.out TEXT pages Command line: ./crashinfo -ofiles 12304crashinfo (4.74)pid p_maxof ofiles p_comm- - - -12304 4096 961 o