Skip to main content

Oracle XE and APEX on OL7

Downloading the software

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

If you have downloaded all the the software on your desktop, you will need to upload it to your server. We'll assume you were bad and put it all in to the /root folder as root.

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:

wget https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
yum install oracle-database-preinstall-18c* -y
yum install oracle-database-xe-18c* -y

The user oracle and the group oinstallare created during the package installation. A default user environment is created during the set up process. 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.

 chown oracle:oinstall /opt/oracle

When the packages are installed and the user is set up, you need to run the initial database configuration script and answer all of the questions.

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

After answering the questions it is going to take several minutes to initialize the database.

Setting up environment

Set up Oracle Database environment variables in order to make users be able to use sqlplus from anywhere. some useful aliases will also be created.

echo '# setting oracle database environment variables and aliases'environment' >> /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/opt/oracle/product/18c/dbhomeXE/bin/oraenv -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

Enable Oracle Database XE service for automatic startup:

systemctl enable oracle-xe-18c

Connecting to database

And we are ready to log into the database.

sqlplus /nolog

Check if everything is good.

-- connect to the database
connect sys as sysdba

-- basic query to check if everything workscame up right
select * from dual;
    
-- check components and their versions
select comp_id, version, status from dba_registry;

-- exit the database
exit

If everything looks good, you've successfully installed the XE instance and it is running.

Oracle Database has multi-tenant architecture, which means there could be several pluggable databases and one multi-tenant 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, edit thetnsnames.ora file and add there a new connection descriptor that we are going to use.

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

Add the following after the standard XE record:

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

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 APEX

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 19.2 version of APEX, it would look like this.

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

From the APEX directory connect to our pluggable database as sysdba and run the installation scripts.

cd /opt/oracle/apex
-- connect to the database
sqlplus sys@pdb1 as sysdba
-- run the script to install a full development environment
@apexins.sql SYSAUX SYSAUX TEMP /i/
   
 -- create an instance administrator user and set their password
@apxchpwd.sql
   

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

-- unlock APEX public user
ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK
ALTER USER APEX_PUBLIC_USER IDENTIFIED BY S0m3aw3s0m3Pw

-- configure REST Data Services (needed for ORDS to serve workspaces and applications static files)
@apex_rest_config.sql

-- disable embedded PL/SQL gateways
exec dbms_xdb.sethttpport(0);
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
alter user apex_public_user account unlock;
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
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
exit

Copy APEX static files to the web server directory.

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

The Application Express installation is complete.

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.

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

Run the ORDS configuration command with the advanced mode to run the interactive installation process.

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

When prompted for ORDS configuration directory, enter config.
Then provide the connection info to your pluggable database  XEPDB1

Follow the on screen instructions.

After the configuration is completed, the values are saved in opt/oracle/ords/config/ords/defaults.xml file. It can be modified there. See more at Oracle Docs.

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

Deploy ORDS to Tomcat application server. Copy the ords.war into the Tomcat webapps directory for this 

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

Done with ORDS and Tomcat, only one step is left. Apache!

Configuration of Apache httpd to map ORDS

The last step is to configure Apache 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.

Create the 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.

systemctl restart httpd
systemctl restart tomcat

And finally, 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.

Back to top