Sweet 18 - A New Oracle XE Release

What a prelude to Oracle OpenWorld 2018 (OOW18)! ICYMI, the next-generation Oracle Database 18c Express Edition (18cXE) was released late last week. The database software comes with a bunch of free enterprise features and options that usually come with a hefty price tag. Among them, Oracle Advanced Analytics, Oracle Advanced Security and Oracle RDF Semantic Graph are my favourites! See the Features Availability for more details on the Oracle great giveaway!

Since Saturday, I have been kept busy working on getting the development environment ready for Fabe. It was just weeks ago since we had the environment up and running on 11gXE. The instance is fully driven on the back of Docker that made it easier to swap out the database.

I couldn't wait for Gerald Venzi and his team to publish the official Docker images for 18cXE, so I went ahead to create my own. My working files are available on GitHub and is somewhat based on their excellent work. Martin D'Souza has since also contributed some few "minor" changes.

Do note however, that I highly recommend using the official images instead, as soon as they are released. Another caution is that data files and database configuration persistance have not been implemented. Dropping your container will likely cause complete data loss. You have been warned.

I followed the "Exporting and Importing Data for Oracle Application Express (APEX) Users" procedure to move data between the two database versions. The process was simple and straight-forward, but I was nervous nonetheless. The following are some additional notes that I thought might be useful to fellow Oracle Application Express developers.

Before launching the new 18cXE container, I performed a data dump using Oracle's Data Pump utility. The existing 11gXE container was then dropped. Prudence would have insisted that I left the container running, but I wanted to reuse the container name, so I had it blown away. The original container was created using the official Docker images and a host directory was mounted to the container as /u01/app/oracle/oradata, keeping the data and configuration files safe, so I had very little concerns deciding to delete the container once the data dump was done.

For convenience, I had set the DUMP_DIR to /u01/app/oracle/oradata/export, ensuring access after the 11gXE container had gone away. Then comes the fun part: building and running the brand new 18cXE Docker container. It took a few minutes, but was otherwise a very smooth process.

Once the database was up and running, the next thing to do is to perform the data dump import process. Using the docker cp (copy) command, I uploaded the dump file to the container's /opt/oracle/oradata/export. After that, I used the docker exec to get a Bash session. Once inside, I changed the ownership of the dump file: chown oracle.oinstall /opt/oracle/oradata/export/db11g.dmp.

With everything in place, I switched to the oracle user (su - oracle), set the environment variables (. oraenv) and then proceeded to use impdp to import the data. Make sure that the REMAP_DIRECTORY parameter was duly added as the default ORACLE_BASE path is different between 11g and 18c. It was rather unnerving as I was seeing a lot more errors than what was listed in the documentation that could be safely ignored. Thankfully, the process appeared to have completed successfully.

Running apxfix.sql was a first for me. APEX had already been updated to 18.2 in the previous 11gXE container and so I executed the following command in SQL*Plus: apxfix.sql APEX_180200. Felt like an APEX patching/upgrade process, but I'm sure there was a little more to it. It didn't take very long to complete and to my relief, it was error free. From here, I skipped over the next two steps in the documentation as I had APEX already configured to use Oracle REST Data Services (ORDS). The last prescribed step was to unlock the ANONYMOUS account in the CDB.

Beyond what was documented, here are a few other steps needed to get the APEX instance up and running successfully:

1. For the ORDS configuration, the db.sid parameter in <CONFIG_DIR>/ords/defaults.xml was replaced with db.servicename and set to XEPDB1, the PDB created by default during the database configuration process.
2. Update the network access control lists (ACL) using the instructions for Oracle Database 12c or later:

begin
  dbms_network_acl_admin.append_host_ace(
    host => '*' -- or be specific on the host to allow access to
    , ace => xs$ace_type(
        privilege_list => xs$name_list('connect')
        , principal_name => 'APEX_180200'
        , principal_type => xs_acl.ptype_db
      )
  );
end;
/

If you are transitioning directly from 11g and have little or no experience with Oracle Multitenant databases, please be especially mindful of the container switching steps (alter session set container=XEPDB1;) in the migration documentation. It takes a little effort to learn the new concepts, but the knowledge and experience are essential as non-CDB databases will soon be history. From now on, your work happens in a pluggable database (PDB) and will pretty much leave the container database (CDB) alone. You get three PDBs to use in 18cXE, more than what's available in current standard and enterprise editions without purchasing the additional multitenant database option.

Feeling privileged? You should! Make full use of these goodies and build even more robust, secure and intelligent APEX apps!