{"id":1152,"date":"2013-11-11T13:32:14","date_gmt":"2013-11-11T05:32:14","guid":{"rendered":"http:\/\/www.yallonking.com\/?p=1152"},"modified":"2013-11-11T13:32:14","modified_gmt":"2013-11-11T05:32:14","slug":"%e5%85%b3%e4%ba%8e-ora-27102-out-of-memory-%e8%a7%a3%e5%86%b3%e4%b8%80%e4%be%8b","status":"publish","type":"post","link":"http:\/\/www.yallonking.com\/?p=1152","title":{"rendered":"\u5173\u4e8e ORA-27102: out of memory \u89e3\u51b3\u4e00\u4f8b"},"content":{"rendered":"

\u95ee\u9898\u63cf\u8ff0\uff1a\u5bf9\u5185\u5b58\u4e3a24GB\u7684linux 64\u4f4d\u4e4b\u4e0a\u7684oracle11g\u8c03\u6574sga\u5927\u5c0f\u5bfc\u81f4\u9519\u8befORA-27102\uff0c\u5e76\u8fdb\u4e00\u6b65\u9610\u8ff0\u53c2\u6570kernel.shmmax\u548c\u53c2\u6570kernel.shmall\u8bbe\u7f6e\u95ee\u9898\u3002<\/p>\n

\u57fa\u672c\u4fe1\u606f
\nOS:linux x86-64 DB:11.2.0.3<\/strong><\/p>\n

\r\n[oracle@tongjidb ~]$ more \/etc\/fstab \r\n\r\n#\r\n# \/etc\/fstab\r\n# Created by anaconda on Fri Nov  1 22:06:11 2013\r\n#\r\n# Accessible filesystems, by reference, are maintained under '\/dev\/disk'\r\n# See man pages fstab(5), findfs(8), mount(8) and\/or blkid(8) for more info\r\n#\r\nUUID=9cf955b0-f18f-4848-90d0-0605663fbab1 \/                       ext4    defaults        1 1\r\nUUID=7d64dc55-51e1-4f9d-89a4-42cbaf6f229a \/boot                   ext4    defaults        1 2\r\nUUID=427C-4136          \/boot\/efi               vfat    umask=0077,shortname=winnt 0 0\r\nUUID=66b835cf-0b77-4a2d-bba9-55c821d3215c swap                    swap    defaults        0 0\r\ntmpfs                   \/dev\/shm                tmpfs   defaults,size=20G        0 0\r\ndevpts                  \/dev\/pts                devpts  gid=5,mode=620  0 0\r\nsysfs                   \/sys                    sysfs   defaults        0 0\r\nproc                    \/proc                   proc    defaults        0 0\r\n[oracle@tongjidb ~]$ df -h\r\nFilesystem            Size  Used Avail Use% Mounted on\r\n\/dev\/sda4             786G   57G  690G   8% \/\r\ntmpfs                  20G     0   20G   0% \/dev\/shm\r\n\/dev\/sda2             3.9G   98M  3.6G   3% \/boot\r\n\/dev\/sda1             3.9G  260K  3.9G   1% \/boot\/efi\r\ntmpfs                  20G     0   20G   0% \/dev\/shm\r\n[oracle@tongjidb ~]$ free -g\r\n             total       used       free     shared    buffers     cached\r\nMem:            23          2         20          0          0          2\r\n-\/+ buffers\/cache:          0         22\r\nSwap:           29          0         29\r\n\r\n<\/pre>\n
\u5f53\u524dsga\u8bbe\u7f6e\u5982\u4e0b<\/p>\n
\r\n[root@tongjidb ~]# su - oracle\r\n[oracle@tongjidb ~]$ sqlplus "\/as sysdba"\r\n\r\nSQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 11 10:30:58 2013\r\n\r\nCopyright (c) 1982, 2011, Oracle.  All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\n\r\nSQL> sho parameter sga\r\n\r\nNAME                                 TYPE        VALUE\r\n------------------------------------ ----------- ------------------------------\r\nlock_sga                             boolean     FALSE\r\npre_page_sga                         boolean     FALSE\r\nsga_max_size                         big integer 7920M\r\nsga_target                           big integer 7920M\r\nSQL> sho parameter spfile\r\n\r\nNAME                                 TYPE\r\n------------------------------------ ----------------------\r\nVALUE\r\n------------------------------\r\nspfile                               string\r\n\/u01\/app\/oracle\/product\/11.2.0\r\n\/db_1\/dbs\/spfiletongji.ora\r\n<\/pre>\n
\u8c03\u5927sga\u53c2\u6570\u8bbe\u7f6e\uff0c\u91cd\u542f\u6570\u636e\u5e93\u62a5\u9519<\/p>\n
\r\nSQL> alter system set sga_target=16g scope=spfile;\r\n\r\nSystem altered.\r\n\r\nSQL> alter system set sga_max_size=16g scope=spfile;\r\n\r\nSystem altered.\r\n\r\nSQL> shutdown immediate;\r\nORA-01507: database not mounted\r\n\r\n\r\nORACLE instance shut down.\r\nSQL> startup nomount;\r\nORA-27102: out of memory\r\nLinux-x86_64 Error: 28: No space left on device\r\n\r\n<\/pre>\n
\u4fee\u6539\u64cd\u4f5c\u7cfb\u7edf\u53c2\u6570[\/etc\/sysctl.conf]<\/p>\n
\r\n#oracle set\r\nfs.aio-max-nr = 1048576\r\nfs.file-max = 6815744\r\n#kernel.shmall = 2097152\r\nkernel.shmall = 5767168\r\n#kernel.shmmax = 4294967295\r\nkernel.shmmax = 23622320128\r\nkernel.shmmni = 4096\r\nkernel.sem = 250 32000 100 128\r\nnet.ipv4.ip_local_port_range = 9000 65500\r\nnet.core.rmem_default = 262144\r\nnet.core.rmem_max = 4194304\r\nnet.core.wmem_default = 262144\r\nnet.core.wmem_max = 1048586\r\n#oracle set\r\n[root@tongjidb ~]# sysctl -p\r\n<\/pre>\n
\u53c2\u6570\u751f\u6548\u540e\u5c1d\u8bd5\u542f\u52a8\u6570\u636e\u5e93\uff0c\u6b63\u5e38\u3002<\/p>\n
\r\nSQL> startup nomount;\r\nORACLE instance started.\r\n\r\nTotal System Global Area 1.9241E+10 bytes\r\nFixed Size                  2236488 bytes\r\nVariable Size            7247761336 bytes\r\nDatabase Buffers         1.1945E+10 bytes\r\nRedo Buffers               45682688 bytes\r\nSQL> sho parameter sga\r\n\r\nNAME                                 TYPE\r\n------------------------------------ ----------------------\r\nVALUE\r\n------------------------------\r\nlock_sga                             boolean\r\nFALSE\r\npre_page_sga                         boolean\r\nFALSE\r\nsga_max_size                         big integer\r\n18G\r\nsga_target                           big integer\r\n18G\r\n<\/pre>\n
\u53c2\u6570\u8bf4\u660e\u4e0e\u8bbe\u5b9a\uff0864\u4f4dOS\uff09\uff1a<\/strong>
\nkernel.shmmax:
\nControls the maximum shared segment size, in bytes<\/p>\n
kernel.shmall:
\nControls the maximum number of shared memory segments, in pages<\/p>\n
[root@tongjidb ~]# getconf PAGE_SIZE
\n4096<\/p>\n
kernel.shmmax \u4e00\u822c\u4e3a\u7269\u7406\u5185\u5b58\u7684\u4e00\u534a\uff0c\u6700\u5927\u4e3a\u7269\u7406\u5185\u5b58-2GB<\/p>\n
\u6211\u4eec\u8bbe\u7f6e\u7684\u53c2\u6570
\nkernel.shmmax \u503c\u4e3a\uff1a24GB-2GB=22GB=22*1024*1024*1024=23622320128
\nkernel.shmall \u503c\u4e3a\uff1akernel.shmmax\/PAGE_SIZE=23622320128\/4096=5767168<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
\u95ee\u9898\u63cf\u8ff0\uff1a\u5bf9\u5185\u5b58\u4e3a24GB\u7684linux 64\u4f4d\u4e4b\u4e0a\u7684oracle11g\u8c03\u6574sga\u5927\u5c0f\u5bfc\u81f4\u9519\u8befORA-27102 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[36],"tags":[246,244,243,242,245],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1152"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1152"}],"version-history":[{"count":1,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1152\/revisions"}],"predecessor-version":[{"id":1153,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/1152\/revisions\/1153"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1152"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}