Info

SQL> show parameter sea
SQL> show parameter target

SQL>
column name format a31;
select name, bytes/1024/1024 Mb from v$sgainfo;
select * from v$sgainfo;

$ free -m

 

Disclaimer: Values of the memory parameters shown on this page are examples, you must use your own parameters

 

!!! IMPORTANT !!!

If the database will not start after our changes, we will have a chance to start it using pfile. Create backup:

SQL> create pfile='/tmp/pfile.txt' from spfile;

RMAN> backup current controlfile;

SQL> alter system set sga_max_size=150G scope=spfile;
SQL> alter system set sga_target=150G scope=spfile;

SQL> show parameter shared_pool_size
SQL> alter system set shared_pool_size=40G scope=spfile;          -- not more than the memory_max_target, remember about the other memory pools

SQL> alter system set java_pool_size=2G scope=spfile;
SQL> alter system set DB_cache_size=6G scope=spfile;
SQL> alter system set use_large_pages=auto scope=spfile;

SQL> shut immediate
SQL> startup

show parameter target

SQL> alter system set memory_max_target=2000m scope=spfile;       -- not more than the SGA_MAX_SIZE
SQL> alter system set memory_target=1400m;                        -- not more than the  memory_max_target

 

ORA-00845: MEMORY_TARGET not supported on this system

$ umount /dev/shm
$ mount -t tmpfs shmfs -o size=5500m /dev/shm

memory_target and memory_max_target should be less that the value above

 

SQL> alter system set memory_max_target=3g;

ORA-20095: specified initialization parameter cannot be modified

SQL> alter system set memory_max_target=3g scope=spfile;

MEMORY_MAX_TARGET is a static parameter and therefore can only be changed with a SCOPE=SPFILE clause

 

SQL> alter system set sga_target=90G scope=spfile;

ORA-27137: unable to allocate large pages to create a shared memory segment
Linux-x86_64 Error: 12: Cannot allocate memory

Note: script from the https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64, already have been tested

hugepages_setting.sh

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
NUM_PG=1
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
do
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
   fi
done
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6' | '3.8' | '3.10' | '4.1' ) echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
esac
# End

$ chmod u+x hugepages_setting.sh
$ ./hugepages_setting.sh 	 Recommended setting: vm.nr_hugepages = 305

 

$ sudo su -
# cp /etc/sysctl.conf /etc/sysctl.conf.bak
# vi /etc/sysctl.conf	  set the value greater than or equal to the value displayed by the script
	  vm.nr_hugepages=306

 

Disable MEMORY_TARGET

SQL> alter system set memory_target=0;