Oracle RAC and Grid Q&A With The Experts

LewisC's An Expert's Guide To Oracle Technology

Q&A about RAC and Grid with the RAC Experts

I recently got the opportunity to sit down and talk to two RAC gurus and learn what RAC is and how it relates to Oracle's Grid technology. Listen to the discussion with Philip Newland and Scott Jesse, both from Oracle Corp. Philip is a technical manager with Oracle's RAC Pack team and Scott is an Oracle Support Services Manager for the RAC Assurance Team. Scott is also the co-author of two Oracle Press books, "Oracle9i for Windows 2000 Tips & Techniques" and "Oracle Database 10g High Availability with RAC, Flashback & DataGuard".

There were plenty of questions asked and answered. Here are five important questions. Listen to the podcast to hear the rest.

What does Oracle mean by Grid?

An Oracle Grid allows you to add computing capacity, CPUs or storage, on demand as needed without pre-purchasing monolithic hardware. On an Oracle Grid, you can add capacity one cheap PC at a time.

There is no product for sale called Oracle Grid. You can't just walk into a store and buy a grid. Oracle Grid is a technology composed of several innovative Oracle products.

What components make up an Oracle Grid?

RAC

Oracle Real Application Clusters (RAC) allows Oracle customers to add database capacity (by adding servers) to an existing cluster. RAC allows a database to be spread across multiple servers. You can dynamically add and remove nodes (servers) as required.

RAC allows an application to transparently scale, add performance and be available 24/7. RAC can be very affordable to scale because it allows you to add cheap servers when you need them.

ASM

Oracle Automatic Storage Management (ASM) eases storage management by abstracting file systems to where DBAs need them. ASM also allows consolidation of storage so that applications that need storage have it available when they need it.

Clusterware

Oracle Clusterware provides the intelligence for a cluster. A cluster is a series of servers acting as a single entity. Clusterware provides the management and monitoring of a cluster.

Oracle Clusterware is not limited to providing scalability and high availability for Oracle Databases. With Oracle Clusterware, you can provide these services for third-party databases, application servers and pretty much any other kind of application.

Cluster File System

Oracle Cluster File System (OCFS & ACFS2) allows an Oracle database cluster to share disk across many servers. OCFS ensures that Oracle sees a consistent image of the disks on each server.

Does RAC work with Oracle Standard Edition?

Oracle Standard Edition, the edition of Oracle for the SMB market, comes with a license for a 4 node RAC cluster. A standard edition license allows up to 4 CPU sockets in a cluster. Those 4 sockets may be in a single server or in two, three or four servers. As long as your cluster does not exceed 4 sockets, RAC is included as part of your Standard Edition License.

Standard Edition does not limit the size of your database in anyway so as long as the processing power for a four socket configuration suits your needs, this can be a great way to save. If you need to scale to a larger cluster, Standard Edition can easily be upgraded to Enterprise Edition allowing you up to 1000 nodes.

Standard Edition RAC configurations are required to use ASM. Because ASM saves time and effort, this is not really a limitation but more of a method for Oracle to help you ensure your success.

How do I monitor my Grid?

Oracle provides both DB Control and Grid Control for monitoring and managing your databases. DB control is a web based tool that allows you to manage one database at a time. Oracle Grid Control allows you to manage entire grids and is an add on package. Grid Control can simplify all of your grid management tasks.

Can I mix and match hardware?

Yes. Oracle expects that vendors are improving hardware constantly and that customers will need to upgrade hardware over time. Rather than force customers to maintain a single vendor or configuration, Oracle RAC will work across various hardware configurations. While one server may be a single CPU and 4GB of RAM, another server in the same cluster can be 4 CPUs (with dual cores) and 16GB of RAM.

The only requirement is that the Operating Systems must match: Windows to Windows, Linux to Linux, 32 bit to 32 bit, etc.

Summary

Grid Computing - Oracle allows you to scale by tying together cheap hardware in a cluster allowing multiple servers to act as one. Oracle provides the software that enables database grids (via RAC) as well as storage grids (via ASM and OCFS). Oracle's Grid allows a business to start as large or small as they need, spending money on hardware and other system resources only when it is actually needed. RAC and Grid is transparent to the application allowing painless and immediate scaling when required.

I think this is one of my better podcasts. Check it out.

LewisC

Del.icio.us : grid, oracle, rac, vldb

When to use Triggers (and when not to!)

LewisC's An Expert's Guide To Oracle Technology

By request, this entry will discuss some issues surrounding DML triggers. Triggers are a nifty feature. When you need them, they are very useful. They can also make maintenance and debugging an absolute nightmare.

API vs Trigger Approaches

I am an API style programmer. I do not mean that I put all of my logic in INSERT/UPDATE/DELETE procedures for each table, although that is not a bad idea. What I mean is that I tend to write functional packages and those packages deal with DML.

As an example, let's say I have an HR system. I have a People table, a Job table, a Pay-Scale table and an Assignment table. Jobs have pay scales and the Assignment table associates a person to a job. All of the tables sequences for primary keys and all tables use database defined referential integrity.

I would have an API for Jobs. That API would deal manipulating Jobs and Pay-Scales. The insert code would select the PK sequences.

I would have an API for adding people. An Employee MUST have an assignment (that is kind of what defines an employee), so the Assignments table is also manipulated in the People API. A person can change Jobs (get a new assignment), be separated (remove the assignment), etc. If for some bizarre reason I wanted to have the Job name in the assignment table in addition to the Job PK (I don't recommend this, it's just an example), I would do that in the API.

So, what how does this relate to this blog topic? Not a single trigger. If there is an error anywhere, I have a couple of APIs to look at.

What would be a trigger based approach? A trigger to populate the PKs. A trigger to populate the denormalized Job name. Possibly even triggers to deal with integrity and referential constraints (but I sure hope not!).

In the trigger based approach, you now have code in many places, any of which can (and at some point will) break. In addition to the maintenance issue, there is also a performance impact with row-level trigger processing.

I would choose the former, API approach over the trigger based approach every time. I have inherited trigger based applications and suffered through every one of them. When I have time, I convert those to APIs as soon as I can.

Referential Integrity

Do not use triggers instead of foreign keys. I have seen trigger based foreign keys and the reason was that the "model was too complex for database referential integrity". In every case where that statement has been made (at least that I have seen), it was due to a poor data model and not a limitation in the database.

Distributed referential integrity is a different beast. I have seen one case where a trigger was used to maintain integrity with data in a different database. I have to admit that in this case, I don't see any alternatives. The trigger incurred some overhead in the application but it was an acceptable amount. If the remote database was down, the trigger failed but that was an intended effect.

Auditing

I have used Triggers for auditing database changes in the past. I even wrote a code generator that generated the triggers for me by looking at the dictionary and dynamically getting columns and such. At one time, I think that was the best approach. Today, Fine Grained Auditing offers an incredibly robust auditing mechanism. It works with SELECT statements and it also allows you to capture other information that is impossible or difficult to get with any other method.

Replication and Data Feeds

Another use of triggers has traditionally been to replicate all or subsets of data to different schemas or databases. Sometimes for application usage and other to aggregate data in a warehouse. Almost all databases now offer some kind of a database link and any database worth using offers some kind of replication.

In many cases, the database will create triggers behind the scenes but at least the database is then responsible for maintaining those triggers. Oracle offers Oracle Streams which is very robust and offers scheduled or real-time replication and Change Data Capture.

Multi-database Support

If you truly need to maintain your application on multiple databases, triggers can be useful. I don't mean that you use a bunch of Oracle features and hope to one day migrate but you actually, actively maintain your code base to run on multiple platforms.

In this case, triggers can keep you from changing your code as often. Take the PK example of using sequences. Some databases use an auto-increment data type, other databases use sequences but the select syntax is different from Oracle's syntax. In this case, you can encapsulate the logic in a trigger and only change that code when required. In the case of migrating to a database with auto-increment, you won't need the trigger at all.

Also, when auditing your application, you may have specific needs that individual databases might not support. In that case, you might want to use triggers to audit. You would want to have a common audit record across database types so it would make sense to use the triggers even on databases that support advanced auditing.

Third-party Applications

If you need to alter data or flow in a third-party application, your only option might be a trigger. In that case, you really have no choice. Before you choose a trigger, think about the other available options. In many cases, Fine Grained Auditing or streams can achieve the same thing and will be a lot less intrusive to the application.

I hope this answers some of the questions about using triggers. Did I miss any cases where it really makes sense to use a trigger? Have you seen triggers used in other ways (good example or bad)?

LewisC

Learn Oracle: Triggers

LewisC's An Expert's Guide To Oracle Technology

Today I will be writing about triggers. One of the questions I get fairly often is "what is the difference between a function, a procedure and a trigger?" I already wrote about functions and procedures in Learn Oracle: Procedures and Functions. You should probably read that one before you read this post.

What is a trigger?

A trigger is a special case of stored procedure that is fired during an event rather than being explicitly executed. A function or a procedure can be called from a command line or from within a different calling program. A trigger is called automatically when an event is fired.

In Oracle, a trigger can have the procedural code embedded in the body of the trigger, you can call out to another existing function or procedure, or a combination of both. The code in a trigger is generally the same as any procedure or function. You are allowed to create Java triggers but I see this rarely in live systems.

You cannot execute transaction control statements with in a trigger. That means no commits or rollbacks. If you need to rollback a transaction due to logic in a trigger, raise an exception and allow the calling program to take the appropriate action. As for commits, the calling application should always commit or rollback as needed.

Technically, there is a way to execute transaction control within a trigger but that can be abused, and really damage your data integrity, if done poorly. I will leave that topic for a discussion on advanced PL/SQL.

What are trigger events?

Various databases support different triggering events. Most all enterprise class databases support DML triggers (excluding SELECT). That means that the database will optionally fire anytime a table has a row updated, deleted or inserted. Many databases, including Oracle, also support DDL triggers. These fire when you create, alter or drop an object.

Oracle supports many events in addition to DML events. Oracle supports system events such as the database starting or stopping or when there is a server error. Oracle also supports user events such as a user logging on or off.

For a new user or developer, it is important that you understand DML triggers as you will find many applications that are Dependant on triggers. DDL, user and system triggers tend to have more advanced uses although you will see examples of those for various security related processing (such as VPD).

For the purpose of this blog entry, I'll stick to DML triggers.

Trigger Syntax

There are three main types of DML triggers: before, after and instead of. A before or after trigger can be a statement level trigger or a row level trigger.

Before

Obviously, a before statement trigger will fire before a statement is executed. A before row level trigger fires before each row that is affected by a statement.

After

An after statement trigger will fire after a statement is fired. A before row level trigger fires before each row that is affected by a statement.

Statement vs Row

The difference between a statement level trigger and a row level trigger is very easy to understand. If you perform an update that will affect 10 rows, a statement level trigger will fire once and a row level trigger will fire 10 times.

A statement level trigger does not have access to individual column values. A row level trigger does have access to column values. A before row trigger can change new column values.

Instead Of

An instead of trigger is built on a view. Many views are updateable with an instead of trigger. Many more views are not updateable for various reasons. With an instead of trigger, you can update any of these views. As the name implies, an instead of trigger is executed instead of Oracle trying to execute the command directly against the view.

Trigger Syntax

The syntax of a trigger varies database by database but for Oracle and PL/SQL (as well as for EnterpriseDB and SPL), the basic syntax of a DML trigger is:

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE|AFTER|INSTEAD OF       
  INSERT|DELETE|UPDATE ON table_name 
         [OF column_names]
  [REFERENCING [NEW AS new_cols] [OLD AS old_cols]]
  [FOR EACH ROW [WHEN (where_condition)]]
  [DECLARE]
  BEGIN
  EXCEPTION
  END; 

Let's look at the command row by row. Anything within square brackets ([]) is an optional clause. Any items delimited with a pipe (|) is part of a list.

Row 1: The create or replace should seem familiar if you are familiar with procedures and functions. Naming of a trigger follows standard naming conventions and rules.

Row 2: The BEFORE|AFTER|INSTEAD OF clause tells the database what type of trigger it will be.

Row 3: This line allows you define if the triggering event will be on UPDATE, DELETE, INSERT, a combination or all of these. The "ON table_name" identifies the table for which the trigger will be created.

Row 4: An optional clause is the "OF column_names". This statement only affects an UPDATE. If you only want to fire an update trigger when certain columns are modified, you may include this optional statement and list the columns that should be monitored.

Row 5: In a row level trigger, you have the values of the columns as they existed before the command and the new values as a result of the command. For an insert, there are no old values and for a delete, there are no new values. In an update, you have both old and new.

The REFERENCING clause allows you to name the old row and the new row. By default, they are named :OLD and :NEW. You refer to them as :OLD.column_name and :NEW.column_name. You can query them in both before and after row triggers, like:

IF :old.gl_id = :new.gl_id THEN...

In a before row trigger, you can even set the values of :new columns, i.e.

:new.gl_id := sequence.nextval();

Statement level triggers cannot access row level column data.

Row 6: A DML trigger, by default, is a statement level trigger. When you use "FOR EACH ROW", you turn it into a row level trigger.

The WHEN clause allows you to introduce additional matching criteria that Oracle will test before executing a trigger. It is standard where clause type criteria,

FOR EACH ROW WHEN (old.order_date BETWEEN sysdate - 10 and sysdate)

Row 7 and on are standard PL/SQL syntax. Think of them as an anonymous block attached to the trigger name and criteria.

Example

Here is a very simple trigger using the Employees table from Oracle XE.

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------

 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

Now, I'll create the trigger on employees. The trigger will only fire on insert or update (not delete), update will only fire when email or hire_date is modified and the trigger body will only fire when the :old.salary is greater than 7500. Because it will only fire when an :old value is set, it will never fire for an insert, even though I am defining it for insert.

SQL> create or replace trigger emp_email
  2  before insert or update of email, hire_date
  3  on employees
  4  for each row when (old.salary > 7500)
  5  begin
  6    dbms_output.put_line('Trigger executing');
  7  end;
  8  /

  Trigger created.

Next, I'll fire two inserts to add a couple of records. Only one of the records will have a salary > 7500. After the inserts, I will update each record. Only the record with a salary > 7500 will display the trigger message.

  SQL> set serveroutput on

  SQL> insert into employees
  2  (employee_id, last_name, email,
  3   hire_date, job_id, salary)
  4  values
  5  (101010, 'Smith', 'smith@barney.com',
  6   sysdate, 'PR_REP', 5000);

   1 row created.

  SQL> insert into employees
  2 (employee_id, last_name, email,
   3 hire_date, job_id, salary)
   4 values
   5 (101011, 'Jones', 'jones@barney.com',
   6 sysdate, 'PR_REP', 8000);

   1 row created.

   SQL> commit;

   Commit complete.

   SQL> update employees set
   2 email = 'under7500@noexec.com'
   3 where employee_id = 101010;

   1 row updated.

   SQL> update employees set
   2 email = 'over7500@willexec.com'
   3 where employee_id = 101011;
   Trigger executing

   1 row updated.

   SQL> commit;

   Commit complete.

   SQL>

That's pretty much it for DML triggers.

LewisC

A Match Made In Heaven

eHarmony, the large, internet dating site selects Oracle. eHarmony, who call themselves the #1 relationship service, has chosen Oracle to run its websites and data warehouse.

eHarmony uses its patented Compatibility Matching System, developed from 35 years of clinical and empirical research, to match highly compatible singles online. To support the more than 10,000 new users who register each day, eHarmony required a data management solution that would scale to accommodate its growing user base and to help ensure that its site is available 24 hours a day, seven days a week. To enhance its online services and end user experience, eHarmony contracted Cloud Creek Systems, Inc., a Southern California-based Oracle Certified Partner, to assist in the migration of eHarmony's Web sites and data warehouse to Oracle. eHarmony deployed Oracle Database 10g, Oracle Real Application Clusters, Oracle Clusterware, Oracle Automatic Storage Management, and Oracle Enterprise Manager 10g on multiple Sun Fire X4600 servers running Windows.

In addition to its clustered environment, eHarmony operates a rapidly growing Oracle data warehouse to support its extensive real-time and historical reporting initiatives. Oracle Partitioning is used in conjunction with the data warehouse to improve performance. eHarmony's Data Analytics Group relies on the information to support critical business decisions such as how to improve service offerings and enhance the eHarmony customer experience.

"We moved to Oracle because our services required greater scalability and availability," said Mark Douglas, vice president of technology, eHarmony. "We felt we had reached the limits with our previous platform. As a result of the move to Oracle, we're able to confidently support more users and we've improved Web site performance."

oracle eharmony

The Market Batteground

Somewhat following on the heels of my post yesterday, Sympathy For The Devil is in the Details, Tim DiChiara of SearchOracle posted The Market Battleground.

Tim mentions that with Oracle's steady lead (41%) in the database wars, maybe Oracle doesn't need to worry. He points out though that microsoft, while enjoying a lower market share, is growing at 28% of over 2005 and may pass DB2 in the market.

Tim didn't even mention open source databases though. Just this week, DBA Village, an Oracle forum/news site, had a poll asking which database most people thought Oracle should worry most about. Now, while this is an unscientific poll and could be impacted by "DB pride", I think it is interesting that the question was asked and that MySQL and EnterpriseDB/PostgreSQL both made the list. That shows that the Oracle community is asking itself if they need to be concerned.

The poll results, by the way, as of tonight, is:

Which database do you consider the biggest threat for Oracle?
With 342 responses so far:

• EnterpriseDB/PostgreSQL: 34%

• SQL Server: 28%

• DB2: 13%

• MySql: 11%

• No idea: 10%

• Other: 3%

These results are not really meaningful in the overall scheme of things but the question is out there. I'm waiting to see how Oracle answers.

LewisC

oracle open source market share database

Sympathy For The Devil is in the Details

Matt Assay has a great blog entry today, Sympathy for the Oracle. He basically describes things I have said many times, Oracle would rule the database world if it adjusted it's licensing model; Oracle Releases 11g Pricing and A Kinder, Gentler Oracle being two examples of that.

Matt talks about Oracle losing sales to SAP because of the approach to selling. SAP starts with thoughts and ends with execution while Oracle begins at the execution step.

Because open source makes it easier to win the bottom-up war.

I don't think Oracle needs open source. It already has the best database. The problem is that Oracle spends a fortune supporting the sales process:

Oracle already makes most of its profit on maintenance revenues and, as my friend reminded me, breaks even or loses money quite often on these initial big license sales (because upwards of 100 people can get involved in closing a $1 million deal with a multibillion dollar prospect). So, a low-cost sales model that gets it in the door and sets it up for downstream maintenance revenues sounds ideal.

And that's my point. Oracle doesn't need open source because open source is (and always will be) playing catch up. Oracle needs to change the model. Give the base model away free or nearly so. Sell the EE stack at a reasonable price (at least 1/4 or less of what it sells it for now), include ALL of the enterprise features as part of the base package and drop the sales force.

People buy from the web: no discounts, no sales people, no bull. Oracle moves from being the database company to being the software company. Partner with Sun to deliver Oracle Apps appliances. Partner with Redhat to deliver complete web solutions. Partner with MS & Dell to deliver SMB appliances.

Oracle would supply all of the software. Sun would supply some hardware and the OS, Dell hardware and MS the OS. The MS solution could have tight integration with .Net and MS-Office. Maybe Oracle would choose Google instead of MS. Same deal there though.

If Oracle doesn't do these things, than someone like EnterpriseDB will. Becuase that is the future of databases. We can have sympathy for the devil but the devil is in the details.

LewisC

oracle database enterprisedb open source future license

In2M Moves From Open Source database to Oracle

In2M is a company who makes an online financial productivity and personal spending management system, including MVelopes Personal which won the prestigious 2006 PC World World Class award and was selected as one of the Top 100 Best Products of 2006.

In2M recently switched from an open source database (I'm not sure which) to Oracle Standard Edition. Oracle Standard is the lower tier database (i.e. cheaper) that does not offer additional features such as partitioning or OLAP processing. Notably though, Standard Edition comes with RAC as part of it's feature set and does not require additional licensing for it. You are limited in the number of nodes you can cluster though.

"The possibilities in Real Application Clusters were another big reason for us choosing Oracle," said Law. "Right now we're running a proof-of-concept, merging databases under Real Application Clusters, to check out the architecture before moving forward. We believe that Real Application Clusters makes Oracle Database Standard Edition powerful enough to support us for the next four years, even given our high growth rate, the critical need for high-availability and high performance for our customers."

What makes this announcement particularly significant is that the company heavily uses open source in it's offering.

"We use Linux and open source platforms because it's cost effective," said Jason Law, Vice President of IT and Systems R & D for In2M. "But we were not comfortable with an open source database product for delivering high-availability software as a service. Oracle Database Standard Edition gives us the reliability we need now, and will scale to meet our future needs for some time to come, even despite our aggressive growth plans. Our target is 99.99 percent availability, and Oracle has not failed us since we started using it."

The migrated application is currently online with 50,000 users and it is expected to grow another 100,000 in the next year. In2M found it quite straightforward to migrate the product's original open source databases to Oracle. And its next product, Mvelopes Business - currently in early testing and expected to be available later this year - will be rolled out from scratch on Oracle Database Standard Edition.

Also notable is that the user interface for the MVelopes Personal application is written in Open Source Adobe Flex. That makes the application completely browser based. The application offers security via SSL.

Features of the application include Transaction History, Online Bill Payment, Net Worth Tracking, and Mobile Access (cell phones).

oracle open source flex database RAC linux