My Oracle Database Developer Choice Awards 2015

Earlier this week, Santa's little helper (ie DHL) knocked on my door and delivered a package containing my trophies from the Oracle Database Developer Choice Awards 2015. I was nominated in three categories, and won awards in two of them: Application Express and ORDS.




Here's a video from the award ceremony at Oracle OpenWorld 2015:

I'd like to send a big THANK YOU to everyone who voted for me, and also to Oracle for organizing the awards and handing out such cool trophies! :-)

SQL Developer 4.1.2: Missing MSVCR100.dll file

I downloaded the latest version of SQL Developer (4.1.2) to a just-provisioned, pristine server running Windows Server 2012 R2.

I downloaded sqldeveloper-4.1.2.20.64-x64.zip, unzipped it, and placed it under c:\program files\sqldeveloper-4.1.2.20.64-x64\. I double-clicked the main sqldeveloper executable and expected it to "just work" (as it usually does), but this time it didn't.

I got the following error message:

sqldeveloper64W.exe - System Error

The program can't start because MSVCR100.dll is missing from your computer. Try reinstalling the program to fix this problem.
 
I searched the disk and found a copy of msvcr100.dll at C:\Program Files\sqldeveloper-4.1.2.20.64-x64\sqldeveloper\jdk\jre\bin\msvcr100.dll.

I then copied the above file to C:\Program Files\sqldeveloper-4.1.2.20.64-x64\sqldeveloper\sqldeveloper\bin\msvcr100.dll

I then double-clicked the main sqldeveloper executable again, and this time it started without errors.

How to change apex_public_user password in ORDS

When you install Oracle REST Data Services (ORDS) as the gateway for your Oracle Application Express (APEX) applications, you run the java -jar ords.war command to configure ORDS, including specifying connection strings and passwords for the service accounts apex_public_user, apex_listener and apex_rest_public_user.

The first time you run this command, you get asked where to store the configuration. A file called defaults.xml gets created in the folder you specify. It looks like this:

There's also a subfolder called conf, which contains a configuration file for each of the three service accounts mentioned above. The one for apex_public_user is called conf/apex.xml and it looks like this:

You can change any of these settings by modifying the config file (remember to restart the web server, ie Tomcat, after you have saved the changes).

But what if you need to change the database password? As can be seen from the screenshot, the password value is encrypted, so you cannot simply modify this value directly. What do you do then? You need to re-run the ORDS config with the setup flag:

# to change the database connection config in ORDS:
su - root
# assuming this is where your ORDS config file is located
cd /u01/ords
# re-run the ORDS config
java -jar ords.war setup
# for an existing setup, should get message "INFO: Using configuration folder: /u01/ords/config/ords"
# follow the prompts to specify the database host, port, sid, and passwords
# should get confirmation "INFO: Updated configurations: apex, apex_al, apex_rt"
# now restart the web server to pick up changes
sudo service tomcat restart

That's all there is to it... simple when you know how! :-)

UPDATE: Kris Rice pointed out that you can also edit the password directly in the config file by placing an exclamation mark before the password, for example "!your_password" (without the quotes). When you restart Tomcat, ORDS will update the config file with an encrypted version of the password.

Apex 5 right side column

The new Universal Theme in Apex 5 includes a nice "right side column" feature that adds a sliding menu on the right side of the page, where you can place additional content, such as (for example) an audit trail, actions/links, comments, whatever.

Let me show you with a picture:

To add this to your page, simply change the page template to "Right Side Column", and note that a "Right Column" template position appears in the grid layout pane:

You can then place whatever you want in this template position (any region, with buttons and items as usual). Here, I have placed two different regions on top of each other:

That's all there is to it. Again, Apex 5 with the Universal Theme makes this very easy.

By the way, have you checked out the Universal Theme sample application? You can install it into your own workspace from the Packaged Applications gallery, and it is also available online at https://apex.oracle.com/ut

Using this application you can browse the various templates and template options built into the Universal Theme. For example, it showcases the "Right Side Column" template with thumbnails and sample pages, and also tells you how to implement it.

Easy tab regions in Apex 5

In Apex 4, if you wanted to implement tabbed regions, you had to use jQuery UI Tabs or some other third-party component.

With Apex 5 and the Universal Theme, tabs come built-in and setting it all up is extremely easy.

To add tabs, just add a Static Content region to the page, and change its template from "Standard" to "Tabs Container".

Then add Sub Regions to the region. In the component tree, any sub regions you add will appear under the "Tabs" node which has a folder icon.

Add as many sub regions as desired. For best results, set the template of these sub regions to "Blank With Attributes". Add content and items to these sub regions.

You can also set some template options for the tab container region, such as "Remember active tab" (between page views) and to set the visual style of the tabs.

That's it! Happy tabbing! :-)

KPI Icon item plugin for Apex

Here's a very simple item plugin that you might find useful. The item plugin displays a Key Performance Indicator (KPI) icon based on the item value.

You can set the threshold values for green and red (and everything in-between will be yellow). Also, you choose whether high values are good or bad.

Since the item itself is a regular Apex item, you can assign a value to it using a computation, process, PL/SQL expression, etc.

The icon is displayed using Font Awesome (bundled with Apex). The value is displayed as a tooltip when you hover over the icon.

Download the plugin here.

Mockup Table region plugin for Apex

Today's post is about a new plugin that I developed for Oracle Application Express (Apex).

The plugin was inspired by a product called Balsamiq, which is a tool for creating mockups (or "wireframes") of web pages and web applications. Balsamiq is used as a drawing tool to quickly sketch out your user interface before building it using whatever technology.

Balsamiq is a nice tool, but so is Apex! :-) Why spend time mocking up screens in Balsamiq when you can use Apex to quickly lay out pages and regions? With Balsamiq, the end result is a set of fake, static wireframes. With Apex, you actually get a real, live, responsive web application that you can iterate and build upon until it is ready to use. You don't have to throw away anything.

However, that doesn't mean that we can't steal any good ideas from Balsamiq! :-)


Balsamiq has a nice Data Grid/Table widget that allows you to quickly mock up a table.

The plugin I created for Apex, called the Table Mockup Region plugin, allows you to do just that -- to quickly mock up table data on an Apex page without having to create database tables and write queries.

Which means you can quickly throw in some data, show it to your potential users, and then create tables and write queries later, when the design has been settled. Using the Apex 5 page designer, it is really easy to change the region type from the Table Mockup type to a classic or interactive report.

I made a short video (my first screencast ever, yay!) to demonstrate how easy it is to use:

Hope you find this useful, I had fun creating it. (The plugin code is actually less than 100 lines of PL/SQL! That's because all the hard work of parsing the delimited data is done using the CSV parsing package from the Alexandria Library.)

Download the plugin here.

ORDS Java heap space OutOfMemoryError

I recently ran into a problem with an Apex application running on ORDS on Tomcat. The application has a page with a custom tabular form (built using the apex_item package). When this page is submitted, the form values are stored in the "g_fxx" arrays (g_f01, g_f02, etc).

The problem was that when the number rows (and therefore the number of elements in the arrays) got too big, the server would respond with a HTTP 500 - Internal Server Error message.

The first thing to do in this case is to check the Tomcat logs at /usr/share/tomcat7/latest/logs to see what the real error message is. Tomcat generates one log file per day, and the log files have the naming format catalina.yyyy-mm-dd.log.

Opening the relevant log file, I found several occurrences of this error message (truncated for brevity):

oracle.dbtools.rt.web.WebErrorResponse internalError
SEVERE: Java heap space
java.lang.OutOfMemoryError: Java heap space
at oracle.jdbc.driver.T4CCallableStatement.doOall8(...)


So, Tomcat/ORDS was basically running out of memory when processing a page submission with dozens or hundreds of values (even though in my case the values were all contained in a few g_fxx arrays).

I googled a bit and found a recent post from the Apex forums that deals with the same issue. In my case I'm using ORDS 2.0.10, but it appears that the same issue can be experienced with ORDS 3.0.1.

Given that in this particular case my server only has 1 GB of memory, perhaps it's not that strange that the Java process would run out of memory (after all, the server is also running Oracle XE, Tomcat and Apache...).

To increase the memory available to Tomcat, you need to create a file called setenv.sh in the Tomcat bin directory, and make it executable.

nano /usr/share/tomcat7/latest/bin/setenv.sh
# paste the below text and save
chmod +x /usr/share/tomcat7/latest/bin/setenv.sh
service tomcat restart

Here is the content I put in the setenv.sh file (based on this):

#! /bin/sh

export CATALINA_OPTS="$CATALINA_OPTS -Xms128m"
export CATALINA_OPTS="$CATALINA_OPTS -Xmx512m"
export CATALINA_OPTS="$CATALINA_OPTS -server"

This sets the initial memory to 128MB and the maximum memory to 512MB (more info). The -server flag is to optimize execution when Java runs as a server (more info).

Remember to restart Tomcat for the new settings to take effect.

Sending SMS text messages from PL/SQL

Do you need to send SMS (Short Message Service) text messages from your Oracle database using PL/SQL? This is actually quite easy to do, the only hard part is deciding on which SMS gateway to use. You need to sign up with a gateway provider to get a username and password to use the gateway, and you typically pay some cents for each message you send.

Do a google search for "sms gateway api" to find a suitable gateway provider. I won't provide any specific recommendations or endorsements; for this blog post I just picked a random provider to use in the code example below. I have no idea if they offer a good/cheap/reliable service or not. (Leave a comment below if you have any recommendations that you would like to share.)

Most gateways offer an API that takes a HTTP(S) GET or POST request with some parameters in the query string to specify the recipient and the message itself, and then returns some kind of status back in some format (XML, JSON, plain text). The concept is usually the same, but the specifics vary.

I've added a new package called SMS_UTIL_PKG to the Alexandria PL/SQL Utility Library that provides a generic interface to any SMS gateway.

To use the package, you need to check the API documentation of your chosen gateway provider, and set up the URL template accordingly. You can use tags like #username# and #password# and #message#, etc. in the template, which will be replaced with actual values when sending a message.

Here is an example of using the package to set up a gateway and send a message:

There's also support for adding a callback to a custom error handler that you can use to determine if the gateway returns an error message. This should be a function that accepts a clob as an input parameter (this will contain the response message from the gateway) and returns a varchar2 with an error message, or return null if successful.

Remember that you might have to modify your database Network ACL settings to open traffic to the gateway's hostname. And if the SMS gateway requires HTTPS, you need to set up an Oracle wallet with the certificate and call the set_wallet procedure before you send the message.

Happy texting! :-)

My nominations for the Oracle Database Developer Choice Awards 2015

I'm honored to have been nominated for the Oracle Database Developer Choice Awards 2015, in no less than three (!) categories: PL/SQL, Application Express (Apex) and Oracle REST Data Services (ORDS).

Here's a short video that explains what the Oracle Database Developer Choice Awards are all about:

If you are a regular reader of my blog, you are probably familiar with my contributions to the Oracle Database Developer community, but here is a short recap of my work in the relevant categories:

• PL/SQL: I've been programming in PL/SQL for almost 20 years. I've written and released as open source the widely-acclaimed Alexandria PL/SQL Utility Library, which contains around 50 PL/SQL packages that deal with everything from general math and string utilities to Amazon web service integration, NTLM authentication, CSV parsing, MS Exchange integration, and much more. Jeffrey Kemp has a great guided tour of the library which highlights some of the packages. As of February 2015, the library had been downloaded more than 12,000 times. Furthermore, I've created a presentation called PL/SQL: The Good Parts, which in Steven Feuerstein's words is a "fast, entertaining high-level glimpse of what makes PL/SQL so great" (and Steven should know what he's talking about! :-).
• Apex: Since 2008 I've published numerous articles related to Oracle Application Express on this blog. Among other things, I've created popular Apex plugins, including a pivot table plugin and a plugin for generating on-demand dynamic PL/SQL content. I've created and open-sourced the jQGrid Integration Kit for PL/SQL. And I created ApexGen, a framework for generating Apex apps using PL/SQL code (which I may revisit and enhance in the future if the Apex team provides a supported API for it). I've also been active in the Apex community by asking and answering questions on the Apex forum on OTN, actively testing Apex early adopter (EA) releases for Apex 4 and 5, and reporting numerous technical issues and bugs back to the Apex development team. My coworkers know me as "the Apex guy" since I constantly promote the virtues of Apex for new development projects.
• ORDS: I've long been interested in the technology that connects the Oracle Database to the web, including the PL/SQL Web Toolkit (aka OWA) and the forerunners of ORDS such as mod_plsql and the Embedded PL/SQL Gateway. Using this knowledge I implemented and released as open source a mod_plsql alternative for Microsoft Internet Information Server (IIS) called the Thoth Gateway. I've published many articles on the use of JSON from PL/SQL, a key part of REST. Earlier this year I wrote a four-part blog post series with all the nitty-gritty details on installing and running Apex with ORDS that became very popular and widely distributed.

Now go and vote for me! :-)

Click here to vote in the PL/SQL category, here to vote in the Apex category, and here to vote in the ORDS category.

Thank you very much for your vote! :-)

Image credit.

PL/SQL: The Good Parts

Somewhat inspired by Douglas Crockford's Javascript: The Good Parts presentation (and book), I have created a presentation called PL/SQL: The Good Parts.

This is not intended as an introduction to PL/SQL, but rather an overview of features you should consider if you want to maximize your enjoyment of programming in the Oracle Database. Also, this is not an exhaustive list of all features, it's simply the ones I personally use frequently.

Note that unlike Javascript, I don't consider PL/SQL to have too many "bad parts" or bizarre quirks. There's a lot of good stuff, hence the title! :-)

Click here to view the presentation.

ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
 
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();
  l_clob := replace (l_clob, '<mytag xmlns="">', '<mytag>');
  -- and then strip out bogus namespace to make parsing easier...
  l_clob := replace (l_clob, ' xmlns="http://tempuri.org/"', '');
 
  l_xml := xmltype (l_clob);

The above had been working before, but now failed with "ORA-22926: specified trim length is greater than current LOB value's length".

Googling turned up a forum post which pointed out that getClobVal is deprecated, and the suggestion is to use XMLSerialize instead.

This can only be used in a SQL statement, not as a PL/SQL expression, so I had to rewrite as follows:

select xmlserialize(document l_xml as clob)
into l_clob
from dual;

And the error went away... (It seems some people have had problems even with the xmlserialize function, but that's quite an old post, so that particular bug could have been fixed a long time ago.)

Installing Oracle XE, ORDS and Apex on CentOS - Part Four: Stress testing

This is part four 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.

After setting up a small CentOS server with Oracle Express Edition (XE), I wanted to stress test it to see how much load such a barebones installation can handle. In other words, how many users can you serve using this minimal, license-free setup?

The test application

I set up a test application in Apex with a single page that has both some static and dynamic (PL/SQL) regions, as well as a query and some processes. I set the page authentication to "No authentication" as the various testing tools need to be able to access this page without logging in (in which case we'd have to make a more complex test script).

Just checking the page via a web browser we can see that it is delivered fairly quickly from the server, in less than 250ms, and that static resources are cached. (Not all automated load testers respect cache settings, though. More about that later.)

First tests - BlazeMeter.com

For testing, I started with BlazeMeter.com, as they have a free plan that can stress test a site with up to 50 concurrent users. This is probably more concurrent users than most business applications built for small and medium companies will have/need, and so the free test works well for our purposes. (This obviously depends on your definition of small/medium company, but if you have so many users/employees, then why are you using Oracle XE? :-)

Setting up a stress test using BlazeMeter is easy, although the user interface is a bit cluttered with advanced options that can all be ignored for simple tests. Because we only want to load test a single page, we need to create a so-called "URL Test", and give it a name and specify the URL. Select an appropriate location to run the test from, the number of users (50 is the max for the free account) and the duration (the default is 20 minutes, which may be a bit long for simple tests; you can set this to 5 or 10 minutes for a quick test).

Running the first test

Having set up the test page in Apex and the test itself in Blazemeter, I started the test and watched it... crash and burn! :-( This is what the test results looked like:

As you can see from the chart above, more than a third of the requests resulted in errors returned from the web server, and the average response time is almost 10 seconds! Not good! I checked the Tomcat logs (at /usr/share/tomcat7/latest/logs if you have followed the setup in this series of blog posts) and found lots of this message:

java.sql.SQLException: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use

Turns out the default ORDS connection pool size is too small. I found this article which gives some advice. I changed the following in the ORDS configuration (/u01/ords/config/ords/conf/apex.xml if you have used the config location suggested in my previous blog posts) and restarted Tomcat:

Re-running the first test

Re-running the test gave the following result:

Much better! No errors, and the response time is pretty good for this minimal server. Remember it only has 1 CPU and 1GB of memory, running Oracle XE, Tomcat and Apache. And yet it quite happily serves 50 concurrent users (average of 3,5 requests per second) without any problems.

Scaling up

One of the nice things about DigitalOcean (and other cloud server providers) is that it is easy to scale up (and down) the server as needed. I decided to re-run the same test using a server with 2 CPUs and 4 GB of memory (although Oracle XE is limited to 1 CPU and 1 GB of memory, it should free up some more resources for the web server and other OS processes).

The test results on this somewhat bigger server shows a flatter response time curve, compared to the 1GB server which had a couple of random peaks. The average response time is less than 700ms, compared to 950ms for the smaller server. We are still limited to 50 users (average of 3,5 requests per second) since this is the maximum that BlazeMeter's free plan allows.

More testing - LoadImpact.com

I did further tests with LoadImpact.com, because they have a free plan with up to 100 concurrent users (or VUs, "Virtual Users", in their terminology). This is the test result with 100 concurrent users, run against the minimal server which been scaled back down to just 1 CPU and 1 GB of memory.

As the chart indicates, the response time remains more or less flat regardless of the number of concurrent users.

Never mind that the chart indicates that each page view took several seconds to complete. The FAQ page of LoadImpact states that "simulated clients in a test will never cache anything (except for cookies). This means that in a test, every client that loads a page from your site will behave like a new visitor to the site and thus be quite 'heavy' on the server." Which means that actual page load times (when client caching is enabled) will be significantly better than the tests indicate, since static files don't have to be downloaded every time.

In other words, the flat response time curve means that the application scales very well, and could probably support many more users. Quite impressive, really, for a setup that costs just USD 10 per month! :-)

Even more tests - loader.io

Finally, I tested with loader.io, who offer a free plan with up to 10.000 (!) clients in 1-minute tests. Running a number of different tests (and also bumping up the jdbc.MaxLimit setting from 60 to 100), I found that the "breaking point" for the server when using my test page was around 12-15 requests per second, which still gave sub-second response times. If pushed any further, the response times would quickly go up to several seconds.

I also ran some tests against a very barebones Apex page that shows nothing but a static HTML region.

When stress testing against this page, the server could handle up to around 25 requests per second while still maintaining sub-second response times. So obviously scalability depends on what you put on your pages.

Conclusions

Real-world performance will depend on a lot of factors. The test page I used was fairly typical for a business application in that it has a report, some dynamic PL/SQL content, and also does an insert into a table (so it's not just read-only). In a typical application, there will be pages that are more complex than this, and pages that are simpler than this, so it should average out.

So let's assume that the server can handle 15 requests per second with acceptable response times. What does that translate to in real-world terms? Here are some quick calculations (I'm assuming most Apex business apps will be used during an 8-hour office hour period each day):

• 450 users doing 1 000 page views each in an 8-hour period per day (450*1000/8/60/60 = 15 requests per second)
• 1 500 users doing 250 page views each in an 8-hour period per day (1500*250/8/60/60 = 13 requests per second)
• 10 000 users doing 45 page views each in an 8-hour period per day (10000*45/8/60/60 = 15 requests per second)
• 25 000 users doing 50 page views each in a 24-hour period per day (25000*50/24/60/60 = 14 requests per second)

Now remember, the above is based on a single server with 1 CPU and 1GB of RAM, running Oracle Express Edition (XE), Apache and Tomcat, and costing USD 10 per month! If this is not good value for money, I don't know what is...

Installing Oracle XE, ORDS and Apex on CentOS - Part Three: Additional configuration

This is part three 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.

By now, you should have a working installation of CentoOS with Oracle XE, Apex, ORDS, TomCat and Apache. There are still some additional configuration and optimization we should do, which we'll take a look at in this third installment of the blog post series.

This part is a "grab bag" of various tips. None of these steps are really required for a working installation, but most of them are strongly recommended for better security and performance.

Disabling root login

So far we have been using the root user to login to the server and perform administrative tasks. For various reasons it is better to disable logins for the root user and instead create an alternative user that can elevate its privileges to root level only when necessary.

This is explained in detail in this article.

Cleaning up Oracle XE

Let's clean up and disable/remove stuff we don't need from Oracle XE:

Optimizing Oracle XE

Let's change some default session, process and memory settings in Oracle XE:

Setting up Oracle XE network ACLs

If you plan to do any network calls from the database, for example to invoke web services, you need to explictly open the database Network Access Control List (ACL) to enable outgoing traffic to certain hosts/IP addresses and ports.

This is explained in detail in this article. Here is a sample script that can be used as a starting point:

Closing XDB port (8080) in the firewall

Remember to close port 8080 in the firewall, as it is not be needed in the default software stack we have set up. Revisit the firewall.sh script we created earlier and make sure that the line that opens port 8080 has been commented out or deleted from the firewall script. Re-run the script if necessary to make sure the change takes effect.

Optimizing ORDS

The default connection pool settings in the ORDS configuration are too small. You'll have to experiment to see what the best settings are for your workload, but the following seem to work well. Edit the /u01/ords/config/ords/conf/apex.xml file and put in the below JDBC settings. Restart Tomcat for the changes to take effect.

Cleaning up Tomcat

Let's remove the sample apps from the default Tomcat install, as we won't be needing any of it:

Cleaning up Apache

Let's remove the default Apache home page and replace it with our own. Do this by creating an index.html page on your local computer and then copy it to the correct folder on the server:

Optimizing Apache

Well, not so much optimizing Apache itself, but optimizing delivery of website content by making sure that the contents is being compressed and cached, which will speed up performance.

Actually, we already did this as part of the Apache installation (in part two of this blog post series). If you look back at the apex.conf file we created, it included configuration for the modules mod_deflate (for compression) and mod_expires (for caching).

You can verify that compression is enabled by looking at the headers returned from the server via Chrome's network tab (look for the Content-Encoding header):

Also, if you enable a two-line display of each request, Chrome will show the uncompressed file size as well as the compressed file size, so you can see how much bandwidth was saved.

You can verify that caching is enabled (for static files such as images and Javascript) by looking at the Expires header, which means that the browser should keep a copy of this file and not download it again until after it has expired:

I usually put an expiration of 7 days on static files. This means that typical business applications that are used daily or several times per week will always have a copy of the static files already downloaded, which speeds up things considerably. And if you do change the files (as part of an application upgrade), then it will take a maximum of one week before all your users have the latest files. (Tip: Users can always force a new download of cached files by holding down the Shift key and reloading the page in the browser.)

Chrome shows files retrieved from the cache as "(from cache)" and no time (0 ms) is spent downloading the file.

Adding robots.txt

Add a robots.txt file to the root of your website to avoid search engines hitting your website and wasting its resources and bandwidth. This assumes that you mostly have private (internal business) Apex applications that require logins, and since the search engine will not be able to login, we might as well tell the search engines to not even bother trying.


Here is a sample robots.txt that advises search engines to avoid certain folders. Note that the robots.txt file in no way blocks access, so don't try to use this to "secure" content.

If, on the other hand, you have applications with public pages that you would like to have indexed by search engines, then by all means change or skip the robots.txt file altogether. (Or look into the use of sitemaps to direct the search engine towards the important pages of your website.)

Setting up SSL certificates

Any website that requires authentication should be running via HTTPS to encrypt the communication between the client and the server. This means you need to install an SSL certificate.

For development environments, you could use a self-signed certificate, which is free. The disadvantage is that browsers will complain (some more than others) about the self-signed certificate being unsafe because it is "untrusted", but as long as you know who installed the certificate (you!), it's fine and you can ignore the warnings.

Installing mod_ssl and configuring a self-signed certificate is explained in this article. Here is a short version:



Modify the default configuration to disable weak/broken ciphers and protocols, and also to set up the port forwarding of the SSL port (443) to Tomcat/ORDS:



For production environments you need an SSL certificate issued by a Certificate Authority (CA). There are many CAs and lots of companies offering SSL certificates. I won't go into the details of buying a certificate here, but pick a cheap one -- there is really not much technical difference between the cheap certificates and the very expensive ones.

After you have set up the SSL certificate on your website, you should test that it works correctly by running the SSL test at ssllabs.com. You should aim for nothing less than a "Grade A" result. If you are using a self-signed certificate, the test will complain about the certificate not being trusted, but you still get the SSL settings of your site verified.

Setting up backups

There are two parts to this: Backing up the server itself, and backing up the database. The latter is the most important to me (as the operating system can always be reinstalled quite quickly, as this series of blog posts show, but the database of course contains the real value -- your data!).

A cloud server provider such as DigitalOcean offers some built-in mechanisms for backups and snapshots, as explained here.

Also, you could use the rsync command to set up your own backup of the OS files.

For backing up the database (schemas), see this blog post I wrote a couple of years ago about using PL/SQL to do Data Pump exports and transfer the files to Amazon's Simple Storage Service (S3).

Monitoring server uptime

Having spent all this effort to set up your website, you want to make sure it is up and available to your users. You should set up one or more automated uptime monitors, using a free service such as Pingdom or UptimeRobot. These services will hit your server from various locations around the world at regular intervals, and send you an email or SMS if the site is unavailable. They will also keep statistics of the average response times and the uptime percentage, which is useful to analyse your server performance over time.

Scaling up (or down) the server

At some point you may want to scale up your server by adding more CPUs, disk space, memory and/or bandwidth. If you are using a cloud service such as DigitalOcean, this is very easy to do. Simply shut down the server, select a new server size from the control panel, and restart the server. This is explained here, and the process will be similar for other virtual server environments.

Note that if you scale down the server and thereby reduce the amount of available memory, you might get an ORA-00845: MEMORY_TARGET not supported on this system error from Oracle and you might need to adjust the operating system memory settings as described in this article. The short version is as follows:

Keeping the server updated

You should keep your server up-to-date with the latest OS patches. You can manually run yum list updates to see what's available, and yum update to download and install all relevant operating system updates that are available. (On DigitalOcean, you will get a warning that says "grubby fatal error: unable to find a suitable template" that has to do with the way Linux kernels are managed in DigitalOcean. You can ignore the warning. If you need to update the kernel, this is done via the server control panel, as described here.)

To set up automatic updates, install the package yum-cron and configure it (described in more detail here).

Next steps

 
That concludes this collection of additional configuration. The next (and final) blog post in this series will deal with performance testing the new server to verify that it can handle an appropriate number of users.

Longer names coming to Oracle?

Have you ever, like me, been silently finger-counting the number of characters in a table name, column name or procedure name that you are about to create in the Oracle Database, to make sure it is short enough? It appears those days will soon (?) be at an end...

The current limit on names (identifiers such as table names, column names, procedure names, parameter names, etc) in Oracle SQL and PL/SQL is 30 characters. Here is what you see when you do a describe of the the user_tab_columns dictionary view in Oracle 11g:

But what do we see if we do the same on an Oracle 12c database? The database on apex.oracle.com was upgraded to 12c some time ago, so let's try the same there:

Interesting...! Looks like the new limit will be 128 characters. All the relevant views such as user_arguments, user_identifiers and user_objects have been likewise altered.

Mind you, as of version 12.1.0.2.0, it's still not possible to actually create objects with long names:

But it appears that the preparations have been done to allow longer names, so I'm guessing this will become possible in the next version (12.2?).

The world moves forward... it will be great to stop worrying about short names (but let's not go crazy and turn our PL/SQL into the equivalent of this silliness !).

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!

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:

• https://www.digitalocean.com/community/tutorials/how-to-create-your-first-digitalocean-droplet-virtual-server
• https://www.digitalocean.com/community/tutorials/initial-server-setup-with-centos-6

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.