Oracle Database on Amazon Cloud - Now Available

Amazon has kept its promise of making Oracle 11g available on per hour billing. Finally, after its announcement in Feb 2011, Oracle 11g database is now available on Amazon RDS. Which means you can use Oracle database on cloud with the same per hour billing rates (pay-as-you-go model) you pay for other resources.

Amazon RDS brings Oracle Standard One, Standard and Enterprise editions on RDS. It’s now the 2^nd database to be available on RDS; MySQL was available for quite some time now. As I had mentioned in my earlier post, Oracle will be available under 2 difference licensing scheme –

BYOL (Bring Your Own License) – Basically allows you to bring and reuse your existing licenses. Start from $0.11 per hour, this is mainly for the underlying hardware.

License Included – No upfront investment/commitment.  Start from $0.16 per hour, this includes underlying hardware and oracle database license cost.

Important to note that only “Standard Edition One” is available under “License included” model which means if you need Enterprise features then you need to go via BYOL which has upfront cost.

Options you see on launching a database on Amazon RDS -

In terms of features, you need not worry about general DBA tasks, backup, patching, monitoring (using CloudWatch) etc. All these tasks will be Amazon’s responsibility. As per the Amazon site, parameter control is available via DB Parameter but on my first glance couldn’t find any editable parameters (maybe I’m missing something). Replication (similar to what’s available with MySQL) is not yet ready. What I liked is that there is no additional charge for backup storage up to 100% of your provisioned database storage for an active DB Instance. You have to pay for it as soon as you terminate the database instance. 

Currently only one version, Oracle 11.2.0.2, is available. It remains to be seen on the choice of version that will be made available in future.

Refer to Amazon RDS site for more details around cost and features.

How to Configure Oracle Restart on Standalone Server

In one of my previous post, I had talked about Oracle Restart – a new feature in 11gR2 that enhances availability in case of single/standalone instances. In this post, I am briefly describing the steps to install and use this great feature for an existing database installation.

Oracle Restart is part of Oracle Grid Infrastructure which needs to be installed without which Oracle Restart cannot be used. One either installs Grid infrastructure first and database later or vice-versa. The difference being that the components either gets automatically added to Oracle restart configuration (if Grid is installed first) or need to be manually added (In case DB is installed first and Grid later).

The Oracle grid infrastructure for a standalone server is the Oracle software that provides system support for an Oracle database including volume management, file system, and automatic restart capabilities. Basically it combines “Oracle Restart” and “ASM” into Grid binaries. So, to use Oracle Restart or ASM, installing grid infrastructure is a MUST (which of course I don’t like). Is there license implication?

Further, Oracle Restart can only manage 11.2 resources. However, Oracle database releases prior to 11.2 can coexist on the same server but without being managed by Oracle Restart.

Is it a separate binary?

Yes, a separate binary available for download - http://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_grid.zip

However if you download the latest version 11.2.0.2 (from metalink) then you do not need to download the above. Grid is part of the installable. What I don’t like is installing Grid even for “Oracle Restart” only.

How to install?

There are 2 options. When you start installing the grid, it throws the following screen; if you just want to install Oracle Restart then choose “Install Grid Infrastructure Software Only” else choose “Install and Configure Grid Infrastructure for a Standalone Server” which will ask for ASM configuration details.Ensure that the grid infrastructure components are installed in a separate Oracle home.

 

To continue the installation, just  follow the screenshots below; the above screenshot is the first step when you run "./runInstaller" from installable folder.

 

At the end of the installation you are required to run the “root.sh” script to successfully complete the installation. Please note that you need to run the script as “root” user.

#/u01/app2/product/11.2.0/grid/root.sh

What do I need to Configure?

Next is to run “roothas.pl” script to ensure to configure Grid Infrastructure for a stand-alone server. Run the following command as the root user:

#/u01/app2/product/11.2.0/grid/perl/bin/perl -I/u01/app2/product/11.2.0/grid/perl/lib -I/u01/app2/product/11.2.0/grid/crs/install /u01/app2/product/11.2.0/grid/crs/install/roothas.pl

One last step before we can term “Oracle Restart” configuration to be complete and add components to it.

# cd $ORA_GRID_HOME/bin

# crsctl enable has

So that’s it! “Oracle Restart” is now configured on your standalone server.

How to add components to Oracle Restart

1.      Add the existing database to “Oracle Restart”

$ srvctl add database –d -o <$ORACLE_HOME> -p -s -t

$ srvctl add database -d testdb -o /u01/app/oracle/product/11.2.0/dbhome_1 

2.      Add listener

$ srvctl add listener –l LISTENER -o /u01/app/oracle/product/11.2.0/dbhome_1

Can I turn off Oracle Restart?

Yes; first check the current status of auto start and then simply run the command (as “root” user) to turn off the autostart option.

#crsctl config has

CRS-4622: Oracle High Availability Services autostart is enabled.

#crsctl disable has

CRS-4621: Oracle High Availability Services autostart is disabled.

Conclusion

That completes the Oracle restart installation and configuration.It's nice feature to have on a standalone instance/server. The best part is the manual procedures we used to adopt to start/stop all components of database and the shell scripting required is gone. And of course, get braced up for they way things work in a RAC environment. Let me know your feedback.

crs_stat deprecated in 11gR2

The most used to command "crs_stat -t" is gone (deprecated) on 11gR2. Instead you would use - 

crsctl status resource [the output will be status of all resources]

You can still use the "-t" option to get a tabular report though ;)

crsctl status resource -t

Specify resource name "ora.mydb.vip" to get the status of any particular resource -

crsctl status resource ora.mydb.vip

In fact, quite a few commands have been deprecated in 11gR2; For the complete list refer to http://download.oracle.com/docs/cd/E11882_01/rac.112/e16794/crsref.htm#CHDHBECE

Instance Caging

Many of you would already be aware of this one while I discover it now but still putting down my thoughts on this 11g new feature.

Most of the times, we end up running multiple instances on a single box for developmental effort. Or even on Production by buying a reasonably big box to save on licensing cost and also in the name of consolidation exercise :). But doing so, throws up the challenge for a DBA in terms of allocating resources to each instance. Some low priority activity on an instance eating up CPU resources and thus depriving the much needed resource to those requiring it. CPU allocation decisions such as this are made solely by the operating system; the user generally has no control over them.

Instance caging in 11gR2 is a method to cage or bound the instance to use a certain no of CPU’s instead hogging all the available CPU’s. All we have to do is set an initialization parameter to control the CPU resources an instance can use. It basically limits the no of CPU’s an instance can use e.g. I can set this parameter to “1” on a 4 CPU machine. It’s very simple (silly :)) to set it –

ALTER SYSTEM SET CPU_COUNT = 4; 

However, it works with resource manager feature so you will need to enable RM and create a resource plan first before running the above command.

Instance caging can be useful on larger box with multiple instances running – some development and a few production allowing CPU and resource allocation be done effectively. Apart from this, it can also be said that it’s Oracle’s way (alternate option) of giving CPU resource allocation control to a DBA rather than leveraging a logical/physical partitioning of a large box resorting to usage of tools/technologies such as VMware, AIX LPAR or Sun containers etc. In a nut shell, it’s the simple way to control the CPU consumption of each database instance.

Maybe sometime later, I will try to post an example. Thanks for reading and appreciate your comments / thoughts.

Oracle Restart - a 11g New Feature

Oracle Restart is a new feature introduced to enhance the availability of Oracle database. I wasn't aware of this till recently. It basically allows various components of Oracle to restart automatically in a stand-alone (non-clustered) installation. This functionality is similar to what Clusterware does in a RAC setup. On Windows and Linux, we were able to achieve all this via creating a service or writing a script. But now it’s not required. So in a way, it also makes the age old practice of writing scripts to restart redundant.

Oracle instances and its dependent components restart automatically after any hardware or software failures and machine reboots. The components that can be restarted are –

• Database instance
• Oracle Listener
• ASM instance
• ONS (Oracle notification service)

It continuously monitors all the Oracle services started via it and restarts any of the failing services. It ensures that the components are starts in proper order e.g. starting the ASM instance before starting the database instance. What’s important is that Oracle restart is integrated with tools like SQL*Plus, LSNRCTL, and ASMCMD; so when you shut down the database or listener with the respective utilities, Oracle Restart does not interpret it as a failure and hence does not attempt to restart.

Much like clusterware, Oracle Restart also comes with a server control utility (SRVCTL); you should start/stop the components with SRVCTL.

In my opinion, this is a pretty useful tool and option to have. Gets rid of any additional scripting that would have required in day-to-day operations. Also serves as an introduction to how to manage services in a RAC which is a good thing for junior DBA's. The only problem the tool still looks for “ORA_CRS_HOME”, which is a bit weird given that it is supposedly for single (non-RAC) instance databases.

Oracle Database on Amazon RDS

Recently, Amazon and Oracle announced that they are going to make "Oracle 11g Database" available on Amazon AWS as a service. It's being brought into AWS's RDS feature which currently offers "MySQL database as a service".

Amazon RDS is a web service that allows you to set up, operate, and scale a relational database in the cloud. You can provision a relational database (currently only MySQL) on RDS in just a few minutes. Amazon RDS will also manage database administration tasks including continuous backups, software patching etc.

When launched (sometime in Q2 of 2011), you will have the option to choose from the licensing options for running the Oracle Database on Amazon RDS ->

• Bring Your Own License – (BYOL): Customers with existing Oracle Database licenses can run Oracle Databases on Amazon RDS with no additional software licensing or support cost. Oracle will provide the technical support required in case BYOL DB instances. 
• On-Demand Database Instances: This is a pay-by-the-hour licensing option with no up-front licensing fee or long-term commitment required. One simply pays by usage depending on the database edition and size of the instance. Brings it on par with other AWS offerings. Technical support for On-demand instances will be provided by AWS.
• Reserved DB Instances: Make a commitment and pay a one-time fee to get a DB instance with a significant discount on the hourly usage charges. The commitment required would be 1 or 3 years. Technical support for On-demand instances will be provided by AWS.

 While you can still choose to install Oracle on an AWS EC2 instance but it had a few drawbacks –>

• Licensing fee – Oracle is an expensive product and it acted as a deterrent to use Oracle on a cloud hosting platform such as Amazon.
• High availability – currently setting up of Oracle RAC is not possible on AWS EC2 hence one cannot achieve high-availability

Given the above mentioned points it definitely was not possible for startups, SMB segments to consider Oracle as the database of their choice. So I believe making Oracle available on RDS is a very good move. However a few things to watch out for –

• Upgrades – Can I choose to opt out of any future upgrades because I don’t want to be forced for an upgrade. E.g. they have similar option available for MySQL so will it be extended to Oracle as well?
• High availability – Would I get RAC? Will it be made available on-demand or forced choice? 
• Data security – Database on shared infrastructure adds the same security concern that’s being on people’s mind for quite some time. Admin controls being with Amazon could be another concern.
• Manageability – How much control do I get and the need to learn a new API to manage the databases.
• TCO – Don’t just go by the hourly usage charges as Amazon will also charge for storage (backup and data), bandwidth / data transfer etc.

It would be good to hear about others opinion and experience.

Edition based redefinition

Oracle 11g brought in a new feature called Edition based redefinition (EBS) basically aimed at reducing the planned downtime during application releases/upgrades etc. In this changing and dynamic world, application also undergoes numerous enhancements; it’s in these situations that one cannot afford to take a downtime and looks for options to minimize or eliminate them. While the application code and the software provide some options, the Oracle database engine had no such option.  

Till recently, Oracle database had only few options – such as online index rebuild and table redefinition to address planned downtime [Also has Workspace manager; Thanks for pointing out Gary Myers]. However, we all know Oracle’s capability to address unplanned downtimes with features such as – Real Applications Cluster (RAC), Physical / logical standby database, Streams etc.

With 11gR2, Oracle claims to have addressed the gap with edition based redefinition.  Oracle now supports multiple versions (editions) of a given object. So now, the objects are called as “editioned object” or “non-editioned objects” based on whether an object can have multiple versions or not. While one version of the object is used by the live application, the other version can be used to carry out changes and tested before making it permanent. However, the editionable feature is available for the following database objects ->

• Synonym
• View
• Procedure
• Function
• Package and Package Body pe
• Trigger
• Type and Type Body
• Library

The all-important and most used object – table and indexes are missing from the above list. So if you look at the list, you will feel that the versioning of code is now extended to database engine. Only view (though that’s also a piece of SQL code) and synonyms have been added. However, one can work around table by creating views and making them editionable but again only simple views without the use of functions etc. can be editioned. As a change, the developer needs to make his/her code work against editionable objects.

The other important thing is data within the table can’t be versioned; a lot of changes happen to be DML in nature so this is another important thing missing though there are workaround.

So, in my opinion this feature is useful in the following scenario =>

• If you make changes to procedures, function etc.
• Changes are done to physical structure of tables rather than data

Otherwise I feel this is not as useful yet. Also it’s a relatively a new feature and yet to mature. I'm sure future releases of Oracle will bring in some enhancements.