Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: RAC for Reporting...!

Re: RAC for Reporting...!

From: Matt <mccmx_at_hotmail.com>
Date: 10 May 2004 00:26:04 -0700
Message-ID: <cfee5bcf.0405092326.4e4948e1@posting.google.com>


Thanks for the feedback...

I don't believe the queries actually reference any LONG columns which means that the Logical Standby approach may still be viable...

I like your idea about setting up a logical standby which is dedicated to Reporting and is a subset of the schema. However won't the overhead of the log transport services from primary to standby be just as much as a localised MV log and fast refreshes at the primary site..

It seems like an additional (unneccesary) step to first copy the changes to a remote standby and then build fast refresh MVs on these 2nd copies of the reporting tables.

I will investigate the use of DBMS_MVIEW.EXPLAIN_REWRITE today to work out why the MV can't be used for rewrite...

Thanks for you help.

Matt

Mark Bole <makbo_at_pacbell.net> wrote in message news:<tNTmc.62025$7r5.35404_at_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 Mon May 10 2004 - 02:26:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US