HPUNIX调整文件打开数

一天，在一个业务高峰期，某省的一个数据库突然宕机了，根据alertlog中发现的报错：Mon Jan 12 09:49:42 2009ARC0: Evaluating archive log 5 thread 1 sequence 142032ARC0: Beginning to archive log 5 thread 1 sequence 142032Creating archive destination LOG_ARCHIVE_DEST_1: /oraarchlog/arch_1_142032.arcMon Jan 12 09:49:56 2009ARC0: Completed archiving log 5 thread 1 sequence 142032Mon Jan 12 09:57:57 2009Errors in file /oracle/app/oracle/admin/orasid/bdump/orasid_lgwr_8267.trc: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 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: 2ORA-00312: online log 1 thread 1: /dev/vg_ora1/rredo_256m_01ORA-27041: unable to open fileHP-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.trc: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 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%，但是一直都没有引起监控人员的注意，终于导致打开文件数被撑爆。我们通过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/34816 0 113494/200010 001:08:56 N/A N/A 1139/4096 0 2678/34816 0 113494/200010 001:08:58 N/A N/A 1135/4096 0 2675/34816 0 113487/200010 001:09:00 N/A N/A 1135/4096 0 2675/34816 0 113487/200010 001:09:02 N/A N/A 1135/4096 0 2675/34816 0 113487/200010 0其中的file-sz就是指打开文件数。其中20多万的那个值是打开文件数的阈值，前者是当前实际打开的文件数。这阈值是有核心参数nfile决定的。我们在设置nfile的时候，建议是(15 * NPROC + 2048)；提醒值是oracle的process数乘以datafile数再加2048；极限值，在存数据库环境应该是os的极限进程数乘以数据文件数，即每个进程都打开所有的数据文件。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 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将设置成63488，如果根提醒值，将有141万，如果根据极限值，将有386万：SQL !oraclemy_db01:/oracle kmtune |grep nprocnproc 4096 - 4096 oraclemy_db01:/oracle exitSQL select 15*4096+2048 from dual;15*4096+2048- 63488SQL show parameter processNAME TYPE VALUE- - -processes integer 1500SQL SQL SQL select count(*) from v$datafile; COUNT(*)- 944SQL select 1500*944+2048 from dual;1500*944+2048- 1418048SQL select 4096*944 from dual; 4096*944- 3866624SQL对于设置6万，肯定是完全不适合我们这个系统；如果设置141万或者设置386万，这似乎又太大了。到底是什么进程需要打开那么多文件数？在sar -v的命令中，我们只看到了一个总数，能具体的去看什么进程打开多少文件数吗？在这里，HP提供了一个工具：crashinfo。可以查看各个进程的打开文件数：crashinfo用法是：在root权限下：my_db01#/tmpchmod +x crashinfomy_db01#/tmp./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 pagesCommand line: ./crashinfo -ofiles crashinfo (4.74)pid p_maxof ofiles p_comm- - - -1 4096 2 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 MpPeriodicDaem72 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 oracle13003 4096 671 oracle13009 4096 670 oracle13012 4096 459 oracle13014 4096 49 oracleTotal = 116775my_db01#/tmpps -ef |grep 12298 oracle 12298 1 0 Jan 15 ? 9:31 ora_dbw0_orasid root 12469 11867 1 02:06:39 pts/te 0:00 grep 12298my_db01#/tmpps -ef |grep 12300 oracle 12300 1 0 Jan 15 ? 9:32 ora_dbw1_orasid root 13363 11867 0 02:16:09 pts/te 0:00 grep 12300my_db01#/tmp通过ps -ef |grep 打开文件数最多的几个进程号，发现打开文件数最多的进程是dbwr，smon，lgwr，chkpt。每个进程基本上800900个打开文件数，且dbwr进程有8个，打开文件数那就更加多了。其他打开文件数较多的进程是LOCAL=NO的进程，通过观察spid关联sql_text，发现是对历史表的操作。历史表是大表，表空间有300多个数据文件，因此，这些进程的打开文件数，也在每个600左右。此时忽然记起，前段时间该省做优化时，当时DBA将dbwr进程数从2调整到了8，dbwr数增加了4倍，导致打开文件数也相应的增加，这应该就是造成nfile在近期慢慢增长到撑爆为止的原因了吧。找到了原因，问题就比较好解决了。找了一个停机的时间，把db_writer_processes调整回2，且为了以防万一，再将nfile值改为40万。附：crashinfo命令的其他用法：Usage: crashinfo options . coredir | kernel coreDefault: coredir=. if INDEX file present else kernel=/stand/vmunix core=/dev/kmemOptions: -h | -help flag,flag. flags: detail -u | -update -v | -verbose -c | -continue -H | -Html -e | -email ,flag,flag. flags: file= from= callid= -t | -trace flag,flag. flags: args regs (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 | -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: skip id= msg= type= -flocks flag,flag. flags: sleep vnode= inode= -sgfr version -fwlog cpu -vmtrace flag,flag. flags: bucket= arena= count= leak cor log parse如：my_db01#/tmpps -ef |grep ckpt oracle 12304 1 0 Jan 15 ? 3:29 ora_ckpt_orasid root 13509 11867 0 02:18:35 pts/te 0:00 grep ckptmy_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 pagesCommand line: ./crashinfo -ofiles 12304 crashinfo (4.74)pid p_maxof ofiles p_comm- - - -12304 4096 961 oraclemy_db01#/