SELECT BLOG;

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

Denis Savenko — Sat, 08 Dec 2018 12:15:00 GMT

As many readers asked for this, I decided to write this continuation of the guide on how to configure a production-like Oracle Application Express environment. In this long awaited chapter we are going to cover configuration of SSL, redundancy and backups in the environment you already have if followed the previous chapters. I should tell you that all these steps are totally optional and are not required for you to start with APEX. But following them you will dramatically increase security, availabilty and reliability of your environment, which is definitely a must for any production environment. Your users will not tolerate it if somebody steals their passwords, or if they cannot access your website because of some media failure on your server. So, if you want to prevent these events, this chapter is definitely for you.

Configuring SSL

In previous chapters we already got rid of unneeded Tomcat applications and tweaked Apache configuration a little bit. But we didn't touch a huge topic of secure connections between users and your web server. I should warn you that configuring this is going to be more advanced than anything we already did before. But it's worth it, because we are talking about security and you shouldn't underestimate it.

So, the first thing to say here - you will need an SSL certificate to set up a secure access to your APEX applications. Such a certificate is issued for a domain name, which you have to possess. Keeping all this in mind we are ready to start.

I should also notice that we are not going to use Oracle Wallet, because our security layer will only take place between our users and the web server, which is Apache httpd in our case.

SSL certificates are generally issued by trusted companies and in most cases this is a paid service. But thanks to Linux Foundation, today we have a totally free option of Let's Encrypt certificates. They are issued for 60 days and are free to renew. There is also very useful software which automates generation and renewal processes of such certificates, it is called certbot and it is available for a variety of operating systems, including CentOS Linux. I will not stop on how to install certbot, because Digital Ocean already composed a detailed tutorial on the topic. And you will need to install certbot using this tutorial. Additionally, you are going to find there information about how to configure automatic renewal of your certificates.

Note that during installation, certbot adds ssl.conf file to the /etc/httpd/conf.d directory, which already contains needed HTTPS configuration for Apache (enabling listening to port 443, proper cipher suite and so on).

Assuming that you succeeded with installation of certbot and generated your certificates following the steps in the manual, the only thing which left is to rewrite our APEX virtual host configuration and restart Apache service.

So, open your 10-apex.conf file in the /etc/httpd/conf.d/ directory (we created it in the second chapter of this guide) and rewrite its contents as following:

# force HTTPS

    ServerName yourdomain.tld
    ServerAlias www.yourdomain.tld

    RewriteEngine on
    RewriteCond %{SERVER_NAME} =www.yourdomain.tld [OR]
    RewriteCond %{SERVER_NAME} =yourdomain.tld
    RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,NE,R=permanent]


# forward ORDS requests to tomcat

    ServerName yourdomain.tld
    ServerAlias www.yourdomain.tld

    # SSL certificates settings
    Include /etc/letsencrypt/options-ssl-apache.conf
    SSLCertificateFile /etc/letsencrypt/live/yourdomain.tld/cert.pem
    SSLCertificateKeyFile /etc/letsencrypt/live/yourdomain.tld/privkey.pem
    SSLCertificateChainFile /etc/letsencrypt/live/yourdomain.tld/chain.pem

    # 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
    ProxyPreserveHost On
    
        ProxyPass "ajp://localhost:8009/ords"
        ProxyPassReverse "ajp://localhost:8009/ords"

Where yourdomain.tld is your domain name. This time this is mandatory to specify one, because your SSL certificates are issued for a particular domain.

Then, as you see, we now have two virtual hosts in the configuration. The first is for the HTTP endpoint, and it only forces usage of HTTPS by leveraging of power of mod_rewrite. The other is for the HTTPS endpoint which looks pretty similar to the configuration we used to have before, the only difference is that it now uses our Let's Encrypt certificates (mind the paths, they should be changed).

Now save the changes and restart the Apache service:

systemctl restart httpd

That's it! By now you should be able to access your APEX applications using HTTPS protocol. No SSL configuration needed for Tomcat, ORDS or Oracle Database itself.

Making it indestructible

As you already know, APEX engine and applications metadata reside in the database. So, making your APEX environment reliable and fault tolerant basically means securing the database underneath.

And Oracle Database has quite many options here. However, not everything depends only on the RDBMS, because it is meaningless to try to achieve high degree of reliability if everything is you have is a single physical drive.

To make our environment more fault tolerant, we are going to configure two main things - redundancy and backups. Redundancy basically means having more than one copies of files, critical for database functionality, which would save our nerves if one of the copies gets corrupted. Backups, in their turn, would make it possible to restore and recover lost data in case of more severe media failure.

It is important to say that different copies of critical database files should reside on different physical drives. But these drives should be local to the database, since database constantly write there and performance of your instance depend on this.

Archived REDO logs and backups should also be placed on a physical drive different from the one where your datafiles are, but it is even better to place them on a different host. You can sacrifice performance here, because archiving and backing up processed do not affect your applications response time. For instance, you could attach an NFS or iSCSI volume to your system and use it in a transparent way like a local hard drive, whereas it would physically reside somewhere else.

There are other things, which you can have a look at in the official Oracle Administration guide. But even these two main steps done properly would make your database almost indestructible.

Setting up redundancy

By default Oracle Database 18c has this configuration in CentOS Linux:

Two copies of control files under oradata directory.
Three groups of one redo log file each.
NOARCHIVELOG mode.

We are going to multiplex the control files to a different drive, add there a member to each redo log group and then enable ARCHIVELOG mode.

Preparing directories

Our datafiles, redo log and control files reside in /opt/oracle/oradata directory by default. Assuming the fact that our second physical drive is mounted to /var/media, let's prepare a directory for copies of our critical database files:

mkdir -p /var/media/oracle/oradata/XE
chown -R oracle:oinstall /var/media/oracle

Notice the fact that I followed the default directory structure, which we have for the main location, but this is totally optional. I also gave ownership of the new directory to user oracle, because it needs full permissions on it.

Multiplexing control files

Official documentation recommends to have at least two copies of database control files, each stored on a different physical disk. Location of control files is stored in the server parameter file (or SPFILE), but this file must not be edited manually.

First, set the new paths by changing the corresponding instance parameter. For this, run sqlplus /nolog and connect to the database as sys.

-- connect as user SYS using bequeath method and enter your password
connect sys as sysdba
-- set the new value of the 'control_files' parameter 
alter system set control_files='/opt/oracle/oradata/XE/control01.ctl','/opt/oracle/oradata/XE/control02.ctl','/var/media/oracle/oradata/XE/control01.ctl','/var/media/oracle/oradata/XE/control02.ctl' scope=spfile;
-- shut the instance down and do not start it yet
shutdown immediate

Now copy the control files to the new location:

cp /opt/oracle/oradata/XE/*.ctl /var/media/oracle/oradata/XE/

And then start the database instance. Execute startup command for this as sys:

-- start the instance
startup

Done. Now the database uses four control files at a time - two on one hard drive and two on the other.

Multiplexing redo log files

The next step is to set up the second redo log files group on a different HDD. You can check the current status of your redo logs by querying the corresponding dynamic performance views:

select * from v$log;
select * from v$logfile;

As you see, you have three redo log groups of one file each of 200 MBytes by default. Let us add one more member into each group. For this, run the following commands in SQL*Plus as sys:

-- add one additional member on a separate disk to each redo log group
alter database add logfile member '/var/media/oracle/oradata/XE/redo01.log' to group 1;
alter database add logfile member '/var/media/oracle/oradata/XE/redo02.log' to group 2;
alter database add logfile member '/var/media/oracle/oradata/XE/redo03.log' to group 3;

You can notice that after adding the files they have INVALID status. This is normal, they become active (with blank status) on the first use. You can force it by invoking this command three times in a row in SQL*Plus as sys:

alter system switch logfile;

Ok, now the databases writes redo log records into two locations simultaneously. And this is what we wanted.

ARCHIVELOG mode on

By default, Oracle Database XE works in the NOARCHIVELOG mode. It means it is not protected from media failure and it would be impossible to fully recover database from a failure. Furthermore, even if we set up backups, it will be possible to restore the database only to the point of time when the backups were created.

But if we enable ARCHIVELOG mode, we will be able to recover our database to the most recent point of time, because in this case after restoring from a backup, it will be possible to recover the database to the current state using archived and current redo log files. Isn't this awesome?

To check the current status of database log mode run the following command in SQL*Plus as sys:

select log_mode from v$database;

As you see, by default the database works in NOARCHIVELOG mode. Now prepare a directory for archive log files. Since I don't have a different host for this, I will be using my second hard drive:

mkdir -p /var/media/oracle/oradata/XE/arch
chown -R oracle:oinstall /var/media/oracle

And then specify it as a destination directory for archive logs in SQL*Plus as sys:

alter system set log_archive_dest='/var/media/oracle/oradata/XE/arch' scope=both;

And check if it worked:

select * from v$archive_dest order by dest_id;

The first destination should be shown as VALID.
Now it's high time to enable the ARCHIVELOG mode. For this start SQL*Plus as user sys and run the commands as following:

shut immediate
startup mount
alter database archivelog;
alter database open;

Note that if this is not a fresh installation of yours, you will need to perform a full backup after this operation, because all the previously created backups are unusable after changing the log mode.

Let's check:

select log_mode from v$database;

Providing you see ARCHIVELOG, everything is ok. Now your database will archive redo log files to the specified destination.

Our database already looks quite reliable. But what's next?

Backups

To be even more confident, we need a backing up strategy for our datafiles. There are several options when it comes to backup an Oracle Database. They are divided into two general classes - physical backups (RMAN, user-managed tools) and logical backups (expdp/impdb, exp/imp, user-defined DDL/DML SQL scripts).

Here we are going to consider managing backups using Recovery Manager (RMAN), because this is the most powerful and officially recommended option by Oracle. RMAN is a perfect addition to the steps we already made in the previous chapters when started to archive redo log files.

RMAN allows you to back up literally everything related to your database, including data, parameter, control and archived redo log files. There is also a huge number of options of how to manage backups with RMAN - they could be written to different media, they could be backup sets or datafile images, the backups could be full or incremental, and if such, then differential or cumulative, and so on.

In this particular article we are going to consider an easy, but still quite a reliable case, and we are going to do so deliberately - our backing up strategy will be to have up to two current copies of full, not compressed backup sets for our data and control files. We are going for the not compressed option since it is more performant and less CPU loading. We are also not going to use recovery catalog, since it is a little bit of a overkill for us. We chose full backups, because incremental backups do not give many benefits for Oracle Database XE (a full backup takes not that much time). But you are welcome to change this configuration for your particular case as you wish.

To start with all this, prepare a directory for the backups (again, I will be using my second drive mount point, for you it could be different):

mkdir -p /var/media/oracle/oradata/XE/backup
chown -R oracle:oinstall /var/media/oracle

Then, create a file where we will write RMAN commands to perform the backing up operation:

cd /var/media/oracle
touch make_backup.rman
mcedit make_backup.rman

And put these lines to the file in the editor:

# This is an Oracle Recovery Manager script to create a full backup of all data and control files
# The script expects an established connection to a target database
# For local target database, run `connect target /` before running the script if you use it manually

set echo on
run
{
  # set device configuration to default (disk with parallelism of 1)
  configure device type disk clear;

  # set channel configuration to default and then specify the destination file format for them
  configure channel device type disk clear;
  configure channel device type disk format '/var/media/oracle/oradata/XE/backup/data_%U_%I_%T.bk';

  # enable backing up of control files and specify the destination file format for them
  configure controlfile autobackup on;
  configure controlfile autobackup format for device type disk to '/var/media/oracle/oradata/XE/backup/cf_%F.bk';

  # set retention policy to 2 backups at a time
  configure retention policy to redundancy 2;
  
  # delete archive logs after 2 backups
  configure archivelog deletion policy to backed up 2 times to device type disk;

  # show the configuration before start
  show all;

  # perform backing up
  backup check logical database;

  # perform crosscheck to sync metadata with backup media data
  crosscheck backup;

  # delete obsolete and lost (not on disk anymore) archived redo log and backup files
  delete noprompt obsolete;
  delete noprompt expired backup;
  delete noprompt archivelog all;
}

Then save changes. As you see, the script is quite self explanatory. First, we perform some preliminary configuration, then run the backup command, and finally erase not needed anymore files.

To run the script we are going to create a shell script:

cd /var/media/oracle
touch make_backup.sh
mcedit make_backup.sh

And put these lines into it:

# Shell script to perform RMAN backup using prepared commands file
# The script expects that environment is set for user `oracle`
# The scripts runs RMAN client as user `oracle`, connects to local database and executes the specified commands file
# The output is written to the specified log file

su - oracle -c "rman target / cmdfile /var/media/oracle/make_backup.rman > /var/media/oracle/make_backup_last.log"

As you see, our shell script runs the preliminary written RMAN commands file as user oracle. This is important, because only this user is allowed to use bequeath connection method to the database instance (by default).

Now we need to tell the system to run the script periodically. To do so, add this line to /etc/crontab file:

0  3  *  *  1  root  /bin/sh /var/media/oracle/make_backup.sh

Mind the fact that there must be a new line character at the end of a line in crontab. Otherwise, your task will not be executed.

This enables weekly backups at 3:00 AM every Monday.

That is it! To create a backup immediately, just run the script as user root as following:

. /var/media/oracle/make_backup.sh

Now, if a catastrophe occurs, the only two RMAN commands which you need in order to recover your database are restore database and recover database. You also have options to restore a particular datafile. Or even a particular corrupted block, however for this you will need Block Media Recovery, which is a paid option, unfortunately still not included with the XE.

Steps which we completed here are generally enough for most cases. But if you like to know more about other options, refer to the official Backup and Recovery documentation.

Final words

In this chapter of the guide I tried to cover security and reliability aspects of an Oracle APEX environment. It happened that APEX is a part of Oracle Database, which has many different options of how to make your applications almost immortal. Here we did it step-by-step, and this is like entering IDDQD to get the GOD mode in Doom.

Do not forget to share your ideas about in what ways this article could be improved. This was the final chapter of my detailed all-in-one guide on how to set up, configure and secure a production-like APEX, Oracle Database, ORDS, Tomcat and Apache environment on CentOS Linux. I hope you enjoyed the write-up and found it useful for you. Stay tuned and don't forget to check out other articles in my blog!

Previous Chapters

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

Hope this post was 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!

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

Denis Savenko — Fri, 16 Nov 2018 05:30:00 GMT

This is the third chapter of this all-in-one guide, covering some additional configuration of the environment. By now, you should already have a working installation of Oracle Database XE, APEX, ORDS, Tomcat and Apache httpd on your CentOS system without proceeding to these steps at all. But when it comes to a production environment, there are always security and performance issues which arise. And if you want to improve these aspects of your setup, keep reading.

SSHd tweaks

It is a good idea to disable direct remote root login. Users which need super user rights, should be able to escalate their rights instead using su command. To do so, edit the SSHd config and reload it:

mcedit /etc/ssh/sshd_config

Add a line as below to the config and save the file

PermitRootLogin no

Then, restart the service:

systemctl restart sshd

Apache httpd tweaks

First, disable the default welcome page by removing the corresponding config file:

rm -rf /etc/httpd/conf.d/welcome.conf

Then, add an additional configuration file 0-extra.conf in the etc/httpd/conf.d/ directory with the contents as below:

# additional apache httpd configuration
# add this to the end of /etc/httpd/conf/httpd.conf
# or put it in a separate file such as /etc/httpd/conf.d/0-extra.conf

# disable sensitive version info
ServerSignature Off
ServerTokens Prod

# enable compression of static content

     SetOutputFilter DEFLATE
     AddOutputFilterByType DEFLATE text/plain text/html text/xml text/css text/javascript


# enable client caching of static content

    ExpiresActive On
    ExpiresByType image/gif "access plus 7 days"
    ExpiresByType image/jpeg "access plus 7 days"
    ExpiresByType image/png "access plus 7 days"
    ExpiresByType text/css "access plus 7 days"
    ExpiresByType text/javascript "access plus 7 days"
    ExpiresByType application/javascript "access plus 7 days"
    ExpiresByType application/x-javascript "access plus 7 days"

This will enable traffic compression and client-side static files caching. On the other hand, it will disable displaying of the sensitive version data.

Tomcat tweaks

If you followed the steps from this guide, there's nothing to clean up in Tomcat. But, just to be sure, you can execute these command which are intended to remove all default Tomcat applications:

cd /usr/share/tomcat/webapps
rm -rf examples/*
rmdir examples
rm -rf sample/*
rmdir sample

Then, I noticed that despite the fact both tomcat and oracle-xe-18c start on the system startup, APEX does not properly work without restarting of tomcat. This happens because the tomcat service starts before the oracle-xe-18c. To fix this, we need to edit the Tomcat service systemd unit file:

mcedit /usr/lib/systemd/system/tomcat.service

There the Unit section should look like this:

[Unit]
Description=Apache Tomcat Web Application Container
After=syslog.target network.target oracle-xe-18c.service
Wants=oracle-xe-18c.service

Save the file after making changes and invoke this command to reload the config:

systemctl daemon-reload

Oracle XE tweaks

Now let's connect to the database using sqlplus /nolog command, clean it up and make some additional configuration. Notice the fact that we are going to use bequeath connection here, because we will need to restart the database instance once:

-- connect to the CDB database to perform system-wide configuration
connect sys as sysdba

-- memory parameters for the instance
-- notice the fact that we are using the maximum allowed memory size for XE
-- so, please, tweak these values in case you do not have such amounts of memory for the RDBMS (otherwise, your instance won't start)
-- also notice that we are using AMM (Automatic Memory Management)
alter system set memory_target=2G scope=spfile;
alter system set memory_max_target=2G scope=spfile;
alter system set sga_target=0 scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;

-- some recommended values for the maximum number of sessions, processes and job_queues
alter system set sessions=250 scope=spfile;
alter system set processes=500 scope=spfile;
alter system set job_queue_processes=100 scope=spfile;

-- restart database
shutdown immediate
startup

-- now change session to use PDB to configure other things
alter session set container = xepdb1;

-- anonymous user is not needed when we don't use XDB
alter user anonymous account lock;
    
-- dropping the demo schema
drop user hr cascade;

-- altering the default password policy (by default passwords will expire in 180 days)
alter profile default limit password_life_time unlimited;

-- creating a tablespace for our APEX workspaces
create tablespace apex datafile '/opt/oracle/oradata/XE/XEPDB1/apex.dbf' size 128M reuse autoextend on next 8M maxsize unlimited;
    
-- creating a schema for our APEX workspaces
create user apex identified by "YourPasswordHere" default tablespace apex temporary tablespace temp;
alter user apex quota unlimited on apex;
grant unlimited tablespace to apex;
grant create session to apex;
grant create cluster to apex;
grant create dimension to apex;
grant create indextype to apex;
grant create job to apex;
grant create materialized view to apex;
grant create operator to apex;
grant create procedure to apex;
grant create sequence to apex;
grant create snapshot to apex;
grant create synonym to apex;
grant create table to apex;
grant create trigger to apex;
grant create type to apex;
grant create view to apex;

exit

As you can see, I created a new schema apex in a new tablespace apex. I would recommend to use it for your APEX applications.

ORDS tweaks

The default connection pool settings in the ORDS configuration are too small. You'll have to experiment to see what settings are the best for your workload, but the following seem to work well:

mcedit /opt/oracle/ords/config/ords/conf/apex.xml

Find these parameters and set their values (or add these lines if they do not exist):

10
10
60

Restart Tomcat to take effect:

systemctl restart tomcat

Checking if everything works

I believe you are dying to open your browser and check how it works. Just do it!

APEX main page must be available on yourdomain.tld/ords address, APEX administration services on yourdomain.tld/ords/apex_admin respectively (where yourdomain.tld is your domain name or the server IP address):

Let's check the headers to be sure that caching and traffic compression work:

Everything looks wonderful!

And as you can see, we use the latest current version of APEX.

Conclusion

Surely, these are not all the steps which could be done. Consider also setting up SSL connection to your server, configuring backups, installing some monitoring tools and systems like influxdb+telegraf+grafana. If you like to know about these things, step further to the final part of the guide

But I am already can say that we successfully installed and secured a reliable, nicely working APEX environment on a CentOS Linux server. Please, provide me with your feedback in the comments and do not hesitate to add anything, which would be great to add to this guide. I would really appreciate it if you corrected my English if I misused some words or structures - I should excuse myself for it in advance.

Thank you very much for reading and I wonder if this series of blog posts would do any better to the great APEX community. Stay tuned for other guides and stories!

Next chapters

Here we started with some additional configuration, but if you are ready for SSL, redundancy and backups, you are welcome to check out the final part of the guide/

Here is the link for your convenience:

Part 4. Setting up SSL, redundancy and backups (optional)

Previous chapters

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

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

Denis Savenko — Fri, 16 Nov 2018 05:20:00 GMT

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:

Database/Database Technology Index/Database Express Edition/Downloads - you will need the package for Linux x64 and the preinstall RPM package (for release 7 of RHEL or CentOS) from there.
Developer Tools/Oracle REST Data Services/Downloads
Developer Tools/Application Express/Downloads

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

    # 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
    
        ProxyPass "ajp://localhost:8009/ords"
        ProxyPassReverse "ajp://localhost:8009/ords"

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!

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

Denis Savenko — Fri, 16 Nov 2018 05:10:00 GMT

In this chapter of the guide we will be covering the installation and configuration process of CentOS Linux on our server (it could be on-prem or VPS - doesn't matter). Then we will prepare our setup a bit for Oracle software stack.

Installation of CentOS Linux

This chapter is here for Linux newcomers, so, if you think you are qualified enough to install the system on your own, be free to skip this chapter totally.

But in case you want to be sure you won't be doing anything wrong, proceed following my recommendations - I tested them myself at least twice - when installed the system on my server and then when prepared this post.

You can obtain the distribution from the CentOS project official website. Any option of the distro will be suitable, because we are going to set up the minimum install. In the examples below I will be using a DVD-iso version. After you download the disk image, you will need to prepare a bootable CD or flash drive (preferred) with it. I won't stop on how to make one, but if you're stuck, there's even an official instruction for you. Only one thing here - do not use Unetbootin for this, because many people report some problems during installation process when after using this stuff - installation just freezes somewhere in the middle. I myself used rawrite32, but there are also other options like dd, dd for windows or Win32 Disk Imager.

Ok, now, when you're ready with your bootable media, let's start the system with it already. After doing so, Anaconda installer should greet you:

After its loading and choosing the language, you're ended up on the Installation Summary screen:

Here is the first tip - it is a good idea to set up a network connection first, because it'll help a bit with the further installation. Choose Network & Host Name in the menu, toggle the connection On and then click the Configure... button in the right bottom corner, here choose IPv4 Settings tab and now you're able to choose DHCP or add a static IP address if needed:

If everything is ok, you should see something like this:

Now we are ready to set up everything else. Let's start with date and time. Go back to the Installation Summary screen and choose Date & Time in the menu. On the next screen set your region and city which in its place will set a correct timezone for you, and switch the Network Time on. Everything else will be set up for you automatically because you should already have an Internet connection.

Then keyboard settings. Click Keyboard in the menu. For many users default settings will be ok here, but if you, like me, need an additional layout, just add it. Then configure layout switching options using the Options button. Yes, I'm from dark ctrl+shift side.

If you need support for a different language, click Language Support in the main menu and add the desired languages.

Under the Installation Source menu item you will see that you're installing the system from local source. It is totally ok and nothing should be done here. Now, just check that you are installing the Minimal Install option under the Software Selection menu item. Everything else we need we are going to install manually.

Now the most interesting part - disk partitioning. Click the Installation Destination menu item and select the desired hard disk. If you have only one, it is checked by default. Now I'd recommend you to select I will configure partitioning in order to be able to partition the disk manually. Click Done then.

On the next screen you're invited to choose your partitioning schema. I prefer clicking the link Click here to create them automatically to pre-create the schema. When you're done, you see a screen like this one.

The size of the partitions depend on the amount of RAM in your computer and on the size of the hard drive respectively. As you can see, by default CentOS 7 offers you a standard partition type for /boot and an LVM-based root partition, both of xfs filesystem. I'm quite ok with this, but if you like, you can choose a standard partition type for your root partition, or a filesystem, different to xfs (for example, ext4, which is also very reliable). Advantage of using LVM-based partitions is the fact they could be easily extended with another physical drive (your logical drive will be the same) or even shrunk (though not on xfs filesystem, for this you will need, for example, ext4). However, LVM is not supported on a boot partition. The only things that I changed in this standard partitioning schema were the name of the Volume Group and the swap size.

Note the fact that Oracle Database XE requires at least 2GB of swap size and recommends the swap size doubles the size of the RAM available.

Look through the summary when you click the Done button and then click Accept Changes.

Click the Begin Installation button to start the installation process finally. During the installation you will be prompted to set the root user password. I'd recommend to set a long enough but easy to remember password, something like WithGreatPowerComesGreatResponsibility or YouKnowNothingJohnSnow.

Click the Reboot button when the installation ends and remove your installation media.

Congratulations! CentOS Linux should be properly installed on your machine now and we are ready to install and configure the needed software.

Updating the operation system

It is a good idea to update your system regularly. Assuming something could change from time the distribution was released, we need to update the system after first start.

yum is the powerful and standard package manager for CentOS Linux. I recommend using it everytime you are installing or deinstalling software, because it keeps a reliable journal of all installations and can automatically resolve all dependencies when you install or deinstall software.

So, to make your system up-to-date execute this:

yum upgrade -y

Now we are ready to install software.

Installing prerequisites and some useful utils

To install some pre-requisites useful software crucial for comfortable usage of your system, execute these commands:

yum install java-1.8.0-openjdk.x86_64 java-1.8.0-openjdk-devel.x86_64 mc net-tools.x86_64 htop iotop iftop unzip wget epel-release -y
yum install rlwrap -y

A few words about the packages installed:

java-1.8.0-openjdk.x86_64 and java-1.8.0-openjdk-devel.x86_64 are Java 8 Development Kit packages, which are needed for Tomcat and ORDS to operate.

Installing Java from official repository using yum should set all the needed environment variables. But you can check it by executing the command java -version and if it works, everything is ok. But if not, then correctly set the $PATH and $JAVA_HOME variables.

mc - Midnight Commander - a really powerful file manager similar to Norton Commander or FAR Manager.
net-tools.x86_64 - useful and customary network utilities such as ifconfig and netstat. The thing is in CentOS 7 they replaced these usual utilities with ip and ss commands, so to be able to use ifconfig it should be installed manually.
htop - a very good alternative to standard top utility. It is much more powerful and can be flexibly configured.
iotop - input/output read/write monitoring utility. Very useful to check disk problems when using a database.
iftop - an utility to monitor network bandwidth.
unzip - unzip utility.
wget - a command-line utility to fetch files from the remote source (HTTP or FTP-host for example).
epel-release - an additional repository for CentOS with loads of useful software, called EPEL (stands for Extra Packages for Enterprise Linux).
rlwrap - a command-line wrapper utility, irreplaceable thing when it comes to use sqlplus on Linux. Always use rlwrap sqlplus instead of just sqlplus to see the difference. These utils installs from EPEL repository.

Initial system configuration

Network time synchronization

There's an utility called chrony for this purpose in the minimal CentOS installation:

systemctl start chronyd
systemctl enable chronyd

Disabling SELinux on CentOS 7

Now, we need to disable selinux. Its configuration and usage is a topic for a different series of blog posts, so here we'll just omit all this.
Type this command:

mcedit /etc/sysconfig/selinux

And change the value SELINUX=enforcing to SELINUX=disabled, then save the config file. After doing this, execute this to disable selinux in the runtime:

setenforce 0

Configuring the firewall

CentOS 7 uses firewalld as a main firewall service, which is an additional abstraction level above iptables. In many other guides you could see people disabling it and returning to use iptables directly. I don't know why, maybe because it always hard to pick up something new. And so, I tried, and really liked firewalld ease of configuration (especially in comparison to iptables if you're new to it).
To configure the firewall, we are going to do these things:

Add a new service called oracle-db.
Set the new service description and add the port to it.
Enable such services as http, https and oracle-xe for the default zone public.
Reload firewall list of rules on-the-fly.
To do this all, execute the commands below:

firewall-cmd --permanent --new-service=oracle-db
firewall-cmd --permanent --service=oracle-db --set-short="Oracle Database Listener" --add-port=1521/tcp
firewall-cmd --permanent --zone=public --add-service=http
firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --permanent --zone=public --add-service=oracle-db
firewall-cmd --reload

Now we are ready to install the Apache Tomcat and httpd.

Installing of Apache Tomcat and Apache httpd

To install both the application and the web server we will be using yum, because it is the safest and the easiest way I know so far. You're still able to manually download and install them, but this is not the aim of this guide. Only one thing here - it is assumed that you have completed all the steps from above (installing JDK in particular) before proceeding with the installation.

So, in order to install Tomcat and httpd run this:

yum install tomcat httpd -y

This will install Tomcat 7 and Apache httpd 2.4 on your system. The 7th version of Tomcat is more than enough for us. Then, to start the services and to start them on startup, let's do the next:

systemctl start tomcat
systemctl enable tomcat
systemctl start httpd
systemctl enable httpd

That's it! By now both Tomcat and httpd should work on your system and should listen ports 8080 and 80 respectively on your server. Note the fact that we intentionally didn't open port 8080 on our server, because we are not going to use it. Instead, httpd will reverse proxy all requests to Tomcat using AJP protocol listener on port 8009.

You even may disable the HTTP connector on port 8080 in Tomcat's server.xml config (this is very optional). I will not tell you how to do this, consider it an exercise. Do not forget to restart the Tomcat service afterwards in case you already opened your mc file manager.

Next Chapters

Here we're done with the operating system and software installation and initial configuration. Our further steps will be to install Oracle software and then to configure it properly.

Here are the links for your convenience:

Previous Chapters

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

Introduction Part. APEX environment architecture and its components

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

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

Denis Savenko — Fri, 16 Nov 2018 05:00:00 GMT

With this introduction post I am starting to write a series of blog posts about how to install the full production environment of Oracle Application Express aka APEX on a CentOS Linux 7 server. Usual APEX production environment involves such components as the OS itself, Apache httpd (or another web server), Apache Tomcat (or another supported application server), ORDS (Oracle Rest Data Services), APEX engine and Oracle Database. If you need something like this for your experiments or a startup, you're at the right place.

Preamble

This series of blog posts assume you're familiar with Oracle software stack (database, APEX, ORDS) at least to a certain extent, but that you're new to Linux and installing all this on this OS.
Then I can openly say that this guide was inspired by well-done made almost the same guide by Morten Braten, although it was about the previous and sometimes outdated versions of the involved software and there are some specialties which I am covering in this guide. Frankly speaking, I personally sometimes used the Morten's guide when was installing and configuring the software for the first time.
There's also a bunch of scripts developed by Martin D'Souza and company which could help you automatically install all the needed software, however this is not the topic of this guide.
The main idea of the current guide is not just to help you with how to install this or that piece of software, but also to comment on each action and to explain why we need it.

Closer look at components

On the schema above (it's clickable) I wanted to show the role of each of the components in our setup. This setup was chosen intentionally, because it consists only of free software and can be obtained by anybody. Now we are going to stop on each component in detail.

CentOS Linux 7

I think it is redundant to explain why we need an operating system. Of course we need one, because without it our server is just a pile of metal. A more interesting question is why CentOS. And I have an answer - because it is a community driven version of a very reliable RPM-based commercial linux distribution called RHEL (Red Hat Enterprise Linux). Each time a new version of RHEL comes out, they have to share their source code to public (due to license limitations), and contributors of CentOS take this new version, re-brand, compile and distribute it for free (CentOS stands for Community enterprise Operating System). There's also another option - Oracle Linux, which developers do almost the same, but the CentOS community seems to be way bigger and it's much easier to find an answer on your question when you're stuck somewhere.

An RPM-based distribution, in its place, was chosen because Oracle distributes their express edition of the RDBMS only as the RPM-package. And to minimize potential problems with the setup, I went for CentOS (though it is still possible to install it on a Debian-based system or even on FreeBSD).

Oracle Database XE

APEX engine lives inside the Oracle Database. It's available for free and can be installed into its Express Edition as well, which is a totally free option. Current version of the RDBMS is 18c, and we are going to install exactly this release. Despite the fact that this is only a limited free version of the RDBMS, it offers a lot of great features, which were usually included only with the Enterprise Edition of it, and you are welcome to use them to your benefit.

APEX

APEX or Application Express is a low-code web development platform. Quoting the official website, it enables you to design, develop and deploy beautiful, responsive, database-driven applications using only your web browser. It is a free Oracle Database feature. APEX needs a web listener to function - and there are three options available at the moment:

Embedded PL/SQL Gateway (EPG) - it is a built-in Oracle XDB feature. It is not recommended to be used in production environments, because it's not as fast and as reliable as other options. Though it surely could be used for development and testing purposes.
mod_plsql with Oracle HTTP Server - this is an obsolete option which is not recommended to be used anymore.
Oracle Rest Data Services (ORDS) - the option which is officially recommended for production use by Oracle and the one we will be observing here.

ORDS

ORDS is a Java EE-based web application which can run in standalone mode or could be deployed to an application server such as Oracle WebLogic, GlassFish or Apache Tomcat. When run in standalone mode, it leverages a built-in web server powered by Jetty.
Besides being a listener for APEX, ORDS could be used to implement RESTful APIs for your databases. And by saying databases I mean relational databases, document stores and even Oracle NoSQL Database.

Apache Tomcat

The Apache Tomcat software is an open source implementation of the Java Servlet, JavaServer Pages, Java Expression Language and Java WebSocket technologies. It is a really powerful and fast application server, which is totally free piece of software and is community driven. At the time it is the most commonly used application server for Java applications.
In our setup it is going to be used because of ability to be flexibly configured and for security reasons. It is also much more convenient to use Tomcat service instead of standalone mode of ORDS.

Apache httpd

Apache httpd is a standard de-facto when in comes to HTTP server software. I think it is extra to tell more about it. We are going to use it because it gives us even more freedom in configuration and because Apache httpd is the fastest solution when it comes to static files such as pictures, style sheets and so on. In our setup it will serve APEX static files and will be reverse-proxying requests to ORDS deployed to Tomcat using the special AJP protocol, which eliminates HTTP-like overhead.

Next chapters

And this is where I wanted to stop with the introduction part. In the next chapters we're going to start with OS installation and then proceed to everything else.

Be free to leave your comments on the above and stay tuned for the next chapters!

Here are the links for your convenience:

Read-Write APEX application fully based on alien data or is it mandatory to use exactly Oracle Database?

Denis Savenko — Mon, 12 Nov 2018 15:52:52 GMT

I am sometimes asked by my fellows whether it is possible or not to base an APEX application on some foreign database, in example PostgreSQL or MySQL. I always answer positively, but so far didn't have a chance to explore the topic in detail. In this article I am going to show you at least two possible ways of how to do it right now.

The aim

We are going to develop an APEX application inside an Oracle Database 18c XE and base it on data provided by an alien database. In our case it is going to be PostgreSQL (but it doesn't have to). On top of PostgreSQL we are going to organize a REST interface. Then, by leveraging Oracle Database and APEX capabilities to consume REST interfaces, we are going to implement CRUD functionality over two simple departments and employees tables, using two different approaches.

So, in the end, our application is going to be able to read and write data from two native PostgreSQL tables, and represent them as interactive reports.

Before we start

In the article I am going to consider a setup with two separate hosts - the first with Oracle Database and APEX and the second with PostgreSQL. In my case they both are going to be virtual machines powered by CentOS Linux 7.5 and resided in the same local network area. IP addresses are 192.168.88.30 and 192.168.88.31 for the Oracle and Postgres hosts respectively.

However, you don't have to go for the same setup and install software on different hosts, it is done here to make the post more general, and you should be quite ok with Oracle Database and PostgreSQL installed on the same machine. In such a case it would even work faster since there would be no network involved.

Also assume that all the commands in the article are run as a root user.

PostgreSQL side

As I already mentioned, as an alien database, where our data is going to be, we will be using PostgreSQL. And we need REST interface on top of it. There are several solutions for this, such as PostgREST or pREST, which are open-sourced standalone tools written in Haskell and Go correspondingly. Both tools give their users a fully functional REST representation of SQL on top of a database, so no particular handlers needed. This is very similar to what we see when use ORDS REST enabled SQL feature. I stopped on PostgREST, which I found quite reliable judging by its users feedback.

Installation of PostgreSQL

Install EPEL and official PostgreSQL repos:

yum epel-release -y
rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

Install PostgreSQL 10 using yum:

yum install postgresql10-server postgresql10-contrib postgresql10 -y

Initialize database and enable PostgreSQL daemon:

postgresql-setup initdb
systemctl start postgresql-10
systemctl enable postgresql-10

Create a new database user which will own our tables:

su - postgres
createuser --interactive

Enter name of role to add: restapi
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

createdb restapi
exit

Edit your PostgreSQL Client Authentication Configuration File:

mcedit /var/lib/pgsql/10/data/pg_hba.conf

And enable password authentication (make sure the file looks like below), then save changes:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
"local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Restart PostgreSQL server:

systemctl stop postgresql-10
systemctl start postgresql-10

Now we are ready to create our tables and load some sample data. For this, download the file I prepared for you and, being in the same directory with the file, load it to the database using psql:

psql -U restapi -f postgresql-data.sql

Installation of PostgREST

Download PostgREST binaries from GitHub and extract them to /usr/bin

wget https://github.com/PostgREST/postgrest/releases/download/v5.1.0/postgrest-v5.1.0-centos7.tar.xz
tar -Jxf postgrest-v5.1.0-centos7.tar.xz -C /usr/bin

Daemonize your PostgREST instance. Instead of doing all this you can run it manually.

Create PostgREST configuration file:

mkdir -p /etc/postgrest
touch /etc/postgrest/config

Put some configuration into it (more here):

db-uri = "postgres://restapi:restapi@localhost:5432/restapi"
db-schema = "public"
db-anon-role = "restapi"
db-pool = 10

# listen on all interfaces
server-host = "0.0.0.0"
# listen on port 8000
server-port = 8000

Create a file for new systemd module for PostgREST service:

touch /usr/lib/systemd/system/postgrest.service

Edit it and add the following

[Unit]
Description=REST API for any Postgres database
After=postgresql-10.service

[Service]
ExecStart=/bin/postgrest /etc/postgrest/config
ExecReload=/bin/kill -SIGUSR1 $MAINPID

[Install]
WantedBy=multi-user.target

Enable and start PostgREST service:

systemctl enable postgrest
systemctl start postgrest

Tweak firewalld to allow connections to PostgREST:

firewall-cmd --permanent --new-service=postgrest
firewall-cmd --permanent --service=postgrest --set-short="PostgREST" --add-port=8000/tcp
firewall-cmd --permanent --zone=public --add-service=postgrest
firewall-cmd --reload

Testing of the environment

Ready to test it? Just open http://your-ip-address-here:8000 in your browser. In my case it's http://192.168.88.31:8000. If you see generated Swagger docs, then everything is alright. Otherwise, look into /var/log/messages where PostgREST writes its logs by default for the reason it doesn't work.

As an alternative, you also can use Postman to test your freshly implemented REST API server.

Oracle Database and APEX side

Be aware of the fact that I am assuming that you already have a running APEX environment. If you don't, then you are welcome to use my very detailed guide on how to create one.

But you also should know that I am going to use some features only available in the newest versions of Oracle Database (working with JSON in PL/SQL) and APEX (APEX_EXEC package). It's quite safe to go for Oracle Database 18c XE and Oracle APEX 18.2, which are currently available to download.

You also should allow outgoing connections for the APEX internal user by creating a proper ACL. This is needed to be able to make REST calls out from APEX applications. For APEX 18.2 this user is called APEX_180200, so to allow outgoing connections without restrictions by host, run this as SYS:

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;
/

Then, to start with the application development, choose a workspace or create a new one and then create a new application in it, all settings could be left in their defaults.

And from now on we'll be considering different approaches to fulfil the task.

Declarative way

At the moment I am writing this post APEX support declarative capabilities for Web Source Modules only partially.

But the functionality is already on the roadmap, so very soon I'll be rewriting this section of the post in order to tell you how to do everything in a declarative way.

Semi-declarative or APEX_EXEC way

This approach consists of three main steps to perform:

Define a Web Source Module under Shared Components for the application.
Add an Interactive Report region onto the application's page, and base it on top of just created Web Source declaratively.
Use the power of APEX_EXEC package to implement writing procedures for the report programmatically.

Let's sort them out in detail.

Definition of Web Source

First of all, go to Shared Components from AppBuilder home page and choose Web Source Modules.

Click Create button and choose From Scratch, then click Next.

Leave Simple HTTP for the Type, since we are not going to use ORDS-based API, specify the Name and the URL Endpoint for the service. In our case they're going to be ALIEN_DEPARTMENTS and http://192.168.88.31:8000/departments respectively.

Leave everything by default on the following two screens, click Next button two times, and then click Discover.

If everything is ok, you'll see the preview screen. Check it out and click the Create Web Source button.

When the module is created, click on it to expand the details. You can check its data profile by clicking on Edit Data Profile button, but since our table is pretty simple, APEX likely to guess all the datatypes correctly without our help. What we need to do is add other operations for our service. To do so, click the Add Operation button.

First, we are going to add the POST operation. It represents the INSERT database operation in PostgREST. So, put dot symbol for URL Pattern, choose POST for the HTTP Method and Insert row for Database Operation. For Request Body Template use this JSON string: {"name":"#NAME#", "location":"#LOCATION#", "country":"#COUNTRY#"}.
Mind how I used placeholders for parameters. Then for each placeholder we need to add a parameter. Also, there's important to add a header parameter for Content-Type. As a result, you should have something like this.

Similarly, we should add PATCH operation for the UPDATE database operation in PostgREST. The difference is in one addtional query string parameter department_id, which specifies what row to update.

Last operation to add here is the DELETE, which serves the same database operation in PostgREST. Here we don't have to specify any parameters, but the only query string parameter department_id, which tells what row to delete.

When you complete these steps, your Web Source Module metadata is succesfully saved in the application and now you can use it for report regions and for APEX_EXEC procedures calls.

The code

Now I'd like you to switch to your favourite PL/SQL IDE and create a package with a set of procedures to work with the web service we have just created. Let's call this package ALIEN_DATA_MANAGEMENT and compile it as a workspace parsing schema user.

Package specification:

create or replace package alien_data_management is

procedure add_department(
  p_name varchar2,
  p_location varchar2,
  p_country varchar2  
);

procedure update_department(
  p_department_id integer,
  p_name varchar2,
  p_location varchar2,
  p_country varchar2  
);

procedure delete_department(
  p_department_id integer
);

end alien_data_management;
/

Package body:

create or replace package body alien_data_management is

procedure add_department(
  p_name varchar2,
  p_location varchar2,
  p_country varchar2  
) is
  -- parameters collection variable
  l_parameters apex_exec.t_parameters;
begin
  -- prepare the parameters for the Web Service operation
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'NAME',     p_value => p_name);
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'LOCATION', p_value => p_location);
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'COUNTRY',  p_value => p_country);

  -- invoke POST operation, defined in the Web Service definition
  begin
    apex_exec.execute_web_source(
        p_module_static_id => 'ALIEN_DEPARTMENTS',
        p_operation        => 'POST',
        p_parameters       => l_parameters
    );
  -- we are handling VALUE_ERROR exceptions because of 
  -- weird ORA-06502: PL/SQL: numeric or value error
  -- after a successful PostgREST API call
  -- more here: https://community.oracle.com/message/14988842
  exception when VALUE_ERROR then
    null;
  end;
end add_department;

procedure update_department(
  p_department_id integer,
  p_name varchar2,
  p_location varchar2,
  p_country varchar2  
) is
  -- parameters collection variable
  l_parameters apex_exec.t_parameters;
begin
  -- prepare the parameters for the Web Service operation
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'department_id', p_value => 'eq.'||to_char(p_department_id));
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'NAME', p_value => p_name);
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'LOCATION', p_value => p_location);
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'COUNTRY', p_value => p_country);

  -- invoke POST operation, defined in the Web Service definition
  begin
    apex_exec.execute_web_source(
        p_module_static_id => 'ALIEN_DEPARTMENTS',
        p_operation        => 'PATCH',
        p_parameters       => l_parameters
    );
  -- we are handling VALUE_ERROR exceptions because of 
  -- weird ORA-06502: PL/SQL: numeric or value error
  -- after a successful PostgREST API call
  -- more here: https://community.oracle.com/message/14988842
  exception when VALUE_ERROR then
    null;
  end;
end update_department;

procedure delete_department(
  p_department_id integer
) is
  -- parameters collection variable
  l_parameters apex_exec.t_parameters;
begin
  -- prepare the parameters for the Web Service operation
  apex_exec.add_parameter(p_parameters => l_parameters, p_name => 'department_id', p_value => 'eq.'||to_char(p_department_id));

  -- invoke POST operation, defined in the Web Service definition
  begin
    apex_exec.execute_web_source(
        p_module_static_id => 'ALIEN_DEPARTMENTS',
        p_operation        => 'DELETE',
        p_parameters       => l_parameters
    );
  -- we are handling VALUE_ERROR exceptions because of 
  -- weird ORA-06502: PL/SQL: numeric or value error
  -- after a successful PostgREST API call
  -- more here: https://community.oracle.com/message/14988842
  exception when VALUE_ERROR then
    null;
  end;
end delete_department;

end alien_data_management;
/

Ok, let's have a closer look at the package and its procedures.

The add_department procedure's purpose is obviously to insert a new department into the table in the alien database. As you can see, before invoking the web service POST operation, we prepare parameter values leveraging special apex_exec.add_parameter API. This function has a set of overloads to accept values of different data types. Then, the only thing which is done in the procedure, is the apex_exec.execute_web_service call. Yes, it's that easy.

The next, update_department procedure is different from the previous one only in two aspects - it has an additional parameter department_id and the type of operation being performed here is PATCH. This tells PostgREST to transform our call into the UPDATE SQL statement instead of INSERT.

The delete_department procedure looks even simplier, because it has only one parameter - department_id. Also, the web service operation which is used here is DELETE, which reflects DELETE SQL statement in the remote database.

Right, now we are ready to start with our interactive report and a form on it.

The report and the form

Go back to AppBuilder and edit your home page. Add an Interactive Report region to the Content Body section. Give it a proper name and base it on the Web Source we created before. You can rename the columns of the report as you wish their names to be.

Next we need to create a new Modal Dialog page which we are going to use to add a new row in our table. Create it and place a Static Content region on the page. Then add three Text Fields items (for the table attributes) and a Save button onto this region.

And finally, the most interesting part. In order for data to be saved, we need to add a PL/SQL process, which will invoke our add_department procedure when a user presses the Add department button. To fulfil our task, we need to add this code as the process source:

alien_data_management.add_department(
  p_name => :P201_NAME,
  p_location => :P201_LOCATION,
  p_country => :P201_COUNTRY
);

Assuming we did all this, our page should look as follows.

Ok, our Add a new department page is ready. Now it's time to make the same page for updating and deleting the existing rows. For this, you can copy the page we just created and rename it. Then, add a new Display Only item for department_id attribute, and instead of Add department there should be Save Changes and Delete department buttons.

To go further, let's make two processes on this page - one for the UPDATE operation, and one for DELETE. Mind the server-side condition - a process should run only when a corresponding button is pressed.

For the Save Changes button process we need this code to be run:

alien_data_management.update_department(
  p_department_id => :P200_DEPARTMENT_ID,
  p_name => :P200_NAME,
  p_location => :P200_LOCATION,
  p_country => :P200_COUNTRY
);

When for the Delete button process it should be:

alien_data_management.delete_department(
  p_department_id => :P200_DEPARTMENT_ID
);

Save the page and check.

Well, we are now ready to link the home page with our form pages. For this, go back to the Home Page in AppBuilder and add a button to open the Add Department page. As a target, choose redirection to the corresponding page.

To link the Home Page with the Update Department page, let's add a link attribute for our report.

Then, we should specify the needed page number to redirect and don't forget to pass the current column values as field values.

Testing

If you did everything right, you are now ready to test the application. And the first thing you're going to see when you run the app, will be our interactive report based on data from alien PostgreSQL database.

Let's compare with what we have in the database:

# psql -U restapi
Password for user restapi:
psql (10.5)
Type "help" for help.

restapi=> select * from departments;
 department_id |            name             |  location   |    country
---------------+-----------------------------+-------------+---------------
             1 | Product Support             | Tanquecitos | United States
             2 | Electronic Data Interchange | Sugarloaf   | United States
             4 | Transportation              | Grosvenor   | United States
             5 | Finance                     | Riverside   | United States
             3 | Legal                       | Dale City   | United States
(5 rows)

Ok, looks familiar, but let's add a row.

After clicking on the Add department button, we see our new row in the report.

But maybe I tricked you and this row wasn't actually sent to the foreign database? Let's check it:

restapi=> select * from departments;
 department_id |            name             |  location   |    country
---------------+-----------------------------+-------------+---------------
             1 | Product Support             | Tanquecitos | United States
             2 | Electronic Data Interchange | Sugarloaf   | United States
             4 | Transportation              | Grosvenor   | United States
             5 | Finance                     | Riverside   | United States
             3 | Legal                       | Dale City   | United States
            38 | IT                          | Omsk        | Russia
(6 rows)

Seems I didn't trick you and our row is there! As a homework, test the UPDATE and DELETE operations on your own.

It could be harsh, but I tried to show you that it's not hard to make an application, capable to consume data from a foreign database and write it back there, even now, and this can be done in an almost declarative way.

Programmatic or APEX_WEB_SERVICE way

In this section I am going to tell you about an alternative, lower-level approach to achieve the same results. This time we are going to organize same interface on top of the employees table.

This approach doesn't require any Web Source Module defined under Shared Components for the application. Instead of this, we are going to perform these steps:

Implement CRUD functionality in our PL/SQL package by leveraging of APEX_WEB_SERVICE API.
Create another Interactive Report and base it on a local PL/SQL pipelined function.
Implement writing capabilities of the report using APEX processes invoking the other PL/SQL package procedures.

I also should warn you that in this chapter I am not going to describe things in as detailed way as I did it before. So, my aim here is to highlight main differences in the approaches.

Additionally, I assume that we will be using the same package we created in the previous chapter. Ok, let's get it started.

The code

So, we are going to base our Interactive Report on top of a pipelined function. One of the easiest ways to implement this function is to invoke a GET request by leveraging of APEX_WEB_SERVICE.MAKE_REST_REQUEST API, get the data in JSON format and then transform it to a relational view by using of the JSON_TABLE function, which is there for us since 12c.

But first we need to define a set of data type for the function result set and the function itself in the package specification:

type t_employee is record (
  employee_id integer,
  department_id integer,
  name varchar2(50),
  email varchar2(255),
  cost_center integer,
  date_hired date,
  job varchar2(255)
);

type tbl_employees is table of t_employee;

function get_employees return tbl_employees pipelined;

Now, let's define its body:

function get_employees return tbl_employees pipelined
is
  E_NO_MORE_ROWS_NEEDED exception;
  pragma exception_init(E_NO_MORE_ROWS_NEEDED, -6548);
  l_response clob;
begin
  -- REST request to get JSON data from alien database
  l_response := apex_web_service.make_rest_request(
    p_url => 'http://192.168.88.31:8000/employees',
    p_http_method => 'GET'
  );

  -- converting JSON data to relational view
  -- and returning it as result set
  for x in (
    select
      employee_id,
      department_id,
      name,
      email,
      cost_center,
      date_hired,
      job
   from json_table(
      l_response,
      '$[*]' columns (
        employee_id integer,
        department_id integer,
        name varchar2(50),
        email varchar2(255),
        cost_center integer,
        date_hired date,
        job varchar2(255)
      )
    )
  ) loop
    pipe row(
      t_employee(
        x.employee_id,
        x.department_id,
        x.name,
        x.email,
        x.cost_center,
        x.date_hired,
        x.job
      )
    );
  end loop;

  return;
-- APEX reacts inadequately on `NO MORE ROWS NEEDED` exception
-- and generates `404 Not Found` error if not suppress it
-- more here: https://community.oracle.com/thread/4076787
exception when E_NO_MORE_ROWS_NEEDED then
  null;
end get_employees;

As you can see, at the beginning the function calls the corresponding API endpoint (http://192.168.88.31:8000/employees in our case) using the GET HTTP method and writes the response to a variable. Then, using power of JSON_TABLE function, it translates JSON-formatted data into relational and returns it as a result.

Also keep in mind the fact that it is important to suppress the NO MORE ROWS NEEDED exception, which a pipelined function could generate if all data to return cannot be placed on one page of a report. Otherwise, APEX engine will generate a 404 Not Found exception. This is a known issue.

Now we are going to define the writing procedures, first in the package specification:

procedure add_employee(
  p_department_id integer,
  p_name varchar2,
  p_email varchar2,
  p_cost_center integer,
  p_date_hired date,
  p_job varchar2
);

procedure update_employee(
  p_employee_id integer,
  p_department_id integer,
  p_name varchar2,
  p_email varchar2,
  p_cost_center integer,
  p_date_hired date,
  p_job varchar2
);

procedure delete_employee(
  p_employee_id integer
);

And then in the package body:

procedure add_employee(
  p_department_id integer,
  p_name varchar2,
  p_email varchar2,
  p_cost_center integer,
  p_date_hired date,
  p_job varchar2
) is
  l_json_body json_object_t := json_object_t();
  l_response clob;
begin
  -- setting HTTP headers before the REST API call
  apex_web_service.g_request_headers(1).name := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8';

  -- preparation of the body
  -- put method of the json_object_t type accepts various data types
  l_json_body.put(key => 'department_id', val => p_department_id);
  l_json_body.put(key => 'name', val => p_name);
  l_json_body.put(key => 'email', val => p_email);
  l_json_body.put(key => 'cost_center', val => p_cost_center);
  l_json_body.put(key => 'date_hired', val => p_date_hired);
  l_json_body.put(key => 'job', val => p_job);

  -- REST request to alien database to perform insert operation
  l_response := apex_web_service.make_rest_request(
    p_url => 'http://192.168.88.31:8000/employees',
    p_http_method => 'POST',
    p_body => l_json_body.to_clob()
  );

end add_employee;

procedure update_employee(
  p_employee_id integer,
  p_department_id integer,
  p_name varchar2,
  p_email varchar2,
  p_cost_center integer,
  p_date_hired date,
  p_job varchar2
) is
  l_json_body json_object_t := json_object_t();
  l_url varchar2(200);
  l_response clob;
begin
  -- setting HTTP headers before the REST API call
  apex_web_service.g_request_headers(1).name := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8';

  -- preparation of the body
  -- put method of the json_object_t type accepts various data types
  l_json_body.put(key => 'department_id', val => p_department_id);
  l_json_body.put(key => 'name', val => p_name);
  l_json_body.put(key => 'email', val => p_email);
  l_json_body.put(key => 'cost_center', val => p_cost_center);
  l_json_body.put(key => 'date_hired', val => p_date_hired);
  l_json_body.put(key => 'job', val => p_job);

  -- prepare the URL
  -- have not found a better way to specify query string parameters
  l_url := 'http://192.168.88.31:8000/employees?employee_id=eq.'||to_char(p_employee_id);

  -- REST request to alien database to perform update operation
  l_response := apex_web_service.make_rest_request(
    p_url => l_url,
    p_http_method => 'PATCH',
    p_body => l_json_body.to_clob()
  );

end update_employee;

procedure delete_employee(
  p_employee_id integer
) is
  l_url varchar2(200);
  l_response clob;
begin
  -- setting HTTP headers before the REST API call
  apex_web_service.g_request_headers(1).name := 'Content-Type';
  apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8';

  -- prepare the URL
  -- have not found a better way to specify query string parameters
  l_url := 'http://192.168.88.31:8000/employees?employee_id=eq.'||to_char(p_employee_id);

  -- REST request to alien database to perform update operation
  l_response := apex_web_service.make_rest_request(
    p_url => l_url,
    p_http_method => 'DELETE'
  );

end delete_employee;

Look how these procedures are different from the ones we wrote in the previous chapter to manage departments.

Here we have to manually set the needed HTTP headers by populating of apex_web_service.g_request_headers collection, because we don't have prepared metadata for this Web Source. Then, we prepare a request body manually. Luckily, in 18c we have very powerful json_object_t data type to help us here - with it we don't have to worry about formatting and data types of the attributes to be put to a JSON string. And the last thing done by the writing procedures, is a REST API call of a corresponding HTTP method.

The report and the form

Again, it's time to go back to AppBuilder and edit your home page. Now add another Interactive Report region to the Content Body section. Give it a proper name to reflect management of the employees table this time. Then specify to base your report on SQL query, and use this query as a source:

select 
    employee_id,
    department_id,
    name,
    email,
    cost_center,
    date_hired,
    job
from alien_data_management.get_employees();

Then you can rename the columns of the report following your preferences. As a result you should get something like this.

The further steps are quite analogical to the ones we performed in the previous chapter. The only difference is in the set of attributes and names of procedures to use. So, I am going to leave them for you to perform them yourselves.

Testing

In case you were brave enough to finish the task, you are ready to test the implemented functionality. Run the application and have a look at the report.

So, we see some data. And that's what we have in the database:

restapi=> select * from employees order by name limit 10;
 employee_id | department_id |        name        |            email            | cost_center | date_hired |            job
-------------+---------------+--------------------+-----------------------------+-------------+------------+----------------------------
         124 |             1 | August Arouri      | august.arouri@aaam.com      |          54 | 2018-08-07 | Customer Advocate
         117 |             1 | August Rupel       | august.rupel@aaaf.com       |           1 | 2018-10-04 | Analyst
         126 |             1 | Ayana Barkhurst    | ayana.barkhurst@aaao.com    |          42 | 2018-08-26 | Sustaining Engineering
         122 |             1 | Carlotta Achenbach | carlotta.achenbach@aaak.com |          99 | 2018-10-22 | Marketing Manager
         120 |             1 | Chaya Greczkowski  | chaya.greczkowski@aaai.com  |          55 | 2018-09-05 | Support Specialist
         131 |             1 | Dania Grizzard     | dania.grizzard@aaat.com     |          53 | 2018-10-30 | Project Manager
         114 |             1 | Dean Bollich       | dean.bollich@aaac.com       |          83 | 2018-08-14 | Usability Engineer
         113 |             1 | Gricelda Luebbers  | gricelda.luebbers@aaab.com  |          82 | 2018-11-06 | Marketing Associate
         132 |             1 | Inez Yamnitz       | inez.yamnitz@aaau.com       |          75 | 2018-09-18 | HR Representitive
         123 |             1 | Jeraldine Audet    | jeraldine.audet@aaal.com    |          41 | 2018-08-18 | Quality Control Specialist
(10 rows)

Looks suspiciously similar, doesn't it? Now let's change an employee's data and click the Save changes button.

The changed row is there in our report.

But what about the database side?

restapi=> select * from employees order by name limit 10;
 employee_id | department_id |        name        |            email            | cost_center | date_hired |            job
-------------+---------------+--------------------+-----------------------------+-------------+------------+----------------------------
         117 |             1 | August Rupel       | august.rupel@aaaf.com       |           1 | 2018-10-04 | Analyst
         124 |             1 | August WE DID IT!  | august.arouri@aaam.com      |          54 | 2018-08-07 | Customer Advocate
         126 |             1 | Ayana Barkhurst    | ayana.barkhurst@aaao.com    |          42 | 2018-08-26 | Sustaining Engineering
         122 |             1 | Carlotta Achenbach | carlotta.achenbach@aaak.com |          99 | 2018-10-22 | Marketing Manager
         120 |             1 | Chaya Greczkowski  | chaya.greczkowski@aaai.com  |          55 | 2018-09-05 | Support Specialist
         131 |             1 | Dania Grizzard     | dania.grizzard@aaat.com     |          53 | 2018-10-30 | Project Manager
         114 |             1 | Dean Bollich       | dean.bollich@aaac.com       |          83 | 2018-08-14 | Usability Engineer
         113 |             1 | Gricelda Luebbers  | gricelda.luebbers@aaab.com  |          82 | 2018-11-06 | Marketing Associate
         132 |             1 | Inez Yamnitz       | inez.yamnitz@aaau.com       |          75 | 2018-09-18 | HR Representitive
         123 |             1 | Jeraldine Audet    | jeraldine.audet@aaal.com    |          41 | 2018-08-18 | Quality Control Specialist
(10 rows)

So this is real! And it's done for the second time, using a different approach.

In fact, we could implement an even more programmatic approach using the UTL_HTTP package, but in this case we would need to write much more code to achieve the same results. Although, in that case we would have even more control of what is done by our procedures.

Downloads

Ok guys, I have good news for you - for those who had some issues with creating of the application on their own, I uploaded the source code of the stuff we were developing here to GitHub. You are free to download and use it in any way you like (don't forget to change the IPs).

The uploaded project consists of:

PostgreSQL database schema and sample data script
ALIEN_DATA_MANAGEMENT PL/SQL package
APEX application export script.

You can find it here: https://github.com/ZZa/apex-on-postgresql-sample

Conclusion

By this very long article I tried to answer the question if it is mandatory to use exactly Oracle Database to store APEX application data. And as you can see, the answer is NO, you are welcome to use any database and DBMS of your preference to base your APEX application on, and you can do it right now, though not in a fully declarative way. In fact, the choice of RDBMS isn't limited by PostgreSQL or MySQL - it literally could be anything with a proper REST API on top of it.

This means you can go for a free Express Edition of Oracle Database to integrate APEX with your companies existing projects. It won't work as fast and smooth as it could if you based your application on a local Oracle Database, but it still has loads of use-cases.

Of course, the code, I demonstrated here, could be improved in many aspects - we could add proper error handling, modularize it better or make it more universal. But my aim here was to mostly show WHAT YOU CAN DO, not write an ideal application. I also should warn you about the fact that I used very simple examples in my post, and if you need much more complex queries or transactions, it would be a good idea to consider using REST API on views of stored procedures, defined in the remote database.

Do not hesitate to comment on the topic if you have something to add or a question appeared in your head - together we will try to sort it out. You are also welcome with you suggestions on how to improve the article.

Drop me a message any time if you feel I could be useful for you, and we give your problem a closer look.

Reblog: Oracle Database + APEX + JavaScript/Python = Awesome!

Denis Savenko — Fri, 02 Nov 2018 08:45:23 GMT

How cool it could be if you were able to write your server-side code in database and APEX applications using any language of your preference? What about a mixture of them? There's a new feature, called MLE or Multilingual Engine which is going to allow you do this all! But don't tell anybody! It's a secret!

The MLE feature is based on the GraalVM project and is planned to be part of Oracle Database in some future. However, it's already possible to estimate how it works in APEX with JavaScript and Python right now (other languages and more complete support are still under development). To do so, check out the most recent announcement by Joel Kallman with loads of practical examples. Enjoy the reading and don't forget to try it yourself!

https://blogs.oracle.com/apex/oracle-database-%2B-apex-%2B-javascriptpython-%3D-awesome

More languages always mean more possibilities and wider community. This feature will enable us to see JavaScript, Python and Java developers joining our database and APEX pride in some time.

And the more people involved the more job is done and the better future we have for our favourite technology!

Oracle APEX and ORDS deployments automation

Denis Savenko — Mon, 29 Oct 2018 19:22:36 GMT

How much time does it take for your team to deliver your freshly updated applications to all the environments and customers? In case you just play with APEX and ORDS, it is totally ok for you to export and import your workspaces, applications and RESTful Services definitions manually using web-based interface (under App Builder > Export/Import and SQL Workshop > RESTful Services > RESTful Data Services). But it is obviously not a suitable way for more or less serious development, and, surely, for the enterprise use.

Why so?

Let's imagine you have a team of several developers who do different job and once a couple of days you need to deliver their changes to all the environments. If your team develops a single application in a single workspace, which is supposed to be deployed to a single test environment and a single production server, you still can do all this manually. It's just not the time for you. Yet.

But imagine you have several testing platforms, and dozens or even hundreds of customers. In such a case it becomes more or less impossible to deliver all the changes as frequently as you could do, providing you had the only customer. However, many people continue exploiting the manual approach, and don't even realise they're doing it wrong.

That's why we need automation - to stop losing our time. More than two years ago Martin D'Souza already wrote about how APEX applications could be exported and imported using SQLcl. That article was a good start for me, though, I didn't want to be dependant on SQLcl or any other specific tool, and needed to do even more - deploy not only APEX applications, but also APEX workspaces and RESTful Data Services modules definitions.

So, my ultimate goal was to work out a way to create an applicable set of SQL-scripts with the definitions of all the mentioned database objects from the database metadata.

Additionally, this set of scripts had to be universal, which means they had to be compatible with the SQL*Plus, SQLcl and SQL Developer.

And this is what I finally ended up with.

The Export

APEX Workspace

The first thing you deploy in order for your APEX applications to work, is your workspaces. Actually, if you have the only server in production environment, automation of this step is quite optional and you can still do the job using the stanard web-based interface (Instance Administration > Manage Workspaces > Export Import > Export Workspace). But if you're going to install your applications on some number of hosts with their own database instances, it's a very good idea not to do everything by your bare hands.

To export workspace definitions we are going to use APEX_EXPORT.GET_WORKSPACE API. Mind the fact that you need to run the script below as a parsing schema user (which is associated with the workspace), or as SYS, otherwise you're going to get the ORA-20987: APEX - Security Group ID (your workspace identity) is invalid. - Contact your application administrator exception.

-- connecting to the instance (parsing schema user could be used instead of SYS)
connect sys as sysdba

set timing on
timing start TIMER_WORKSPACE_EXPORT

-- SQL*Plus environment settings
-- they are crucial to retrieve a consistent export file
set feedback off
set heading off
set echo off
set flush off
set termout off
set pagesize 0
set long 100000000 longchunksize 32767
column output format a4000
set linesize 4000
set trimspool on

-- variable for storing the export data
variable contents clob

-- generating the export file
declare
    l_files apex_t_export_files;
begin
    l_files := apex_export.get_workspace(
        p_workspace_id => 100500,
        p_with_date => false,
        p_with_team_development => false,
        p_with_misc => true
    );
    -- in the first file we have the needed export data
    :contents := l_files(1).contents;
end;
/

-- you can specify any file name here
spool my_workspace_export.sql

print contents

spool off

timing stop TIMER_WORKSPACE_EXPORT

exit

Notice that the size of the export data can be large (though this is mostly true for application exports), that's why we need to set proper SQL*Plus environment settings.

APEX Application

Exporting of your APEX applications looks very similar to what you've just seen above. But there's a huge difference in the frequency of its usage - you need to export an application every time you need to get a new version of it in order to deliver the changes to your customers. Even if there's only one.

As before, to export application we are going to use APEX's PL/SQL API, but this time it's going to be APEX_EXPORT.GET_APPLICATION. Again, you must connect as a parsing schema user (which is associated with the workspace with the application to export), or as SYS to run the script.

-- connecting to the instance (parsing schema user could be used instead of SYS)
connect sys as sysdba

set timing on
timing start TIMER_APP_EXPORT

-- SQL*Plus environment settings
-- they are crucial to retrieve a consistent export file
set feedback off
set heading off
set echo off
set flush off
set termout off
set pagesize 0
set long 100000000 longchunksize 32767
column output format a4000
set linesize 4000
set trimspool on

-- variable for storing the export data
variable contents clob

-- generating the export file
declare
    l_files apex_t_export_files;
begin
    l_files := apex_export.get_application(
        p_application_id            => 150,
        p_with_ir_public_reports    => true,
        p_with_ir_private_reports   => true,
        p_with_ir_notifications     => true,
        p_with_translations         => true,
        p_with_comments             => true,
        p_with_acl_assignments      => true
    );
    :contents := l_files(1).contents;
end;
/

-- you can specify any file name here
spool my_app_export.sql

print contents

spool off

timing stop TIMER_APP_EXPORT

exit

As a result you will get a huge SQL-script which contains metadata of all the application pages and even static application files. You also have an option to generate a separate file for each application page if you specify the p_split parameter value as true.

ORDS Modules

In this chapter we are going to consider only the ORDS-based RESTful Services, which is quite a standard at the moment.

To export ORDS modules you, as usual, can use the comfortable APEX's browser interface (under SQL Workshop > RESTful Services > RESTful Data Services > Export), but again it doesn't hurt much to automate the process, at least if your RESTful services tend to evolve through time. And once more time, there's an existing blog post by Tim Hall on how to do all this using SQLcl. And still, I didn't want to be dependant on it, hence needed a different approach.

So, we're going to use the ORDS_EXPORT PL/SQL package in the ORDS_METADATA schema, which is, unfortunately, still not documented properly. Actually, under the hood exactly this package is used by the APEX SQL Workshop's pages.

To get a correct export file, you need to run the script below as a REST-enabled schema user.

-- connecting to the instance (you must use the REST-enabled schema user - RESTAPI in this case)
connect restapi

set timing on
timing start TIMER_REST_EXPORT

-- SQL*Plus environment settings
-- they are crucial to retrieve a consistent export file
set feedback off
set heading off
set echo off
set flush off
set termout off
set pagesize 0
set long 100000000 longchunksize 32767
column output format a4000
set linesize 4000
set trimspool on

-- variable for storing the export data
variable contents clob

-- generating the export file
begin
    :contents := ords_metadata.ords_export.export_schema();
end;
/

-- you can specify any file name here
spool my_rest_modules_export.sql

print contents
-- the trailing '/' symbol is needed to terminate the generated PL/SQL block
prompt /

spool off

timing stop TIMER_REST_EXPORT

exit

After running the script, you end up with exactly the same SQL-script as though you used the SQL Workshop's interface.

It's also possible to export a particular module, if you like, for this exact purpose there's another function export_module(p_module_name => 'MY_MODULE') in the ORDS_EXPORT package. Check out the package specification for more info on this.

The Import

Generally, the import process is quite simple if you did everything right, since all the exported files are usual SQL-scripts which can be executed by your favourite tool. The only difference here is the user as whom you should run the scripts.

APEX Workspace

To import a workspace, you must be connected to the database as APEX internal user (for instance, APEX_180100) or as SYS (but in this case you should change your current schema to the APEX internal user's by invoking, in our case, alter session set current_schema = APEX_1801000; statement).

Note the fact that if you go for the APEX internal user, it is usually in the locked state, so you first need to unlock it and set a password for the user. After doing the job (the import), the APEX internal user must be locked back, because leaving it unlocked is way dangerous.

You also should be in the directory where the script resides, since we're going to load it directly.

-- connecting to the instance as SYS
connect sys as sysdba

-- changing the current schema
alter session set current_schema = APEX_180100;

set echo on
set timing on
set serveroutput on
set linesize 120

whenever sqlerror continue none

timing start TIMER_WORKSPACE_IMPORT

spool workspace_import.log

@my_workspace_export.sql

spool off

set serveroutput off

purge recyclebin;

timing stop TIMER_WORKSPACE_IMPORT

exit

APEX Application

Things could be a little different with the APEX applications import, due to the fact it has its own API to configure the process. It's done by using the APEX_APPLICATION_INSTALL package. Though, its usage is totally optional and is needed only if you intend to change something about the application before the actual import. There's even a set of examples on how to use the package in the official documentation.

Here I'm going to show the trivial situation when you're okay with the defaults (no need to change of application ID, offset, workspace, alias or any other application property). The script below should be run as a parsing schema user.

-- connecting to the instance as a parsing schema user APEX_APP
connect apex_app

set echo on
set timing on
set serveroutput on
set linesize 120

whenever sqlerror continue none

timing start TIMER_APP_IMPORT

spool app_import.log

@my_app_export.sql

spool off

set serveroutput off

purge recyclebin;

timing stop TIMER_APP_IMPORT

exit

ORDS Modules

To import your recently exported RESTful Data Services definitions you should switch to the REST-enabled schema user and change the name of the script.

-- connecting to the instance as a REST-enabled schema user RESTAPI
connect restapi

set echo on
set timing on
set serveroutput on
set linesize 120

whenever sqlerror continue none

timing start TIMER_REST_IMPORT

spool rest_services_import.log

@my_rest_modules_export.sql

spool off

set serveroutput off

purge recyclebin;

timing stop TIMER_REST_IMPORT

exit

That's it!

Obviously, the mentioned scripts could be put together when you work with your particular situation and when you know what you're doing. The only point here is that separate scripts give you more freedom when it comes to automation. You can set up your own pipelines to run particular scripts when a particular event occurs.

But where is the automation?

Right. We only wrote a bunch of scripts and nothing more. But as I like to say, automation starts where interactivity stops. All web-based user interfaces are interactive and need a human to function, but the set of scripts, which we worked out, could be used in both interactive and non-interactive ways. Which means, you can definitely use them with your favourite CI/CD tool.

It could cost you some time and effort in the beginning to set up all this, but will save much more in the future. And stopping wasting time on the routine on every day basis is not the only reason. Automation of deployments enables you to implement such things as automated testing, continious integration and delivery, which, in their turn, decrease number of bugs in your code and shortens the time your customers see the brand-new funtionality.

I think it's enough to try, isn't it?

Do you have any other ideas how to automate development process with APEX and ORDS worth sharing? You're warmly welcome to put it here in the comments section - we'll definitely discuss them all!

ODC Appreciation Day: Significance of Sharing and Feedback

Denis Savenko — Thu, 11 Oct 2018 00:00:00 GMT

Today is the Oracle Developer Community Appreciation Day of the year 2018. And despite the fact it's more than a month to the official Thanksgiving Day, I think it's a good opportunity to say thanks once again to all those people who helped us on everyday basis by contributing to the community this whole year. To people who wrote great articles in their blogs and shared their personal experience with us, to those who tried to sort out our troubles on forums and in chats, and of course to those who supported and motivated us by liking and spreading our posts and ideas in Twitter. And special thanks to the awesome Oracle APEX community which accepted me and led me to the right direction so many times this year! I hope this would always stay the same and all these people carry on making our world a bit better place to live!

And while being thankful to others, let's remember if you spread your fresh knowledge to people around you.

Significance of Sharing

Indeed, it happened that IT community is used to all that stuff available on the net and take it for granted. What does one do if they got into trouble with some piece of technology nowadays? They google it. They seek for a prepared answer in advance on their question. And in most cases they find and then use it. But what happens if they do not? Well, some 'experts' stop here and give up, but the better half tries to sort out the problem themselves. And in most cases they succeed - that's what we are paid for, after all, and then our ways part.

Unfortunately, after working out a solution for a specific issue, most developers never share their experience with anybody. Even with their immediate colleagues, not saying about global community. They forget that a little while ago they themselves were seeking for a piece of advice on the matter, they forget how useful their solution could be for others, who stuck with the same problem.

That's, of course, not true about everybody - we are lucky enough to have people with different thinking on our side. But here's my first point - it's very important to share your experience with others, especially when you already confirmed the fact that there's no information available regarding your issue. Don't think your issue is that exclusive and isn't worth discussing - this is not true in most cases.

Furthermore, by sharing your experience you do better not only for others, but for yourself in the first place. Indeed, if you look deeper at this, you realise that by composing a proper blog post on some particular topic you:

Consolidate your fresh knowledge. By writing a post you put things in order in your own brain which helps you remember things better. Don't be scared by the fact you may seem incompetent - we all learn and by learning we become better. If one stops learning it doesn't mean they know everything, it only means they stop developing.
Save your own notes for future you. It'll be you who will refer to them in a some while in the first place. Do you really think you'll never come across this issue again in your whole life? Will you remember what you did before to sort it out?
Show others you know how things work and who you are. And this stands out you from other invisible developers, about whom we know absolutely nothing. Remember - your posts describe you better than any kind of CVs.

I really hope these reasons would motivate at least someone to compose their first post in their brand-new blog. In this case I'd say my mission was accomplished. At least they were the reasons which did the job for me.

I agree that it is time and energy consuming to write a good enough post, but the more you write the easier it is for you. And maybe we'll see more talented specialists sharing their knowledge on the web in some future.

Significance of Feedback

My second point is that even if you don't write your own posts, don't underestimate the power of feedback on those you use in your work. Your words of gratitude surely motivate authors to carry on, you criticism helps authors polish their articles to your requests. Your questions give writers ideas about such things as:

How to improve a post, what information to add into it, what aspects of the subject to consider in more details.
What interest their readers and about what to write in the future.
What to change in their style and how to improve the language.

You even can't imagine how hard it could be for authors to realise some things which seem obvious for their readers. Sometimes it's on the surface, but is not seen for the blogger from their perspective of view, because of different life experience.

Significance of All Kinds of Feedback

Let me start with some of my recent observations about difference in russian and european mentality. It happened that I wrote exactly the same post in two languages - in russian for the local community on a very popular collaborative blogging platform and in english for the rest of the world here (and then tweeted about it). And I realized that behaviour of different communities was like they were from different worlds.

The russian version of the article got loads of immediate feedback like Why so complicated? It would be much easier if instead of A you'd use B.. or So many movements, it wasn't needed to do X at all.. and the type. And all this was said after my disclaimer that I wasn't a guru in that particular area, I was just sharing my recent experience of working with the stuff I came across for the first time in my life and people were warmly welcome to offer their, better solutions in comments. Instead of this, they only criticised without actually offering anything valuable instead, like You solution sucks and that's it. This, of course, was not true about everybody and my post got its up-votes, but what really surprised me was the fact how easily people left negative feedback and how hard it was for them even to say thanks for the job done. I mean, where's golden mean?

The situation with the english version was orthogonal - I got a lot of likes and comments like Great job, dude, carry on, but there wasn't a single suggestion on how to improve the guide. I found this fact very weird - like my guide was ideal, though I knew it was not, and it was really easy for people to praise me, whereas they didn't tend to seek for any negative aspects of the post.

Furthermore, this difference in peoples' mentality seems to affect them even more when they are given unexpected type of feedback. I mean, when somebody praises a post in a russian blog, author doesn't know how to react and often think they're just made fun of. People tend to forget what sincere gratitude look like. In the english-speaking part of the world, on the contrary, I noticed that if one gets reasonable criticism on their job, they also answer inadequately, or don't answer at all. It's as though they were insulted rather offered some really valuable information about how they could improve their post, or product.

But what frustrates me even more here is the fact how easily english-speaking community spread the word about something positive, and how lasily they do so when something unpleasant happens. I have a really good example about exploiting undocumented features of some product - people tend to praise so happily those who find such features (and they are surely useful sometimes), but if you point out the fact of lacking of documentation on them or that they shouldn't be there in the first place, this is likely to be ignored totally. Even despite the fact it is the right thing to say - this kind of feedback helps vendors find gaps in their products and docs, which, to my mind, should be spread by community very eagerly, if they want the vendor to pay attention on the problem.

Maybe I don't know how things work, and behind the curtains people take all kinds of feedback very seriously (please, leave your thoughts about this in the comments below), however it's not clear for us, the end-users, customers or readers, for whom products and content exist. We don't see any reaction on our feedback, and we don't know if we are heard. Yes, it could be unpleasant to accept you made a mistake, but it's natural to make mistakes. Bother to appreciate the fact that community pointed out them and helped you find them.

This was a long preamble to my third point which I wanted to denote in this article - the fact that all kinds of feedback are important. Positive and praising feedback gives us more energy and helps understand we're doing the right thing, whereas negative type of it helps us to improve our product and content.

I would only add that criticising in the sake of criticising is surely a bad thing which doesn't lead anywhere. If you bother to criticise, also bother to offer something in replace and motivate your point of view using strong argumentation.

To finish this article, I'd like my readers to remember one very simple fact - if each of us becomes today just a little bit better than they were the day before, we'll see our world shining very soon.

And I believe that it's there in our nature to want to be better, so, just be yourself, keep calm and carry on. And thank you very much for your attention, I hope this post would be useful for at least some of my followers! #ThanksODC!

VirtualBox: thorny path from GUI to headless command-line interface

Denis Savenko — Thu, 09 Aug 2018 18:33:27 GMT

This post, as I already promised on Twitter, is going to be a story behind bash scripts, which I recently shared on GitHub. Their purpose is to simplify management of headless Oracle VirtualBox virtual machines on linux-based host systems. So, in case you would like to know how I ended up with them, carry on reading!

Low start

It happened that I use virtual machines on my Windows desktop PC as development and testing environments for some of my projects. This is very convenient, because first - I sandbox my working environment from my personal stuff and have an ability to save states of the machines when needed. In example, when I'm not sure with some process or feature, I like testing them, but I'd rather create a snapshot before I begin - I find this function really useful. And this is very easy since VirtualBox has simple and clean graphical interface.

But, as you know, sometimes things change. My customer demanded to be able to look at and test the application, I develop for them, before I deploy it to production. And, this wasn't suitable for me to keep my laptop switched on for the whole day long to enable customer's employees to estimate the application. So, I decided to move my virtual machines to my tiny home server.

However, that wasn't that easy, since the operation system, which I have on the server, is CentOS Linux without any X Window System (only the command line with bash is available). And, I didn't want to lose in convenience.

Setting up VirtualBox

So, the first thing which I faced was installation of VirtualBox itself. There're several options to do this. In example, it's possible to download Linux packages for a vast majority of distributions right from official website. But, I'd have needed to resolve all the dependencies manually in this case.

That's why I preferred the most recommended way on the web - to install it from official repository. Luckily, there're a number of good instructions on how to do it, which makes the installation process as easy as a pie.

And, ten minutes later, VirtualBox was succesfully installed on my Linux system. The issue was that I still didn't have graphical interface on my server and wasn't going to start using it, so, I started the research on alternatives to it.

Overview of VirtualBox alternative front-ends

In fact, there're a few additional options to the standard and well-known Qt-based VirtualBox interface as it is given in the documentation:

VBoxSDL is an alternative, simple graphical front-end with an intentionally limited feature set, designed to only display virtual machines. It's here just for mention since this option is another type of graphical interface, which wasn't suitable for me.
VBoxManage is a command-line interface tool for automated and very detailed control of every aspect of VirtualBox. This sounded exactly like something I looked for.
Finally, VBoxHeadless is yet another front-end that produces no visible output on the host at all. As opposed to the other graphical interfaces, the headless front-end requires no graphics support.

Actually, as I found out a bit later, on a server without any GUI, the one needs to combine leveraging of vboxmanage and vboxheadless. In such a case the first one is used to control the virtual machines, their settings and states, when the second is used to run headless instances of them.

I want it easy

I can't put this in words what I felt when saw vboxmanage docs for the first time. This command-line tool is very powerful and have a lot of options, hence not that easy for a newcomer. Especially when you are used to the convinience of the standard GUI.

So, what I wanted is a set of scripts which would be very easy to use and kind of repeat the GUI functionality for the features, mostly used by me. Basically, I needed scripts for starting, stopping, powering off specified virtual machines and for managing of machines snapshots, since I'm an active user of them.

That's where I came up with an idea to write a set of scripts which would be able to take command line arguments on the one hand and user input on the other, when the argument is not provided. And you already know what I had as a result of my efforts.

Usage of the scripts is very easy - just give them the execute permission and hit the enter button on a desired script. Optionally, it's possible to specify a command line argument, in example, when you want to automate powering on of a particular virtual machine on system startup.

To put it simple, the main purpose of the scripts is to enable the user to control their headless virtual machines with a hit on a button instead of leveraging of the complex command-line utility vboxmanage, which is almost as convinient as using the GUI.

Moving the VMs

It wasn't hard for me to move the virtual machines, I even didn't have to export/import them. Everything which I did was copying virtual machines files from my laptop to the server. Then, using the prepared register script, I just registered them with the new environment and that's it.

Though, things weren't that easy, because my machines didn't start after moving. There were two reasons why. First - I use bridged network on my VMs, and they didn't see the bridging interface anymore (since it was from my desktop), second - 3d-acceleration feature was not supported on the headless environment and I needed to switch it off before running the VMs.

To fix this, I had to run these two commands for each VM:

vboxmanage modifyvm  --nic1 bridged --bridgeadapter1 
vboxmanage modifyvm  --accelerate3d off

Where is the name of a VM and is the name of the bridging network interface (determined by ifconfig, for instance).

After doing so, I succesfully powered on all my virtual machines with my brand new 02_start.sh script.

What do I do with the VM without interface?

I think some of you would ask me what to do with a headless VM? The answer is to use it remotely. Since I still prefer setting up my machines on my laptop, I always enable some way of remote control of the VM. If guest operation system is a unix-based one, it's usually enough for me to have the SSH daemon enabled. If it's a VM with Windows, then you can always set up Remote Desktop. Then I move them to the server for the further exploitation.

Alternatively, it's possible to intall VirtualBox extention pack and set up VRPD, which is kind of extention of standard remote desktop protocol. This is very useful if you want to create a VM on your server from scratch, though this is a totally different story.

Conclusion

As it appeared, it's more than possible to manage VirtualBox machines using only command line. Maybe that's not as convenient as usage of GUI, but I hope my scripts will simplify the most common tasks not only for me, but for somebody else.

To make it short, you need to perform these steps to complete movement of your VMs from GUI to headless CLI:

Set up and configure your VMs on your desktop system, in example on your laptop, using convenient GUI.
Install VirtualBox on your Linux server system.
Move all VMs files to the server.
Register your VMs using vboxmanage or my 01_register.sh script.
(Optional) Fix the bridging network interface and 3D acceleration issues.
Manage you VMs using my bunch of scripts or vboxmanage and vboxheadless utilities directly.
Use your VMs remotely through SSH or RDP.
...
PROFIT!

Do not hesitate to comment on them and on the approach in general. I'm sure there're many other good options of how to do the same things. I didn't pay much attention to the teleporting feature of VirtualBox environment, for example, though it might be quite suitable for a similar case to the one I was talking about in this article.

Migrating CentOS system from HDD to smaller SSD on XFS filesystem

Denis Savenko — Sat, 04 Aug 2018 18:00:00 GMT

I think I am not alone who acquired an SSD drive and wanted to move the whole living Linux system to it. The problem is that it appeared to be not an easy task for me because of these reasons:

The SSD drive is much smaller than the old HDD (SSD is expensive).
I use CentOS 7 which has xfs as default filesystem (as well as RHEL 7, Oracle Linux 7). Hence, my disk partitions are on xfs.
System configuration, permissions and so on must be saved.

Let me explain in short why these things make everything harder.

First, if the size of the SSD drive was the same or greater than the HDD size, it would be possible to perform partition cloning. There are a lot of utilities which can do this - dd, ddrescue, partclone or clonezilla. On LVM partitions (which is also in CentOS 7 by default) moving data to another disk of actual or bigger size could be done even easier with pvmove command.

Then, if the filesystem was something different from xfs, for example, ext4, it would be possible to 'shrink' partitions to the size smaller than the new disk and after that perform an operation mentioned above. But, unfortunately, shrinking an xfs partition is not possible (due to filesystem limitations), you can only extend it.

Last, but not the least, if it was not necessary to save working system, it would be much easier just to save a couple of important files and then to reinstall the system from the scratch. It didn't fit me because of two reasons. First, I have just spent two weeks configuring all and everything, so I hated the idea to repeat this all. And second, it was kind of a challenge to me, which I didn't want to refuse.

Stage 1. Preparation

Ok, let's make a list of what we need to start migration:

Working Linux system to migrate. In my case it was CentOS 7.4, but I'm sure everything would work on any Linux system with xfs on disk partitions.
CentOS bootable live-cd or USB flash drive. I love the Gnome version, but there's a KDE option if preffered. I won't stop on how to burn a live-cd or make a bootable flash drive, there're loads of articles about it out there. CentOS live-cd distribution contains everything we need out-of-the-box since all that we need is xfsdump package which is preinstalled.
Data on the previous drive must fit the size of the new one. In my case only 10GB were busy on the 1TB disk, so, it was not a problem for me at all.
You also will need a cup of coffee to relax.

Stage 2. Moving the data

A good start will be to make a sip from your coffee cup and then boot the system from live-cd media. Then open a terminal window.

All operations should be performed by super user (root).

Step 1. Enabling remote access (optional)

For me it is more convenient to perform operations from my desktop PC, because I can just copy and paste prepared in advance commands. If this is also about you, then enable remote access to your system. Set root user password and start SSH daemon for this:

su
passwd
systemctl start sshd

Now, connect to the system using your SSH client (for example, PuTTY).

Step 2. Partitioning your new disk

You can use any tool for doing this, but in this guide I will use fdisk intentionally, since other tools like gparted seem not to support NVMe disks yet (and so my SSD as well).

We should partition the new disk the same way the old one was partitioned. I am not partition-maniac and so on my previous disk there were only two partitions: 1GB /boot standard linux partition, 4GB swap and the rest of the disk was \ (swap and root were under LVM volume group main).

So, let's do it:

lsblk # check your new disk name
fdisk /dev/nvme0n1 # nvme0n1 here is the name of my new disk
n # create new partition (for /boot)
p # primary partition
# leave default (1st partition)
# leave default 
+1G # size for /boot partition
# done!
n # create new partition (for LVM volume group)
p # primary partition
# leave default (2nd partition)
# leave default 
# leave default (all the rest of the disk)
# done!
a # set bootable flag
1 # partition 1
p # check that everyting is ok
w # write partition table to the disk

Since /boot partition should be a standard linux partition, let's create a filesystem on it:

mkfs.xfs /dev/nvme0n1p1 -f

And now we need to create LVM structure on new disk. And I will use name newmain for the new volume group:

pvcreate /dev/nvme0n1p2 # create a new physical volume in LVM
vgcreate newmain /dev/nvme0n1p2 # create a volume group and add PV into it
lvcreate -L 4G -n swap newmain # create logical volume for swap of size 4G
lvcreate -l 100%FREE -n root newmain # create logical volume for root partition with the rest of the disk
vgchange -a y newmain # make the new volume group active

Now we are ready to create a filesystem on logical volumes:

mkfs.xfs /dev/newmain/root # create file system on new root partition
mkswap -L swap /dev/newmain/swap # recreating swap in new place
swapon /dev/newmain/swap

Step 3. Active phase

Before we start, we need to make the old LVM volume group active:

vgchange -a y main

Now, create directories for mount points and mount old and new partitions to them:

mkdir -p /mnt/old/boot
mkdir -p /mnt/old/root
mkdir -p /mnt/new/boot
mkdir -p /mnt/new/root
mount /dev/sda1 /mnt/old/boot
mount /dev/nvme0n1p1 /mnt/new/boot
mount /dev/main/root /mnt/old/root
mount /dev/newmain/root /mnt/new/root

Let's check is everything is ok with lsblk:

lsblk
NAME             MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda                8:0    0 931.5G  0 disk
├─sda1             8:1    0     1G  0 part /mnt/old/boot
└─sda2             8:2    0 930.5G  0 part
  ├─main-swap    253:0    0   3.6G  0 lvm  [SWAP]
  └─main-root    253:1    0 926.9G  0 lvm  /mnt/old/root
nvme0n1          259:0    0 119.2G  0 disk
├─nvme0n1p1      259:3    0     1G  0 part /mnt/new/boot
└─nvme0n1p2      259:4    0 118.2G  0 part
  ├─newmain-swap 253:5    0     4G  0 lvm  [SWAP]
  └─newmain-root 253:6    0 114.2G  0 lvm  /mnt/new/root

If you see something like this, you're in one step to real magic - we are going to use xfsdump to move the data. This utility is very smart and knows how xfs works, that's why it copies only busy blocks and does it really fast saving all the metadata like permissions. So, let's use it to dump the data from old partitions and restore it on the fly to the new ones:

xfsdump -l0 -J - /mnt/old/boot | xfsrestore -J - /mnt/new/boot # clone boot partition
xfsdump -l0 -J - /mnt/old/root | xfsrestore -J - /mnt/new/root # clone root partition

A couple of words about the flags used:

-J disables verbosity
- tells xfsdump and xfsrestore to use stdout and stdin respectively instead of a file.

This operation can take several minutes (depends on how much data you have). So, it is a good time to finish your coffee.

If you done well, your data is copied. Now you need to fix some configs and install Grub2 on the new disk in order to make the system on new disk bootable.

Step 4. Making new disk bootable

First, find out UUIDs of your old and new /boot partitions using blkid:

blkid
...
/dev/nvme0n1p1: UUID="3055d690-7b2d-4380-a3ed-4c78cd0456ba" TYPE="xfs"
/dev/sda1: UUID="809fd5ba-3754-4c2f-941a-ca0b6fb5c86e" TYPE="xfs"
...

Assuming sda1 is the old \boot partition, and nvme0n1p1 - the new one, perform UUID replacement like this:

sed -i "s/809fd5ba-3754-4c2f-941a-ca0b6fb5c86e/3055d690-7b2d-4380-a3ed-4c78cd0456ba/g" /mnt/new/root/etc/fstab
sed -i "s/809fd5ba-3754-4c2f-941a-ca0b6fb5c86e/3055d690-7b2d-4380-a3ed-4c78cd0456ba/g" /mnt/new/boot/grub2/grub.cfg

This two commands will prepare your system configs for the new disk.

Now, it's high time to place the new LVM volume group in the place where the old was and unmount disks:

umount /mnt/{old,new}/{boot,root}
vgrename -v {,old}main
vgrename -v {new,}main

The only thing which is left is to install Grub2 to the new disk. It must be done using chroot:

mount /dev/main/root /mnt
mkdir -p /mnt/boot
mount /dev/nvme0n1p1 /mnt/boot
mount -t devtmpfs /dev /mnt/dev
mount -t proc /proc /mnt/proc
mount -t sysfs /sys /mnt/sys
chroot /mnt/ grub2-install /dev/nvme0n1

Step 5. Done!

Now you need to restart the system and use the new disk as a boot device. You can remove the old disk from the system if everything is ok:

systemctl reboot -f

If something went wrong and your system doesn't work, you are able to rollback the changes by booting from live-cd again and renaming LVM volume groups back by performing vgrename -v {,new}main and vgrename -v {old,}main

Using the old HDD as media storage

In case you, like me, want to use your old disk as media storage, perform these operations.

First, repartition the disk:

fdisk /dev/sda
d # delete partition 2
d # delete partition 1
n # new partition
p # primary
# default
# default
# 100%
# done!
p # check if everything is ok
w # write partition

We won't create a filesystem on the new partition. Instead, we will create a new LVM volume group and add this disk to it. After that we will create a logical volume within the group which is going to occupate all the available space. And only after that we will create filesystem on the logical volume:

pvcreate /dev/sda1 # new LVM physical volume
vgcreate media /dev/sda1 # new LVM volume group
lvcreate -l 100%FREE -n media1 media # new logical volume
vgchange -a y media # make the volume group active

mkfs.xfs /dev/media/media1 # create filesystem on new logical volume

mkdir -p /var/media # dir for mount point
mount /dev/media/media1 /var/media # mount new disk to the system

Using LVM volume group allows you to extend this partition in the future on-the-fly very easily (for example, when you run out of free space).

In order to save changes after reboot, you need to add a record about new mount point to /etc/fstab:

/dev/mapper/media-media1 /var/media                       xfs     defaults        0 0

And finally we can say that we successfully moved the working system on another physical drive, which was smaller than the original one. As a bonus, we started to use the original disk as media storage.

Extending the media storage with an additional physical drive

It happened, that I ran out of free space of my media storage really fast. And I decided to obtain another hard drive as an extention. So, I installed it on my system and powered up the server.

At this point I had several options of 'how' to use a new drive. It could be a standard partition, a new logical volume in my LVM volume group media or, which I actually wanted to do the most, this disk could be a true extention of the existing logical volume media1, which we previously created.

So, is this chapter I'd like to share with you how easy it is if you use xfs filesystem on a LVM volume group.

First, let's check what we have after we installed a physical drive.

lsblk
NAME             MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda                8:0    0 931.5G  0 disk
sdb                8:16   0 931.5G  0 disk
└─sdb1             8:17   0 931.5G  0 part
  └─media-media1 253:2    0 931.5G  0 lvm  /var/media
nvme0n1          259:0    0 119.2G  0 disk
├─nvme0n1p1      259:1    0     1G  0 part /boot
└─nvme0n1p2      259:2    0 118.2G  0 part
  ├─main-root    253:0    0 114.2G  0 lvm  /
  └─main-swap    253:1    0     4G  0 lvm  [SWAP]

As you can see, the new disk became sda because of some reason, and the old one is now sdb. So, let's create a partition table on the new disk, we're going to use fdisk again for this purpose (though I'm not sure if this step is mandatory, so, do not hesitate to comment on this).

fdisk /dev/sda
# DOS partition table is created automatically on a new disk after running `fdisk`
n # new partition
p # primary
# default
# default
# 100%
# done!
p # check if everything is ok
w # write partition

Let's check.

lsblk
NAME             MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda                8:0    0 931.5G  0 disk
└─sda1             8:1    0 931.5G  0 part
sdb                8:16   0 931.5G  0 disk
└─sdb1             8:17   0 931.5G  0 part
  └─media-media1 253:2    0 931.5G  0 lvm  /var/media
nvme0n1          259:0    0 119.2G  0 disk
├─nvme0n1p1      259:1    0     1G  0 part /boot
└─nvme0n1p2      259:2    0 118.2G  0 part
  ├─main-root    253:0    0 114.2G  0 lvm  /
  └─main-swap    253:1    0     4G  0 lvm  [SWAP]

So, now we have a partition sda1, which we're ready to add to our LVM volume group. Then, we need to extend our logical volume inside the volume group by all unallocated space. And after that, grow the filesystem on the volume in order for it to occupy the free space. Sounds scarier than it actually is.

pvcreate /dev/sda1 # create a new LVM physical volume for the new partition
vgextend media /dev/sda1 # extend the LVM volume group with the physical volume
lvextend -l +100%FREE /dev/media/media1 # extend the logical volume
xfs_growfs /dev/media/media1 # grow the filesystem

That's it! The new hard drive is now a part of our old media storage, and we don't need to think about where to put our new media - we still can save it in the old place. What a wonderful thing LVM!

Hope this post will be helpful for somebody else besides myself. Do not hesitate to express your thoughts in comments if any.

Reblog: APEX as a PWA

Denis Savenko — Thu, 02 Aug 2018 10:12:20 GMT

In the most recent APEX AskTOM Office Hours I heard a question about turning an APEX application into a PWA. I didn't know about the fact such a term existed before that moment. And now we have a complete guide on how to make a PWA from an Oracle APEX application.

You could think I only reblog articles by Vincent Morneau, because I'm his fan. And though this is not true (he is not the only one whose fan I am), I definitely think that every blog post composed by him is a true golden nugget.

And this complete guide on how to turn an APEX application to a PWA is not an exception. PWA (or Progressive Web Application) - is a web application which is a traditional website (or set of web pages), but can appear to the user like a native mobile or a traditional application, which also work offline. This guide, which consists of eight parts and a demo, will introduce you to this world and Oracle APEX's place in it. Enjoy the reading!

http://vmorneau.me/apex-pwa/

Thanks, Vincent, for the great effort which you applied to compose such a complete instruction and for sharing it with us! I'm quite assured that such pieces of knowledge should be shared amongst more people and hope my 2 cent will help here a little.

Lenovo ThinkPad X1 Carbon 2018: personal impression of laptop

Denis Savenko — Fri, 20 Jul 2018 19:06:18 GMT

It was quite a while since the last time I composed a blog post. And this time it's going to be an off-topic laid-back article about my new toy.

I have not used my old laptop regularly for at least 5 years - always preferred working on my desktop PC over it. However, in the past, when I was a university student, I used to work a lot on it. Why things changed? Because I graduated from the university, hence didn't want to take it with me every day, because it's quite heavy and not comfortable to be taken casually, and maybe because it's not that cool to open a full-sized notebook from 2008 in a cafe in the era of MacBooks.

On the other hand, recently I often was in a situation when I lack a device on a conference or even on a workday meeting. So, I started to scan the market casually to find out what machine I would like to obtain. And when I came across a tweet by Tim Hall about his new purchase, I dared and got my Lenovo ThinkPad X1 Carbon (6th gen). And here's my personal impression of it.

This is not going to be an ordinary review of the laptop (if you still need one, you can get it here or here), it's rather going to be a kind of my answer on the question Why?. There's not going to be much of technical stuff or specs, but rather more personal impressions and thoughts. So, if you like this kind of reading, enjoy!

Criteria of choice

I may seem naive, but what I value the most in my devices is not the specs or numbers in review tests, but comfort and pleasure when using them. I am a developer and I spend a lot more time with my tech than with family or friends, which sometimes frustrates me. Hence my life partially consists of using this tech, and since we have only one life, why waste it on devices we don't like. So, I advocate subjective approach here than following the objective guidelines (within reasonable limits, of course).

That's why I paid the most of my attention on such things as look and feel of laptops, their mobility and keyboard usage comfort. It was also crucial for me to have a good set of ports (which is not an often case nowadays), because I wanted to be able to connect even my old peripherals to the machine. Then, I'm not that guy who changes their tech every year, so I wanted a device which would be actual for at least half a dozen years.

Well, I suppose that is it, let's start with what models I took into consideration.

Competitors

There's a number of devices which meet my expectation at least to a certain extent. Though there was a bit of a problem - unfortunately, it was impossible for me to phisically play with the devices before buying, because the local shops in my town don't have them in their show rooms. Hence I could rely only on the reviews in the web. And here's what I had.

Dell XPS 13

The full review is here.

Likes

Tim Hall uses the 15-inch version of it. This means this piece of tech is awesome.
Really nice overall dimensions - it is one of the smallest in the niche.
Very thin screen bazels.
Shell materials.
Well-known and reliable brand.

Dislikes

Awkward camera placement - and this was unacceptable for me. I know that I'm not Brad Pitt, but such camera would make me look just ridiculous during video calls.
Not the best keyboard for me.
Not enough ports (seriously, let me live with more than 3 USB-C ports for the at least next five years).
Lid opens only up to 135 degrees. I sometimes like laying on my sofa with my knees bent and a laptop laying on them. It's a very comfortable pose to read or watch some videos, or even to type someting. But to make it comfortable, the device should be opened up to about 160 degrees.

HP Spectre 13

The full review is here.

Likes

Very light and thin.
They promised that there won't be fingerprints on the laptop's surface.
Bundled cover case.
Better keyboard layout. Really liked the PgUp, PgDn, Home and End buttons placement.

Dislikes

Not really my design, no, it's awesome, but just not mine - don't like all these gold and bronze shiny elements. Although, I'd definitely consider this notebook for my girlfriend.
Very weird lid opening mechanism - the device don't look solid when opened.
The trackpad don't look great.
Very few very strangely places ports (again only three USB-C ports over here).

Huawei MateBook X Pro

The full review is here.

Likes

The design is very appealing to me. I find it nicer than the MacBook's.
Very thin bazels around the display.
Good battery lifetime promised (about 12 hours).

Dislikes

Again very awkward camera placement.
Keyboard sucks in all aspects (layout and look and feel).
Very few ports one more time (only two USB-C here and one USB-A).
No options without useless touchscreen.
No localized version for my country.
No SD card slot.
Huawei making notebooks? Okay, they convinced me they can make good phones, but I can't say the same about laptops.

Apple MacBook Pro

The full review is here.

Likes

It's a MacBook. We all know about a MacBook quality. MacOS works perfectly smooth, there're tons of applications.
Good shell materials.
The display quality.
Deeper iPhone integration - and since I'm an owner of this smartphone, this is a plus for me.

Dislikes

It's a MacBook. MacOS is not always the best option for a database developer. Also, people often complain how Apple ruins everything with the OS updates.
Gosh, it is pricey.
The worst keyboard ever. This was the only option which I could touch before buying, and I loathed the feel of keyboard. And when I started to hear very 'nice' feedback about it from the current users, I loathed it even more.
Not long battery life.
Very thick borders around the screen for these days.

And finally.

Lenovo ThinkPad X1 Carbon (6th gen)

The full review is here.

Likes

I just loved the keyboard. There's adequate room between the keys, the keys move deeply, good shape of the keys.
14-inch screen instead of 13.3.
Very light (1.12 kg) and compact by overall dimensions.
Made of carbon, must be very durable.
Good battery life promised.
Thanks gosh, a good set of ports (two USB-C, two USB-A and separate HDMI).
Dispay lid can be opened up to 180 degrees (I described above why it's cool).
MicroSD card slot.

Dislikes

Keyboard layout. Placement of Fn and Ctrl buttons is very disturbing.
Poor fingerprint sensor.
Pricey.

Note: All these points were made before actual purchase of the laptop.

Personal impression

And finally, let's sort out what I ended up with.

As I already mentioned above, I went for the machine by Lenovo. For those who already desperate to know - I'm not dissapointed. I loved my new toy a lot from the first sight - this is really hard to decribe that difference between the new device and my old Samsung R460. So, the tech was definitely worth its money.

I must say that almost all the points which I made about the ThinkPad X1 Carbon before buying it came true. The only thing I'm not totally sure about is the battery life time. It rather feels like 8 to 9 hours, not 12 as was promised. But maybe this was because I used it very actively during the first days when was setting it up.

But I'm sure you are here not for the praise, but for the blame. Well, I have something to say here.

First of all, not very good quality of the fingerprint sensor was confirmed. I'm already spoiled by the iPhone's touchID, which works extremely fast and accurate, and in comparison to it, the sensor on X1 Carbon leaves much to be desired. Though, it fulfils its function and it's still more than possible to log in using it.

Then, what frustrated me the most is the soft touch surface material. It's a real pleasure to touch the device, but it's as well extremely easy to leave your fingerprints on it, and what is even worse - it's harder to remove these stains then than to leave new ones.

Also, there could be an issue with the noise when the battery charger is connected to the laptop. This is because of the default thermal settings in BIOS/UEFI - every time the laptop is on AC, it by default chooses the maximum performance profile, and starts to heat much faster, so I'd recommend to switch the profile if you don't like the noise.

But enough about the drawbacks, there're some unexpected points which made me a bit happier. First - the problem with the keyboard layout (Fn and Ctrl buttons placement) is easily fixed - you can switch the buttons functions in BIOS/UEFI or in the Windows Control Panel using Lenovo Keyboard Manager application.

And another nice addition - the phisical camera shutter. I guess this is a crucial feature to have in the world where even Mark Zuckerberg tapes his webcam.

To sum up all this, I can say that my new Lenovo ThinkPad X1 Carbon had a really good first impression on me. However, how it often is, it's not ideal, but the found drawbacks are not crucial for me, so I'm still a happy owner of a new toy with a lot of use cases. And what I like the most about it - being this light and compact on the one hand and really powerful on the other makes this device truly universal for me.

The only thing which is left is to decide where to apply my red #orclapex sticker on this brand-new tool. And then I'm fully equiped to start with APEX development on it!

Gallery

View with the lid closed and the Lenovo ThinkPad Laser Mouse accessory.

View with the lid opened.

Comparison with my old Samsung R460 laptop. Top view.

Comparison with my old Samsung R460 laptop. Rear view.

Comparison with my old Samsung R460 laptop. Front view.

Reblog: How to avoid JavaScript mess in APEX applications

Denis Savenko — Thu, 19 Jul 2018 13:37:48 GMT

APEX is a great tool, and how it often happens, you can achieve same things in a dozen of ways using it. That's great, but sometimes it's really hard to determine how to organize and structure your applications, because they tend to become a catastrophy with the speed of light.

Why care, if it still works? Because a well-structured application is much easier to improve even for different developers, it's much easier for them to work together when there're clear development guidelines. And I haven't even mentioned errors, which are much more likely to be made in a messy application.

That's why I wanted to share with you an awesome guide by Vincent Morneau on how to keep your applications clean and tidy when it comes to custom JavaScript. Enjoy the reading!

http://vmorneau.me/avoid-javascript-mess/

I find all the tips really useful, but what is even more important is how coherently they are given. Seriously, guys, if only we had such guides on all aspects of APEX development, such as database architecture, business logic development using SQL and PL/SQL and version control, it would be ridiculously simple to start making world-class applications. Thank you, Vincent!

How APEX executes SQL statements without having permissions?

Denis Savenko — Fri, 22 Jun 2018 13:54:36 GMT

I don't know about you, but I was shocked when saw how easily APEX executes SQL statements against my schemas for the first time. Seriously, I didn't grant a single permission to any APEX user, I only added a workspace to schema assignment inside the instance administration panel. And after that, all workspace developers started to have the same rights as the owner of the database schema, associated with the workspace. And this raises a set of questions.

How it works?

So, what mechanisms work under the APEX's hood to make it able to execute SQL and PL/SQL statements with the owner rights? And if you google this, you could find some sources telling that it leverages the package called DBMS_SYS_SQL in the SYS schema.

DBMS_SYS_SQL is an undocumented package, which is not meant to be used outside of Oracle, because it gives the invoker an ability to execute anything with rights of anybody. Mostly, it repeats the functionality of DBMS_SQL, but instead of, for instance, parse function, there's the parse_as_user and so forth.

In the recent Database Administration AskTOM Office Hours I asked this question to Connor Mc'Donald, a recognized Oracle Master, and he confirmed the fact that APEX uses DBMS_SYS_SQL to execute statements. And he insisted on not granting EXECUTE permission on the package to anyone. Seriously. Nobody.

But if you go deeper, you will be surprised even more. The thing is that DBMS_SYS_SQL is indeed not granted to be executed by anyone. I tried everything to prove the opposite - from querying system views such as DBA_TAB_PRIVS and ALL_TAB_PRIVS to Pete Finnigan's Who-Can-Access-set-of-scripts, but I failed to find a single user permitted to use the package!

This means that nobody can use DBMS_SYS_SQL directly (except SYS, of course), neither can APEX. But how then does APEX do it? And why did Connor Mc'Donald confirm it?

That's because APEX calls DBMS_SYS_SQL through a broker package, called WWV_DBMS_SQL_APEX_180100 (the suffix depends on the version of APEX installed), which is also owned by the user SYS, and this package is granted to the APEX internal user APEX_180100 (the user name also depends on the version of APEX installed).

So, this literally means everybody who has access to the APEX internal user, has potential to elevate their rights to execute any SQL with the rights of any database user.

Is it a big security concern?

It depends.

The thing is that if we query ALL_TAB_PRIVS to get the users who have grants on the WWV_DBMS_SQL_APEX_180100 package, we'll find out there's nobody, but APEX_180100.

And the user APEX_180100 is locked during APEX installation process. It means that, at least, nobody can connect to your database as this user to leverage its power.

Though, there're other packages which are owned by this user and some of these packages are granted to PUBLIC. Hence, every user in the database can execute subprograms in them. And when they invoke a procedure from one of those packages, this procedure is invoked with the rights of the package owner (read APEX_180100).

To be even more precise, there's a query to retrieve a list of packages dependant on the dangerous WWV_DBMS_SQL_APEX_180100:

select distinct owner||'.'||name from all_dependencies where referenced_owner = 'SYS' and referenced_name = 'WWV_DBMS_SQL_APEX_180100' order by 1;

We see a plenty:

APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE
APEX_180100.WWV_FLOW_AUTHORIZATION
APEX_180100.WWV_FLOW_CALENDAR
APEX_180100.WWV_FLOW_CUSTOM_AUTH_STD
APEX_180100.WWV_FLOW_DATALOAD_XML
APEX_180100.WWV_FLOW_DATA_UPLOAD
APEX_180100.WWV_FLOW_DISP_PAGE_PLUGS
APEX_180100.WWV_FLOW_DML
APEX_180100.WWV_FLOW_DYNAMIC_EXEC
APEX_180100.WWV_FLOW_EXEC_LOCAL
APEX_180100.WWV_FLOW_EXEC_REMOTE
APEX_180100.WWV_FLOW_F4000_PLUGINS
APEX_180100.WWV_FLOW_FEEDBACK_INT
APEX_180100.WWV_FLOW_INSTANCE_ADMIN
APEX_180100.WWV_FLOW_INSTANCE_REST_ADMIN
APEX_180100.WWV_FLOW_ITEM
APEX_180100.WWV_FLOW_LOAD_DATA
APEX_180100.WWV_FLOW_LOAD_EXCEL_DATA
APEX_180100.WWV_FLOW_PLUGIN
APEX_180100.WWV_FLOW_PLUGIN_DEV
APEX_180100.WWV_FLOW_PLUGIN_UTIL
APEX_180100.WWV_FLOW_PROPERTY_DEV
APEX_180100.WWV_FLOW_REST
APEX_180100.WWV_FLOW_SECURITY
APEX_180100.WWV_FLOW_SESSION
APEX_180100.WWV_FLOW_SESSION_RAS
APEX_180100.WWV_FLOW_SW_API
APEX_180100.WWV_FLOW_TEAM_FILE
APEX_180100.WWV_FLOW_TREE
APEX_180100.WWV_FLOW_UTILITIES
APEX_180100.WWV_FLOW_WEB_SERVICES
APEX_180100.WWV_FLOW_WS_API
APEX_180100.WWV_FLOW_WS_ATTACHMENT
APEX_180100.WWV_FLOW_WS_GEOCODE
APEX_180100.WWV_FLOW_WS_IMPORT
APEX_180100.WWV_FLOW_WS_SETUP
SYS.WWV_DBMS_SQL_APEX_180100

After rewriting our query to get only those which are granted to PUBLIC:

select 
  distinct owner||'.'||name from all_dependencies x
where referenced_owner = 'SYS' 
  and referenced_name = 'WWV_DBMS_SQL_APEX_180100' 
  and exists (select null from all_tab_privs where table_name = x.name and grantee = 'PUBLIC')
order by 1;

It looks like we still see some..

APEX_180100.WWV_FLOW_CUSTOM_AUTH_STD
APEX_180100.WWV_FLOW_DATA_UPLOAD
APEX_180100.WWV_FLOW_ITEM
APEX_180100.WWV_FLOW_PLUGIN_UTIL
APEX_180100.WWV_FLOW_REST
APEX_180100.WWV_FLOW_UTILITIES

But should we start to panic right now? I don't think so. Why? Because I believe that people who developed these packages thought about security and that there's no possibility to abuse subprograms in this set of packages, which can make it possible to elevate rights of a target user to a desired level.

But we should remember that such probability still exists.

What to do?

So what to do now? Nothing. Seriously. There're no what to do's, there're what NOT to do's:

Never unlock the internal APEX user (in example, APEX_180100 for the 18.1 version of APEX). It was locked intentionally.
Never grant EXECUTE privilege on either DBMS_SYS_SQL or WWV_DBMS_SQL_APEX_180100 to anybody. Seriously. They are not documented and not meant to be used deliberately.
Never set a weak password for the APEX instance administrator user. These strong default password complexity rules are there on purpose. Remember, by creating a workspace-schema assignment, the instance administrator automatically gives owner's privileges on the given database schema objects to all workspace developers.
Do not associate APEX workspaces with powerful schemas. Always create a specific schema dedicated to a particular APEX workspace. Then give needed permissions on needed database objects to this particular schema user. Otherwise, you risk to end up giving much more power to workspace developers and APEX applications than they need. And giving more than needed is always a bad thing when it comes to security.

Conclusion

I should warn you that despite the fact that I definitely have some experience in working with Oracle Database, I am not an Oracle Ace, neither by title, nor by skills. So, I can surely be wrong in some of my conclusions. But I believe if I'm mistaken somewhere or missing something, I'd just be told so.

The message of this blog post is simple - APEX is a great tool, I loved it from the first sight (c'mon, I'm a database developer who was given a chance to play with desktop and mobile web applications using the tools I'm good at), but still, you should know how the tool works under the hood, regardless you use it for a long time, or only going to try the platform. You should, because it enables you to know what to expect from the framework and how to cook it better. The only point here which makes me sad a bit is the fact that there's very few official guidelines by Oracle on how to cook stuff better in APEX - and we don't have any choice but sort them out ourselves. Maybe, in a some while, we'll see more official whitepapers and other sources of recommendations on the matter.

It happened that we are people who work with the most valuable thing of our customers - data, so, don't let yourself think you can afford not to care about its security.

And remember - with great power comes great responsibility!