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
**********************************************************************

Queries on Locks and Blocks

Blocking session details from GVLock:

set lines 1000
select l1.sid || ' inst#'|| l1.INST_ID||' is blocking ' || l2.sid|| ' inst#'||l2.inst_id from gv$lock l1 , gv$lock l2
where l1.block > 0 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

Blocking session details from GVSession:

set lines 200 pages 100

col blocking_session for a15

select

   blocking_session,

   sid,

   serial#,

   wait_class,

   seconds_in_wait

from

   gv$session

where

   blocking_session is not NULL

order by

   blocking_session

/

Blocking session details from GVSession and GVLock:

set lines 2000 pages 100

col blocking_session for a15

col program for a40

col username for a15

col event for a40

col module for a40

col action for a40

col owner for a40

col object_name for a40

col osuser for a40

col logon_time for a40

SELECT b.inst_id,

       CASE WHEN A.request != 0 THEN '-> '||a.sid|| ' (BLOCKED)' ELSE TO_CHAR(a.sid) END sid,

       a.id1,

       a.id2,

       CASE a.lmode

       WHEN 0 THEN a.lmode||' - None'

       WHEN 1 THEN a.lmode||' - null'

       WHEN 2 THEN a.lmode||' - row-S'

       WHEN 3 THEN a.lmode||' - row-X'

       WHEN 4 THEN a.lmode||' - share'

       WHEN 5 THEN a.lmode||' - S/Row-X'

       WHEN 6 THEN a.lmode||' - exclusive'

       ELSE a.lmode||' - Unknown'

       END Lock_mode,

       CASE a.block

       WHEN 0 THEN a.block||' - Not blocking'

       ELSE a.block||' - Blocking'

       END block_type,

       CASE a.request

       WHEN 0 THEN a.request||' - None'

       WHEN 1 THEN a.request||' - null'

       WHEN 2 THEN a.request||' - row-S'

       WHEN 3 THEN a.request||' - row-X'

       WHEN 4 THEN a.request||' - share'

       WHEN 5 THEN a.request||' - S/Row-X'

       WHEN 6 THEN a.request||' - exclusive'

       ELSE a.request||' - Unknown'

       END request,

       b.blocking_session,

       b.blocking_instance,

       b.blocking_session_status,

       b.program,

       b.username,

       b.status,

       b.event,

       b.module,

       b.action,

       o.owner,

       o.object_name,

       b.osuser,

       to_char(b.logon_time,'DD/MON/YY HH24:MI:SS') logon_time

 FROM gv$lock a,

      gv$session b,

       dba_objects o

 WHERE (a.id1, a.id2) IN (SELECT id1, id2

                            FROM gv$lock

                           WHERE lmode = 0)

   AND a.inst_id = b.inst_id

   AND a.sid = b.sid

   AND o.object_id(+) = b.row_wait_obj#

 ORDER BY a.id1, a.id2, a.request;

Other details from GVSession and GVLock:

set lines 2000 pages 100

col username format A15

col SESSION for a15

col machine for a75

col program for a75

select

ses.inst_id,

ses.sid||','||ses.serial# "SESSION",

proc.spid,

ses.action,

ses.module,

ses.status,

to_char(ses.logon_time, 'MM/DD/YYYY HH24:MI:SS') LOGIN_TIME,

ses.username,

ses.client_identifier clienid,

ses.machine,

ses.program,

ses.sql_id,

ses.last_call_et,

ses.event,

ses.state,

ses.wait_time,

ses.seconds_in_wait

from gv$session ses , gv$process proc

where ses.paddr = proc.addr

and ses.inst_id = proc.inst_id

and exists (select block from gv$lock lck where lck.inst_id=ses.inst_id and lck.sid=ses.sid and lck.block=1)

order by ses.logon_time

/

APEX, ORDS and TOMCAT Setup

##########################################################

Environment      

##########################################################

APEX Version : 22.1.0

ORDS Version : 21.4.3.117.0405

TOMCAT Version : 9.0.65

Database Version : Oracle Database 12.1.0.2 (Used existing EBS database @ OCI)

Operating System : Oracle Linux 7

##########################################################

Install APEX        

##########################################################

=> Remove existing APEX installation from CDB

cd $ORACLE_HOME/apex

sqlplus / as sysdba @apxremov_con.sql

=> Download APEX Software and Copy to database Server as oracle user. unzip apex_22.1.zip

=> https://download.oracle.com/otn_software/apex/apex_22.1.zip

[oracle@ebsdevdb apex]$ pwd
/u01/app/oracle/product/apex
[oracle@ebsdevdb apex]$ ls -ltr
total 235384
-rw-r--r-- 1 oracle oinstall 241017674 Jul 25 11:16 apex_22.1.zip
drwxr-xr-x 2 oracle oinstall      4096 Jul 25 11:16 META-INF
drwxr-xr-x 6 oracle oinstall      4096 Jul 25 12:44 apex
[oracle@ebsdevdb apex]$

=> We are installing APEX in PDB, lets create tablespace in PDB to store APEX data 

SQL> alter session set container=DEVPDB;
Session altered.
SQL> create tablespace APEX_DATA datafile size 1G autoextend on maxsize 31g;
Tablespace created.
SQL>

=> Capture existing invalid objects in PDB before installing APEX

create table invalids_ch as (select object_name,owner from dba_objects where status='INVALID');

Post installation verify for any new invalid objects 

select count(*) from dba_objects where status='INVALID' and object_name not in (select object_name from invalids_ch);

=> Navigate to apex folder that got create after unzipping apex software and run below command after connecting to PDB

APEX_DATA is the tablespace to store APEX data

TEMP is the temporary tablespace for APEX Schema

SQL> alter session set container=DEVPDB;
Session altered.
SQL> @apexins.sql APEX_DATA APEX_DATA TEMP /i/
.
.
.
ok 3 - 18 actions passed, 0 actions failed                              |   1.75


Thank you for installing Oracle APEX 22.1.0
Oracle APEX is installed in the APEX_220100 schema.
The structure of the link to the Oracle APEX administration services is as follows:
http://host:port/ords/apex_admin
The structure of the link to the Oracle APEX development interface is as follows:
http://host:port/ords
timing for: Phase 3 (Switch)
Elapsed: 00:01:45.27
timing for: Complete Installation
Elapsed: 00:10:42.04
SYS>


SQL> alter session set container=DEVPDB;
Session altered.
SQL> SELECT STATUS FROM DBA_REGISTRY WHERE COMP_ID = 'APEX';
STATUS
--------------------------------------------
VALID
SQL>

=> Change ADMIN Password for APEX

SQL> alter session set container=DEVPDB;
Session altered.
SQL> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Oracle APEX
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN]
User "ADMIN" does not yet exist and will be created.
Enter ADMIN's email [ADMIN]
Enter ADMIN's password []
Created instance administrator ADMIN.
SQL>

=> Create the APEX_LISTENER and APEX_REST_PUBLIC_USER users by running the "@apex_rest_config.sql" 

SQL> alter session set container=DEVPDB;
Session altered.
SQL> @apex_rest_config.sql
PL/SQL procedure successfully completed.

Enter a password for the APEX_LISTENER user              []
Enter a password for the APEX_REST_PUBLIC_USER user              []
...set_appun.sql
...setting session environment
.
.
.
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.
SQL>

=> Set password for the APEX_PUBLIC_USER, APEX_LISTENER, APEX_REST_PUBLIC_USER and unlock the account

ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK IDENTIFIED BY "*******";
ALTER USER APEX_LISTENER ACCOUNT UNLOCK IDENTIFIED BY "*******";
ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK IDENTIFIED BY "*******";

##########################################################

Install ORDS       

##########################################################

=> Download ORDS Software and Copy to application server, I created user called xxapache on application server 

=> https://download.oracle.com/otn_software/java/ords/ords-21.4.3.117.0405.zip

[xxapache@ebsdevapp01 ~]$ cd /u01/ords
[xxapache@ebsdevapp01 ords]$ ls -ltr ords-21.4.3.117.0405.zip
-rw-rw-r--. 1 xxapache xxapache 81861484 Jul 26 12:49 ords-21.4.3.117.0405.zip
[xxapache@dlebsdevapp01 ords]$

=> JAVA Pre-Requisite:

This Version of ORDS requires Java 11 and above to run. Please set JAVA_HOME to appropriate version and update PATH.

Download JDK and set environment 

[xxapache@ebsdevapp01 ~]$ cat java.env
export JAVA_HOME=/u01/ords/jdk/jdk-11.0.15.1
export PATH=$JAVA_HOME/bin:$PATH
[xxapache@ebsdevapp01 ~]$

[xxapache@ebsdevapp01 ~]$ which java
/u01/ords/jdk/jdk-11.0.15.1/bin/java
[xxapache@ebsdevapp01 ~]$ java -version
java version "11.0.15.1" 2022-04-22 LTS
Java(TM) SE Runtime Environment 18.9 (build 11.0.15.1+2-LTS-10)
Java HotSpot(TM) 64-Bit Server VM 18.9 (build 11.0.15.1+2-LTS-10, mixed mode)
[xxapache@ebsdevapp01 ~]$

=> Database Parameter Pre-requisite 

Set below parameter as per note id - Queries Against SYS.ALL_SYNONYMS Inside A PDB Are Very Slow (Doc ID 2297471.1)

alter system set "_common_data_view_enabled" = false scope=both;

=> Run below command to configure ORDS and provide required details - provide the details of PDB where APEX is already installed. 

Ensure that sysdba connection is working from application server through password file.

[xxapache@ebsdevapp01 ords]$ java -jar ords.war setup advanced

Specify the database connection type to use.

Enter number for [1] Basic  [2] TNS  [3] Custom URL [1]:

Enter the name of the database server [localhost]:ebsdevdb

Enter the database listen port [1521]:

Enter 1 to specify the database service name, or 2 to specify the database SID [1]:

Enter the database service name:DEVPDB

Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:

Enter the database password for ORDS_PUBLIC_USER:

Confirm password:

Requires to login with administrator privileges to verify Oracle REST Data Services schema.

Enter the administrator username:sys as sysdba

Enter the database password for sys as sysdba:

Confirm password:

Connecting to database user: sys as sysdba url: jdbc:oracle:thin:@//ebsdevdb:1521/DEVPDB

Retrieving information.

Enter the default tablespace for ORDS_METADATA [SYSAUX]:APEX_DATA

Enter the temporary tablespace for ORDS_METADATA [TEMP]:TEMP

Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:APEX_DATA

Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:TEMP

Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.

If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:

Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:

Enter the database password for APEX_PUBLIC_USER:

Confirm password:

Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:

Enter the database password for APEX_LISTENER:

Confirm password:

Enter the database password for APEX_REST_PUBLIC_USER:

Confirm password:

Enter a number to select a feature to enable:

   [1] SQL Developer Web  (Enables all features)

   [2] REST Enabled SQL

   [3] Database API

   [4] REST Enabled SQL and Database API

   [5] None

Choose [1]:

2022-07-27T05:28:08.226Z INFO        reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|]

Installing Oracle REST Data Services version 21.4.3.r1170405

... Log file written to /home/xxapache/ords_install_core_2022-07-27_092808_00977.log

... Verified database prerequisites

... Created Oracle REST Data Services proxy user

... Created Oracle REST Data Services schema

... Granted privileges to Oracle REST Data Services

... Created Oracle REST Data Services database objects

... Log file written to /home/xxapache/ords_install_datamodel_2022-07-27_092949_00542.log

... Log file written to /home/xxapache/ords_install_scheduler_2022-07-27_093020_00136.log

... Log file written to /home/xxapache/ords_install_apex_2022-07-27_093047_00988.log

Completed installation for Oracle REST Data Services version 21.4.3.r1170405. Elapsed time: 00:03:07.668 

[xxapache@ebsdevapp01 ords]$ 

=> Configure Network ACL 

SQL> alter session set container=DEVPDB;
Session altered.
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'APEX_220100',
principal_type => xs_acl.ptype_db));
END;
/  2    3    4    5    6    7    8
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.

##########################################################

Install Apache TOMCAT

##########################################################

I created user called xxapache on application server.

=> installation folder - /u01/xxapache

=> download software - wget https://downloads.apache.org/tomcat/tomcat-9/v9.0.65/bin/apache-tomcat-9.0.65.tar.gz

=> untar software - tar -xvf apache-tomcat-9.0.65.tar.gz -C .

=> start apache 

cd /u01/xxapache/apache-tomcat-9.0.65/bin

[xxapache@ebsdevapp01 bin]$ ./catalina.sh start

Using CATALINA_BASE:   /u01/xxapache/apache-tomcat-9.0.65

Using CATALINA_HOME:   /u01/xxapache/apache-tomcat-9.0.65

Using CATALINA_TMPDIR: /u01/xxapache/apache-tomcat-9.0.65/temp

Using JRE_HOME:        /

Using CLASSPATH:       /u01/xxapache/apache-tomcat-9.0.65/bin/bootstrap.jar:/u01/xxapache/apache-tomcat-9.0.65/bin/tomcat-juli.jar

Using CATALINA_OPTS:

Tomcat started.

[xxapache@ebsdevapp01 bin]$

[xxapache@ebsdevapp01 bin]$ netstat -an|grep 8080

tcp6       0      0 :::8080                 :::*                    LISTEN

[xxapache@ebsdevapp01 bin]$ ps -ef|grep xxapache

xxapache  5692     1  7 13:53 pts/1    00:00:05 //bin/java -Djava.util.logging.config.file=/u01/xxapache/apache-tomcat-9.0.65/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 -Dignore.endorsed.dirs= -classpath /u01/xxapache/apache-tomcat-9.0.65/bin/bootstrap.jar:/u01/xxapache/apache-tomcat-9.0.65/bin/tomcat-juli.jar -Dcatalina.base=/u01/xxapache/apache-tomcat-9.0.65 -Dcatalina.home=/u01/xxapache/apache-tomcat-9.0.65 -Djava.io.tmpdir=/u01/xxapache/apache-tomcat-9.0.65/temp org.apache.catalina.startup.Bootstrap start

##########################################################

Linking TOMCAT, ORDS and APEX

##########################################################

=> Copy /u01/ords/ords.war to /u01/xxapache/apache-tomcat-9.0.65/webapps/ords.war

[xxapache@ebsdevapp01 ords]$ pwd

/u01/ords

[xxapache@ebsdevapp01 ords]$ ls -ltr ords.war

-rw-r--r--. 1 xxapache xxapache 79563604 Jul 26 13:00 ords.war

[xxapache@ebsdevapp01 ords]$

[xxapache@ebsdevapp01 webapps]$ pwd

/u01/xxapache/apache-tomcat-9.0.65/webapps

[xxapache@ebsdevapp01 webapps]$ ls -ltr ords.war

-rw-r--r--. 1 xxapache xxapache 79563604 Jul 26 13:00 ords.war

[xxapache@ebsdevapp01 webapps]$

=> Copy apex images /u01/app/oracle/product/apex/apex/images to Tomcat /u01/xxapache/apache-tomcat-9.0.65/webapps/i/

[oracle@dlebsdevdb images]$ pwd

/u01/app/oracle/product/apex/apex/images

[oracle@dlebsdevdb images]$

[xxapache@ebsdevapp01 i]$ pwd

/u01/xxapache/apache-tomcat-9.0.65/webapps/i/

=> Access ords URL - http://<Apache-IP>:8080/ords

Login through INTERNAL Workspace and ADMIN user password