Adventures with AWS RDS and APEX

AWS Logo

Disclaimer: These notes were written in November 2015. The services described here may change rapidly based on Amazon offerings. Please verify with Amazon as some of this information may have changed.

Recently I have had the opportunity to test and learn a little about the Amazon web services (AWS) Relational Database Service (RDS) offering. RDS is Amazon’s preconfigured version of database offerings from several popular platforms including Oracle. In my view – it is a commodity database setup. Just walk in and get one off the shelf. There are a lot of clever management tools that have been embedded into the RDS offering. (Backups, restarts, monitoring, etc)

For my purpose, I am interested in RDS as an Oracle database service. Specifically an Application Express (APEX) host. Here are some of my findings.

Database Creation is a Snap

Simply fill out a simple form with a few tricky questions (VPC Security Groups), and within minutes – you have an instance of Oracle that is ready to use. But not all databases are the same….
aws-db-name

APEX is an add-on feature

The 12c version of the RDS database support 4.2.6 as of the time of this writing. The 11g database offers 4.1.1. There is a concept that AWS RDS uses for database features called Option Groups. You have to add to your RDS installation to have APEX installed. For 12c the options needed are:

  • APEX (Runtime Environment)
  • APEX_DEV (Development environment)

Even with the option installed, the accounts are locked and must be opened up by the super user. The standard setup steps of unlocking and setting passwords for the APEX_PUBLIC_USER still apply. You will also need to set the default ADMIN password for apex administration. This is all documented in the options section of the AWS docs.

APEX 5

Let’s get the obvious finding out of the way. APEX 5 is not an option for installation as a service option. In fact, without hacking the installation completely, you can not install 5 with the given permissions that are granted to the super user account.

RDS is only Half the Equation

For an APEX architecture, at a minimum you need the database and a web server. Although APEX is listed as a feature, you still need an EC2 instance to run your web server and ORDS listener. The network connection between the web server and database can be configured using IP or AWS security group notation. So although creating the database was simple, the web server is another activity that needs to be accounted for.
inbound_rules

Permissions

After getting everything configured and working – most APEX administrators would go about creating workspaces. Hold on there – you need a schema for that. The default behavior of using APEX to create the workspace and schema at the same time using the APEX administration console is not functional by default. This is probably a security feature as much as anything else, but I was unable to find it in the documentation. The APEX_040200 schema does not have the appropriate grants to accomplish the task of creating a schema.
When you create your ORACLE RDS instance – you create a super user and password. This user (schema) is given DBA privilege, but not SYSDBA privilege, and the APEX_040200 user is not granted the full set of privileges as would be expected from a normal installation. Because of this, you can either create the user manually, using scripts and methods of your choice, or you can level set the grants given to the APEX_040200 user. My preference is to create the schema manually as I normally have a requirement to adjust the grants given to a specific schema.

Other RDS concerns

A couple years ago, Pythian did an article on AWS Oracle RDS with some feature considerations from the perspective of performance DBA. Many of their notes still apply even with the changes amazon is making to their offering. My concern from the APEX side is the ability to do Database calls from the database using SSL certificates. Because of the need for a wallet, these types of calls are not functional. From my testing – I could not do a basic REST service call out of the RDS database. (This could be my configuration)

Summary

If you are looking for an easy method of setting up a schema to run either a packaged application or an APEX 4.2 application, this is a good service. AWS has some very mature and involved management and security features that should support any implementation requirement like monitoring and automatic snapshots.
aws-monitor

That being said, it is not as advanced in the areas of the APEX development as I require it to be.
AWS is constantly changing and adapting it’s service offerings, so this may change in the future. Refer to the AWS documentation to get the latest offerings.

5 thoughts on “Adventures with AWS RDS and APEX

  1. Excellent write up, Tim. Thanks for sharing. I found myself playing with AWS last month and ended up with a $170 bill from Amazon. Their customer service, however, was kind enough to waive the charge, after I explained to them that I was just playing around and don’t really want the account.

  2. Hi Tim, I too have been playing with apex on AWS RDS. Another thing that I’ve come across is that outbound traffic is not currently permitted for DB instances.

    This means you can’t consume webservices and presumably no emails from Apex which, too me, is a big issue.

    Funnily enough, the webservice I was accessing started to work overnight – I queried on the aws forums if anything had changed.

    Response:
    “We are aware that outbound network calls such as utl_http() are able to succeed in a limited set of circumstances, but this feature is not officially supported. Hence, we recommend you not to build any features or requirements around outbound network calls.”

    @christopher – first thing I did was set up an alarm to trigger when I’d spend any money – I misconfigured something and was alarmed after 64 cents.

  3. Hi, Tim,
    Thanks for this article on Apex in AWS. We recently installed 4.2.6 using RDS and an EC2 instance for the app server. I am using it for a class I am teaching and I just found out today that there seems to be an issue with the Developer mode. I can develop an application, but when I do to Edit it, the developer toolbar is not present. Also, since I am not ‘recognized’ as the developer of the application, when I go to save an interactive report, it does not allow me to save a report as a default report — so the reports I create are only available to me as a user–which is a big problem if I am wanting to share the report or have students build a report to share.

    I wonder if there are certain privileges used in this mode of Apex that are not being installed? Or maybe some other issue is at play.

    Pat Miller

  4. It sounds like you are loosing either the URL or the Cookie that recognizes a session as being part of a developer login. Hard to tell without the details.
    There was an OLD bug that prevented the developer bar from displaying. It was there, just not shown.
    The “Save Default Report” issue you describe points back to the first issue. Depending on how AWS web server was setup, you may be changing URL’s that would prevent your session from being linked to the page builder.

Leave a Reply

Your email address will not be published. Required fields are marked *