How to install Oracle Apex 20.1 on CentOS Linux 7

By Vladimir Goncharov, [email protected]

Very short instruction.

Includes installation of Oracle XE 18c, Tomcat 9, ORDS 19.2, Java 11 and NGINX as a reverse proxy.

Prereqs:

Minimal installation of CentOS 7.

Download oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm from oracle.com

Download oracle-database-xe-18c-1.0-1.x86_64.rpm from oracle.com

Download Apex 20.1 apex_20.1_en.zip from apex.oracle.com (probably https://www.oracle.com/tools/downloads/apex-downloads.html)

Download ORDS 19.2 ords-19.2.0.199.1647.zip from https://www.oracle.com/database/technologies/appdev/rest-data-services-v192-downloads.html

Download latest Tomcat 9 apache-tomcat-9.0.34.zip

Installation instructions:

# yum update

-- Optional, but I want to have it on every linux

# yum install telnet screen mlocate

# vi /etc/hosts

-- add or edit the line:

123.45.67.9 yourdomain.com www.yourdomain.com

-- Where 123.45.67.9 is ip address of yourdomain.com

-- Edit the line with 127.0.0.1 by adding yourdomain.private

127.0.0.1 localhost yourdomain.private

# hostnamectl

# hostnamectl set-hostname yourdomain.com

# reboot

# yum localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

# yum localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm

# /etc/init.d/oracle-xe-18c configure

-- We do not want to allow connections to Oracle Database from Internet.

# vi /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora

Change HOST=... to HOST=yourdomain.private

# su - oracle

$ vi .bash_profile

-- Add the following lines to the end of file:

--- cut ---

export ORACLE_SID=XE 

export ORAENV_ASK=NO 

. /opt/oracle/product/18c/dbhomeXE/bin/oraenv

--- cut ---

$ exit

# su - oracle

$ sqlplus / as sysdba

SQL> alter system set audit_trail='OS' scope=spfile sid='*';

SQL> alter system set db_create_file_dest='/opt/oracle/oradata' scope=spfile sid='*';

SQL> alter system set db_create_online_log_dest_1='/opt/oracle/oradata' scope=spfile sid='*';

-- Do not forget to change YOURDOMAIN.private to relevant value that you previously put in /etc/hosts for 127.0.0.1

SQL> alter system set dispatchers='(INDEX=0)(ADDRESS=(PROTOCOL=TCP)(HOST=YOURDOMAIN.private))(SERVICE=XEXDB)' scope=both sid='*';

SQL> alter system set memory_target='2G' scope=spfile sid='*';

SQL> alter system reset pga_aggregate_target scope=spfile sid='*';

SQL> alter system reset sga_target scope=spfile sid='*';

-- parameters below are optional, but I use it in my project

SQL> alter system set max_dump_file_size='1M' scope=both sid='*';

SQL> alter system set plsql_warnings='DISABLE:ALL','ENABLE: 5005' scope=both sid='*';

SQL> alter system set statistics_level='ALL' scope=spfile sid='*';

SQL> exit

-- Change HOST=... to HOST=yourdomain.private (all entries, should be 2 entries)

$ vi /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora

$ exit

-- if we have 4G RAM or less then /dev/shm size is too small to run Oracle.

-- To run Oracle on such systems do the following:

# vi /etc/fstab

-- Add the line:

none  /dev/shm  tmpfs  defaults,size=2G  0 0

# reboot

# /etc/init.d/oracle-xe-18c start

-- To check that we do not listen public IPs:

netstat -ntlp

-- we do not need rpcbind

# systemctl stop rpcbind

# systemctl stop rpcbind.socket

# systemctl disable rpcbind

-- to autostart

# systemctl daemon-reload

# systemctl enable oracle-xe-18c

# reboot

-- we want to have Oracle and SSH only

# netstat -ntlp

-- Optional, but we want maximum for varchar2 to be 32k for new databases

# su - oracle

$ sqlplus / as sysdba

SQL> ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE sid='*';

SQL> shutdown immediate;

SQL> startup upgrade

SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;

SQL> exit

$ cd $ORACLE_HOME/rdbms/admin

$ mkdir /tmp/utl32k_cdb_pdbs_output

-- Prepare SYS password 

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/tmp/utl32k_cdb_pdbs_output' -b utl32k_cdb_pdbs_output utl32k.sql

-- must be completed successfully

$ sqlplus / as sysdba

$ sqlplus / as sysdba

SQL> shutdown immediate;

SQL> startup

SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;

SQL> exit

$ cd $ORACLE_HOME/rdbms/admin

$ mkdir /tmp/utlrp_cdb_pdbs_output

-- Prepare SYS password 

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/tmp/utlrp_cdb_pdbs_output' -b utlrp_cdb_pdbs_output utlrp.sql

-- must be completed successfully

Installing Application Express into Different PDBs  

-- https://docs.oracle.com/en/database/oracle/application-express/20.1/htmig/installing-AE-into-different-PDBs.html#GUID-1BD78FA9-CA7C-47CF-A5A9-30CA3084315B

# su - oracle

$ sqlplus / as sysdba

SQL> alter session set container=XEPDB1;

SQL>

create tablespace apex_200100

 default table compress for oltp

 index compress advanced high;

SQL>

create tablespace apex_files

 default table compress for oltp

 index compress advanced high;

SQL>

create tablespace ords_1902

 default table compress for oltp

 index compress advanced high;

SQL> exit

$ cd /tmp

$ unzip apex_20.1_en.zip

$ cd apex

$ sqlplus / as sysdba

SQL> alter session set container=XEPDB1;

SQL> @apexins.sql APEX_200100 APEX_FILES TEMP /i/

SQL> ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

SQL> ALTER USER APEX_PUBLIC_USER IDENTIFIED BY new_password;

SQL> create profile apex_public_user limit password_life_time UNLIMITED failed_login_attempts UNLIMITED;

SQL> alter user apex_public_user profile apex_public_user;

SQL> @apex_rest_config.sql

Time to install tomcat 9, java and ORDS 19.2

-- Links used:

-- https://oracle-base.com/articles/linux/apache-tomcat-9-installation-on-linux

-- https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.2/aelig/installing-REST-data-services.html#GUID-6F7B4E61-B730-4E73-80B8-F53299123730

# yum install java-11-openjdk

# cd /opt

# mkdir tomcat 

# mkdir tomcat/ords-19.2

# useradd tomcat 

# cp /root/apache-tomcat-9.0.34.zip tomcat/ 

# cp /root/ords-19.2.0.199.1647.zip tomcat/ 

# chown -R tomcat.tomcat tomcat 

# su - tomcat

$ vi .bash_profile

-- Add the following lines to the end of file:

export ORACLE_SID=XE 

export ORAENV_ASK=NO 

. /opt/oracle/product/18c/dbhomeXE/bin/oraenv

export JAVA_HOME=/etc/alternatives/jre 

export CATALINA_HOME=/opt/tomcat/latest 

export CATALINA_BASE=/opt/tomcat/config/instance1

$ exit

# su - tomcat

$ cd /opt/tomcat

$ unzip apache-tomcat-9.0.34.zip

$ rm apache-tomcat-9.0.34.zip

$ ln -s apache-tomcat-9.0.34 latest

$ mkdir -p config/instance1

$ cp -r latest/conf config/instance1/

$ cp -r latest/logs config/instance1/

$ cp -r latest/temp config/instance1/

$ cp -r latest/webapps config/instance1/

$ cp -r latest/work config/instance1/

$ unzip -d ords-19.2 ords-19.2.0.199.1647.zip

$ rm ords-19.2.0.199.1647.zip

$ cd ords-19.2

-- To change URL /ords/ to /somethingcool/ do the following:

-- $ mv ords.war somethingcool.war 

-- Do not forget to change ords.war to somethingcool.war in this instruction below.

$ cd installer

$ sqlplus /nolog

SQL> conn sys/[email protected]/XEPDB1 as sysdba

SQL> @ords_installer_privileges.sql pdbadmin

SQL> exit

$ cd ..

$ java -jar ords.war install advanced

Enter the location to store configuration data: conf

Enter the name of the database server [localhost]: yourdomain.private

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: XEPDB1

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: <create new password here> 

Confirm password: <confirm the password>

Enter the administrator username: pdbadmin

Enter the database password for pdbadmin: <you created it on XE creation>

Confirm password:

Enter the default tablespace for ORDS_METADATA [SYSAUX]: ords_1902

Enter the temporary tablespace for ORDS_METADATA [TEMP]:

Enter the default tablespace for ORDS_PUBLIC_USER [SYSAUX]:ords_1902

Enter the temporary tablespace for ORDS_PUBLIC_USER [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: <you set it earlier>

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: <you created it earlier>

Confirm password:

Enter the database password for APEX_REST_PUBLIC_USER: <you created it earlier>

Confirm password:

Enter 1 if you wish to start in standalone mode or 2 to exit [1]: 2

$ cp ords.war / ../config/instance1/webapps/ 

$ cp -r /tmp/apex/images ../config/instance1/webapps/i

$ cd ../config/instance1/webapps/ 

$ chown -R tomcat.tomcat i

$ chmod +x $CATALINA_HOME/bin/*.sh

$ $CATALINA_HOME/bin/startup.sh

-- Now open you preferred browser and go to https://yourdomain.com:8080/ords/

-- Do not forget to replace /ords/ to /somethingcoll/ if you have ords.war renamed to somethingcool.war

-- Hope, you see Apex 20.1 login page.

Almost done.

-- We still have something to do with security, performance and ORDS pool settings.

-- Among our next steps: autostart tomcat after reboot, NGINX as a reverse proxy with SSL and openvpn server to connect to Oracle.

-- Tomcat autostart on reboot

-- https://tecnstuff.net/how-to-install-tomcat-9-on-centos-7/

-- Create a file "tomcat.service" with the following content.

# vi /etc/systemd/system/tomcat.service

--- cut ---

# Systemd unit file for tomcat

[Unit]

Description=Apache Tomcat Web Application Container

After=syslog.target network.target

[Service]

Type=forking

Environment=JAVA_HOME=/usr/lib/jvm/jre

Environment=CATALINA_PID=/opt/tomcat/config/instance1/temp/tomcat.pid

Environment=CATALINA_HOME=/opt/tomcat/latest

Environment=CATALINA_BASE=/opt/tomcat/config/instance1

#Environment='CATALINA_OPTS=-Xms512M -Xmx1024M -server -XX:+UseParallelGC'

#Environment='JAVA_OPTS=-Djava.awt.headless=true -Djava.security.egd=file:/dev/./urandom'

ExecStart=/opt/tomcat/latest/bin/startup.sh

#ExecStop=/bin/kill -15 $MAINPID

ExecStop=/opt/tomcat/latest/bin/shutdown.sh

User=tomcat

Group=tomcat

UMask=0007

RestartSec=10

Restart=always

[Install]

WantedBy=multi-user.target

--- cut ---

# systemctl daemon-reload

# systemctl start tomcat

# systemctl status tomcat

# systemctl enable tomcat

# reboot

-- We do not want to listen public IP using Java

-- So we want to hide it behind NGINX reverse proxy

# su - tomcat

$ vi /opt/tomcat/config/instance1/conf/server.xml

-- Change this line:

<Connector port="8080" protocol="HTTP/1.1"

-- to:

<Connector port="8080" protocol="HTTP/1.1" address="yourdomain.private"

-- Now set IPv4 for tomcat

$ mkdir /opt/tomcat/config/instance1/bin 

-- Create such file with just one line of code:

$ vi /opt/tomcat/config/instance1/bin/setenv.sh

--- cut ---

JAVA_OPTS="$JAVA_OPTS -Djava.net.preferIPv4Stack=true -Djava.net.preferIPv4Addresses=true "

--- cut ---

$ exit

# systemctl restart tomcat

-- Nobody should listen public IP except sshd, java must listen tcp, not tcp6.

# netstat -ntlp

Install NGINX.

# yum install nginx

-- Below I show the simplest NGINX configuration. Usually we do something more sophisticated.

-- Create such file: do replace yourdomain.private with your value.

# vi /etc/nginx/default.d/ords.conf 

--- cut ---

# to upload large photos and application exports

client_max_body_size 32m;

location /ords {

  proxy_pass    https://yourdomain.private:8080;

  proxy_set_header Origin "";

  proxy_set_header Host $host;

  proxy_read_timeout 120;

  proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;

  proxy_set_header Authorization  "";

  proxy_hide_header DAV;

  proxy_hide_header MS-Author-Via;

  gzip  on;

 }

 location /i/ {

  root      /opt/tomcat/config/instance1/webapps;

  gzip on;

 }

--- cut ---

# systemctl start nginx

# systemctl enable nginx

# reboot

The end

-- Now is the time to install SSL for NGINX and openvpn to get access to yourdomain.private to work with Oracle and tomcat directly.

-- Also if you have access to MyOracleSupport consider Apex Known Issues

-- https://www.oracle.com/tools/downloads/apex-downloads/apex-201-known-issues.html

-- You may fix some issues by installing PSE 30392181.

Email [email protected] if any questions.