ipcs、ipcrm、sysresv、kernel.shmmax

 

1.1  BLOG文件結構圖

 

 

 

1.2  前言部分

1.2.1  導讀和注意事項

各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① ipcs的使用

② ipcrm釋放oracle記憶體段

③ sysresv的使用

④ 核心引數kernel.shmmax

⑤ 如何快速的清理Oracle的程式

⑥ 其它維護操作

 

Tips：

① 本文在itpub（http://blog.itpub.net/26736162）、部落格園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）上有同步更新。

② 文章中用到的所有程式碼、相關軟體、相關資料及本文的pdf版本都請前往小麥苗的雲盤下載，小麥苗的雲盤地址見：http://blog.itpub.net/26736162/viewspace-1624453/。

③ 若網頁文章程式碼格式有錯亂，請下載pdf格式的文件來閱讀。

④ 在本篇BLOG中，程式碼輸出部分一般放在一行一列的表格中。

⑤ 本文適合於初中級人員閱讀，資料庫大師請略過本文。

⑥ 不喜勿噴。

本文若有錯誤或不完善的地方請大家多多指正，您的批評指正是我寫作的最大動力。

1.3  本文簡介

最近有朋友因為kernel.shmmax核心引數的問題導致資料庫不能啟動。小麥苗之前碰到過一次，只是沒有記錄下來，而且以前安裝資料庫的時候也沒有詳細介紹這幾個引數的含義，趁這次機會就把這個引數在詳細介紹一下吧。

 

1.4  相關文章連結

① 【故障解決】IPCS和IPCRM使用：http://blog.itpub.net/26736162/viewspace-2112518

② ORACLE核心引數：http://blog.itpub.net/26736162/viewspace-2112447/

③ sysresv：http://blog.itpub.net/26736162/viewspace-2112443/

④ 影片講解IPCS和IPCRM使用：http://www.iqiyi.com/w_19rs33qqsp.html

⑤ 有關“TNS-12518: TNS:listener could not hand off client connection”的更多內容請參考：【故障|監聽】TNS-12518、TNS-00517和 Linux Error：32：Broken pipe：http://blog.itpub.net/26736162/viewspace-2135468/

 

第二章 ipcs/ipcrm命令

更多內容請參考：http://blog.itpub.net/26736162/viewspace-2112518

 

unix/linux下的共享記憶體、訊號量、佇列資訊管理

在Unix或Linux下，經常有因為共享記憶體、訊號量，佇列等共享資訊沒有乾淨地清除而引起一些問題。

檢視共享記憶體的命令是：ipcs [-m|-s|-q]。若ipcs命令不帶引數，則預設會列出共享記憶體、訊號量，佇列資訊，而-m列出共享記憶體，-s列出共享訊號量，-q列出共享佇列。

清除命令是：ipcrm [-m|-s|-q] id，其中，-m刪除共享記憶體，-s刪除共享訊號量，-q刪除共享佇列。

[oracle@rhel6lhr ~]$ ipcs -h ipcs provides information on ipc facilities for which you have read access. Resource Specification:         -m : shared_mem         -q : messages         -s : semaphores         -a : all (default) Output Format:         -t : time         -p : pid         -c : creator         -l : limits         -u : summary -i id [-s -q -m] : details on resource identified by id usage : ipcs -asmq -tclup         ipcs [-s -m -q] -i id         ipcs -h for help.

 

 

 

2.1  ipcs

1. 命令格式

 

ipcs [resource-option] [output-format]

ipcs [resource-option] -i id

 

2. 命令功能

 

提供IPC裝置的資訊

 

3. 使用方法

 

resource選項：

ipcs -m 檢視系統共享記憶體資訊

ipcs -q 檢視系統訊息佇列資訊

ipcs -s 檢視系統訊號量資訊

ipcs [-a] 系統預設輸出資訊，顯示系統內所有的IPC資訊

[martin@localhost data]$ ipcs -a   ------ Message Queues -------- key        msqid      owner      perms      used-bytes   messages      ------ Shared Memory Segments -------- key        shmid      owner      perms      bytes      nattch     status      0x00000000 229376     martin     600        4194304    2          dest         0x00000000 196609     martin     600        524288     2          dest         0x00000000 327682     martin     600        393216     2          dest         0x00000000 491525     martin     600        2097152    2          dest           ------ Semaphore Arrays -------- key        semid      owner      perms      nsems

 

輸出格式控制：

 

ipcs -c 檢視IPC的建立者和所有者

ipcs -l 檢視IPC資源的限制資訊

ipcs -p 檢視IPC資源的建立者和使用的程式ID

ipcs -t 檢視最新呼叫IPC資源的詳細時間

ipcs -u 檢視IPC資源狀態彙總資訊

[martin@localhost data]$ ipcs -u --human   ------ Messages Status -------- allocated queues = 0 used headers = 0 used space = 0B   ------ Shared Memory Status -------- segments allocated 4 pages allocated 1760 pages resident  339 pages swapped   0 Swap performance: 0 attempts     0 successes   ------ Semaphore Status -------- used arrays = 0 allocated semaphores = 0

 

 

額外格式控制：

ipcs -l --human

以人類可以閱讀的方式顯示size

[martin@localhost data]$ ipcs -l --human   ------ Messages Limits -------- max queues system wide = 3644 max size of message = 8K default max size of queue = 16K   ------ Shared Memory Limits -------- max number of segments = 4096 max seg size = 16E max total shared memory = 16E min seg size = 1B   ------ Semaphore Limits -------- max number of arrays = 128 max semaphores per array = 250 max semaphores system wide = 32000 max ops per semop call = 32 semaphore max value = 3276

 

 

 

[oracle@rhel6lhr ~]$ ipcs -l   ------ Shared Memory Limits -------- max number of segments = 4096 max seg size (kbytes) = 98442 max total shared memory (kbytes) = 3221512 min seg size (bytes) = 1   ------ Semaphore Limits -------- max number of arrays = 2048 max semaphores per array = 250 max semaphores system wide = 256000 max ops per semop call = 100 semaphore max value = 32767   ------ Messages: Limits -------- max queues system wide = 7643 max size of message (bytes) = 65536 default max size of queue (bytes) = 65536

 

2.2  ipcrm

1. 命令功能

透過指定ID刪除刪除IPC資源，同時將與IPC物件關聯的資料一併刪除，只有超級使用者或IPC資源建立者能夠刪除

2. 使用方法

ipcrm -M shmkey

移除用shmkey建立的共享記憶體段

ipcrm -m shmid

移除用shmid標識的共享記憶體段

ipcrm -S semkey

移除用semkey建立的訊號量

ipcrm -s semid

移除用semid標識的訊號量

ipcrm -Q msgkey

移除用msgkey建立的訊息佇列

ipcrm -q msgid

移除用msgid標識的訊息佇列

 

2.3  如何快速的清理Oracle的程式？

 

真題1、 如何快速的清理Oracle的程式？

答案：若想要快速清理掉Oracle的程式，則最直接的辦法是殺pmon程式。有如下3條命令可供選擇，其中加粗的orcl替換成ORACLE_SID的值即可。

kill -9 `ps -ef|grep orcl| grep -v grep | awk '{print $2}'` ps -ef |grep orcl|grep -v grep|awk '{print $2}' | xargs kill -9 ipcs -m | grep oracle | awk '{print $2}' | xargs ipcrm shm

若想要快速殺掉叢集的程式，則可以執行如下命令：

kill -9 `ps -ef|grep d.bin| grep -v grep | awk '{print $2}'`

注意，生產庫上嚴禁使用，否則可能導致叢集不能正常啟動。

第三章 sysresv命令

3.1  若是一個主機上有多個oracle例項的話該如何確定哪個共享記憶體段屬於我們該清掉的oracle例項的記憶體段？

答案：使用sysresv命令。sysresv是Oracle在Linux/Unix平臺提供的工具，用來檢視Oracle例項使用的共享記憶體和訊號量等資訊。sysresv存放的路徑：$ORACLE_HOME/bin/sysresv。使用時需要設定LD_LIBRARY_PATH環境變數，用來告訴Oracle共享庫檔案的位置。sysresv用法如下：

[oracle@rhel6lhr ~]$ sysresv -h sysresv: invalid option -- 'h' usage   : sysresv [-if] [-d <on/off>] [-l sid1 <sid2> ...]           -i : Prompt before removing ipc resources for each sid           -f : Remove ipc resources silently, oevrrides -i option           -d <on/off> : List ipc resources for each sid if on           -l sid1 <sid2> .. : apply sysresv to each sid Default : sysresv -d on -l $ORACLE_SID Note    : ipc resources will be attempted to be deleted for a           sid only if there is no currently running instance           with that sid. [oracle@rhel6lhr ~]$ which sysresv /u01/app/oracle/product/11.2.0/dbhome_1/bin/sysresv

 

 

 

來看一下簡單使用：

oracle@sunvs-b@/oracle/oracle $ uname -a SunOS sunvs-b 5.10 Generic_139555-08 sun4u sparc SUNW,Sun-Fire-480R oracle@sunvs-b@/oracle/oracle $ ps -ef|grep pmon   oracle 26257     1   0   5月 24 ?         140:42 ora_pmon_H2   oracle 15479 14078   0 14:01:36 pts/4       0:00 grep pmon oracle 12449     1   0   8月 17 ?          17:44 ora_pmon_U2   oracle@sunvs-b@/oracle/oracle $ sysresv -l H2   IPC Resources for ORACLE_SID "H2" : Shared Memory: ID              KEY 1979711594      0x00000000 1979711595      0x00000000 1979711596      0x00000000 1979711597      0xce653c24 Semaphores: ID              KEY 16777316        0x25393874 Oracle Instance alive for sid "H2"   oracle@sunvs-b@/oracle/oracle $ ipcs -ms IPC status from <running system> as of 2011年08月29日 星期一 14時11分51秒 CST T         ID      KEY        MODE        OWNER    GROUP Shared Memory: m 1577058426   0xf5649758 --rw-r-----   oracle oinstall m 1577058425   0          --rw-r-----   oracle oinstall m 1577058424   0          --rw-r-----   oracle oinstall m 1577058423   0          --rw-r-----   oracle oinstall m 1979711605   0x4e65af   --rw-r--r--   oracle oinstall m 1979711604   0x3e65af   --rw-r--r--   oracle oinstall m 1979711603   0x1e65af   --rw-r--r--   oracle oinstall m 1979711602   0xe65af    --rw-r--r--   oracle oinstall m 1979711597   0xce653c24 --rw-r-----   oracle oinstall m 1979711596   0          --rw-r-----   oracle oinstall m 1979711595   0          --rw-r-----   oracle oinstall m 1979711594   0          --rw-r-----   oracle oinstall m 1979711511   0x31f4002  --rw-rw-rw-    cupsz cupucuse m  754974788   0xc93f     --rw-rw-rw-     hsm1 cupucuse m  754974787   0xc93e     --rw-rw-rw-     hsm1 cupucuse m  754974786   0xc93d     --rw-rw-rw-     hsm1 cupucuse m  754974785   0xc93c     --rw-rw-rw-     hsm1 cupucuse m  754974784   0xc93b     --rw-rw-rw-     hsm1 cupucuse m  754974783   0xc93a     --rw-rw-rw-     hsm1 cupucuse m  754974782   0xc939     --rw-rw-rw-     hsm1 cupucuse m  754974781   0xc938     --rw-rw-rw-     hsm1 cupucuse m  754974780   0xc937     --rw-rw-rw-     hsm1 cupucuse m  754974779   0xc936     --rw-rw-rw-     hsm1 cupucuse m  754974778   0xc935     --rw-rw-rw-     hsm1 cupucuse m  754974777   0xc934     --rw-rw-rw-     hsm1 cupucuse m  754974776   0xc933     --rw-rw-rw-     hsm1 cupucuse m  754974775   0xc932     --rw-rw-rw-     hsm1 cupucuse m  754974774   0xc930     --rw-rw-rw-     hsm1 cupucuse m  754974773   0xc92f     --rw-rw-rw-     hsm1 cupucuse m  754974772   0xc92e     --rw-rw-rw-     hsm1 cupucuse m  754974771   0xc92d     --rw-rw-rw-     hsm1 cupucuse m  754974770   0xc931     --rw-rw-rw-     hsm1 cupucuse m         45   0x741cc1a6 --rw-rw-rw-     root     root m         44   0x741cc1a5 --rw-rw-rw-     root     root m         43   0x741cc1a4 --rw-rw-rw-     root     root m         42   0x741cc1a3 --rw-rw-rw-     root     root m         41   0x741cc1a2 --rw-rw-rw-     root     root m         40   0x741cc1a1 --rw-rw-rw-     root     root m         39   0x741cc1a0 --rw-rw-rw-     root     root m         37   0x435dce60 --rw-rw-rw-     root     root m          0   0x22bb     --rw-rw----     root      dba Semaphores: s   16777324   0x25393ad4 --ra-r-----   oracle oinstall s   16777320   0x1e65af   --ra-ra-ra-   oracle oinstall s   16777319   0xe65af    --ra-ra-ra-   oracle oinstall s   16777316   0x25393874 --ra-r-----   oracle oinstall s   16777296   0          --ra-ra-ra-    cupst cupucuse s   16777294   0          --ra-ra-ra-    cupst cupucuse s   16777289   0          --ra-ra-ra-    cuput cupucuse s   16777287   0          --ra-ra-ra-    cuput cupucuse s   16777282   0          --ra-ra-ra-   cupvip cupucuse s   16777280   0          --ra-ra-ra-   cupvip cupucuse s   16777279   0          --ra-ra-ra-    cupfb cupucuse s   16777277   0          --ra-ra-ra-    cupfb cupucuse s   16777268   0          --ra-ra-ra-    cupuc cupucuse s   16777266   0          --ra-ra-ra-    cupuc cupucuse s   16777261   0          --ra-ra-ra-    cuphx cupucuse s   16777259   0          --ra-ra-ra-    cuphx cupucuse s   16777258   0          --ra-ra-ra-    cupsz cupucuse s   16777256   0          --ra-ra-ra-    cupsz cupucuse s          1   0x55064bec --ra-r--r--     root     root s          0   0x710644ac --ra-ra-ra-     root     root

 

 

 

說明一下：在安裝ORACLE產品前，需要設定系統的共享記憶體段的最大值和個數限制，例項在啟動後，應儘量保證SGA在一個共享記憶體段上，這裡由於我是在RAC的一個節點上進行的測試，所以例項記憶體被分配到4個共享記憶體段上。

 

IPC的清理可以使用sysresv –if，如果例項正在執行，清理操作會被終止：

oracle@sunvs-b@/oracle/oracle $ sysresv -fi -l H2   IPC Resources for ORACLE_SID "H2" : Shared Memory: ID              KEY 1979711594      0x00000000 1979711595      0x00000000 1979711596      0x00000000 1979711597      0xce653c24 Semaphores: ID              KEY 16777316        0x25393874 Oracle Instance alive for sid "H2" SYSRESV-005: Warning         Instance maybe alive - aborting remove for sid "H2"

 

另外如果需要清理記憶體段和訊號量，而sysresv發現例項是alive的，可以使用ipcrm命令：

ipcrm -m <memid> ipcrm -s <semid>

 

 

3.1.1  實驗

[ZFXDESKDB2:oracle]:/oracle>ps -ef|grep ora_pmon_   oracle 12255344 21626964  0 17:43:01  pts/0  0:00 grep ora_pmon_   oracle 17629238       1  0 18:57:42     -  0:09 ora_pmon_raclhr2   oracle 20250806       1  0 18:57:42     -  0:10 ora_pmon_oraESKDB2 [ZFXDESKDB2:oracle]:/oracle>which sysresv /oracle/app/oracle/product/11.2.0/db/bin/sysresv [ZFXDESKDB2:oracle]:/oracle>ORACLE_SID=raclhr2 [ZFXDESKDB2:oracle]:/oracle>sysresv   IPC Resources for ORACLE_SID "raclhr2" : Shared Memory: ID             KEY 5242886        0xffffffff 5242883        0xffffffff 1048583        0xd92489e0 Oracle Instance alive for sid "raclhr2" [ZFXDESKDB2:oracle]:/oracle>ipcs IPC status from /dev/mem as of Wed Jun  1 17:43:47 BEIST 2016 T       ID    KEY       MODE      OWNER   GROUP Message Queues: q        0 0x9283a0d2 -Rrw-------    root  system q        1 0xffffffff -----------    root  system   Shared Memory: m  1048576  00000000 --rw-r-----    grid     dba m  1048577  00000000 --rw-r-----    grid     dba m  1048578 0x210000aa --rw-rw----    root  system m  5242883  00000000 --rw-r-----  oracle asmadmin m  1048580  00000000 --rw-r-----  oracle asmadmin m  1048581  00000000 --rw-r-----  oracle asmadmin m  5242886  00000000 --rw-r-----  oracle asmadmin m  1048583 0xd92489e0 --rw-r-----  oracle asmadmin m  1048584 0xd1a4a5d8 --rw-r-----    grid     dba m  8388617 0x3f516768 --rw-r-----  oracle asmadmin m 759169034 0x21000148 --rw-rw----  oracle     dba Semaphores: s  3145728 0x0100324a --ra-ra-r--    root  system s        1 0x620025b4 --ra-r--r--    root  system s        2 0x02001958 --ra-ra-ra-    root  system s        3 0x01001958 --ra-ra-ra-    root  system s        9 0x010024be --ra-------    root  system s  1048590 0x410000a8 --ra-ra----    root  system s  11534361 0x41000147 --ra-ra----  oracle     dba [ZFXDESKDB2:oracle]:/oracle>ipcs -m IPC status from /dev/mem as of Wed Jun  1 17:43:56 BEIST 2016 T       ID    KEY       MODE      OWNER   GROUP Shared Memory: m  1048576  00000000 --rw-r-----    grid     dba m  1048577  00000000 --rw-r-----    grid     dba m  1048578 0x210000aa --rw-rw----    root  system m  5242883  00000000 --rw-r-----  oracle asmadmin m  1048580  00000000 --rw-r-----  oracle asmadmin m  1048581  00000000 --rw-r-----  oracle asmadmin m  5242886  00000000 --rw-r-----  oracle asmadmin m  1048583 0xd92489e0 --rw-r-----  oracle asmadmin m  1048584 0xd1a4a5d8 --rw-r-----    grid     dba m  8388617 0x3f516768 --rw-r-----  oracle asmadmin m 759169034 0x21000148 --rw-rw----  oracle     dba [ZFXDESKDB2:oracle]:/oracle>ipcrm -m 5242886 [ZFXDESKDB2:oracle]:/oracle>ipcrm -m 5242883 [ZFXDESKDB2:oracle]:/oracle>ipcrm -m 1048583 [ZFXDESKDB2:oracle]:/oracle>sysresv   IPC Resources for ORACLE_SID "raclhr2" : Shared Memory ID             KEY No shared memory segments used Oracle Instance not alive for sid "raclhr2" Oracle Instance not alive for sid "raclhr2" [ZFXDESKDB2:oracle]:/oracle>ps -ef|grep ora_pmon_   oracle 17629238       1  0 18:57:42     -  0:09 ora_pmon_raclhr2   oracle 20250806       1  0 18:57:42     -  0:10 ora_pmon_oraESKDB2   oracle 23330844 21626964  0 17:44:46  pts/0  0:00 grep ora_pmon_ [ZFXDESKDB2:oracle]:/oracle>sqlplus / as sysdba   SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 1 17:44:52 2016   Copyright (c) 1982, 2013, Oracle.  All rights reserved.   Connected to an idle instance.   SYS@raclhr2> shutdown abort ORACLE instance shut down. SYS@raclhr2> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

 

 

 

第四章 Oracle核心引數

檢視：more /proc/sys/kernel/shmmax

臨時生效：echo 3145728 > /proc/sys/kernel/shmmax

永久生效，修改檔案：/etc/sysctl.conf，並使修改引數立即生效：/sbin/sysctl -p

重要的幾個引數如下所示：

kernel.shmall = 2097152 kernel.shmmax = 1054472192 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128

其含義分別如下所示：

（一）kernel.shmall = 2097152 # kernel.shmall引數是控制共享記憶體頁數。Linux 共享記憶體頁大小為4KB，共享記憶體段的大小都是共享記憶體頁大小的整數倍。如果一個共享記憶體段的最大大小是16G，那麼需要共享記憶體頁數是 16GB/4KB = 16777216KB/4KB = 4194304（頁），也就是64Bit系統下16GB實體記憶體，設定kernel.shmall = 4194304才符合要求（幾乎是原來設定2097152的兩倍）。簡言之，該引數的值始終應該至少為： ceil(SHMMAX/PAGE_SIZE)。這個值太小有可能導致資料庫啟動報錯（ORA-27102: out of memory）。

（二）kernel.shmmax = 1054472192  #定義一個記憶體段最大可以分配的記憶體空間，單位為位元組。如果定義太小，那麼會導致啟動例項失敗，或者SGA就會被分配到多個共享記憶體段。那麼記憶體中的指標連線會給系統帶來一定的開銷，從而降低系統效能。這個值的設定應該大於SGA_MAX_TARGET或MEMORY_MAX_TARGET的值，最大值可以設定成大於或等於實際的實體記憶體。如果kernel.shmmax為100M，sga_max_size為500M，那麼啟動Oracle例項至少會分配5個共享記憶體段；如果設定kernel.shmmax為2G，sga_max_size為500M，那麼啟動Oracle例項只需要分配1個共享記憶體段。

（三）kernel.shmmni = 4096 #設定系統級最大共享記憶體段數量，該引數的預設值是4096。這一數值已經足夠，通常不需要更改。。

（四）kernel.sem = 250 32000 100 128 #訊號燈的相關配置，訊號燈semaphores是程式或執行緒間訪問共享記憶體時提供同步的計數器。可以透過命令“cat /proc/sys/kernel/sem”來檢視當前訊號燈的引數配置，如下所示：

[root@edsir4p1 ~]# cat /proc/sys/kernel/sem 250     32000   100     128

其4個值的含義分別如下：

① 250表示SEMMSL，設定每個訊號燈組中訊號燈最大數量，推薦的最小值是250。對於系統中存在大量併發連線的系統，推薦將這個值設定為PROCESSES初始化引數加10。

② 32000表示SEMMNS，設定系統中訊號燈的最大數量。作業系統在分配訊號燈時不會超過LEAST(SEMMNS,SEMMSL*SEMMNI)。事實上，如果SEMMNS的值超過了SEMMSL*SEMMNI是非法的，因此推薦SEMMNS的值就設定為SEMMSL*SEMMNI。Oracle推薦SEMMNS的設定不小於32000。

③ 100表示SEMOPM，設定每次系統呼叫可以同時執行的最大訊號燈操作的數量。由於一個訊號燈組最多擁有SEMMSL個訊號燈，因此有推薦將SEMOPM設定為SEMMSL的值。Oracle驗證的10.2和11.1的SEMOPM的配置為100。

④ 128表示SEMMNI，設定系統中訊號燈組的最大數量。Oracle10g和11g的推薦值為142。

 

4.1  kernel.shmmax引數

4.1.1  實驗1

下面臨時設定kernel.shmmax為3M，會導致Oracle不能啟動，設定sqlplus不能進入：

[root@edsir4p1 ~]# echo 3145728 > /proc/sys/kernel/shmmax  <<<====  臨時設定3M [oracle@edsir4p1- ~]$ more /proc/sys/kernel/shmmax <<<==== 檢視是否生效 3145728 [root@edsir4p1 ~]# /sbin/sysctl -a | grep shm vm.hugetlb_shm_group = 0 kernel.shmmni = 4096 kernel.shmall = 2097152 kernel.shmmax = 3145728 [root@edsir4p1 ~]# more /etc/sysctl.conf | grep kernel.shm kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 [root@edsir4p1 ~]# su - oracle [oracle@edsir4p1- ~]$ . PROD1_env [oracle@edsir4p1-PROD1 ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 14 10:09:08 2017   Copyright (c) 1982, 2009, Oracle.  All rights reserved.   ERROR: ORA-12547: TNS:lost contact     Enter user-name:     [oracle@edsir4p1-PROD1 ~]$ oerr ora 12547 12547, 00000, "TNS:lost contact" // *Cause: Partner has unexpectedly gone away, usually during process // startup. // *Action: Investigate partner application for abnormal termination. On an // Interchange, this can happen if the machine is overloaded.

 

告警日誌：

Linux Error: 32: Broken pipe Tue Nov 14 10:00:38 2017 14-NOV-2017 10:00:38 * (CONNECT_DATA=(SID=PROD1)(CID=(PROGRAM=emagent)(HOST=edsir4p1.us.oracle.com)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.190.104.111)(PORT=26305)) * establish * PROD1 * 12518 TNS-12518: TNS:listener could not hand off client connection TNS-12547: TNS:lost contact   TNS-12560: TNS:protocol adapter error    TNS-00517: Lost contact     Linux Error: 32: Broken pipe

 

 

 

或啟動報錯：

SYS@PROD1> startup ORA-00443: background process "PMON" did not start SYS@PROD1> startup ORA-12547: TNS:lost contact SYS@PROD1>

 

有關“TNS-12518: TNS:listener could not hand off client connection”的更多內容請參考：

【故障|監聽】TNS-12518、TNS-00517和 Linux Error：32：Broken pipe：http://blog.itpub.net/26736162/viewspace-2135468/

 

 

4.1.2  實驗2

下面臨時設定kernel.shmmax為100M，sga_max_size為500M，則至少需要5個共享記憶體段，檢視臨時段的個數：

[root@edsir4p1 ~]# echo 104857600 > /proc/sys/kernel/shmmax [root@edsir4p1 ~]# more /proc/sys/kernel/shmmax 104857600 [root@edsir4p1 ~]# su - oracle [oracle@edsir4p1- ~]$ . PROD1_env [oracle@edsir4p1-PROD1 ~]$ sysresv   IPC Resources for ORACLE_SID "PROD1" : Shared Memory ID              KEY No shared memory segments used<<<==== 無例項的共享記憶體段 Semaphores: ID              KEY 98304           0xa3dda878 Oracle Instance not alive for sid "PROD1" [oracle@edsir4p1-PROD1 ~]$ ipcs   ------ Shared Memory Segments -------- key        shmid      owner      perms      bytes      nattch     status      0x00000000 32768      vncuser   644        790528     2          dest         0x00000000 65537      vncuser   644        790528     2          dest         0x00000000 98306      vncuser   644        790528     2          dest           ------ Semaphore Arrays -------- key        semid      owner      perms      nsems     0xa3dda878 98304      oracle    660        154         ------ Message Queues -------- key        msqid      owner      perms      used-bytes   messages    [oracle@edsir4p1-PROD1 ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 14 10:29:07 2017   Copyright (c) 1982, 2009, Oracle.  All rights reserved.   Connected to an idle instance. SYS@PROD1> startup     ORACLE instance started.   Total System Global Area  313860096 bytes Fixed Size                  1336232 bytes Variable Size             251661400 bytes Database Buffers           54525952 bytes Redo Buffers                6336512 bytes Database mounted. Database opened. SYS@PROD1>  show parameter sga   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ lock_sga                             boolean     FALSE pre_page_sga                         boolean     FALSE sga_max_size                         big integer 500M sga_target                           big integer 300M SYS@PROD1> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@edsir4p1-PROD1 dbs]$ [oracle@edsir4p1-PROD1 ~]$ sysresv   IPC Resources for ORACLE_SID "PROD1" : Shared Memory: ID              KEY 1245194         0x00000000 1277963         0x00000000 1310732         0x00000000 1343501         0x00000000 1376270         0x00000000 1409039         0x90c3be20 Semaphores: ID              KEY 917504          0xa3dda878 Oracle Instance alive for sid "PROD1" [oracle@edsir4p1-PROD1 ~]$ ipcs   ------ Shared Memory Segments -------- key        shmid      owner      perms      bytes      nattch     status      0x00000000 32768      vncuser   644        790528     2          dest         0x00000000 65537      vncuser   644        790528     2          dest         0x00000000 98306      vncuser   644        790528     2          dest         0x00000000 1245194    oracle    660        8388608    30                      <<<==== 該共享記憶體段為8M 0x00000000 1277963    oracle    660        104857600  30                      0x00000000 1310732    oracle    660        104857600  30                      0x00000000 1343501    oracle    660        104857600  30                      0x00000000 1376270    oracle    660        104857600  30                      0x90c3be20 1409039    oracle    660        100663296  30                      <<<==== 每個共享記憶體段為100M   ------ Semaphore Arrays -------- key        semid      owner      perms      nsems     0xa3dda878 917504     oracle    660        154         ------ Message Queues -------- key        msqid      owner      perms      used-bytes   messages

 

下面臨時設定kernel.shmmax為2G，sga_max_size為500M，則只需要1個共享記憶體段，檢視臨時段的個數：

[oracle@edsir4p1-PROD1 ~]$ ss   SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 14 10:49:21 2017   Copyright (c) 1982, 2009, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SYS@PROD1> select 2*1024*1024*1024 from dual;   2*1024*1024*1024 ----------------       2147483648   SYS@PROD1> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@edsir4p1-PROD1 ~]$ sudo echo 2147483648 > /proc/sys/kernel/shmmax -bash: /proc/sys/kernel/shmmax: Permission denied [oracle@edsir4p1-PROD1 ~]$ su - root Password: [root@edsir4p1 ~]# echo 2147483648 > /proc/sys/kernel/shmmax [root@edsir4p1 ~]# exit logout [oracle@edsir4p1-PROD1 ~]$ ipcs -m   ------ Shared Memory Segments -------- key        shmid      owner      perms      bytes      nattch     status      0x00000000 32768      vncuser   644        790528     2          dest         0x00000000 65537      vncuser   644        790528     2          dest         0x00000000 98306      vncuser   644        790528     2          dest         0x00000000 1245194    oracle    660        8388608    30                      0x00000000 1277963    oracle    660        104857600  30                      0x00000000 1310732    oracle    660        104857600  30                      0x00000000 1343501    oracle    660        104857600  30                      0x00000000 1376270    oracle    660        104857600  30                      0x90c3be20 1409039    oracle    660        100663296  30                      <<<==== 需要重啟資料庫，重新分配共享記憶體段   [oracle@edsir4p1-PROD1 ~]$ ss   SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 14 10:50:23 2017   Copyright (c) 1982, 2009, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SYS@PROD1> startup force ORACLE instance started.   Total System Global Area  523108352 bytes Fixed Size                  1337632 bytes Variable Size             343934688 bytes Database Buffers          171966464 bytes Redo Buffers                5869568 bytes Database mounted. Database opened. SYS@PROD1> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@edsir4p1-PROD1 ~]$ sysresv   IPC Resources for ORACLE_SID "PROD1" : Shared Memory: ID              KEY 1474570         0x90c3be20 Semaphores: ID              KEY 1081344         0xa3dda878 Oracle Instance alive for sid "PROD1" [oracle@edsir4p1-PROD1 ~]$ ipcs   ------ Shared Memory Segments -------- key        shmid      owner      perms      bytes      nattch     status      0x00000000 32768      vncuser   644        790528     2          dest         0x00000000 65537      vncuser   644        790528     2          dest         0x00000000 98306      vncuser   644        790528     2          dest         0x90c3be20 1474570    oracle    660        528482304  31                      <<<====共享記憶體段為500M   ------ Semaphore Arrays -------- key        semid      owner      perms      nsems     0xa3dda878 1081344    oracle    660        154         ------ Message Queues -------- key        msqid      owner      perms      used-bytes   messages

 

4.2  kernel.shmall

該引數設定過小，有可能導致資料庫啟動報錯。很多人調整系統核心引數的時候只關注SHMMAX引數，而忽略了SHMALL引數的設定。

[root@edsir4p1 ~]# echo 10 > /proc/sys/kernel/shmall [root@edsir4p1 ~]# [root@edsir4p1 ~]# [root@edsir4p1 ~]# [root@edsir4p1 ~]# [root@edsir4p1 ~]# more /proc/sys/kernel/shmall 10 [oracle@edsir4p1-PROD1 ~]$ ss   SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 14 11:13:53 2017   Copyright (c) 1982, 2009, Oracle.  All rights reserved.   Connected to an idle instance.   SYS@PROD1> startup ORA-27102: out of memory Linux Error: 28: No space left on device SYS@PROD1>

 

 

 

4.3  其它部落格內容

4.3.1  原文地址：ORACLE核心引數 作者：it_newbalance

伺服器記憶體為4G的情況下 

修改/etc/sysctl.conf檔案 (ROOT賬戶) 

 

kernel.shmmax = 2147483648 

//公式:2G*1024*1024*1024=2147483648(位元組) 

//表示最大共享記憶體，如果小的話可以按實際情況而定，一般為實體記憶體的一半（單位:位元組） 

 

kernel.shmmni=4096 

//表示最小共享記憶體固定4096KB（由於32位作業系統預設一頁為4K） 

 

kernel.shmall=1048576 

//公式:4G*1024*1024/4K = 1048576(頁) 

//表示所有記憶體大小（單位：頁） 

 

kernel.sem=250 32000 100 128 

//4個引數依次是SEMMSL：每個使用者擁有訊號量最大數，SEMMNS：系統訊號量最大數，SEMOPM：每次semopm系統呼叫運算元，SEMMNI：系統辛苦量集數最大數。這4個引數為固定內容大小 

 

fs.file-max=65536 

//file-max固定大小65536 

 

net.ipv4.ip_local_port_range=1024 65000 

//ip_local_port_range表示埠的範圍，為指定的內容 

 

以上步驟做完執行 /sbin/sysctl -p 使核心生效 

 

驗證引數（root賬戶執行）： 

#/sbin/sysctl -a | grep shm 

#/sbin/sysctl -a | grep sem 

#/sbin/sysctl -a | grep file-max 

#/sbin/sysctl -a | grep ip_local_port_range

 

最近解決了一些這方面的問題，並在網路上查詢了一些相關資料終於發現一個比較全面解釋這類問題的官方文件。本來打算當一次活雷鋒全文翻譯的，後來考慮自己英文一般，並且對於其中一些OS相關的知識也沒有深入瞭解。就保留英文大家自己去領會其中的要領，自己簡單總結了一下解決這類問題的關鍵點並整理一下英文原文。這個文件是oracle官方技術支援網站Metalink的資料，裡面引用了一些其它的文件例如NOTE:115235.1 。

    對於unix作業系統中Semaphores問題只是針對和oracle相關問題作一些解釋。對於訊號量和共享記憶體段引數在不同的系統中可能有不同的引數對應，具體你去查詢對應的OS文件。

在解決這類問題的時候我發現大部分問題都是因為在安裝oracle時沒有仔細閱讀針對指定OS的安裝說明造成安裝例項失敗，一般oracle的官方文件都詳細說明在對應作業系統上如何設定這些核心引數。還有就是因為其他原因OS管理人員調整了引數，但是沒有通知DBA，一旦oracle崩潰再次重新啟動的時候就可能因為新的核心引數不合適而無法啟動。 如果是oracle意外停機之後重新啟動不成功，並出現類似ora-27123的錯誤那麼一定要詢問是否有其他人修改過核心引數，有時候你沒有修改並不代表其他人沒有修改喲，我遇到過不少這樣的情況！

 

 

 

1、與oracle相關的訊號量和共享記憶體段引數

   一般unix系統中和訊號量相關的是三個引數SEMMNI SEMMSL SEMMNS。他們相互關聯決定系統可以分配的訊號量。Oracle使用訊號量完成內部程式之間的通訊。

   關於共享記憶體段使用shmmx引數進行總體控制。它指定了系統可以分配的共享記憶體段最大大小，實際並沒有分配那麼多隻是給出一個可以使用的最大限制。

   對於類核引數的修改必須要重新啟動系統之後才會生效。

2、出現訊號量和共享記憶體段相關問題的情況

  oracle只有在startup nomount的時候才會請求os的這些資源，用於建立SGA和啟動後臺程式。

   有些情況下因為oracle崩潰之後os沒有清除oracle分配的SGA，也可能造成共享記憶體段不足，需要人工清除。

3、如何解決相關的問題

   你可以簡單的修改init引數減少oracle對共享記憶體段和訊號量的需求。

   對於控制訊號量的三個引數SEMMNI SEMMSL SEMMNS 。最終可以使用的訊號量由下面公式 提取 (semmsl * semmni) 或者 semmns中最小的值。

    例如在linux下. 進入目錄/proc/sys/kernel；用cat命令或more命令檢視semaphore當前引數的值： 

cat sem 

命令執行後將會出現如下的結果： 

250 32000 32 128 

其中, 250 是引數SEMMSL的值,32000是引數SEMMNS的值, 32是引數SEMOPM的值，而128則是引數SEMMNI的值。250*128=32000

對於oracle7需要訊號量的設定等於init中processes的設定。對於8i 9i需要等於processes*2。

對於訊號量引數的設定一定要小心，因為不正確的設定可能會讓系統使用預設值。這個值一般比oracle系統要求的低。在HP unix上遇到過這樣的問題，當時在引數配置的時候指定兩個不同的sem-mni造成系統使用預設的設定。

對於共享記憶體段，系統的設定至少要等於SGA的大小。

 

Semaphores and Shared Memory

 

BULLETIN Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 05-AUG-2001

Last Revision Date: 05-AUG-2002

PURPOSE-------

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 or

administering an Oracle Database. It is intended to compliment the semaphore and

shared memory information already provided in the Oracle Installation Guides.

 

關於訊號量和共享記憶體段的背景知識

----------------------------------------------------------------------------------

Semaphores and shared memory are two very distinct sets of Operating System

resources. Semaphores are a system resource that Oracle utilizes for interprocess

communication 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 controlled

by the configuration of the operating system kernel('kernel' referring to the

centralized core components of the underlying operating system).

 

There are three OS kernel parameters that work together to limit semaphore

allocation and one OS kernel paramater that dictates the maximum size of a shared

memory segment.

 

Operating System kernel parameters generally cannot be tuned on the fly. If they

are 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 automatically

allocate a given amount, but will allow up to that given amount to be available

upon request.

 

 

什麼時候訊號量和共享記憶體段問題最有可能發生

----------------------------------------------------------------------------------

 

Both semaphore or shared memory errors appear primarily at instance startup (The

'startup nomount' stage specifically). This is the only time that Oracle tries to

acquire semaphores and shared memory for the instance. Errors related to

semaphores or shared memory rarely appear during normal database operations.

 

The most common circumstance in which these errors occur is during the creation of

a new database.

Sometimes when an Oracle instance crashes, however, it's 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.

 

如何解決訊號量和共享記憶體段問題：

How to resolve semaphore and shared memory errors:

----------------------------------------------------------------------------------

In addressing both semaphore and shared memory errors at instance startup, there

are 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.ora

parameters, you will have to modify the operating system kernel to allow the OS to

provide more semaphores or allow larger shared memory segments.

 

SEMAPHORES

================================================== ================================

IMPORTANT NOTE: ORACLE DOES NOT UTILIZE SEMAPHORES ON AIX OR DIGITAL/TRU64.

 

與訊號量相關的的ORA錯誤

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 instance

VERY 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 instance

 

If you want a very specific explanation of causes for the above errors, refer to:

[NOTE:15566.1] TECH Unix Semaphores and Shared Memory Explained

 

However, while their exact cause varies, all these error messages indicate that

your 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 Semaphores

2. Understand How Many Semaphores Your Oracle Instance(s) Will Attempt to Grab

From The Operating System.

3. Configure Your OS Kernel To Accomodate all Your Oracle Instance(s) And also

Allow 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 or

set.(Semaphore List)

 

Ultimately however, the OS can limit the total number of semaphores available

from the OS. Controlled by:

SEMMNS --> The total Number of Semaphores allowed system wide.

 

For instance: Let's say SEMMNI = 100000000 and SEMMSL= 100000000 while SEMMNS=10

Even 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: Let's 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 Parameter

SEMMNI, 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 the

setting the 'processes' parameter in the init.ora for the instance.

 

With Oracle8, Oracle8i and Oracle9i: The number of semaphores required by an

instance 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 internal

use.

[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. The

popular 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 kernel

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

Oracle's 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 the

number of semaphores or the amount of shared memory Oracle will try to grab at

instance startup.

Keep in mind, with Oracle8, we grab 2 X 'processes' then release half. This measure

was 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 your

Operating 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 made

while editing these files, the OS will defer to a default value which is usually to low to accomodate Oracle. So it's a good idea to check runtime values with utilities like '/etc/sysdef'.

 

 

I've 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 your

Operating 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 semm

If 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系統上

進入目錄/proc/sys/kernel；用cat命令或more命令檢視semaphore當前引數的值： 

cat sem 

命令執行後將會出現如下的結果： 

250 32000 32 128 

其中, 250 是引數SEMMSL的值,32000是引數SEMMNS的值, 32是引數SEMOPM的值，而128則是引數SEMMNI的值。250*128=32000

 

如何獲得當前正在使用的訊號量

How can I determine how many semaphores are currently being utilized?

----------------------------------------------------------------------------------

On most Unix systems, current semaphore allocation can be displayed with the OS

command 'ipcs -s'. 

% ipcs -s

While good to know, this command is seldom used as part of troubleshooting semaphore errors.

 

 

SHARED MEMORY

==================================================

OS如何分配共享記憶體段

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 kernel

parameter 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 application's request can be fulfilled with one shared memory segment.

 

SHMMAX和SGA的關係

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 Administrator's Reference" available on the Oracle Install CD

 

Additionallly:

 

HP-UX: [NOTE:77310.1] HP-UX Large SGA support for HP, Memory Windows

[NOTE:69119.1] HP-UX SGA Sizing Issues on HP-UX 

Solaris: [NOTE:61896.1] SOLARIS: SGA size, sgabeg attach address and Sun

與共享記憶體當相關的錯誤

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-7334

 

VERY 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 segment"

 

[NOTE:115753.1] UNIX Resolving the ORA-27123 error

 [NOTE:1028623.6] SUN SOLARIS: HOW TO RELOCATE THE SGA

如何設定SHMMAX

What should I set 'shmmax' to?

----------------------------------------------------------------------------------

 

On some Unix platforms, the Install Guide recommends specific values. Previous

versions of the Install Guide recommended setting SHMMAX to .5 *(physical memory present in machine). Most recently it's 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 that SHMMAX is a high water mark, meaning that the OS will attempt to allow up to that amount for an application.

解決缺少共享記憶體段的問題

Quick fix for resolving lack of shared memory errors:

-----------------------------------------------------------------------------------

 

NOTE: If you have never configured your OS kernel for shared memory, you cannot employ this 'Quick Fix'. You will have to first configure the OS kernel. The amount of shared memory Oracle requests is roughly equal to the size of the SGA. The first and most simple fix is to modify the init.ora to reduce the amount of shared memory Oracle will try to grab at instance startup.

 

This document lists the init.ora parameters that contribute to the size

of the SGA:

 

[NOTE:1008866.6] HOW TO DETERMINE SGA SIZE (8.0, 8i, 7.x)

 

oracle崩潰之後重新啟動失敗的問題

My instance crashed. When I try to restart it, I receive errors related to shared

memory. What should I do?

-----------------------------------------------------------------------------------

This may indicate that the shared memory segment associated with the SGA of the crashed instance is still in memory. In this case it may be appropriate to manually remove the segment using OS commands.

 

THIS PROCESS SHOULD NOT BE ATTEMPTED UNLESS YOU FULLY UNDERSTAND THE CONCEPTS BEHIND IT!!!

 

The basic steps are:

1. Identify the shared memory segment that is 'stuck' in memory.

2. Remove the 'stuck' shared memory segment using the OS command 'ipcrm'.

 

[NOTE:68281.1] DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS

[NOTE:69642.1] also describes this process - Step 9.

[NOTE:123322.1] SYSRESV UTILITY: This note describes the new 8i 'sysresv' utility that can be used on Solaris to associate a given ORACLE_SID with it's shared memory segment(s). .

 

4.3.2  Oracle 效能最佳化之核心的shmall 和shmmax 引數

1. 核心的 shmall 和 shmmax 引數

 

SHMMAX= 配置了最大的記憶體segment的大小 ——>這個設定的比SGA_MAX_SIZE大比較好。

 

SHMMAX引數：Linux程式可以分配的單獨共享記憶體段的最大值。一般設定為記憶體總大小的一半。這個值的設定應該大於SGA_MAX_TARGET或MEMORY_MAX_TARGET的值，因此對於安裝Oracle資料庫的系統，shmmax的值應該比記憶體的二分之一大一些。

SHMMIN= 最小的記憶體segment的大小 。

 

SHMMNI= 整個系統的記憶體segment的總個數 。設定系統級最大共享記憶體段數量。Oracle10g推薦最小值為4096，可以適當比4096增加一些。

 

SHMSEG= 每個程式可以使用的記憶體segment的最大個數

 

shmall=是全部允許使用的共享記憶體大小，shmmax 是單個段允許使用的大小。這兩個可以設定為記憶體的 90%。例如 16G 記憶體，16*1024*1024*1024*90% = 15461882265，shmall 的大小為 15461882265/4k(getconf PAGESIZE可得到) = 3774873。

 

shmall設定共享記憶體總頁數。這個值太小有可能導致資料庫啟動報錯。很多人調整系統核心引數的時候只關注SHMMAX引數，而忽略了SHMALL引數的設定。

－

 

2.配置訊號燈（ semphore ）的引數

 

訊號燈semaphores是程式或執行緒間訪問共享記憶體時提供同步的計數器。

SEMMSL= 設定每個訊號燈組中訊號燈最大數量，推薦的最小值是250。對於系統中存在大量併發連線的系統，推薦將這個值設定為PROCESSES初始化引數加10。

 

SEMMNI= 設定系統中訊號燈組的最大數量。Oracle10g和11g的推薦值為142。

 

SEMMNS=設定系統中訊號燈的最大數量。作業系統在分配訊號燈時不會超過LEAST(SEMMNS,SEMMSL*SEMMNI)。事實上，如果SEMMNS的值超過了SEMMSL*SEMMNI是非法的，因此推薦SEMMNS的值就設定為SEMMSL*SEMMNI。Oracle推薦SEMMNS的設定不小於32000，假如資料庫的PROCESSES引數設定為600，則SEMMNS的設定應為：

 

SQL> select (600+10)*142 from dual;

 

(600+10)*142

------------

      86620

1

2

3

4

5

SEMOPM引數：設定每次系統呼叫可以同時執行的最大訊號燈操作的數量。由於一個訊號燈組最多擁有SEMMSL個訊號燈，因此有推薦將SEMOPM設定為SEMMSL的值。Oracle驗證的10.2和11.1的SEMOPM的配置為100。

 

透過下面的命令可以檢查訊號燈相關配置：

 

# cat /proc/sys/kernel/sem

250 32000 100 128

1

2

對應的4個值從左到右分別為SEMMSL、SEMMNS、SEMOPM和SEMMNI

 

－

 

3.修改 /etc/sysctl.conf

 

kernel.shmmax=15461882265

kernel.shmall=3774873

kernel.msgmax=65535

kernel.msgmnb=65535

 

執行 sudo sysctl -p

 

可以使用 ipcs -l 看結果,ipcs -u 可以看到實際使用的情況

 

-------------------------------------------------------------------------

---

---

About Me

............................................................................................................................................. ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2147273/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest/p/7838547.html ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-11-01 09:00 ~ 2017-11-30 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面寶典》讀者群       小麥苗的微店 .............................................................................................................................................