A Most Simple Cloud: Is Amazon RDS for Oracle Right for You?

Jun 11, 2013 / By Jeremiah Wilton

Tags: , , , , , ,

Amazon Web Services has offered Relational Database Service as part of their cloud offering since 2011.  These days, RDS provides easy to deploy, on-demand database-as-a-service for MySQL, Oracle, and SQL Server.  When you compare it to essentially any other method of hosting and licensing Oracle, RDS seems to have a variety of really appealing qualities.

With RDS/Oracle, you don’t really need a DBA to take care of your database. With the notable exception of tuning, most of the DBA tasks, such as database creation and configuration, backups, upgrades, and disaster recovery are simply features of the service.

Oracle databases on RDS can be created with “license included“. This means that for certain Oracle editions, you can pay as you go for your license. This is currently limited to Standard Edition One, but rumors abound that higher editions, including Enterprise Edition, will be available with license included in the near future.

The Oracle versions available on RDS are limited to a few modern, stable releases. This keeps customers from encountering oddball bugs and version quirks.

So far, RDS seems like a clean, simple, elegant solution, and it is. It clearly has a place with certain enterprises that use or want to use Oracle. So the question you might have is: “Is it right for me?” Since no solution is perfect for every deployment, it is helpful to explore the factors that can help you decide if RDS/Oracle will fit your needs.

Availability of technical personnel: If you already run an enterprise that employs DBAs, there may not be as great an upside to deploying a largely DBA-free solution like RDS. On the other hand, if your in-house database expertise is not deep, RDS has the advantage of low technical barriers to entry. With RDS/Oracle, provisioning, doing backups, monitoring, upgrading, and patching are managed and controlled via the web API. The major missing component is tuning. With RDS/Oracle, you still need to have some knowledge of Oracle and SQL tuning to run a successful RDS service.

Tuning: While we are on the topic, let’s discuss Oracle tuning and RDS. As with conventionally-hosted databases, diagnostic pack (and ASH / AWR) is available and supported, as long as you are running Enterprise Edition and you are licensed for those options. AWS even provides Enterprise Manager DB Control as an option. For all other editions however, there is a major hitch. Statspack, the tried and true alternative to AWR, is not supported on RDS. You can still query the v$ views to access current and aggregated wait event information, but the lack of Statspack support is a big stumbling block. SQL tracing and event 10046 (and many other diagnostic events) are available on RDS. Moreover, a recent enhancement has provided access to these files via the web API. Previously, access to alert and trace files was via external tables and SQL only.

Privileges: RDS grants you limited management privileges for Oracle, but it stops short of the SYSDBA role, which would have total control over the system. Some applications require SYSDBA, especially during schema installation. If you have an application that absolutely cannot function without SYSDBA privileges, RDS is off the table. However, most of the application schema deployment scripts that purport to need SYSDBA privilege actually need no such thing. In many of these cases, minor changes to schema build scripts would make them RDS-compatible.

Loading/migration: Most Oracle customers are accustomed to migrating their databases from one hosting solution to another via datafile copy. In the case of very large databases, migration by physical standby switchover is the method of choice. With RDS, there is no access to the underlying filesystem, so datafile-based migration methods are impossible. Since the only access to RDS/Oracle is via SQL Net, data must be loaded using database links. This means using Data Pump, DML over database link, or materialized views. This final option is particularly interesting. If they first pre-create all of the tables and indexes to be migrated in RDS, customers can then build fast-refresh materialized views on the tables and continually refresh them from the source system. When the customer wants to cut over to RDS, it can be accomplished simply by stopping application access to the source, refreshing all MVs one more time, and converting the MVs in RDS back to tables by dropping the MV objects. While this method is prone to problems stemming from schema design, high rate of change and large transactions, it is likely the best and only solution to a minimal-downtime migration to RDS/Oracle.

Database versions: If you are planning to migrate to RDS from a conventional hosting solution and you don’t already use one of the Oracle versions supported by RDS, your migration to RDS will also amount to a database upgrade. There is nothing fundamentally wrong with this, since you will be moving to a version well tested and certified by Amazon. However, any third party (or homegrown) software will have to be checked and tested to make sure it is run and supported on one of the available versions under RDS. Also be aware that database upgrades can come with their share of issues. The most common upgrade issues are small numbers of SQL statements that perform worse after upgrade because of optimizer regressions.

Backup and recovery: RDS/Oracle backs up the database using storage snapshots, and boasts point-in-time recovery. There are some clear advantages to this method. On the one hand, backups complete quickly, and you can execute them as often as you want. On the other hand, because Recovery Manager is not supported, some of the nice things you can do with that tool are missing from RDS. For instance, simple small repairs such as single block, single datafile, or single tablespace recovery are impossible with RDS. Recovery using storage snapshots is an all-or-nothing proposition.

High availability and disaster recovery: On the plus side, RDS/Oracle provides a very easy way to set up redundancy across Amazon availability zones (which you can think of as separate datacenters in the same region). This configuration, called multi-zone, provides synchronous storage replication between your production RDS database and a replica in one of the other zones within the same region. For the MySQL version of RDS, the replica is readable. However, this is not so for Oracle or SQL Server. So multi-zone RDS provides redundancy for Oracle, but not a read replica. Significantly, because nearly all viable replication options are unsupported, including Data Guard (standby database) and Streams, RDS does not provide a cross-region DR solution for customers.

Limitations on features, parameters and character sets: RDS/Oracle has enabled and supports a broad range of Oracle features, parameter settings and character sets. However, a subset of each of these categories is not supported, either because of how RDS is architected or because Amazon has not seen the demand for those things to be great enough to merit the engineering effort involved in supporting them. Depending on the needs of the application, any limitations arising from the following lists may or may not affect you.

Features supported / not supported on RDS/Oracle (note that RAC is not supported on EC2 either)
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html#Oracle.Concepts.FeatureSupport

Character sets supported (note that this list does not include UTF8 or WE8ISO8859P1, AKA Latin-1, both very common)
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.OracleCharacterSets.html

The available parameters and allowed settings are dictated by the edition, options and version of Oracle you have deployed. The complete list can be obtained via the RDS web API.

To summarize, Amazon RDS for Oracle is a really exciting option. The right application with limited requirements might find RDS to be a totally suitable platform for hosting a database. However, as the enterprise requirements and resulting degree of complexity of the database solution increase, RDS is gradually ruled out as an option. For larger and more complex deployments, the issues around tuning, migration, and HA/DR completely eliminate RDS as a solution. For these more complex cases, Amazon’s Elastic Compute Cloud (EC2) can provide a much broader range of possible solutions. I would never be one to discount anything Amazon Web Services offers. Any deficiencies I call out in a blog post like this one will probably be made obsolete as Amazon announces new RDS capabilities.

Would you deploy your databases on Amazon RDS for Oracle?  Why or why not?

4 Responses to “A Most Simple Cloud: Is Amazon RDS for Oracle Right for You?”

  • Jeffrey Kemp says:

    Great summary. I’ve looked at RDS myself (I’m currently using EC2) as you don’t have to worry so much about backups, recovery and upgrades.

    The big barrier for me personally is that I’m on Apex 4.2 and recently upgraded to Apex 4.2.2, but RDS is stuck on Apex 4.1 – and apex applications cannot be downgraded. Rewriting all my Apex apps and losing the new features (especially the features for mobile) would be met with howls of protest by my users :)

    Another barrier is that anything requiring TCP (e.g. UTL_TCP, UTL_SMTP, UTL_MAIL, APEX_MAIL, etc.) is not supported and cannot be used.

    If Amazon were to allow us to upgrade the Apex installation on our RDS instance, and allowed TCP connectivity, and allowed unlimited schemas, I’d move there in a shot.

    Also, if they upgraded Apex to 4.2.2, but still blocked us from doing our own upgrades, I’d feel locked in (having to wait for Apex 5.0 would be terrible!) :) I’d want to see Amazon keeping up with the Apex upgrades.

  • EVa says:

    Hi, I would like to know if RDS/Oracle and APEX. Can we use APEX under the Licence included model? or do we need to pay extra licensing for Apex?

  • DC says:

    AWS RDS Oracle 11.2.3 is available with Apex v4.2.4
    http://aws.amazon.com/about-aws/whats-new/2013/12/16/amazon-rds-now-supports-oracle-database-11-2-0-3/

    SQL> select version from sys.dba_registry where comp_id = ‘APEX’;

    VERSION
    ——————————
    4.2.4.00.08

  • al says:

    great post. found exactly what i was looking for. looking to have to oracle RDS primary instance in one region and have read-replica in another region for our users there. the Multi A-Z option is for inter-region failover and standy’s are not accessible. = <
    works like a charm for mysql replication though@!!!

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>