Madhu,
I would have two snapshots and one synonym. Have the
webpages point to the synonym, and the synonym point
to one of the snapshots while the other is refreshing.
When the other is finished refreshing, recreate the
synonym to point to the refreshed one and start the
refresh on the other. The outage will be very brief,
on the order of less than a second.
The same thing can be accomplished via a view, but
since you are not changing the presentation of the
data a synonym might be a simpler choice.
On later versions of Oracle any stored procedure that
relies on this synonym will become invalid when you
drop and recreate the synonym, however, they will
automatically recompile when they are run again.
This process can lead to very brief outages on the
webpage, if someone just happens to hit the webpage at
the exact instant that the synonym is being recreated.
I think that this might be a cost you have to bear to
have the quick refresh capability you are talking
about. You might be able to figure out how to briefly
restrict access to the database/website/webpage during
that split second, or just do your synonym swap at 3AM
when no one is looking.
hth,
jack
- "Reddy, Madhusudana"
<Madhusudana.Reddy_at_bestbuy.com> wrote:
> Jack , DG and ALL,
> I Have problem in creating the Fast Refresh MVs,
> from the existing code ,
> which i can not change in present situation.
> So I am still looking for another option to minimize
> the down time( blank
> web pages at the time of MV refresh ) , even by
> using the COMPLETE refresh .
> For me space is not a problem ..
>
> I would like to hear some more ideas to eliminate
> the down time , with the
> existing MVs ( Complete Refresh )
>
> Hope i hear you all soon ,
> Thanks again
> Madhu
>
>
>
>
> -----Original Message-----
> Sent: Friday, June 21, 2002 5:33 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Thanks Mahu. Do you get the feeling that I might
> have
> done that a few times? ;)
>
> Snapshots and materialized views are the same thing.
> I
> guess I might start calling them materialized views
> in
> the next version or two, but it is so hard to give
> up
> old habits.
>
> Another thing you might need to know - you can't
> easily change a job in the Oracle job queue unless
> you
> are the owner, which means that you can't do it as
> DBA.
>
> There is a package called dbms_ijob that will allow
> you to change jobs as a dba even if you don't own
> them. There is usually no public synonym for this
> package, so you will have to refer to it as
> sys.dbms_ijob. I believe that this package is not
> officially supported, so you might not find a lot of
> documentation on it, but I have used it for over a
> year without any problems.
>
> To turn off a snapshot refresh, use the
> sys.dbms_ijob.broken function.
>
> *BE ADVISED*
>
> If you unbreak a job in the Oracle job queue, it
> will
> try to run immediately. This includes snapshot jobs.
> If you unbreak a *complete* snapshot refresh job,
> the
> first thing it does is truncate the target table.
> Unbreak a complete snapshot refresh job in the
> middle
> of the day and viola, the users suddenly have no
> data.
> Be careful.
>
> hth,
> jack
>
>
> --- "Reddy, Madhusudana"
> <Madhusudana.Reddy_at_bestbuy.com> wrote:
> > Jack,
> > Nice picture of the whole thing .
> >
> > Through out your solution , mentioned SNAPSHOT ,
> you
> > mean Materialized view
> > ???
> >
> > Thanks,
> > Madhu
> >
> >
> >
> > -----Original Message-----
> > Sent: Friday, June 21, 2002 2:33 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Recreate the snapshot to allow fast refresh, (you
> > will
> > have to create a snapshot log on your source
> table)
> > and refresh once every 5/10/20/30 minutes.
> >
> > Fast refreshes are just one commit that is either
> > committed or rolled back at the end. Viola, fresh
> > data
> > instantaneously.
> >
> > You can do a refresh immediate when you recreate
> the
> > snapshots so it will build the data right away.
> >
> > To do it really fast, create a new snapshot with
> the
> > correct definition, rename the old snapshot,
> rename
> > the new snapshot to the old name, recompile your
> > packages and procedures, drop the old snapshot,
> and
> > viola, new snapshot.
> >
> > Snapshots refresh via a job in the Oracle job
> queue.
> > You can adjust timing on this job to adjust your
> > refresh frequency.
> >
> > Make sure you get your indexes, stats, and grants
> in
> > place on the new snap too. Check your synonyms as
> > well.
> >
> > hth,
> >
> > jack
> >
> >
> >
> > --- "Reddy, Madhusudana"
> > <Madhusudana.Reddy_at_bestbuy.com> wrote:
> > > Hello All,
> > >
> > > I have a set of Materialized views in my DB . we
> > > refresh ( COMPLETE) these
> > > MVs, couple of times a day. Web server (
> > application
> > > ) will hit these MVs to
> > > show the data on web pages. But the complete
> > Refresh
> > > of MVs are consuming
> > > much time and , at this point of time ,
> > Application
> > > is not able to show
> > > right data on web pages. This is like a down
> time.
> > I
> > > need some suggestions
> > > from you all, in order to minimize or zeroing
> this
> > > down time.
> > >
> > > The first thing I can think of is , FAST refresh
> ,
> > > but one of my Sr.DBA told
> > > me that the MV definition will not allow us for
> a
> > > FAST refresh( Are there
> > > any limitations for FAST refresh ???? ). Here is
> a
> > > sample MV Definition :
> > >
> > > CREATE MATERIALIZED VIEW GENRELOB
> > > NOLOGGING
> > > BUILD IMMEDIATE
> > > REFRESH COMPLETE ON DEMAND
> > > DISABLE QUERY REWRITE
> > > AS SELECT DISTINCT
> > > '1' AS CLIP,
> > > LOB.LOB_ID,
> > > LOB.LOB_CD,
> > > GENRE.GENRE_ID,
> > > GENRE.GENRE_DESC,
> > > GENRE.GENRE_DESC AS INSTANCENAME
> > > FROM
> > > GENRE,
> > > GENRE_LOB_XREF,
> > > LOB,
> > > GENRE_PRODUCT_XREF
> > > WHERE
> > > GENRE.GENRE_ID = GENRE_LOB_XREF.GENRE_ID AND
> > > GENRE_LOB_XREF.LOB_ID = LOB.LOB_ID AND
> > > GENRE_PRODUCT_XREF.genre_id = GENRE.genre_id
> AND
> > > GENRE.DSPLY_IND = 'Y'
> > > ORDER BY
> > > LOB_CD,
> > > GENRE_DESC
> > > ;
> > >
> > >
> > > My Goal is to view the FRESH data on web pages
> all
> > > the time , irrespective
> > > of MV Refresh. Would anybody suggest me some
> > bright
>
=== message truncated ===
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Jun 24 2002 - 08:33:21 CDT