Adjusting the memory_max_target based on available memory.
This example is Linux x86-64.
If you can afford to set the memory_max_target higher then the memory_target this will give you room to grow the memory_target without restarting the database.
SQL> show parameters memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 17920M
SQL> show parameters memory_max_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 17920M
System has 36GB physical RAM available.
SQL> !grep MemTotal /proc/meminfo
MemTotal: 36912956 kB
Server has now set aside 24GB for use with Oracle. Kernel shared memory parameter.
SQL>!df -h /dev/shm/
Filesystem Size Used Avail Use% Mounted on
tmpfs 24G 11G 14G 44% /dev/shm
Increase memory_max_target to 24GB.
SQL> ALTER SYSTEM SET memory_max_target = 24G SCOPE=SPFILE;
System altered.
Shutdown Oracle.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Startup Oracle.
SQL> startup
ORACLE instance started.
Total System Global Area 2.5655E+10 bytes
Fixed Size 2213776 bytes
Variable Size 2.0133E+10 bytes
Database Buffers 5368709120 bytes
Redo Buffers 151166976 bytes
Database mounted.
Database opened.
Update your pfile.
SQL> create pfile from spfile;
File created.
Verify the new settings. Max now 24GB and memory target is 17.9GB. We can now increase the memory_target if the need arises without shutting the database down.
SQL> sho parameters memory_max_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target big integer 24G
SQL> sho parameters memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 17920M
