Increase SGA in OCI VMDB Systems with Huge Pages Setup

 => Check the number of Huge Pages configured on the server 

[oracle@sitdb ~]$ grep -i huge /etc/sysctl.conf
vm.nr_hugepages=3495
[oracle@sitdb ~]$

[root@sitdb ~]# grep HugePages /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:    3495
HugePages_Free:      169
HugePages_Rsvd:        4
HugePages_Surp:        0
[root@sitdb ~]#

Which means there are 3495 Huge Pages configured on the server and each page is of size 2 MB or 2048 KB. This will consume 3495 * 2048 KB of RAM memory or 6990 MB of RAM 

=> The parameter USE_LARGE_PAGES in the database if set to ONLY then the SGA's of all databases and ASM in the server put together should be maximum of 6990 MB. If the SGA exceeds this number for example if SGA is set to 13G then database would not start as the huge pages are not available on the server. 

In this scenario, to increase SGA we have to increase huge pages on the server based on the RAM on the server and reboot the server to make the changes persistent and then increase SGA 

Note: PGA will not be allocated from HugePages, so PGA can be increased depending on the amount of free memory available on the server 

=> Now I want to increase the SGA to 11 GB. My server has 15 GB of RAM, so I have set 80% of RAM for HugePages i.e 12GB of RAM 

12 * 1024 * 1024 KB RAM = 12582912 KB RAM 

Each Huge Page size is 2048 KB, so number of huge pages = 12582912 / 2048 = 6144 

[oracle@sitdb ~]$ grep -i huge /etc/sysctl.conf
#vm.nr_hugepages=3495
vm.nr_hugepages=6144
[oracle@sitdb ~]$

=> Reboot the server and verify the changes

[oracle@sitdb ~]$ grep -i huge /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:    6144
HugePages_Free:     6144
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
[oracle@sitdb ~]$

=> Check the memlock limit of oracle user

I have set it to unlimited. This should be slightly higher than all SGA's run on the server through oracle user

[oracle@sitdb ~]$ grep -i memlock /etc/security/limits.conf
#        - memlock      11000000
grid soft memlock unlimited
grid hard memlock unlimited
oracle soft memlock unlimited
oracle hard memlock unlimited
[oracle@sitdb ~]$

[oracle@sitdb ~]$ ulimit -l
unlimited
[oracle@sitdb ~]$

Note: 

These settings can also be done in a *.conf file under /etc/security/limits.d/ directory. 

Also if there is a *.conf file in /etc/security/limits.d/ that has settings for memlock, setting it up in /etc/security/limits.conf may not work. 

Make sure you check the files /etc/security/limits.d/*.conf files.

=> Now increase SGA

SQL>  alter system set sga_max_size=11g scope=spfile;
System altered.
SQL> alter system set sga_target=11g scope=spfile;
System altered.

=> Restart database and verify SGA

[oracle@sitdb ~]$ srvctl stop database -d SITCDB_dxb1mz
[oracle@sitdb ~]$ srvctl start database -d SITCDB_dxb1mz
[oracle@sitdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 26 10:15:19 2022
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management and Real Application Testing options
SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 11G
sga_target                           big integer 11G
unified_audit_sga_queue_size         integer     1048576
SQL>

=> check available huge pages 

[oracle@sitdb ~]$ grep HugePages /proc/meminfo
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:    6144
HugePages_Free:      522
HugePages_Rsvd:       12
HugePages_Surp:        0
[oracle@sitdb ~]$

=> Huge Page allocation to SGA can be verified in alert log as well

Supported system pagesize(s):
Fri Aug 26 10:13:55 2022
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
Fri Aug 26 10:13:55 2022
     2048K             6144            5634            5634        NONE
Fri Aug 26 10:13:55 2022
 Reason for not supporting certain system pagesizes:
Fri Aug 26 10:13:55 2022
  4K - Large pagesizes only
Fri Aug 26 10:13:55 2022
**********************************************************************