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.

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.

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?

About the Author

Jeremiah Wilton has over eighteen years of Oracle database administration and systems architecture experience. As Amazon.com’s first database administrator, he helped lead Amazon.com’s database group from the pre-IPO period through the company’s years of exponential growth. He now works for Pythian, a leader in remote database administration for Oracle, Oracle Applications, SQL Server and MySQL. Jeremiah also teaches the Oracle Certificate Program at the University of Washington. At Oracle OpenWorld in 2001, Oracle Education honored Jeremiah as one of the first eight Oracle Certified Masters in the world. Jeremiah is a member of the OakTable, and co-author of the Oak Table’s Oracle Insights. He has presented at numerous conferences and user groups, including Oracle OpenWorld, Collaborate! and UKOUG, and is the author of a variety of technical whitepapers and articles.

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.

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@!!!

I agree, 12c has a number of new and useful features that make it preferable to 11gR2. The one thing that drives me nuts is not having the ability to do External Tables referencing the local filesystem. Or at least make external tables referencing some EBS volume even if I can’t access the partition that the OS or Oracle are running on.

Can AWS RDS replace Oracle RAC deployed on on-premise ? I have to migrate the application which is having the Oracle RAC. But as AWS not supporting oracle RAC it is defiicult to have whole migration oncloud. I know there is other option to have Oracle RAC on on-premise and other apps on AWS but i needed to know if we migrate from Oracle RAC to RDS, will it beneficial?

PYTHIAN®, LOVE YOUR DATA®, and ADMINISCOPE® are trademarks and registered trademarks owned by Pythian in North America and certain other countries, and are valuable assets of our company. Other brands, product and company names on this website may be trademarks or registered trademarks of Pythian or of third parties. Use of trademarks without permission is strictly prohibited.