My Oracle XE setup adventure with Docker

NOTE: This blog post was left in draft limbo for several months. Some things in this post may feel disjointed and some things are now obviously being referenced in the past tense. 

DISCLAIMER: This is not an authoritative Oracle installation guide. Neither is this post the opinions of someone with expert knowledge of Oracle. This is just a blogger's tale of getting an installation of Oracle XE up and running with Docker with the minimum of pain and unnecessary administration so he can easily have an Oracle XE instance available for testing with MapGuide/FDO and other geospatial software.

So before I start the release cycle for the first beta release of MapGuide Open Source 3.1, I'd thought I'd try to investigate (and try to knock off) some long standing issues with Oracle with the King FDO provider.

Naturally, this requires I have an installation of Oracle (XE at a minimum) lying around, which I don't. So that means I've to install it. Having had the displeasure of working with Oracle in the past and encountering its horrible, UI-from-the-last-millennium setup process and administrative UIs, I really didn't want to go down that path.

And in YEAR(GETDATE()), I should no longer have to install Oracle to a bare metal OS. I shouldn't even have to do it inside a clean test Virtual Machine. We have docker, and there's bound to be an Oracle docker image on the docker hub, that I can pull down, spin up a container and have an Oracle installation ready to go.

So that's what I set out to do. Since Windows is the first priority platform of focus for any MapGuide/FDO issues, I needed to get docker installed on Windows. Unlike Linux, Docker support on Windows is not first-class (they're working on that ^) and currently requires a virtualization layer (eg. VirtualBox) for docker on windows to interface with. Fortunately, docker provides the Docker Toolbox package which makes setting up Docker on Windows or Mac an easy one click installer affair with everything to get docker up and running on Windows or Mac

• Docker engine
• Docker machine
• Docker compose
• VirtualBox to interface with the barebones Linux VM hosting the docker engine
• Kitematic, for downloading of docker images and spinning up of docker containers through an easy to use GUI

With docker installed, it was now a case of finding an appropriate Oracle docker image on the docker hub, and Kitematic makes this dead simple. So after typing in "oracle xe" a few images were available, I gravitated towards the one with the one with the most likes and downloads.

Clicking the Create button starts the downloading process

Once downloaded, Kitematic automatically spins up a running container for the freshly downloaded docker image. Kitematic has a Web Preview option, which I presume points to the Oracle APEX manager, which I recall from my frail memory was the web-based admin for an Oracle XE installation. I don't know what black magic Kitematic does to auto-magically know this container was web-previewable, but the fact this was available was much appreciated. So clicking the preview button opened up Chrome to what I presumed to be the URL of Oracle APEX.

Well, at this point I got an unexpected http authentication prompt. Okay, so I tried the documented login for this container with no luck. What the ...? Okay, so I cancelled the prompt and tried refreshing the URL and then it suddenly takes me to the Oracle APEX login page. Weird!

So anyways, now that I'm at the login page, I login with the given credentials and was greeted with the visual confirmation that I now had a running Oracle XE installation via docker!

Except, this is not the APEX web admin, it's actually the database home page. Turns out, if I put in this address manually in the web browser (http://192.168.99.100:32768/apex) it will take me to APEX, if I open this through the web preview option in Kitematic, it takes me to the database home page. Weird!

Still at the end of the day, I had a running Oracle XE instance. Much more pleasant than my previous Oracle installation experiences!

Now it's time to bootstrap this Oracle database with some spatial data, so in the interest of eating my own dogfood, I gave FDO Toolbox a spin to see if I can load data into the fresh Oracle XE instance. So first things first, we check the running container in Kitematic for the IP and ports of this container and the ports that are exposed.

Then I proceed to use FDO Toolbox to try and create an Oracle data store via the King Oracle provider

But then ...

No dice, I don't know what happened here. I swore recalling some time ago in the long distant past that it should've been possible to bootstrap a clean Oracle database from scratch with the King Oracle FDO provider and FDO Toolbox, but try as I might, I just couldn't get it to work this time round!

So when FDO failed, it was time to make the hot tag and bring OGR into the fold. Running the venerable ogr2ogr utility brought the SHP file data into oracle flawlessly. Except, it is excruciatingly s l o w to get anything out of this Oracle database! A basic spatial table listing from (eg. A FDO feature class listing) takes an eternity.

It was a this point I paged a resident Oracle expert and realized that one shouldn't try connecting and loading in data as the SYS/SYSTEM user as that it means we bring in EVERYTHING when doing the simplest thing like listing spatially-enabled tables from SDO_GEOM_METADATA

So I fire up SQLPlus and connect to this XE container using the documented admin login

sqlplus system/oracle@//192.168.99.100:32769/xe

I then make a MapGuide oracle user

SQL> create user mapguide identified by mapguide;

Then give it sufficient privileges

SQL> grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
>   CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
>   CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
>   CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
>   to mapguide;

Then I exit SQLPlus and proceed to ogr2ogr my sample SHP file into this user's oracle schema

ogr2ogr -f OCI OCI:mapguide/mapguide@192.168.99.100:32769/xe Parcels.shp

Now when I connect with FDO Toolbox again (with emphasis on specifying my oracle user's schema, otherwise it's going to I presume, hit ALL_SDO_GEOMETRY_METADATA un-filtered, which performance-wise means we'd be back to slow square one!)

It is now connecting and performing at the performance level we are expecting.

And if we can connect to this Oracle XE database with FDO Toolbox, it means we can also connect to it with MapGuide proper, which is a case of setting up a new Feature Source, with the same connection parameters.

And from here, it's the standard MapGuide authoring process of creating layers pointing to this Feature Source, styling them up and composing them together on a map.

So what have I learned from this exercise?

1. Docker is just plain awesome. I didn't want to touch the Oracle installation process and I also didn't want to "taint" my host environment with an Oracle installation either. Docker made spinning up a repeatable and disposable Oracle XE environment dead simple. Even docker on windows, which requires a Virtual Machine intermediate layer is still a seamless experience thanks to the Docker Toolbox suite of integrated tools.
2. Either something regressed or FDO Toolbox will need some work to be able to load data into Oracle using the King.Oracle provider, which is strange as FDO is supposed to be an abstraction layer and FDO Toolbox is just simply working against the abstractions provided by the FDO API. If I have to code in Oracle-specific code paths and behaviour to get this functionality to work, then that is a sign of a leaky abstraction. I hope I don't have to do this!
3. Thankfully where FDO failed in this task, OGR was able to pick up the slack. FDO and OGR have this nice duality where one's strengths covers the other's weakness.
4. Always create a new Oracle user after install and load the spatial data into this user's schema, so you don't have to ask yourself why the default SYS/SYSTEM login/schema takes an eternity querying SDO_GEOM_METADATA tables.
5. Be sure to set that OracleSchema connection property in your FDO connection, even if it's a non-SYSTEM user.

Many thanks to Gordon Luckett, for the various pointers on Oracle.

^ Windows 10 and Server 2016 now have native docker hosts which can run both Linux and Windows containers. No more intermediate virtualization layer required. Being able to containerize windows applications is game changer! Older versions of Windows will still have to go the virtualization route via VirtualBox for the Windows Docker client to work.