Increase oracle memory

====================
General Oracle memory on Linux
====================

1. Increase /dev/shm in Linux
as root

vi  /etc/fstab

from

tmpfs /dev/shm tmpfs defaults,size=4096M 0 0

to

tmpfs /dev/shm tmpfs defaults,size=9216M 0 0

17408M for 17Gb

25600M for 25Gb

mount -o remount /dev/shm

df -hP | grep shm

tmpfs                          9.0G   39M  9.0G   1% /dev/shm

(or systemctl daemon-reload)

2. Backup oracle spfile

as oracle

cp /software/oracle/admin/igt/pfile/spfileigt.ora /software/oracle/admin/igt/pfile/spfileigt.ora_YYYYMMDD

sqlplus / as sysdba

SQL> show parameter spfile

NAME      TYPE      VALUE

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

spfile    string    /software/oracle/19/dbhome_1/dbs/spfileigt.ora

                                                 

CREATE PFILE='/software/oracle/admin/igt/pfile/pfileigt.ora' FROM SPFILE='/software/oracle/admin/igt/pfile/spfileigt.ora';

!cp /software/oracle/admin/igt/pfile/pfileigt.ora /software/oracle/admin/igt/pfile/pfilei!cp gt.ora_YYYYMMDD

3. Get current status

COL name for A30

COL value for A30
SELECT name, value FROM V$PARAMETER WHERE name like 'memory%';

NAME                           VALUE

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

memory_target                  2147483648

memory_max_target              2147483648

Linux server has 64Gb memory

oracle@my_server:~>% free -m

      total    used    free   shared  buff/cache   available

Mem:  64265   15031    8500     4614       40733       44232

Swap: 16383       0   16383

4. Increase of Linux Cluster - scp the spfileigt.ora file to the other node

--4096M

ALTER SYSTEM SET MEMORY_MAX_TARGET=4096M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=4096M scope=spfile;

--8192M

ALTER SYSTEM SET MEMORY_MAX_TARGET=8192M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=8192M scope=spfile;

16384M - 16Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=16384M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=16384M scope=spfile;

16384M - 16Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=16384M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=16384M scope=spfile;

24576M - 32Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=32768M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=32768M scope=spfile;

36864M - 36Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=36864M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=36864M scope=spfile;

43008M - 42Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=43008M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=43008M scope=spfile;

49152M - 48Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=49152M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=49152M scope=spfile;

65536M - 64Gb

ALTER SYSTEM SET MEMORY_MAX_TARGET=65536M scope=spfile;

ALTER SYSTEM SET MEMORY_TARGET=65536M scope=spfile;

4. In case of a cluster - scp the spfileigt.ora file to the other node.

scp /software/oracle/admin/igt/pfile/spfileigt.ora oracle@srv-1-aps-1b:/software/oracle/admin/igt/pfile/spfileigt.ora 

5. Restart Oracle

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;

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

Theory

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

MEMORY_MAX_TARGET - The max memory that can be allocated form host to Oracle. It is an initialization parameter. Changing this value requires a restart.

MEMORY_TARGET - This is a dynamic parameter, It can be changed without restart to the database.

Both are used for automatic PGA and SGA memory sizing.

For example:

By setting memory_max_target=20G you allow your instance to get hold of 20G RAM. 

At this stage if you set second parameter memory_target=15G that means you are instructing oracle instance to only use 15G out of 20G RAM. 

If you do not set memory_target parameter then oracle will use 20G or RAM.

The remaining 5G are still occupied by oracle and are not in use. OS can not use this until you shutdown the instance.

Why we need memory_max_target when we have memory_target?

Having this option to adjust memory_target dynamically you can find what is the suitable memory target for the particluar database, without restarts.

By Example:

ALTER SYSTEM SET MEMORY_MAX_TARGET=20G SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

ALTER SYSTEM SET MEMORY_TARGET=10G SCOPE=BOTH;

Or

ALTER SYSTEM SET MEMORY_TARGET=10G;