Re: RAC for Reporting...!

From: Mark Bole <makbo_at_pacbell.net>
Date: Fri, 07 May 2004 22:08:25 GMT
Message-ID: <tNTmc.62025$7r5.35404@newssvr25.news.prodigy.com>

Matt wrote:

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

>
>
> Thanks for the feedback....
>
> Unfortunately the need for up-to-date reporting data has become much
> more of a requirement and the pressure is on to devise a solution to
> this issue. This rules out Data Guard (at least physical standby).
>
> I have had a rethink.... An easier solution would probably be the
> following:
>
> Set up a Primary/Standby RAC (i.e. Active/Passive).. This will avoid
> the potential problems of cache fusion (performance and admin) while
> at the same time providing a HA solution which doesn't incur data loss
> or downtime (or at least minimises them).
>
> In order to handle the reporting queries, create materialised views
> for the main hitters (heavy SQL) and enable query re-write to avoid
> the query overhead on the OLTP users.
>
> I can configure these Mat Views for 'on demand' refresh (scheduled
> nightly) and then give certain 'super' users the ability to refresh
> these at suitable intervals if they need up to the minute data.
>
> Only problem I have now is working out the exact limitations of Mat
> Views in 9i. I have tried created some for my existing queries in 8i
> and they don't allow query re-write with the expressions I have used.
> So I need to prove that they will work in v9.2 and if so, the solution
> looks like a goer.
>
> The best part of this solution is that the users won't need to be
> re-directed to the standby for queries (which would be a real
> nightmare in our environment) and also the queries don't need to be
> changed because Mat Views are transaparant to the users.
>
> What do you think..?
>
> Thanks again..
>
> Matt

You seem to have your heart set on the RAC approach, but let me toss in another pitch for the Logical Standby approach which you originally considered as an alternative.

First, as HJR mentioned, by all means keep one physical standby dedicated exclusively to disaster recovery. Trying to combine HA and reporting into a single solution will necessarily compromise both (will you have to turn off your production reporting function if you are forced to fail over for HA purposes?). HA can also mean a cluster package at the OS level, such as Veritas, HP Service Guard, or Red Hat Clustering, and disk mirroring.

Assuming your reporting queries don't include the LONG datatypes, you should be able to set up a logical standby (LS) that skips those specific values, so that all the other data of interest can be updated in "real time". You're going to want to convert those LONG datatypes to LOB anyway -- although Oracle has not announced a de-support date, it is clear LONG is going away eventually.

One of the beauties of LS is precisely that you can place certain schema objects under the control SQL Apply from the primary redo logs, while doing whatever you want with other objects. You can even have your LS in noarchivelog mode, to further decrease overhead (if this fits your overall recovery strategy, of course).

For example, I am testing a reporting instance where the main schema of interest is maintained read-only via SQL apply. Then, I can create MV logs on tables, and then in another schema (local to the LS, not on the primary), I can create the fast-refresh MV's which use these logs, and then use query re-write against these MV's.

Under 9i, try the DBMS_MVIEW.EXPLAIN_REWRITE procedure, it is very helpful when trying to figure out why rewrite didn't work as expected.

--Mark Bole Received on Fri May 07 2004 - 17:08:25 CDT