Re: RAC for Reporting...!

Matt wrote:

> Hello everyone,

[snip]

> In my search for alternatives I have been looking into utilising RAC
> to provide this joint function. For example I could establish a two
> node cluster and direct online users to one node and query users to
> the other. This has the advantage of not requiring us to maintain 2
> copies of the data (one for query, one for online), since the two
> nodes will access the same DB. This setup would also mean that the
> query data is always current, and wont require any scripting to
> regularly resync the 2 databases.
>
> However this will have its own problems because Query users will force
> inter node block transfer to occur which will add a large performance
> overhead to the application. (And some of the users' queries are
> absolute nightmares in terms of LIOs).

No, no! RAC is free of hidden costs, works like a dream, never causes a problem, and you just have to bolt additional boxes into the cluster whenever the mood takes you for things to speed up immeasurably!!!

Sorry, I couldn't resist. You strategy is sound, and your appreciation of the attendant problems is also sound. Marketing would have you believe otherwise, but internode block transfer, as you correctly deduce, is definitely not a free lunch. With enough investment in a decent interconnect and careful tuning (with perhaps the use of multi block locks on appropriate tablespaces) you might get it to work acceptably. But I wouldn't hold my breath.

> Has anyone implemented RAC to provide both HA and reporing
> functions....?

I know people that have. I also know people that wish they hadn't.

> I have also considered Advanced Rep, but this setup requires a large
> amount of administration, and potentially incurs a large performance
> overhead to keep the data current (i.e. Synchronous Replication).
>
> Anyone have any details of how they have overcome the problem of
> dealing with an up-to-date reporting server while at the same time
> providing a failover/HA function.

You said that managed standby was adequate for your purposes, but the problem was that the standby, when acting as a reporting database, was getting out of synch (true enough). You also mentioned the problem of non-current data -but presumably you've been using this mechanism for long enough that your users are accustomed to that fact. In which case, it seems to me that the divergence between the standby and your primary is the real issue.

You are aware, aren't you, that you can have more than one standby database? If your primary had two standby databases, one of which is used for reporting, you would always have the other standby to fall back on in a disaster. You'd still be stuck with the divergence issue for the database that is acting as the reporting server, but at least your recovery times would not be affected.

Seems to me that adding one more standby to the mix is a lot simpler than embarking on two possible new technologies which do indeed have significant issues.

On the other hand, you've mentioned a couple of times the desire for synchronous transport of data, and that rather makes me suspect that your business rules are changing such that out-of-date reporting is becoming no longer acceptable. Are they? Or is it just a 'would like' rather than a 'must have'.

Regards
HJR Received on Thu May 06 2004 - 03:24:39 CDT