SELECT BLOG;

Oracle Database XE, APEX, ORDS, Tomcat and httpd on CentOS 7: all-in-one guide - part two

Denis Savenko -

This is the second part of the series of blog posts dedicated to tell you in detail how to organize a usual production-like environment for APEX. In this part we are going to sort out the installation and configuration process of Oracle Database XE, ORDS and APEX itself. This instruction assumes you prepared your system following the instructions from the part one of this guide.

Let's go straight to the point starting with the downloading the software to our server.

Downloading the software

The first thing to do here is to download the software from Oracle Technology Network:

You have to have an ODC account (you can register right there, it is free) and accept the license agreement before you can start to download the stuff.

In case you have downloaded all the the software on your desktop computer, you will need to upload it to your server. It could be done by leveraging of an SCP or SFTP client, for instance pscp or WinSCP if you're a Windows user.

After everything is done I am assuming all the downloaded Oracle software is on your server in the root user home directory, which is usually /root.

Installation of Oracle Database 18c XE

Before we start, I just want you to be reminded about the minimum system requirements for the installation:

  • 1GB RAM (2GB is strongly recommended)
  • 2GB swap
  • 10GB of disk space

Installation of RDBMS

After you checked them, to install the RDBMS, you need to install the preinstall RPM package first and then install the database software as following:

cd /root
# uncomment the next line to download the preinstall package in case you have not downloaded it from the OTN
# wget https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
yum localinstall oracle-database-preinstall-18c* -y
yum localinstall oracle-database-xe-18c* -y

Notice that I used yum to install the local packages instead of rpm. This will enable us to use all the power of yum in the future if needed (for example, to remove a package with its dependencies).

The user oracle and the group oinstall (not dba as it was previously) are created during the package installation, so we do not need to create them explicitly. Also, the default user environment is created during the set up process (so we do not have to do it explicitly as it was previously). If you like, you can set a password for this user by invoking passwd oracle command. This user is the owner of the /opt/oracle directory where the Oracle Database is located and this must stay unchanged.

Now, when the packages are installed and the user is set up, you need to run the initial database configuration script:

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

And answer the questions prompted. After answering all the questions, it is going to take several minutes to initialize the database.

Setting up environment

Now it is a good idea to set up Oracle Database environment variables in order to make users be able to use sqlplus from anywhere. Along with this, we are setting up some useful aliases:

echo '# setting oracle database environment variables and aliases' >> /etc/profile.d/oraenv.sh
echo 'ORACLE_SID=XE' >> /etc/profile.d/oraenv.sh
echo 'ORAENV_ASK=NO' >> /etc/profile.d/oraenv.sh
echo '. /usr/local/bin/oraenv -s' >> /etc/profile.d/oraenv.sh
echo 'alias sqlplus="rlwrap sqlplus"' >> /etc/profile.d/oraenv.sh
echo 'alias rman="rlwrap rman"' >> /etc/profile.d/oraenv.sh
. /etc/profile.d/oraenv.sh

Then enable Oracle Database 18c XE service for automatic startup:

systemctl enable oracle-xe-18c

Note that CentOS 7 uses systemd instead of sysconfig to run system services, hence you should also stop and start the oracle-xe-18c service using systemctl instead of using lsnrctl command. Otherwise, you are risking to find yourself with the listener not working properly.

Connecting to database

And we are ready to log into the database and check if everything is good:

sqlplus /nolog
-- connect to the database
SQL> connect sys as sysdba

-- basic query to check if everything works
SQL> select * from dual;
    
-- check components and their versions
SQL> select comp_id, version, status from dba_registry;
    
SQL> exit

That is it! By now we have successfully installed the XE instance and it is up and running.

Note that since 12c Oracle Database has multitenant architecture, which means there could be several pluggable databases and one multitenant container database. By default, the XEPDB1 pluggable database is created during the installation of XE.

To make it easier to connect to the pluggable database, I recommend editing of tnsnames.ora file and add there a new connection descriptor that we are going to use:

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

Add this record there below the standard XE record:

 PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

And save the changes.

Installation of the latest version of APEX

Previously installation process of the latest version of Oracle Application Express (also known as APEX) consisted of deinstalling of the previous version and then installing of a new version. In 18c Oracle stopped to ship the Express Edition of their RDBMS with APEX preinstalled. So the step to deinstall the preinstalled version od APEX is not needed anymore.

Another difference will be in the fact that we are going to utilize the multitenant architecture of the 18c XE and will be installing our environment into the pluggable database. This enables us to potentially have different versions of APEX installed into different PDBs.

So now the installation process roughly consists of unzipping of the downloaded archive with the freshest version of APEX, connecting to the PDB, running a few installation scripts and then copying static files to your web server directory.

Installation of current version of APEX

So let's get it started. Change your directory back to /root, unzip the APEX archive and make the user oracle the owner of the directory. Considering we are installing the 18.2 version of APEX, it would look like this:

cd /root
mkdir -p /opt/oracle/apex
unzip apex_18.*.zip -d /opt/oracle
chown -R oracle:oinstall /opt/oracle/apex

Note that we will be installing the full development environment of APEX. This could be converted to a runtime only environment if needed. Refer to the official documentation for this. The full development environment should be used only when the Application Builder is needed. On a production environment it is strongly recommended to choose the runtime only setup.

From the APEX new home directory connect to our pluggable database as sysdba and run the installation scripts (we will be using SYSAUX schema for APEX metadata):

cd /opt/oracle/apex
-- connect to the database
sqlplus sys@pdb1 as sysdba

-- run the script to install a full development environment
SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/
   
 -- create an instance administrator user and set their password
SQL> @apxchpwd.sql
    
-- configure REST Data Services (needed for ORDS to serve workspaces and applications static files)
SQL> @apex_rest_config.sql

-- disable embedded PL/SQL gateways
SQL> exec dbms_xdb.sethttpport(0);
SQL> exec dbms_xdb.setftpport(0);

-- unlock and set up APEX public user, this is needed for ORDS to be able to connect to APEX engine
SQL> alter user apex_public_user account unlock;
SQL> alter user apex_public_user identified by "APEX_PUBLIC_USER";

-- add ACL to enable outgoing connections for APEX internal user
-- this is needed for the APEX_EXEC and APEX_WEB_SERVICE APIs to function properly
-- change it for a more strict policy if needed
SQL> begin
    dbms_network_acl_admin.append_host_ace(
        host => '*',
        ace => xs$ace_type(
            privilege_list => xs$name_list('connect'),
            principal_name => 'APEX_180200',
            principal_type => xs_acl.ptype_db))
        ;
end;
/

-- now disconnect from the database
SQL> exit

Then we need to copy APEX static files (images, stylesheets, JS files and so on) to the web server directory:

mkdir -p /var/www/apex/images
cp -a /opt/oracle/apex/images/. /var/www/apex/images

Now we finished with the Application Express installation.

Installation of ORDS

The Oracle Rest Data Services (ORDS) installation consists of unzipping the downloaded archive, running the configuration command, and then deploying the ords.war file into the Tomcat webapps folder.

Change back your directory to /root and unzip the ORDS archive:

cd /root
mkdir -p /opt/oracle/ords
unzip ords-18.*.zip -d /opt/oracle/ords

Run the ORDS configuration command before deployment. Choose the advanced mode - in this case the installation process will be interactive:

cd /opt/oracle/ords
java -jar ords.war install advanced

When prompted for ORDS configuration directory (the first question), enter config. Then provide the connection info to your pluggable database (specify XEPDB1 for the service name).

Note that we specified XEPDB1 here, not PDB1, because ORDS needs the service name, not your TNSNAMES entry by default to connect to your database. However, when you complete the installation, you can change ORDS settings to use TNS as the connection method. Find out how in the official documentation.

Note that "RESTful Services" are required by APEX 5 and above, so enable this by specifying passwords for the APEX_LISTENER and APEX_REST_PUBLIC_USER when prompted.

After the configuration is completed, the values are saved in opt/oracle/ords/config/ords/defaults.xml file and may be modified there. You can find more information about possible ORDS configuration options in the official documentation.

The tomcat user (created as part of Tomcat install) must have read-write access to the ORDS configuration folder:

chown -R tomcat:tomcat /opt/oracle/ords/config

Now it's high time to deploy ORDS to Tomcat application server. Copy the ords.war into the Tomcat webapps directory for this (and we will restart the Tomcat service later):

cp -a /opt/oracle/ords/ords.war /usr/share/tomcat/webapps/

Done! We succeeded in installing of ORDS and deploying it to Tomcat by now. Only one step is left.

Configuration of Apache httpd to map HTTP requests to ORDS

The last, but not the least step in this part of the guide is to configure Apache httpd to map HTTP-requests to ORDS and therefore APEX engine.

For this, add a custom httpd configuration file. By default, every .conf file placed in the etc/httpd/conf.d/ directory is read by httpd as an additional configuration file to the main /etc/httpd/conf/httpd.conf config file.

Note that these additional config files are read and processed by httpd in alphabetical order, so name your custom config accordingly if you use multiple config files.

So, let's create the 10-apex.conf file in the etc/httpd/conf.d/ directory with the contents as below:

# additional apache httpd configuration for apex requests proxying
# add this to the end of /etc/httpd/conf/httpd.conf
# or put it in a separate file such as /etc/httpd/conf.d/10-apex.conf

# forward ORDS requests to tomcat
<VirtualHost *:80>
    # uncomment the lines below if you plan to serve different domains 
    # on this web server, don't forget to change the domain name
    # ServerName yourdomain.tld
    # ServerAlias www.yourdomain.tld
    
    # alias for APEX static files
    Alias "/i" "/var/www/apex/images/"

    # uncomment the line below if you want 
    # to redirect traffic to ORDS from root path
    # RedirectMatch permanent "^/$" "/ords"

    # proxy ORDS requests to tomcat
    ProxyRequests off
    <Location "/ords">
        ProxyPass "ajp://localhost:8009/ords"
        ProxyPassReverse "ajp://localhost:8009/ords"
    </Location>
</VirtualHost>

Now you are ready to save the configuration file and restart the services.

Restarting of the services

In order for changes to take effect, we need to restart the services:

systemctl restart httpd
systemctl restart tomcat

And finally, you're ready to access APEX from your web browser using a link like http://yourdomain.tld/ords (or http://yourdomain.tld in case you switched on force redirection), where yourdomain.tld is the domain name or the IP-address of your server.

In case you're facing the situation when ORDS does not map APEX application and workspace static files properly after its installation (I mean, APEX engine static files work fine, but images, style sheets and other files from your applications generate the 404 not found error), try validating of the ORDS installation (and then restart the tomcat service):

cd /usr/share/tomcat/webapps/
java -jar ords.war validate
systemctl restart tomcat

Next Chapters

My congratulations! If you achieved this point, it means all the mandatory things are set up and should be running nicely. The next, final chapters of this guide will be covering additional steps to improve your users experience and security of the installation, but all the further actions are not required for the installation.

Here are the links for your convenience:

Previous Chapters

In case you missed some previous steps, please, use the following links to catch up:

Hope this post will be helpful for you. Be free to leave your comments if you have some advice how to improve the guide or if you found a mistake!