Multiple Databases?

A colleague recently asked me about using separate databases for a single business function/project. With a little thought (he needed feedback quickly), I came up with four reasons.

• Security - the data/access must be totally separated from other data/access. While the usage of schemas, privileges, roles, etc. can be used to restrict access within an application, it does not restrict access to those with broad privileges (such as the dba team). If there is a legal or other requirement that no one be able to access both sets of data, then multiple databases makes sense.
  
• Recovery - different schemas may have different recovery requirements. If the database is several hundred terabytes, but only a few hundred gigabytes is business critical, then it makes sense to separate the business critical component. In case of database recovery, the business critical data can be restored promptly while the remainder is recovered less urgently.
  
• Performance - although much of the performance setting has been made 'automatic', there are still times where different applications need different settings/configuration for optimal performance. While some of these settings can be handled via login triggers or sql hints, it may be easier to separate them out into separate databases. Each release seems to reduce the need for different parameters, so this issue is decreasing in importance.
  
• Features/Versioning - this is probably the main reason for separate databases. When a single database is used for multiple applications supplied by different vendors, you can find yourself in a situation where the vendor requirements are in conflict. VendorA needs FeatureA, but that feature actually breaks the code for VendorB. Not only do you need separate databases, but you often need separate Oracle versions/homes.

Well...that's what I came up with (in a few moments notice). I'd like to hear what I am missing or just plain wrong about!

User Groups

One of my New Year's Resolutions is to be more active in regional Oracle User Groups. I have to admit I've been slacking off with my participation for RMOUG and I will be changing that for this year.

At a time when training budgets are being cut, regional user groups offer tremendous value for the money. Memberships are usually low cost and meetings are often free for members. The larger training events, like RMOUG Training Days, usually cost a few hundred dollars, but pay for themselves in the quality and convenience. If you are willing to volunteer, the cost is even less...and the experience more valuable.

Over the years, I have volunteered as Program Chair and Newsletter editor for RMOUG. One of the constant challenges was getting presentations and articles. Presenting and/or writing is a great way to share your knowledge and learn something new. One of my first articles for RMOUG's newsletter was an exploration of rollback segments...a topic I knew little about when I decided to write the article.

If you are part of a regional Oracle User Group and are looking for articles and presentations, please contact me at daniel.fink@optimaldba.com. I'd love to contribute and help your group with their mission to increase the Oracle knowledge and skills of it's members.

KCOUG - Advanced SQL

On January 27th, I will be presenting on Advanced SQL at the Kansas City OUG quarterly meeting.

This presentation covers Analytical Functions and Regular Expressions, two topics that I find very useful, but not well understood. The content is very heavy on actual implementation (about 1/3 of the time is demo). I have been using analytical functions to report on performance metrics for the past few years and have found them to be very useful in minimizing table access and maximizing performance of sometimes complicated queries. I recently used regular expressions to remove multiple white spaces from sql text with a single query instead of a pl/sql loop.

Advanced registration is not required. The exact time has not been set, but it will be in the evening. Check the www.kcoug.org site for updates.

RMOUG Training Days 2009 - Deadline Extended!

*** Advance Registration has been extended to January 13, 2009! ***

RMOUG Training Days 2009 is just over a month away (February 10 - 12, 2009). It is one of the best regional Oracle conferences in the US (and perhaps world). Speakers from around the world come to this conference (the names are too numerous to mention).

Here are links to the Agenda and Registration

Agenda (tentative)
Registration *Please note that Advance Registration ends on January 6, 2009.