Oracle XE 11g Win64 on Microsoft Azure cloud

(Or: "How to build your own Oracle Cloud in 25 minutes"... ! )

After downloading the recently released Oracle Express Edition for 64-bit Windows, I decided to test it out on a Windows Server 2008 R2 running in the Azure cloud (recently renamed from "Windows Azure" to "Microsoft Azure", I guess because you can run Linux on it, as well).

Here are the high-level steps needed to get this up and running:

1. Sign up for Azure and login to the management portal.
2. Create a new Virtual Machine, select Windows Server 2008 R2. The provisioning of the VM takes around 5 minutes. (I chose to use an A1 instance type, which costs around USD 50 per month, and has 1 core and 1,75GB of memory. Note that XE is limited to use only 1GB of memory, leaving some extra memory for the OS itself and for IIS).
3. When you create the VM, you specify a DNS name for it, for example "myxetest". This becomes the subdomain of Microsoft's "cloudapp.net" domain, for example "myxetest.cloudapp.net". If you want, you can use your own domain name by adding a CNAME record to your domain and pointing it towards the cloudapp.net subdomain.
4. Use Remote Desktop to login to the new server.
5. Download Oracle XE for 64-bit Windows. The file is around 300MB, so this takes less than a minute to download.
6. Unzip the XE file and run setup.exe. Specify a password for the SYS and SYSTEM users, and let the installer do its work. The whole setup process took about 10-15 minutes on my server.
7. Check that the database is running using sqlplus on the command line on the server.
8. Oracle XE 11g comes with Apex 4.0 preinstalled, and running via the Embedded PL/SQL Gateway (DBMS_EPG) on port 8080 (localhost only). You can verify that it's up and running by going to http://127.0.0.1:8080/apex/apex on the server. If you want EPG to work for remote hosts, you have to execute the dbms_xdb.setlistenerlocalaccess procedure and pass FALSE to enable non-local access. (You can also change the port used by EPG via the dbms_xdb.sethttpport procedure.)
9. You need to modify the Windows Firewall settings on the server to allow outbound traffic on port 8080. Finally, you also need to open up port 8080 for the VM in the Azure management portal (you'll find this setting under "Endpoints" for the VM.)

Here's a screenshot after the database has been installed:

The Apex homepage on localhost:

The Apex builder in action, accessed via the default cloudapp.net domain:

A note on production deployments

The Embedded PL/SQL Gateway (DBMS_EPG) is intended for development environments, and is not recommended for production. Since you are running on a Windows server, you probably want to leverage the excellent built-in Internet Information Server (IIS), with the Thoth Gateway which is an open source ASP.NET implementation of the OWA interface which is what the EPG, mod_plsql and the Apex Listener (now called Oracle REST Data Services, or ORDS) use to communicate with the database. You'll need to enable/install IIS first, as this is not enabled by default on a Windows 2008 server. Then download the Thoth Gateway and follow the installation instructions in the downloaded zip file.

Oracle Express Edition (XE) 11g for Windows 64 bit

As announced by Kris Rice (who is the product manager for SQL Developer, Apex Listener and Oracle XE), the Oracle Express Edition (XE) Database version 11g is now (finally!) available for 64-bit Windows.

It's been a looong wait; the 64-bit version for Windows was expected to be released around the same time as the 32-bit version, some 3 years ago.

Anyway, it's here now, which is great! Thanks Kris! :-)

Oracle Express Edition 11g can be downloaded here, it now comes in three flavors (Win 32 bit, Win 64 bit, and Linux 64 bit).

Useful Git commands

This is not related to Oracle at all, I'm just putting this here as a reminder to myself...

git log

Show a compact list of commits for a given period, formatted nicely:

git log --since="2 weeks ago" --pretty=format:"%h - %an, %ar : %s"

gitk

Start the default graphical interface for viewing the logs/commits:

gitk


Start the graphical interface and show the history for a single file:

gitk db/src/some_file.sql


More to come...

Oracle on Windows Azure pricing revealed

I just noticed that prices for Oracle Database running on Windows Azure have now been published.

The prices for the virtual machines with the Oracle license included run from about USD 820 per month for Oracle Standard Edition (SE) running on 2 cores, to a whopping USD 9300 per month for Oracle Enterprise Edition (EE) running on 8 cores. These prices are for Oracle software only and do not include the cost of the VM, which is billed separately.

Alternatively, with "License Mobility", you obtain a license as normal from Oracle and run that software on Windows Azure by deploying a Windows Server or Linux VM and installing a copy of the licensed Oracle software. Azure is listed as an "Authorized Cloud Environment" in the Oracle Cloud Licensing Policy. As I understand it, this policy allows you to use Oracle Standard Edition One (SE1) on Azure (up to 4 cores = 1 processor license, up to 8 cores = 2 processor licenses), which would be a quite cheap entry-level option for small businesses, since you would pay once for a perpetual database license, and then just pay monthly for the Azure VM (ie just the Windows/Linux Server software, not the database).

There's also an article at ZDNet with more details.

Pivot Table plugin for Apex

UPDATE 10.02.2018: A new version of the plugin has been released, see this blog post.

An interactive Pivot Table lets the user analyze a data set by selecting which fields to use as rows and columns in a grid. You've probably seen this in Excel, something like this:

I've created a region plugin for Apex which turns any SQL query into an interactive pivot table. The plugin is a simple wrapper for a pivot table written in Javascript by Nicolas Kruchten. There's also a nice tutorial page that explains the user interface.

This animated screenshot shows how the plugin is set up and used in Apex:

Note that you can have multiple Pivot tables on the same page if you want. You can also turn off the interactive features if you just want to display a preset summary.

You can download the plugin here. Enjoy!

Thoth Gateway version 1.3.7 available, including source code

There is a new version of the Thoth Gateway, a mod_plsql replacement for IIS, available for download. The latest version is 1.3.7.

This version also includes the source code for the gateway. The project has been "open source" (under a BSD license) since day 1, but I never got around to actually publishing the source code, until now that is! :-)

This version fixes an issue with page rendering that was only impacting Apex 4.2 installations. Thanks to Patrick and Christian on the Apex developer team who responded quickly to my inquiries about the underlying changes in Apex that caused this problem to appear (the problem was caused by improper CGI setup in IIS/Thoth, not a problem with Apex itself).

Note also that this version removes support for OWA basic authentication (see the release notes in the downloaded archive for details). This should not have any impact on Apex applications at all (but may impact older, "pure OWA" applications).

Upgrades: For existing installations, simply overwrite the existing PLSQLGatewayModule.dll file in the "bin" folder with the latest version from the downloaded archive.

Roadmap for the Thoth Gateway

The goal for the next release of the gateway (tentatively called version 1.4.0) is to change the Oracle Data Provider for .NET (ODP.NET) from the "Unmanaged" to the "Managed" driver. By using the Managed (pure .NET) driver, the installation of the Thoth Gateway becomes even simpler, because it removes the need to have the full Oracle client (or even the Instant Client) on the system. Note that this will change the minimum system requirements from .NET Framework 3.5 to 4.0 (the version that the managed driver requires).

For Thoth Gateway version 2.0, it might be fun to implement RESTful Web Services, to catch up feature-wise with the Apex Listener. (The Thoth Gateway already has automatic SOAP Web Services built-in, though.) By the way, now that the source code is available, anyone can pick up the challenge and contribute new features to the gateway!

First mentions of Oracle 12c XE (Express Edition)

Oracle released an Express Edition (XE) of its 10g database back in 2005 or thereabouts, and this was later followed up by an Oracle 11g Express Edition database.

Oracle XE is great because it is lightweight and "free to develop, deploy and distribute". Dietmar Aust has posted a thorough clarification on XE licensing, where representatives from Oracle have confirmed that XE is indeed free. (The post also includes some good security advice in the comments.) Oracle XE has its limitations, but I've previously posted some performance benchmarks for Oracle XE, which shows that it is more than good enough for many use cases.

Back to the topic of this post... during the Q&A part of the "Oracle Database 12c Launch Webcast" there were several questions about a 12c version of XE:

Will there be an Oracle 12c Express Edition? It is planned, but there are no dates yet.

Is there an Express edition of 12c available? Planned, but no dates yet.

Will 12c be available for Express Edition? If so, schedule? Planned but no dates yet.

So basically, we now know that there will be an Oracle 12c Express Edition coming out, but we have no idea when. Hopefully it won't take 5-6 years, like the wait between 10g and 11g... (and when it arrives it will probably be for 64-bit Windows, which was promised for XE 11g, but which never materialized UPDATE: Oracle XE 11g for 64-bit Windows was released in June 2014).

But hey, it's free, and it's a great product, so it will be worth the wait! :-)