oracle和OS內(nèi)核參數(shù)匹配問題.doc

oracle和OS內(nèi)核參數(shù)匹配問題 我最近解決了一些這方面的問題，并在網(wǎng)絡(luò)上查詢了一些相關(guān)資料終于發(fā)現(xiàn)一個(gè)比較全面解釋這類問題的官方文檔。本來打算當(dāng)一次活雷鋒全文翻譯的，后來考慮自己英文一般，并且對于其中一些OS相關(guān)的知識(shí)也沒有深入了解。就保留英文大家自己去領(lǐng)會(huì)其中的要領(lǐng)，自己簡單總結(jié)了一下解決這類問題的關(guān)鍵點(diǎn)并整理一下英文原文。這個(gè)文檔是oracle官方技術(shù)支持網(wǎng)站Metalink的資料，里面引用了一些其它的文檔例如NOTE:115235.1 。 對于unix操作系統(tǒng)中Semaphores問題只是針對和oracle相關(guān)問題作一些解釋。對于信號量和共享內(nèi)存段參數(shù)在不同的系統(tǒng)中可能有不同的參數(shù)對應(yīng)，具體你去查詢對應(yīng)的OS文檔。在解決這類問題的時(shí)候我發(fā)現(xiàn)大部分問題都是因?yàn)樵诎惭boracle時(shí)沒有仔細(xì)閱讀針對指定OS的安裝說明造成安裝實(shí)例失敗，一般oracle的官方文檔都詳細(xì)說明在對應(yīng)操作系統(tǒng)上如何設(shè)置這些內(nèi)核參數(shù)。還有就是因?yàn)槠渌騉S管理人員調(diào)整了參數(shù)，但是沒有通知DBA，一旦oracle崩潰再次重新啟動(dòng)的時(shí)候就可能因?yàn)樾碌膬?nèi)核參數(shù)不合適而無法啟動(dòng)。 如果是oracle意外停機(jī)之后重新啟動(dòng)不成功，并出現(xiàn)類似ora-27123的錯(cuò)誤那么一定要詢問是否有其他人修改過內(nèi)核參數(shù)，有時(shí)候你沒有修改并不代表其他人沒有修改喲，我遇到過不少這樣的情況！1、與oracle相關(guān)的信號量和共享內(nèi)存段參數(shù) 一般unix系統(tǒng)中和信號量相關(guān)的是三個(gè)參數(shù)SEMMNI SEMMSL SEMMNS。他們相互關(guān)聯(lián)決定系統(tǒng)可以分配的信號量。Oracle使用信號量完成內(nèi)部進(jìn)程之間的通信。 關(guān)于共享內(nèi)存段使用shmmx參數(shù)進(jìn)行總體控制。它指定了系統(tǒng)可以分配的共享內(nèi)存段最大大小，實(shí)際并沒有分配那么多只是給出一個(gè)可以使用的最大限制。 對于類核參數(shù)的修改必須要重新啟動(dòng)系統(tǒng)之后才會(huì)生效。2、出現(xiàn)信號量和共享內(nèi)存段相關(guān)問題的情況 oracle只有在startup nomount的時(shí)候才會(huì)請求os的這些資源，用于建立SGA和啟動(dòng)后臺(tái)進(jìn)程。 有些情況下因?yàn)閛racle崩潰之后os沒有清除oracle分配的SGA，也可能造成共享內(nèi)存段不足，需要人工清除。 3、如何解決相關(guān)的問題 你可以簡單的修改init參數(shù)減少oracle對共享內(nèi)存段和信號量的需求。 對于控制信號量的三個(gè)參數(shù)SEMMNI SEMMSL SEMMNS 。最終可以使用的信號量由下面公式 提取 (semmsl * semmni) 或者 semmns中最小的值。 例如在linux下. 進(jìn)入目錄/proc/sys/kernel；用cat命令或more命令查看semaphore當(dāng)前參數(shù)的值： cat sem 命令運(yùn)行后將會(huì)出現(xiàn)如下的結(jié)果： 250 32000 32 128 其中, 250 是參數(shù)SEMMSL的值,32000是參數(shù)SEMMNS的值, 32是參數(shù)SEMOPM的值，而128則是參數(shù)SEMMNI的值。250*128=32000 對于oracle7需要信號量的設(shè)置等于init中processes的設(shè)置。對于8i 9i需要等于processes*2。 對于信號量參數(shù)的設(shè)定一定要小心，因?yàn)椴徽_的設(shè)置可能會(huì)讓系統(tǒng)使用默認(rèn)值。這個(gè)值一般比oracle系統(tǒng)要求的低。在HP unix上遇到過這樣的問題，當(dāng)時(shí)在參數(shù)配置的時(shí)候指定兩個(gè)不同的sem-mni造成系統(tǒng)使用默認(rèn)的設(shè)置。 對于共享內(nèi)存段，系統(tǒng)的設(shè)置至少要等于SGA的大小。 Semaphores and Shared Memory BULLETIN Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 05-AUG-2001Last Revision Date: 05-AUG-2002PURPOSE-To provide an overview of shared memory and semaphores, answer common questions related to these OS resources and provide links to more detailed information.SCOPE & APPLICATION-This document is intended for anyone who is responsible for creating oradministering an Oracle Database. It is intended to compliment the semaphore andshared memory information already provided in the Oracle Installation Guides.關(guān)于信號量和共享內(nèi)存段的背景知識(shí)-Semaphores and shared memory are two very distinct sets of Operating Systemresources. Semaphores are a system resource that Oracle utilizes for interprocesscommunication and they occupy a relatively small memory space, while shared memory is utilized to contain the SGA and can garner a large portion of physical memory.How many of these resources are available and how they are allocated is controlledby the configuration of the operating system kernel(kernel referring to thecentralized core components of the underlying operating system).There are three OS kernel parameters that work together to limit semaphoreallocation and one OS kernel paramater that dictates the maximum size of a sharedmemory segment.Operating System kernel parameters generally cannot be tuned on the fly. If theyare modified, the changes will not take place until the system is rebooted.Remember also that the kernel parameters related to semaphores and shared memory represent high-water marks. Meaning that the OS will not automaticallyallocate a given amount, but will allow up to that given amount to be availableupon request.什么時(shí)候信號量和共享內(nèi)存段問題最有可能發(fā)生-Both semaphore or shared memory errors appear primarily at instance startup (Thestartup nomount stage specifically). This is the only time that Oracle tries toacquire semaphores and shared memory for the instance. Errors related tosemaphores or shared memory rarely appear during normal database operations.The most common circumstance in which these errors occur is during the creation ofa new database.Sometimes when an Oracle instance crashes, however, its shared memory segments may not be released by the OS. This limits the overall amount of shared memory available for the instance to start up again. In this case, you will need to remove those segments manually.如何解決信號量和共享內(nèi)存段問題： How to resolve semaphore and shared memory errors:-In addressing both semaphore and shared memory errors at instance startup, thereare two separate areas that should be considered for reconfiguration.The first and most simple fix is to modify the init.ora to reduce the number of semaphores or the amount of shared memory Oracle will try to grab at instance startup.If your situation requires that you not reduce the appropriate init.oraparameters, you will have to modify the operating system kernel to allow the OS toprovide more semaphores or allow larger shared memory segments.SEMAPHORES= =IMPORTANT NOTE: ORACLE DOES NOT UTILIZE SEMAPHORES ON AIX OR DIGITAL/TRU64. 與信號量相關(guān)的的ORA錯(cuò)誤What kind of ORA errors are related to semaphores?-Out of memory type errors are seldom related to semaphores. Error messages which reference a SEMM* function are related to semaphores.IMPORTANT NOTE: THESE ERRORS ONLY OCCUR AT INSTANCE STARTUP.ORA-7250 spcre: semget error, unable to get first semaphore set. ORA-7279 spcre: semget error, unable to get first semaphore set. ORA-7251 spcre:semget error, could not allocate any semaphores. ORA-7252 spcre: semget error, could not allocate any semaphores. ORA-7339 spcre: maximum number of semaphore sets exceeded.NOTE:115235.1 Resolving ORA-7279 or ORA-27146 errors when starting instanceVERY COMMON On Oracle8i and Oracle9i:ORA-3113 end-of-file on communication channel at instance startup. ORA-27146 post/wait initialization failed NOTE:115235.1 Resolving ORA-7279 or ORA-27146 errors when starting instanceIf you want a very specific explanation of causes for the above errors, refer to:NOTE:15566.1 TECH Unix Semaphores and Shared Memory ExplainedHowever, while their exact cause varies, all these error messages indicate thatyour init.ora is configured to grab more semaphores than the OS has available.If you configure your OS as indicated in the following sections, you will not get any of the errors indicated above. 成功配置信號量的步驟The Basic Steps to Semaphore Success:-1. Understand The Basic Concept Behind Semaphores2. Understand How Many Semaphores Your Oracle Instance(s) Will Attempt to GrabFrom The Operating System.3. Configure Your OS Kernel To Accomodate all Your Oracle Instance(s) And alsoAllow For Future Growth.STEP 1 How are semaphores released by the OS for use by an application?-There are 3 OS kernel parameters that work together to limit semaphore allocation.When an application requests semaphores, the OS releases them in sets.Illustrated here as 2 sets: +-+ +-+| | | | | | |+-+ +-+Controlled by SEMMNI -OS limit on the Number of Identifiers or sets.Each set contains a tunable number of individual semaphores.Illustrated here as 2 semaphores per semaphore set: +-+ +-+| S | | S | S | | S |+-+ +-+Controlled by SEMMSL -The number of semaphores in an identifier orset.(Semaphore List)Ultimately however, the OS can limit the total number of semaphores availablefrom the OS. Controlled by:SEMMNS - The total Number of Semaphores allowed system wide.For instance: Lets say SEMMNI = 100000000 and SEMMSL= 100000000 while SEMMNS=10Even though SEMMNI is 100000000 and SEMMSL is 100000000, the max # of semaphores available on your system will only be 10, because SEMMNS is set to 10.Inversely: Lets say SEMMNI = 10 and SEMMSL = 10 while SEMMNS=100000000000000000000000000 Because SEMMNI is 10 and SEMMSL is 10, the max # of semaphores avail on your system will only be 100 or (10 X 10), despite what SEMMNS is set too.THIS NOTION CAN BE SUMMARIZED BY THE FOLLOWING STATEMENT:The max # of semaphores that can be allocated on a system will be the lesser of:(semmsl * semmni) or semmns.On HP: semmsl is hardcoded to 500. NOTE:74367.1 HP-UX SEMMSL Kernel ParameterSEMMNI, SEMMSL & SEMMNS are the basic names for OS semaphore kernel parameters,the full name may vary depending on your OS. Consult your OS specific Oracle Install guide.NOTE:116638.1 Understanding and Obtaining Oracle Documentation)STEP 2 How many semaphores will my Oracle instance(s) require?-With Oracle7: The number of semaphores required by an instance is equal to thesetting the processes parameter in the init.ora for the instance.With Oracle8, Oracle8i and Oracle9i: The number of semaphores required by aninstance is equal to 2 times the setting of the processes parameter in the init.ora for the instance. Keep in mind, however, that Oracle only momentarily grabs 2 X processes then releases half at instance startup. This measure was apparently introduced to ensure Oracle could not exhaust a system of semaphores.Oracle may also grab a couple of additional semaphores per instance for internaluse. STEP 3 Configure your OS kernel to accomodate all your Oracle instances.-There seems to be some confusion of how to deal with lack of semaphore errors. Thepopular theory being that if Oracle cannot find enough semaphores on a system,increase semmns. This is not always the case, as illustrated in STEP 1.Once you have determined your semaphore requirements for Oracle and compensated for future growth, contact your System Administrator or OS vendor for assistance in modifying the OS kernel.What should I set semmni, semmsl & semmns to?-Oracle Support typically does not recommend specific values for semaphore kernelparameters. Instead, use the information provided in this document to set the parameters to values that are appropriate for your operating environment.For more info please look at the following note : NOTE:15654.1 TECH: CalculatingOracles SEMAPHORE Requirements快速解決信號量問題Quick fix for resolving lack of semaphore errors:-Reduce the number of semaphores Oracle requires from the OS.The first and most simple fix is to modify the init.ora to reduce thenumber of semaphores or the amount of shared memory Oracle will try to grab atinstance startup.Keep in mind, with Oracle8, we grab 2 X processes then release half. This measurewas apparently introduced to ensure Oracle could not exhaust a system of semaphores.如何查找OS配置的信號量How can I find out how my OS kernel is configured for semaphores?-The files that are used to tune kernel parameters varies depending on yourOperating System. Consult your system administrator or OS vendor, because viewing the system file may not show accurate information about the runtime values.However, an important point to remember is that if a typographical error is madewhile editing these files, the OS will defer to a default value which is usually to low to accomodate Oracle. So its a good idea to check runtime values with utilities like /etc/sysdef.Ive tuned my OS kernel parameters, but I am still having semaphore problems.-常見問題！This may mean that you made a typographical error or did not rebuild yourOperating System kernel correctly(if a typographical error is made while editing these files, the OS will defer to a default value which is usually to low to accomodate Oracle).On Solaris, check current OS kernel values with this command: /etc/sysdef|grep -i semmIf these values do not reflect what you put in your system file, you likely made a typographically error.On HP, be sure the OS kernel was rebuilt correctly and that the OS was booted off the correct file. Contact your System Administrator or HP for more information. 在Linux系統(tǒng)上進(jìn)入目錄/proc/sys/kernel；用cat命令或more命令查看semaphore當(dāng)前參數(shù)的值： cat sem 命令運(yùn)行后將會(huì)出現(xiàn)如下的結(jié)果： 250 32000 32 128 其中, 250 是參數(shù)SEMMSL的值,32000是參數(shù)SEMMNS的值, 32是參數(shù)SEMOPM的值，而128則是參數(shù)SEMMNI的值。250*128=32000 如何獲得當(dāng)前正在使用的信號量How can I determine how many semaphores are currently being utilized?-On most Unix systems, current semaphore allocation can be displayed with the OScommand ipcs -s. % ipcs -sWhile good to know, this command is seldom used as part of troubleshooting semaphore errors.SHARED MEMORY= OS如何分配共享內(nèi)存段How is shared memory allocated by the OS?-This process varies slightly depending on Unix platform, but the basic premise is this:An application requests a given amount of contiguous shared memory from the OS. The OS dictates how large of a shared memory segment it will allow with the kernelparameter SHMMAX(Shared Memory Maximum). If the amount of shared memory requested by the application is greater than SHMMAX, the OS may be granted the shared memory in multiple segments. Ideally, however, you want the amount requested by the application to be less than SHMMAX so that the applications request can be fulfilled with one shared memory segment.SHMMAX和SGA的關(guān)系 How does SHMMAX relate to my SGA?-Since the SGA is comprised of shared memory, SHMMAX can potentially limit how large your SGA can be and/or prevent your instance from starting.What limits the size of my SGA?-In no particular order.5. The amount of Physical Memory and Swap space available on your system.6. The kernel paramater SHMMAX.7. Other OS specific limitations on shared memory.Memory SHMMAX OS Limits +-+ +-+ +-+| | | | | | +-+| | | | | | | S | | | | | | | G | | | | | | | A | | | | | | +-+-+ +-+ +-+Some OS specific limitations are discussed in the following documents:Oracle Administrators Reference available on the Oracle Install CDAdditionallly:HP-UX: NOTE:77310.1 HP-UX Large SGA support for HP, Memory WindowsNOTE:69119.1 HP-UX SGA Sizing Issues on HP-UX Solaris: NOTE:61896.1 SOLARIS: SGA size, sgabeg attach address and Sun與共享內(nèi)存當(dāng)相關(guān)的錯(cuò)誤What kind of ORA errors are related to shared memory?-Error Messages referencing a SHMM* function are related to shared memory.ORA-7306, ORA-7336, ORA-7329, ORA-7307, ORA-7337, ORA-7320, ORA-7329, ORA-7334VERY COMMON IN 8i: ORA-27100 shared memory realm already exists ORA-27102 out of memory ORA-27125 unable to create shared memory segment and/or linux 43 identifier removed ORA-27123 unable to attach to shared memory segmentNOTE:115753.1 UNIX Resolving the ORA-27123 error NOTE:1028623.6 SUN SOLARIS: HOW TO RELOCATE THE SGA如何設(shè)置SHMMAXWhat should I set shmmax to?-On some Unix platforms, the Install Guide recommends specific values. Previousversions of the Install Guide recommended setting SHMMAX to .5 *(physical memory present in machine). Most recently its been suggested SHMMAX be set to 4294967295 (4GB). This may not seem appropriate, particularly if the system has considerably less physical memory available, but it does prevent you from having to modify your system kernel everytime a new instance is created or additional physical memory is added to the system. Remember