Re: replicating snapshots (multitiered materialized views)..

Did you setup the mviews as following?

site A:

     create materialized view a_mview
     ....
     as select ... from table1 where;

     create materialized view log on a_mview;

site B:
     create materialized view b_mview
     refresh fast
     ...
     as select * from a_mview_at_siteA;

? Then that would count as a nested mview. To me it appears that you have the setup a little different?

The error you are getting states that the mview log of the master has been purged. As a result, the necessary
data required to fast refresh the slave is not there. Now how would the data in mview log of master get purged? 1. If you create the b_mview *before* you create the mview log on a_mview, then you would get the error.
2. When an mview is created, the mview is first registered at the master site (look in dba_registered_mviews).

    This then helps oracle to decide whether to purge a mview log. If all the registered mviews have been refreshed,

    then oracle knows that its safe to purge the mview log since all the registered mviews have been refreshed.

    The registering is usually done automatically when you create a mview .. or when you do a complete refresh

    of the mview.
    Consider a case when there is mview log on a_mview, and there are two mviews defined on a_mview (called

    b_mview and c_mview)
    In case the registering has failed for b_mview, its possible that c_mview defined on the master (a_mview)

    is the only mview registered. And when c_mview refreshes against the master (a_mview)

    then oracle purges the mview log (of a_mview), before b_mview gets a chance to refresh. Thus as a result,

    b_mview gets an error stating that the materialized view log on a_mview is younger than the last refresh.

    The solution to this would be to recreate the b_mview or do a complete refresh .. and make sure that

     b_mview is registered in the master site (dba_registered_mviews).

Hope that is a better explanation. You might want to specify exactly how you set up these mviews / mview log's in case it still does not work for you.

Anurag Received on Fri Feb 11 2005 - 11:58:14 CST