Cloudy With a Chance of Database: Part I

There has been a lot of talk in the ubiquitous cloud space recently and more specifically the PaaS service delivery model. Naturally this piqued my curiosity. How does the database fit in the cloud?
Just about every major enterprise vendor is jumping on the cloud bandwagon. And rightfully so. Private clouds are now being pushed by Oracle with Exalogic Elastic Cloud and Exadata, IBM with SmartCloud and VMware with Cloud Foundry just to mention a few. Amazon AWS and Rackspace are the major players in the public cloud stratosphere. Add to this community Clouds like OpenStack

The easiest to do an evaluation is by using Amazon AWS public cloud services. Amazon AWS provides Relational Database Service (RDS) for MySQL and Oracle Database 11g. Strictly speaking EC2 is a Infrastructure as a Service (IaaS) delivery model. However, the use of AMIs enable PaaS characteristics where complete environments can be easily packaged and deployed in EC2.
There are several publicly available Oracle Database AMIs on Amazon AWS. These are fully-supported and licensable environments. Setting up an EC2 instance involves several steps. First you need to sign up for an AWS EC2 and RDS account.

AWS EC2 instance (large) create 11.2.0.1 on EL5.5
Once you log into EC2 you are prompted with Terms of Use and license Agreements for OTN license and EL license. 
Here is a viewlet of creating an EC2 instance from AWS Console.
Unable to display content. Adobe Flash is required.


Key Features of EC2
  • Database configuration can be customized
  • Full control over options and parameters
  • Remote login access
  • Monitoring using Enterprise Manager database control/grid control
  • APIs and CLI tools to manage instances
Caveats
  • Scaling requires rebuilding or adding EC2 instances
  • No RAC support
  • No ASM support
AWS RDS Oracle DB Instance
The AWS RDS services allows you to either Bring your own license (BYOL) or pay-per use for Oracle Standard Edition One. Oracle Standard Edition and Enterprise Editions supports the BYOL licensing model. Similar to the EC2 instances they come in different levels of resource combinations: small, medium, large etc.
Launch an Oracle DB instance in RDS is similar to a MySQL DB instance.
Here is the list of available AWS RDS instances.


The AWS RDS instance creates a user with limited privileges. A lot of security considerations has been taken into account. They are not totally secure however as one fellow blogger/tweeter points out: Sydney Oracle Lab: Locked down in Oracle Amazon RDS. Here are few of the roles and privileges granted to the admin user.


iMac:~ leighton$ sqlplus orardsuser/*******@orards.c2rlslv1ais6.us-east-1.rds.amazonaws.com:1521/orards
SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 25 16:13:27 2011
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
SQL>

SQL> select dbms_metadata.get_granted_ddl('ROLE_GRANT', user)
from dual;  2
   GRANT "CONNECT" TO "ORARDSUSER" WITH ADMIN OPTION
   GRANT "DBA" TO "ORARDSUSER" WITH ADMIN OPTION

SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', user)
from dual  2  ;
  GRANT UNLIMITED TABLESPACE TO "ORARDSUSER" WITH ADMIN OPTION

SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT', user)
from dual;  2
  GRANT EXECUTE ON "SYS"."DBMS_LOCK" TO "ORARDSUSER"
  GRANT EXECUTE ON "SYS"."DBMS_PIPE" TO "ORARDSUSER"
  GRANT EXECUTE ON "SYS"."DBMS_SHARED_POOL" TO "ORARDSUSER"
A lot of the DBA related tasks can be accomplished by the use of wrapper scripts. This limits the potential for unwary users to do something dangerous to the database.
The Amazon AWS RDS User Guide has a lot of information about this and other features.


Monitoring an RDS instance requires the CloudWatch monitoring service. The downside of this is that it does not have the level of detail and functionality provided with Enterprise Manager.
Tools like SQL Developer can be used to connect to and manage your database just like any other Oracle instance.

View Sessions through SQL Developer
Manage AWS RDS for Oracle with SQL Developer

Default Oracle RDS users for Standard Edition One

Key features
  • Provision a fully functional database in a few clicks
  • Backups are automatically setup
  • Automatic minor version upgrade
  • No System Admin required
  • No up-front costs
  • Can easily scale without rebuilding instances
  • APIs and CLIs provided to manage instances
Caveats
  • No remote login access
  • No Enterprise Manager database control monitoring capability
  • No replication (to be supported in the future)
  • No RAC Support
  • No ASM Support
  • APEX not installed/supported
  • On-demand licensing for Standard Edition One only

As you can see an RDS instance will provide simple and rapid deployment for databases for developers and users without requiring significant database administration experience. Databases launched on EC2 however provide a lot more flexibility and hence requires DBAs to perform a lot of the tasks in setting up an instance. In both cases deployment is faster than a standard deployment lifecycle in a local data center.

In part II of the series I will go into more details of setup and administration of AWS EC2 and RDS databases using the CLI tools. I hope to also briefly cover the licensing costs which is a major factor in determining whether or not you should run your Oracle database in the cloud.

    Comments

    1. Eric SchrauthJune 22, 2011 at 10:21 AM

      Have you ever used data pump up to RDS?

      ReplyDelete
    2. LeightonnJune 22, 2011 at 4:06 PM

      No I haven't. I could be wrong but I don't think it's supported. The AWS Data Import guide http://aws.amazon.com/articles/Amazon-RDS/4173109646282306 describes using traditional exp/imp but not datapump. I will have to follow-up in the next part of the series.

      ReplyDelete
    3. AnonymousJune 29, 2011 at 9:44 AM

      SQL> grant EXECUTE on SYS.DBMS_PIPE to user_name;
      grant EXECUTE on SYS.DBMS_PIPE to user_name
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-20900: Error in rdsadmin.rdsadmin. ORA-20900: GRANT:OBJECT
      PRIVILEGE:SYS:DBMS_PIPE
      ORA-06512: at "RDSADMIN.RDSADMIN", line 183
      ORA-06512: at line 2

      I am getting this error for the object grants. Any ideas? Thanks in advance.

      ReplyDelete
      Replies
      1. AnonymousAugust 22, 2012 at 2:58 PM

        try just:
        grant EXECUTE on DBMS_PIPE to user_name;

        Delete
    4. farnandasApril 16, 2016 at 5:14 PM

      Nice post.

      ReplyDelete

    Post a Comment

    Popular posts from this blog

    Setup a Wordpress site in 10 Minutes using Oracle Container Cloud Service

    Image
    Oracle Container Cloud Service Oracle Container Cloud Service lets you easily deploy applications based on docker images. If you don't have an existing application you can create one on docker. However, the easiest method to get a full application up and running is by deploying a stack. Oracle Container Cloud Service provides several example stacks that you can deploy, including web servers based on NGINX or Apache, MongoDB database, and Wordpress. You can find more examples on Oracle GitHub Site . Let's see just how easy it is to spin up a Wordpress site using the Oracle Container Cloud Service. Prerequisites: Access to Oracle Cloud Account Subscription to Oracle IaaS Service First, go to the Oracle Container Cloud Service console and launch a new service. In this example, I created a service with three containers. One manager and two worker nodes. Choose an appropriate "shape" for your compute instances. Note: Oracle uses the term "shape" to r...
    Read more

    Oracle Home Cloning: Tools and Techniques

    One of the many necessary and frequent tasks that a database administrator performs before creating a database is installing the Oracle database binaries. These binaries form what is known as an Oracle Home (referenced by the ORACLE_HOME environment variable on Linux and UNIX environments). Unfortunately, this is also one of the most mundane and uninteresting tasks a DBA can perform. So like all good DBAs know, the best way to deal with such repetitive task is to automate it. Automating installations of Oracle database binaries enables us to reliably repeat the process to enable faster installation as well as provide identical copies of the software in multiple environments. Fortunately, Oracle allows us to clone the Oracle database binaries from one environment to another. Cloning your Oracle binaries from a source copy with all the necessary patches applied enables us to have gold images which can then be deployed to multiple environments. The procedure for cloning an Oracle home...
    Read more

    Using XFS on Oracle Linux

    Install xfs packages on Oracle Linux 6.6 via Oracle Public Yum [root@matthew ~]# yum install xfsprogs Loaded plugins: refresh-packagekit Setting up Install Process public_ol6_UEKR3_latest | 1.2 kB 00:00 public_ol6_UEK_latest | 1.2 kB 00:00 public_ol6_UEK_latest/primary | 19 MB 00:02 public_ol6_UEK_latest 407/407 public_ol6_addons | 1.2 kB 00:00 public_ol6_addons/primary | 77 kB 00:00 public_ol6_addons 269/269 public_ol6_latest | 1.4 kB 00:00 public_ol6_latest/primary | 46 MB 00:05 public_ol6_latest 29302/29302 http://public-yum.oracle.com/beta/repo/OracleLinux/OL6/uek3/x86_64/repodata/repomd.xml: [Errno 14] PYCURL ERROR 22 - "The requested URL returned error: 404 Not Found" Trying other mirror. Resolving Dependencies --> Running transaction check ---> Package xfsprogs.x86_64 0:3.1.11-1.0.5.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ===================================================================================================...
    Read more