Monday, June 15, 2015

Installing Oracle XE, ORDS and Apex on CentOS - Part Two: Installation

This is part two in a series of blog posts about how to install Oracle 11g Express Edition (XE) with Oracle Application Express (Apex) on a CentOS Linux server, with Apex served by Oracle REST Data Services (ORDS) running on top of Tomcat and Apache.

Let's get right to it, starting with Oracle XE.

Installing Oracle XE

This is actually quite straightforward, assuming you have followed the prerequisite steps in part one.



As part of the installation, we created a user called oracle. It is useful to set up the default environment of this user to include the path to the sqlplus executable, so we can start sqlplus from anywhere.



Now, let's log in to Oracle as SYS and check that everything looks OK:



At this point you have an Oracle XE instance running, which also includes Apex 4.0 and the Embedded PL/SQL Gateway (EPG) running on port 8080. That is nice, but it's an old Apex version and the EPG web server is not really suited for heavy usage. We want the latest Apex version, and we want to use ORDS. Read on...

Installing Java


ORDS and Tomcat are both Java applications, so we need to install Java. Actually, we need the Java JDK (Java Development Kit), as opposed to just the Java JRE (Java Runtime Environment). There may already be something called the OpenJDK on the CentOS server, but we want the Oracle-supplied JDK, so let's remove OpenJDK and install the JDK that we downloaded from Oracle:

To have the Java binaries available from anywhere, we add the Java path to the bash profile of the root user:


Installing Tomcat


To install Tomcat, we will download the installation file directly to the server using the wget command, and then unzip it. Create a tomcat user to run the tomcat process.

To avoid conflicts with Oracle XE running the Embedded PL/SQL Gateway on port 8080, change Tomcat's default port number to 8090 by editing the server.xml file. (Note: Because we will put Apache in front of Tomcat, we won't ever access Tomcat directly on port 8090, and we will soon disable EPG, but let's just avoid possible conflicts anyway by assigning different ports.) It's also important to set the URIEncoding to UTF-8.

Next we need to create a script that can be used to start, stop and restart Tomcat as a service. Save the following as tomcat under /etc/init.d/


Then we need to set up the above script to run automatically if the server is rebooted.


Installing ORDS


The Oracle Rest Data Services (ORDS) installation consists of unzipping the installation file, running the configuration to specify database details, and then copying the ords.war file into the Tomcat webapps folder.


Installing Apache


The last step in completing our web stack is to install the Apache HTTP server and place it "in front of" Tomcat. This means that all requests to the server go to Apache first. Requests for static files (images, Javascript and CSS) is served directly by Apache. Requests for dynamic content (ie the actual HTML pages generated by Apex via ORDS) is served by Tomcat, using Apache as a proxy.

Installing Apache is very straightforward:

Then we need to add our custom configuration. By default, Apache is set up to read any .conf file placed in the /conf.d/ subfolder, so let's create an apex.conf file there. Note that these additional config files are read and processed in alphabetical order, so name your custom config accordingly if you use multiple config files.


Installing (upgrading to) latest Apex version


Finally, we need to upgrade the Apex installation that came bundled with Oracle XE to the latest and greatest Apex version (version 5.0 at the time of writing).

This is done by unzipping the Apex installation file, then running the Apex installation script via sqlplus. There are two different Apex installations to choose from: Either a full installation that includes the Application Builder (suitable for a development environment), and a more lightweight and secure "runtime-only" installation (suitable for test and production environments). Running the full installation on the standard 1GB server at DigitalOcean should take about 12-15 minutes.


We also need to make sure the apex_public_user schema is unlocked (and stays that way!).

We need to copy all the static Apex files (images, CSS, Javascript) to the Apache web folder.

When running on top of ORDS, Apex 5 uses the "RESTful Service" feature to serve any application-specific or workspace-specific static files, so we need to configure Apex with REST:

Now (finally!), if everything works, we should be able to access the new Apex installation by going to the following URL:

  http://servername/ords/apex

If everything works, you should see this familiar page:



Did it work? Great, now enjoy Apex 5! But wait, we are not fully done yet! In the next part of this series, I will describe various additional configuration that you should perform for a more secure and scalable server.

Stay tuned!

Tuesday, June 9, 2015

Installing Oracle XE, ORDS and Apex on CentOS - Part One: Preparation

This is part one in a series of blog posts about how to install Oracle 11g Express Edition (XE) with Oracle Application Express (Apex) on a CentOS Linux server, with Apex served by Oracle REST Data Services (ORDS) running on top of Tomcat and Apache.


Provisioning the server


First of all, we need a server to install the software on. There are many hosting providers that offer cheap Linux servers. I have been trying out DigitalOcean, where you can get a nice little server suitable for Oracle XE for as little as USD 10 per month. After you sign up, a new server complete with the operating system installed can be up and running in as little as 50 seconds (!). It is also very easy to scale up (or down) the server according to your needs.

A note on security


Linux security is a big topic in itself. In order to keep this blog post short and sweet, I will describe some security recommendations in a separate blog post. But in general, always use strong passwords (and/or certificates and/or two-factor authentication), never install more components than you need, and always apply the principles of minimal privileges and defense in-depth.

Creating the server


First you need to sign up to DigitalOcean (or another cloud provider of your choice). The sign-up process is quick and painless. I like that you can pay DigitalOcean using PayPal, so you don't need to give them your credit card details.




After signing up, create a new server (called a "droplet" in DigitalOcean's terminology).
When choosing a server size, keep in mind that Oracle XE cannot use more than 1 GB of memory. As we will see later, you can happily run Oracle XE together with Apache, Tomcat and ORDS, all on a 1GB server. (It is tempting to compare that to, say, the minimum requirements for Sharepoint... but let's stay on topic! :-)



You get to choose the physical location of the server. Select a data center that is geographically close to you and your users, as this will obviously reduce data transfer times across the network.

Select the 64-bit version of CentOS 6.5 (while CentOS 7.x is also available, this is not in widespread use yet, so I'm sticking with the more well-known version 6.x for now).


Logging in to the new server


When the server is created, you get the "root" password emailed to you, so you can login to the server. The root user on Linux is similar to the local Administrator account in Windows. As we will get back to later, it is actually recommended not to use the root user regularly, and only "become root" (via the "sudo" command or via other users defined as administrators in the "sudoers" file) when necessary. To keep things simple, we'll use the root user for now, but get back to security best practices in a later post.

Logging in to the new server is done via "ssh" (secure shell). If you have a Mac, just open a terminal and type ssh root@server_ip_address and when prompted enter your password to login.

If you have a Windows machine, there is no built-in ssh client, so you need to install some additional software. I recommend pimping up your Windows console window with cmder, a good-looking console emulator (make sure you download the full version of cmder that bundles the "msysgit" suite which includes a bunch of Unix commands, including ssh). Once you have cmder installed, use the same command as for the Mac above to login.

If you managed to login as root, run the command ls -la / and you should see something similar to this:



Congratulations, you now have your very own Linux server to play around with! :-)

Check out these links for more information:

Installing some basic utils


After logging in to the new server for the first time, let's install some basic utilities that we will need later. In CentOS, the "package manager" that you use to download and install software from a standard repository is called "yum". Run the following commands:


The basic utilities include "nano" (a text editor which I prefer over the default "vim" editor; the latter is probably very productive for power users, but a pain in the ass to use unless you remember a series of cryptic commands -- a bit like Linux in general I guess!), as well as "wget" (to get/download files from the Internet using the command line), "unzip" (self-explanatory) and "bc" (a basic calculator).

Setting the server time zone


It's useful to have the server date and time automatically synchronized based on a remote server, so let's set up Network Time Protocol (NTP).


Adding swap space


Oracle XE needs a certain amount of swap space (a file where the operating system can "swap" stuff from memory to disk when there is too little physical memory available), so let's set that up.


Setting up the fully qualified domain name (FQDN)


The "hosts" file contains a mapping between IP addresses and domain names. We need to make sure that the server's IP address is mapped to a "fully qualified domain name" (FQDN), otherwise the Oracle XE installation will fail, as described in detail here.

So, to make sure we have a fully qualified domain name in our hosts file, run nano /etc/hosts and put in a line with your IP address and your server name.

Setting up a firewall


We should only allow access to the server on the ports that we need. For a typical web server, this will be ports 80 and/or 443 (for HTTP and/or HTTPS), and port 22 (for SSH). Actually, you should change the default SSH port from 22 to some other random number, but we will get back to that later when we talk about hardening the server.

Tim Hall has written an excellent article about the Linux firewall, known as "iptables". Rather than repeat what Tim has already explained, I suggest you read his article, and then copy the following and save it as firewall.sh in the /root folder of your server. Then make the file executable by running chmod u+x /root/firewall.sh and then type /root/firewall.sh to run the script.


Uploading Oracle software to the server


As the final part of preparing to install Oracle XE, ORDS and Apex on the server, we need to upload the different installation files to the server, so we can run them there.

Lets create a folder on the server where we can put the installation files:

mkdir -p /u01/download

The -p flag makes it possible to create two (or more) folders with a single command.

Why is the root folder called "u01"? Turns out this is a naming convention for Oracle software that goes way back. Linux folder names don't always make much sense, but I guess "u01" is as good as any.

And I guess there is an argument to be made for the sub-folder to be called "upload", as we will copy, or upload, stuff into it, but because it holds installation files that we would normally download directly to the server, I have called it "download".

The thing is, when downloading stuff from Oracle, you generally have to click an "Accept License Agreement" radio button and also login with an OTN account to actually get access to the file you wish to download. If it wasn't for this, the "wget" command could be used from the command line on the server to download the files directly to the server. As things stand, I find it best to download the desired software using a regular web browser on the client computer (ie my laptop) and then use the "scp" (secure copy) command to upload the files to the server.

So, go download the following software to your local computer:
  • Oracle Express Edition (XE) 11g for Linux x64
  • Java JDK 1.7 for Linux x64
  • Oracle Rest Data Services (ORDS) 2.0.10
  • Oracle Application Express (Apex) 5.0
I won't provide download links, as Oracle often changes the URLs on its web page and thus breaking the links, but a Google search should easily find these files on OTN. Download these to your local computer. Then copy the files to the server. If using a Mac, simply open a terminal window and use the scp command to copy the files:

If using Windows, either install a Unix emulation package (such as CygWin, cmder, msysgit, etc) that includes the scp command, or use the free WinSCP program if you prefer a graphical interface.

You should now have the installers for the various Oracle applications sitting in the /u01/download folder on the server.

Next Steps


Finally, we are now ready to install the Oracle software! This will be covered in part two of this series of blog posts.

Monday, June 8, 2015

Installing Oracle XE, ORDS and Apex on CentOS

I am writing a series of blog posts about how to install Oracle 11g Express Edition (XE) with Oracle Application Express (Apex) on a CentOS Linux server, with Apex served by Oracle REST Data Services (ORDS) running on top of Tomcat and Apache.

This is perhaps better explained with an illustration of the setup:

Best of all, this setup consists only of free (license-free) software, and as you will see in this series of blog posts, you will be able to run it all on a cloud server for as little as USD 10 per month, serving hundreds of concurrent users. Gotta love Apex! :-)

The articles will be divided up as follows:

These blog posts will assume that you are familiar with Oracle and Apex, but that (like me) you are a relative newcomer when it comes to Linux. Keep in mind that with Linux, there are typically many alternative ways to accomplish things, and I have chosen the approaches that seem most straightforward to me. Your mileage may vary.

Stay tuned for part one!

Thursday, April 23, 2015

Thoth Gateway moved to GitHub, now supports Apex 5.0

As with the Alexandria PL/SQL Utility Library, the source code for the Thoth Gateway, a mod_plsql and Apex Listener/ORDS equivalent for Microsoft IIS, now has a new home on GitHub. This should make it easy for anyone to look at and perhaps contribute to the code.



If you don't care about the source code, you can head straight to the releases page to download just the binaries. Updated installation instructions for IIS 8 will be forthcoming, but deploying the Thoth Gateway is really as easy as just dropping the binaries into the wwwroot and using IIS Manager to turn the folder into an application.

Also, there were a few minor tweaks needed to support Apex 5.0. As far as I can tell from my own testing, everything now works, but do let me know if you have any issues.

Download the lastest version of the Thoth Gateway with Apex 5.0 support here.

Tuesday, February 24, 2015

Alexandria PL/SQL Utility Library moved to GitHub

The PL/SQL Utility Library, codenamed "Alexandria", now has a new home at GitHub. This will make it easier to collaborate on the project. If you want to contribute, just fork and submit a pull request.


The liibrary contains around 50 PL/SQL packages as well as a few types. There are no table dependencies, which reflects the general-purpose nature of the utilities. Jeffrey Kemp has a great guided tour of the library which highlights some of the packages.

Before the move to GitHub, the latest version (1.7.0) had been downloaded almost 7,000 times, and all versions a total of 12,000 times. So I guess it's being pretty widely used...


You can download, clone or fork the library at https://github.com/mortenbra/alexandria-plsql-utils

Sunday, November 16, 2014

Thoth Gateway 1.4.0 beta release 1 available

As mentioned in my previous post about the Thoth Gateway, a mod_plsql and Apex Listener alternative that runs on IIS, 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).

There's now a beta version of Thoth Gateway 1.4.0 available for download here. I've tested it myself successfully on Windows Server 2012 with IIS 8.5 and .NET Framework 4.5. I would appreciate feedback from those who want to download and test it out on the same, or other, configurations.

Please note the following:
  • The gateway no longer requires an Oracle client installed on the same machine (the web server). Similar to the way the "Thin JDBC" driver works, the "Managed ODP.NET" driver is just a single file (Oracle.ManagedDataAccess.dll) that is placed in the same "bin" folder as the gateway.
  • The supplied web.config file includes a configSections/SectionGroup with the .NET 4.0 sections. If upgrading from an earlier framework version running the gateway, you probably need to update the config file.
  • The "requestValidationMode" attribute of the "httpRuntime" node should be set to "2.0" to avoid problems when Apex passes HTML markup in the querystring (for success messages and the like).
 Please leave a comment here, or in the Google Code issue log, if you had any problems or indeed if you were able to install and run it successfully.

Sunday, July 13, 2014

The APEX_JSON package: Generating JSON from PL/SQL

In my previous blog post, I took a look at the new APEX_JSON package that ships with Apex 5.0 and its capabilities for parsing JSON.

In this blog post, I am going to look at how the APEX_JSON package can be used to generate JSON from data in your database using PL/SQL.

There are multiple ways of creating JSON output using the APEX_JSON package. There are several overloaded write() procedures which can output simple types (like varchars, numbers, and dates), as well as those that print complex types (such as xmltype and ref cursors).

First, a simple example which prints a simple JSON structure with some hard-coded values combined with a value based on user input. The page setup in the Apex Page Designer looks like this:



Note the call to apex_json.initialize_output() which disables the automatic sending of JSON headers in the HTTP response. This is normally desirable when you are sending (just) JSON back to the client, but in my test application I want to output the JSON inside a normal HTML page, so I need to disable the JSON headers. The rest of the code consists of simple calls to open/close_object and write() to write the name/value pairs.

The output looks like this:



Next up is the very useful ability to generate JSON based on a ref cursor. This means that basically any SQL query, dynamic or static, can be transformed into JSON output, with just a single line of code. In my simple test application I've set up a PL/SQL region that opens a ref cursor based on user input, and then passes the cursor to the write() procedure.
NOTE: Using dynamic SQL based on unsanitized user input makes your application vulnerable to SQL injection. If possible, you should always use static SQL instead of dynamic SQL. Also, never trust user input and always validate it (see, for example, the dbms_assert package) before using it in a dynamic SQL statement. The example code below does not follow these security best practices, in order to keep the example simple.



Here's the result in the running application. Note that the output even supports nested queries (via the CURSOR statement), which makes it possible to generate complex/nested JSON from a single SQL statement.


Lastly, the APEX_JSON package also supports output of JSON via XML, by passing an xmltype value to the write() procedure. This is very useful in itself, but can also be combined with other PL/SQL features, such as the ability to convert any database object type to XML via the XMLType constructor.

Consider this example, where I create a database object type called T_CAR, with multiple attributes. The example SQL statement shows I can instantiate a T_CAR object and then convert the object into XML.



I can then pass the resulting XML into the write() procedure of the APEX_JSON package to generate a JSON representation of the T_CAR object type.



Here's the output:



This quick look at the APEX_JSON package from Apex 5.0 shows that this new package gives developers a lot of power and flexibility in terms of parsing and generating JSON from PL/SQL.