RAC for Reporting...!

Hello everyone,

I am in the process of upgrading my database to 9i (from 8i)...

As part of this process I need to propose a HA solution which will also provide a reporting function.

We currently use managed standby database which works fine for our purposes, but in order to utilise this for reporting I need to suspend recovery and open the DB in Read Only mode. This means that while the users are offloading adhoc Queries to the Standby, the 2 databases are becoming more and more out of sync which increases recovery time and makes the query data non-current.

The obvious replacement for this solution 9i would be to move to Logical Standby which will allow me to maintain an up-to-date standby copy which can concurrently be used as a reporting server. Unfortunately the App makes extensive use of LONG datatypes which aren't supported with Logical Standby.

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).

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

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.

Thanks for your time

Matt Received on Thu May 06 2004 - 02:52:13 CDT