Hello Guys,<\/p>\n\n\n
Let’s investigate this issue on Exadata At Customer – or Exadata Machine:<\/p>\n\n\n
[oracle@tech01 ~]$ sqlplus \/ as sysdba\nSQL*Plus: Release 19.0.0.0.0 - Production on Mon May 18 16:15:50 2020\nVersion 19.7.0.0.0\nCopyright (c) 1982, 2020, Oracle. All rights reserved.\nConnected to an idle instance.\nSQL> startup nomount pfile='\/home\/oracle\/init.ora';\nORA-27154: post\/wait create failed\nORA-27300: OS system dependent operation:semget failed with status: 28\nORA-27301: OS failure message: No space left on device\nORA-27302: failure occurred at: sskgpbitsper\nSQL> quit\nDisconnected\n<\/code><\/pre>\n\n\nThe solutions is:<\/p>\n\n\n
Reduce parameters processes, sessions, transactions and open_cursors for all databases. Follow the maximum used on each database to avoid to use much more than the database needs – and yes put some extra value since your database can grow. Update sysctl.conf parameters for your linux accommodate all databases. See the steps below: <\/p>\n\n\n
=================================================================11) Check results for max limits on database used\n=================================================================\ncolumn rname\tformat a30 \t heading 'Resource Name';\ncolumn curu\tformat 999,999,990\theading 'Current|Utilization' just c;\ncolumn maxu\tformat 999,999,990\theading 'Maximum|Utilization' just c;\ncolumn inita format a10 heading 'Initial|Allocation' just c;\ncolumn lim format a10 heading 'Limit' just r;\nset lines 200\nselect inst_id, resource_name rname\n , current_utilization curu\n , max_utilization maxu\n , initial_allocation inita\n , limit_value lim\nfrom gv$resource_limit ;\nselect inst_id, resource_name rname\n , current_utilization curu\n , max_utilization maxu\n , initial_allocation inita\n , limit_value lim\nfrom gv$resource_limit\nWHERE resource_name in ('processes','sessions');\nSQL> set lines 200\nSQL> SQL> select inst_id, resource_name rname\n 2 , current_utilization curu\n 3 , max_utilization maxu\n 4 , initial_allocation inita\n 5 , limit_value lim\n 6 from gv$resource_limit\n 7\nSQL> \/\n Current Maximum Initial\n INST_ID Resource Name Utilization Utilization Allocation Limit\n---------- ------------------------------ ------------ ------------ ---------- ----------\n 1 processes 165 186 2048 2048\n 1 sessions 170 208 3100 3100\n 1 enqueue_locks 76 98 36542 36542\n 1 enqueue_resources 62 87 14684 UNLIMITED\n 1 ges_procs 153 173 2051 2051\n 1 ges_ress 11,663 87,988 69331 UNLIMITED\n 1 ges_locks 343 547 99571 UNLIMITED\n 1 ges_cache_ress 12,072 16,162 0 UNLIMITED\n 1 ges_reg_msgs 176 4,119 6126 UNLIMITED\n 1 ges_big_msgs 39 360 3826 UNLIMITED\n 0 0 1000 1000\n 1 gcs_resources 47,386 47,460 810054 UNLIMITED\n 1 gcs_shadows 33,188 34,349 810054 UNLIMITED\n 1 smartio_overhead_memory 855,160 1,026,192 0 UNLIMITED\n 1 smartio_buffer_memory 343,416 1,486,280 0 UNLIMITED\n 1 smartio_metadata_memory 0 56 0 UNLIMITED\n 1 smartio_sessions 3 4 0 UNLIMITED\n 1 dml_locks 0 21 13640 UNLIMITED\n 1 temporary_table_locks 0 5 UNLIMITED UNLIMITED\n 1 transactions 0 5 3410 UNLIMITED\n.....\n108 rows selected.\nSQL>\n<\/code><\/pre>\n\n\nOracle suggested:<\/p>\n\n\n
SEMMNI should be increased to accomodate more semaphores.\n1. Query the current semaphore values in the kernel\n # \/sbin\/sysctl -a | grep sem\n2. Modify SEMMNI value in the \/etc\/sysctl.conf.\nFrom\nkernel.sem = 250 32000 100 128\nTo\nkernel.sem = 250 32000 100 200\n3. # \/sbin\/sysctl -p<\/code><\/pre>\n\n\nWhat worked for me:<\/p>\n\n\n
### ANDRE ROCHA<\/p>\n\n\n
# kernel.sem = 1024 60000 1024 256 -> original<\/p>\n\n\n
kernel.sem = 1512 1200000 1512 1024<\/p>\n\n\n
<\/p>\n\n\n
My original recipe:<\/p>\n\n\n
<\/p>\n\n\n
=================================================================\nList of best practices: ANDRE ROCHA\n=================================================================1) Configure huge pages - Doc ID 361323.1 401749.1\n2) Disable Transparent HugePages - Doc ID 1557478.1\n3) Check if system using hugepage have this parameter USE_LARGE_PAGES enabled Doc ID 1392497.1\n4) Check swap is set correct and swapiness - Doc ID 2240180.1 2404462.1 - vm.swappiness=10\n5) Network Considerations - Doc ID 811306.1\n6) Collect data for sar output and print graph to get swap memory results\n7) About async IO, please check on aio.max-nr and fs.aio-nr.\n8) Revise Kernel parameters with best practices\/recommendations\n9) Set\/check kernel parameters can affect SGA size and shared pool\n10) Check results of cluvfy for packages, kernel limits and general check results\n14) Appendice of list of commands to be executed\/get results\nhttps://sarchart.weebly.com\/howto.html\n=================================================================Linux Kernel Lowmem Pressure Issues and Related Kernel Structures (Doc ID 452326.1)\nALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)\nHugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)\nSystem Hangs Due To Process Kswapd0 (Doc ID 2240180.1)\nWhy is SWAP being used instead of available physical memory? (Doc ID 2404462.1)\nhttps:\/\/access.redhat.com\/documentation\/en-us\/red_hat_enterprise_linux\/6\/html\/performance_tuning_guide\/s-memory-tunables\nA low swappiness value is recommended for database workloads. For example, for Oracle databases,\nRed Hat recommends a swappiness value of 10. vm.swappiness=10\nTop 5 Issues That Cause Node Reboots or Evictions or Unexpected Recycle of CRS (Doc ID 1367153.1)\nIf the platform is Linux, set up hugepages and set kernel parameter vm.min_free_kbytes to reserve 512MB.\nSetting hugepages is probably the single most important thing to do on Linux. Note that memory_target can not\nbe set when using hugepages.\nALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)\nBecause Transparent HugePages are known to cause unexpected node reboots and performance problems with RAC,\nOracle strongly advises to disable the use of Transparent HugePages. In addition, Transparent Hugepages may\ncause problems even in a single-instance database environment with unexpected performance problems or delays.\nAs such, Oracle recommends disabling Transparent HugePages on all Database servers running Oracle.\nNote: on UEK2 and above, check the existence of the \/sys\/kernel\/mm\/transparent_hugepage\/ directory.\nIf this directory does not exist, then the transparent hugepage is removed from the kernel, so there is no need to\ndisable the transparent hugepage\nFor Oracle Linux 7, Oracle published MOS document 2066217.1\nOracle Linux 7 - How to disable Transparent HugePages for RHCK kernel?\nAlso, add the following line to \/etc\/default\/grub file:\ntransparent_hugepage=never\ncat \/etc\/grub.conf\ntitle Oracle Linux Server (2.6.32-300.25.1.el6uek.x86_64)\n root (hd0,0)\n kernel \/vmlinuz-2.6.32-300.25.1.el6uek.x86_64 ro root=LABEL=\/ transparent_hugepage=never\n initrd \/initramfs-2.6.32-300.25.1.el6uek.x86_64.img\n=================================================================1) Configure huge pages - Doc ID 361323.1\n=================================================================https:\/\/oracle-base.com\/articles\/linux\/configuring-huge-pages-for-oracle-on-linux-64\nImplement HugePages for the database instances. HugePages provides a method to have larger page sizes and it will\nlock the SGA into physical memory thus eliminating the need for system page table lookups for the SGA.\nThis is especially important on systems with high memory allocations due to eliminate the management overhead\nassociated with the such configurations. HugePages is also recommended in environments where server stability\nissues are evident \u2013 various spins\/hangs, which are not attributed to Oracle Clusterware or other known OS issues.\nTo compute size, see Document 401749.1. For more information, see Document 361323.1. In 11gR1 and above, you must disable\nAMM to use HugePages as directed in Document 749851.1.\n$ grep Huge \/proc\/meminfo\nAnonHugePages: 0 kB\nHugePages_Total: 0\nHugePages_Free: 0\nHugePages_Rsvd: 0\nHugePages_Surp: 0\nHugepagesize: 2048 kB\n$\nExecute hugepages_setting script:\n$ .\/hugepages_setting.sh\nRecommended setting: vm.nr_hugepages = 305\n$\nEdit file and add the result for the script hugepages_settings.sh: vm.nr_hugepages\n\/etc\/sysctl\nRun the following command as the \"root\" user.\n# sysctl -p\nTo be permanet, edit the \"\/etc\/grub.conf\" file, adding \"hugepages=306\" to the end of the kernel\nline for the default kernel and reboot.\nYou can now see the HugePages have been created, but are currently not being used.\nCheck the HugePages information again.\n$ grep Huge \/proc\/meminfo\nAnonHugePages: 0 kB\nHugePages_Total: 306\nHugePages_Free: 98\nHugePages_Rsvd: 93\nHugePages_Surp:\nHugepagesize: 2048 kB\n$\n$\nIf you prefer, you can set these parameters to a value just below the size of physical memory of\nthe server. This way you can forget about it, unless you add more physical memory.\nUpdate Oracle database\nALTER SYSTEM SET use_large_pages=only SCOPE=SPFILE;\nSHUTDOWN IMMEDIATE;\nSTARTUP;\n=================================================================2) Setting Shell Limits for the Oracle User\n=================================================================\nhttps:\/\/docs.oracle.com\/cd\/E51773_01\/doc.12105\/e51150\/dbinstall.htm\nTo improve the performance of the software, you must increase the shell limits for the oracle user.\nAdd the following lines to the \/etc\/security\/limits.conf file:\noracle soft nproc 2047\noracle hard nproc 16384\noracle soft nofile 1024\noracle hard nofile 65536\nIf not already present, add the following lines to the \/etc\/pam.d\/login file:\nsession required \/lib64\/security\/pam_limits.so\nsession required pam_limits.so\nDepending on the oracle user's default shell, you need to make changes to it. For the Bourne, Bash, or Korn shell, add the following lines to the bottom of the \/etc\/profile file:\nif [ $USER = \"oracle\" ]; then\n if [ $SHELL = \"\/bin\/ksh\" ]; then\n ulimit -p 16384\n ulimit -n 65536\n else\n ulimit -u 16384 -n 65536\n fi\nfi\nAdd the following entries into the \"\/etc\/security\/limits.conf\" script or\n\"\/etc\/security\/limits.d\/99-grid-oracle-limits.conf\" script, where the setting is at least the size\nof the HugePages allocation in KB (HugePages * Hugepagesize).\nIn this case the value is 306*2048=626688.\n* soft memlock 626688\n* hard memlock 626688\n=================================================================2) Disable Transparent HugePages - Doc ID 1557478.1\n=================================================================\nIf you are running RedHat\/OEL 6\/7, SLES 11\/12 or UEK2 kernels, be sure to disable Transparent HugePages to prevent\nperformance problems and Node\/Instance evictions. Our long standing recomendation of using general HugePages still stands\n(THP is a different implementation than general HugePages). See Document 1557478.1 for additional details.\nYou can check the current setting using the following command, which is displaying the default value of \"enabled=[always]\".\n# cat \/sys\/kernel\/mm\/transparent_hugepage\/enabled\n[always] madvise never\nFor Oracle Linux 6 the preferred method to disable Transparent HugePages is to add \"transparent_hugepage=never\" to the kernel boot line in the \"\/boot\/grub\/grub.conf\" file.\ntitle Oracle Linux Server (2.6.39-400.24.1.el6uek.x86_64)\n root (hd0,0)\n kernel \/vmlinuz-2.6.39-400.24.1.el6uek.x86_64 ro root=\/dev\/mapper\/vg_ol6112-lv_root rd_NO_LUKS KEYBOARDTYPE=pc KEYTABLE=uk\nLANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 rd_NO_DM rd_LVM_LV=vg_ol6112\/lv_swap rd_LVM_LV=vg_ol6112\/lv_root rhgb quiet numa=off\ntransparent_hugepage=never\n initrd \/initramfs-2.6.39-400.24.1.el6uek.x86_64.img\nThe server must be rebooted for this to take effect.\nAlternatively, add the following lines into the \"\/etc\/rc.local\" file and reboot the server.\nif test -f \/sys\/kernel\/mm\/transparent_hugepage\/enabled; then\n echo never > \/sys\/kernel\/mm\/transparent_hugepage\/enabled\nfi\nif test -f \/sys\/kernel\/mm\/transparent_hugepage\/defrag; then\n echo never > \/sys\/kernel\/mm\/transparent_hugepage\/defrag\nfi\nWhichever method you choose, remember to check the change has work after reboot.\n# cat \/sys\/kernel\/mm\/transparent_hugepage\/enabled\nalways madvise [never]\n#\n=================================================================3) Check if system using hugepage have this parameter USE_LARGE_PAGES enabled Doc ID 1392497.1\n=================================================================show parameter USE_LARGE_PAGES\n=================================================================4) Check swap is set correct and swapiness - Doc ID 2240180.1 2404462.1 - vm.swappiness=10\n=================================================================\nFree -h\nTake a backup of the current sysctl.conf file to be used in case of rollback\ncp \/etc\/sysctl.conf \/root\/sysctl.conf_TICKETNUMBER\nReview current swappiness configuration\ncat \/proc\/sys\/vm\/swappiness\n60\nChange the current in-memory setting for vm.swappiness\necho 10 > \/proc\/sys\/vm\/swappiness\nMaking Permanent the mv.swappiness\n# echo 'vm.swappiness=10' >> \/etc\/sysctl.conf\nConfirm the new swappiness value\n # cat \/proc\/sys\/vm\/swappiness\n---Result 10.\n=================================================================5) Network Considerations - Doc ID 811306.1\n=================================================================\nRAC and Oracle Clusterware Best Practices and Starter Kit (Linux) (Doc ID 811306.1)\nAs workload dictates, rmem_max and wmem_max kernel parameters should be increased beyond the default 256kb. These values determine how much kernel buffer memory is allocated per socket opened for network reads and writes:\nnet.core.rmem_default=262144\nnet.core.rmem_max=4194304 (for 11g and all RDS implementations)\nnet.core.rmem_max=2097152 (for 10g)\nnet.core.wmem_default=262144\nnet.core.wmem_max=1048576 (with RDS use at least 2097152)\nChanging Kernel Parameter Values - sysctl.conf for 19c\nhttps:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/ladbi\/changing-kernel-parameter-values.html#GUID-FB0CC366-61C9-4AA2-9BE7-233EB6810A31\nfs.aio-max-nr = 1048576\nfs.file-max = 6815744\nkernel.shmall = 2097152\nkernel.shmmax = 4294967295\nkernel.shmmni = 4096\nkernel.sem = 250 32000 100 128\nnet.ipv4.ip_local_port_range = 9000 65500\nnet.core.rmem_default = 262144\nnet.core.rmem_max = 4194304\nnet.core.wmem_default = 262144\nnet.core.wmem_max = 1048576\n=================================================================6) Collect data for sar output\n=================================================================\n### All Days available on SAR\nDT=$(ls \/var\/log\/sa\/sa[0-9][0-9] | tr '\\n' ' ' | sed 's\/\\\/var\\\/log\\\/sa\\\/sa\/ \/g')\n>\/tmp\/sar-$(hostname)-multiple.txt\nfor i in $DT; do\nLC_ALL=C sar -A -f \/var\/log\/sa\/sa$i >> \/tmp\/sar-$(hostname)-multiple.txt\ndone\nls -l \/tmp\/sar-$(hostname)-multiple.txt\n=================================================================7) About async IO, please check on aio.max-nr and fs.aio-nr\n=================================================================\nWhat value should kernel parameter AIO-MAX-NR be set to ? (Doc ID 2229798.1)\nThe minimum value suggested as per http:\/\/docs.oracle.com\/database\/121\/LTDQI\/toc.htm#BABFIFCGfor aio-max-nr is 1048576 .\nthe DOCS provide the minimum required for a good install and basic usage.\nThis is how we calculate aio-max-nr parameter value -\nshow parameter processes\naio-max-nr =no of process per DB * no of databases * 4096\nAfter deriving the value using this formula , you should still monitor the \/proc\/sys\/fs\/aio-nr value and also monitor I\/O\nloading.\nPoints to know -\n1) Number of processes per database is not background server process number, it's the number of background server\nprocess + user process.\n2) Parameter aio-max-nr is a limitation of aio-nr, no impact was reported if you set it too high. But we should still\nmonitor \/proc\/sys\/fs\/aio-nr value and tune this value.\ncat \/proc\/sys\/fs\/aio-nr\n=================================================================8) Revise Kernel parameters with best practices\/recommendations\n=================================================================https:\/\/docs.oracle.com\/database\/122\/LADBI\/app_manual.htm#LADBI7865\nhttps:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/cwlin\/changing-kernel-parameter-values.html#GUID-FB0CC366-61C9-4AA2-9BE7-233EB6810A31\n=================================================================\n9) Set\/check kernel parameters can affect SGA size\nand shared pool\n=================================================================\nshmmax 64G 85% of 16G\nkernel.shmmax 68719476736 kernel.shmmax=15032385536\nneed fit\/affect SGA\nkernel.shmall 4294967296=4G 40% 16G = 6,4G = 6871947673\nneed fit\/affect Shared pool\n=================================================================\n10) Check results of cluvfy for packages, kernel limits and general check results\n=================================================================\nDownload it\nhttps:\/\/www.oracle.com\/database\/technologies\/cvu-downloads.html\n### All Days available on SAR\nDT=$(ls \/var\/log\/sa\/sa[0-9][0-9] | tr '\\n' ' ' | sed 's\/\\\/var\\\/log\\\/sa\\\/sa\/ \/g')\n>\/tmp\/sar-$(hostname)-multiple.txt\nfor i in $DT; do\nLC_ALL=C sar -A -f \/var\/log\/sa\/sa$i >> \/tmp\/sar-$(hostname)-multiple.txt\ndone\nls -l \/tmp\/sar-$(hostname)-multiple.txt\n=================================================================\nGeneral results\/recommendations :\n=================================================================\nKernel: Reduce shmmax to 85% of 16G\nKernel: increase shmall to 40% of 16G\nSet hugepages: vm.nr_hugepages = 964\nSwap is under the recommendation but sounds ok since was not using swap memory on it.\nNext approach to continue to work on it: Investigate sga_target memory settings since\nsga is using only 2G memory entire memory.\nex.\nshmmax 64G 85% of 16G (total memoria)\nkernel.shmmax 68719476736 kernel.shmmax=15032385536\nneed fit\/affect SGA\nkernel.shmall 4294967296=4G 40% 16G = 6,4G = 6871947673\nneed fit\/affect Shared pool (total memoria)\nOracle Exadata Database Machine Setup\/Configuration Best Practices (Doc ID 1274318.1)\ns.file-max = 6815744 512 x processes (for example 6815744 for 13312 processes)\nkernel.shmmax = RAM times 0.5 (or higher at customer's discretion - see Note:567506.1)\nOracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Mac OS X,Solaris,Tru64 Unix Operating Systems\nInstallation and Configuration Requirements Quick Reference (8.0.5 to 11.2) (Doc ID 169706.1)\nUpon startup of Linux database get ORA-27102: out of memory Linux-X86_64 Error: 28:\nNo space left on device (Doc ID 301830.1)\nWhat value should kernel parameter AIO-MAX-NR be set to ? (Doc ID 2229798.1)\nTECH: Unix Semaphores and Shared Memory Explained (Doc ID 15566.1)\nExadata sysctl comments:\nMaximum shared memory allowed is 85 percent of physical RAM\nMaximum shared memory pages allowed are 85 percent of physical RAM divided by page size\nkernel.sem = 1024 60000 1024 256\nCheck processes -> used to calculate aio-max-nr\nshow parameter memor\ngrep SwapTotal \/proc\/meminfo\n=================================================================\n11) Check results for max limits on database used\n=================================================================\ncolumn rname\tformat a30 \t heading 'Resource Name';\ncolumn curu\t format 999,999,990\theading 'Current|Utilization' just c;\ncolumn maxu\t format 999,999,990\theading 'Maximum|Utilization' just c;\ncolumn inita format a10 heading 'Initial|Allocation' just c;\ncolumn lim format a10 heading 'Limit' just r;\ncolumn inst_id format a10 heading 'Limit' just r;\nset lines 200\nselect inst_id, resource_name rname\n , current_utilization curu\n , max_utilization maxu\n , initial_allocation inita\n , limit_value lim\nfrom gv$resource_limit ;\nselect inst_id, resource_name rname\n , current_utilization curu\n , max_utilization maxu\n , initial_allocation inita\n , limit_value lim\nfrom gv$resource_limit\nWHERE resource_name in ('processes','sessions');\n=================================================================\n12) Check results for swap and compare with total of memory\n=================================================================\nRequirements for Installing Oracle Database 12.1 on RHEL5 or OL5 64-bit (x86-64) (Doc ID 1529433.1)\nb.) Swap disk space proportional to the system's physical memory as follows:\nRAM\tSwap Space\nBetween 1 GB and 2 GB\t1.5 times the size of RAM\nBetween 2 GB and 16 GB\tEqual to the size of RAM\nMore than 16 GB\t16 GB\nNOTE: The above recommendations (from the Oracle\u00ae Database Installation Guide 12c Release 1\n(12.1) for Linux) are MINIMUM recommendations for installations. Further RAM and swap space may be\nrequired to tune\/improve RDBMS performance.\ngrep SwapTotal \/proc\/meminfo\n=================================================================\nList of commands to be executed\n=================================================================\nSwap check and memory\ngrep SwapTotal \/proc\/meminfo\nfree -m\nuname -r\ncat \/etc\/sysctl.conf\ncat \/etc\/security\/limits.conf\ncat \/sys\/kernel\/mm\/transparent_hugepage\/enabled\ncat \/proc\/sys\/fs\/aio-nr\ncat \/proc\/sys\/vm\/swappiness\ncat \/etc\/default\/grub file:\ncat \/etc\/grub.conf\ncat \/etc\/security\/limits.conf\nshow parameter memory\nshow parameter sga\nshow parameter processes\nshow parameter USE_LARGE_PAGES\nNAME |Current\/Acutal |Suggested\n------------------------------------|------------------------|------------------------------\nHugePage not set vm.nr_hugepages = 964\nshmmax 64G 85% of 16G\nkernel.shmmax 68719476736 kernel.shmmax=15032385536\nkernel.shmall 4294967296=4G 40% 16G = 6,4G = 6871947673\nHugePage not set vm.nr_hugepages = 964\n = 64G\nkernel.shmmax = 68719476736\n### Exadata 202005 - standard\n# kernel.sem = 1024 60000 1024 256\nkernel.sem = 1512 1200000 1512 1024\n <\/code><\/pre>\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
Hello Guys, Let’s investigate this issue on Exadata At Customer – or Exadata Machine: The solutions is: Reduce parameters processes, sessions, transactions and open_cursors for all databases. Follow the maximum used on each database to avoid to use much more … Continue lendo