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. Have you ever used data pump up to RDS?

      ReplyDelete
    2. 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. 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. try just:
        grant EXECUTE on DBMS_PIPE to user_name;

        Delete

    Post a Comment

    Popular posts from this blog

    Viewing ASM trace files and alert logs in Unix/Linux

    ORA-00020: maximum number of processes (%s) exceeded

    Troubleshooting RAC Public Network Failure