This discussion is archived
2 Replies Latest reply: Nov 27, 2012 1:57 PM by 976070 RSS

data in matrialized view group doesn't refresh

976070 Newbie
Currently Being Moderated
I have created multiple materialized view on my site and add them to a materialized view group; Then every object ( materialized view ) are added to a refresh group....
The materialized view is refresh fast and updatble.. when I do some update on my materialized view, changes are seen on the master site and vice-versa, BUT when i try to do the same work ( creating materialized view and add them on materialized view group ) with another Master site (lets say master site2), changes on the materialized view are note seen on the master site2.
note that i have followed steps declared on oracle documentation.
Do i have to add some command when I try to create multiple materialized view group.. or what is the reason that prevent the update on the master site..
pls help me :S
  • 1. Re: data in matrialized view group doesn't refresh
    damorgan Oracle ACE Director
    Currently Being Moderated
    No version numbers ... no code ... no actual error messages.

    No help is possible at this time.

    Your post reads roughly equivalent to the following:
    "I have two cars one of them starts and the other doesn't please tell me why my second car won't start."
    A complete and total lack of useful information is what connects your post and my second car. To get help you need to provide ALL relevant information.
  • 2. Re: data in matrialized view group doesn't refresh
    976070 Newbie
    Currently Being Moderated
    Thank you for replying..
    I am a beginner in the "world of oracle".
    for simplifying,,I am using oracle 10g for implementing a distributed database; In my distributed environment I have two master site (where data on every site are different) and a client (where i want to reassemble all data).

    Here is my code:

    *@master 1* :
    connect sysman/password@master;

    -ALTER SYSTEM SET GLOBAL_NAMES=False SCOPE=SPFILE;

    -BEGIN
    DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
    username => 'sysman' );
    end;
    /

    -GRANT COMMENT ANY TABLE TO sysman;

    -GRANT LOCK ANY TABLE TO sysman;

    -GRANT SELECT ANY DICTIONARY TO sysman;

    -BEGIN
    DBMS_DEFER_SYS.REGISTER_PROPAGATOR ('sysman');
    commit;
    end;
    /

    -BEGIN
    DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
    username => 'sysman',
    privilege_type => 'receiver',
    list_of_gnames => NULL );
    END;
    /

    -BEGIN
    DBMS_DEFER_SYS.SCHEDULE_PURGE (
    next_date => SYSDATE,
    interval => 'SYSDATE+1/24',
    delay_seconds => 0);
    END;
    /

    -BEGIN
    DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
    username => 'sysman',
    privilege_type => 'proxy_snapadmin',
    list_of_gnames => NULL );
    END;
    /

    -GRANT SELECT_CATALOG_ROLE TO sysman;

    -GRANT CREATE SESSION TO sysman;

    -GRANT SELECT ANY TABLE TO sysman;

    -BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPGROUP ( gname => 'repg');                
    END;
    /

    -BEGIN
    DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
    gname => 'repg',
    sname => 'sysman',
    oname => 'Chercheurs',                              
    type => 'TABLE',
    use_existing_object => TRUE ,
    copy_rows => FALSE );
    end;
    /

    -BEGIN
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
    sname => 'sysman',
    oname => 'Chercheurs',
    type => 'TABLE',
    min_communication => TRUE);
    end;
    /

    -BEGIN
    DBMS_REPCAT.RESUME_MASTER_ACTIVITY ( gname => 'repg');
    end;
    /

    -create materialized view log on Chercheurs;

    -commit;



    *@master 2* :
    same work as above but with a different master group name;
    P.S: this site have a materialized view wish contain object(table) from master site 1.


    *@client* :
    +//here where i try to reassemble data from master site 1 and 2 by using materialized view.+
    -connect sysman/password@mv;

    -ALTER SYSTEM SET GLOBAL_NAMES=False SCOPE=SPFILE;

    -BEGIN
    DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA ( username => 'sysman');
    end;
    /

    -GRANT COMMENT ANY TABLE, LOCK ANY TABLE TO sysman;

    -GRANT SELECT ANY DIcTIONARY TO sysman;

    -BEGIN
    DBMS_DEFER_SYS.REGISTER_PROPAGATOR ('sysman');
    commit;
    end;
    /

    -GRANT CREATE SESSION, ALTER ANY MATERIALIZED VIEW TO sysman;

    -BEGIN
    DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
    username => 'sysman',
    privilege_type => 'receiver',
    list_of_gnames => NULL );
    end;
    /

    -create database link master connect to sysman identified by password using 'master';     

    -BEGIN
    DBMS_DEFER_SYS.SCHEDULE_PURGE (
    next_date => SYSDATE,
    interval => 'SYSDATE+1/24/60',
    delay_seconds => 0,
    rollback_segment => '');
    end;
    /

    -BEGIN
    DBMS_DEFER_SYS.SCHEDULE_PUSH (
    destination => 'master',                                                                 
    interval => 'SYSDATE+1/24/60',
    next_date => SYSDATE,
    stop_on_error => FALSE,
    delay_seconds => 0,
    parallelism => 0 );
    end;
    /

    -BEGIN
    DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
    username => 'sysman',
    privilege_type => 'proxy_snapadmin',
    list_of_gnames => NULL);
    end;
    /

    -GRANT SELECT_CATALOG_ROLE TO sysman;

    -GRANT CREATE SESSION TO sysman;

    -GRANT SELECT ANY TABLE TO sysman;

    -BEGIN
    DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
    gname => 'repg',
    master => 'master',                                        
    propagation_mode => 'ASYNCHRONOUS');
    end;
    /

    -BEGIN
    DBMS_REFRESH.MAKE (
    name => 'refg',
    list => '',
    next_date => SYSDATE,
    interval => 'SYSDATE + 1/24/60',
    implicit_destroy => FALSE,
    rollback_seg => '',
    push_deferred_rpc => TRUE,
    refresh_after_errors => FALSE);
    end;
    /

    -create materialized view mv_ch refresh fast with primary key for update as select * from Chercheurs@master;

    -BEGIN
    DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
    gname => 'repg',
    sname => 'sysman',
    oname => 'mv_ch',
    type => 'SNAPSHOT',
    min_communication => TRUE);
    END;

    -BEGIN
    DBMS_REFRESH.ADD (
    name => 'refg',
    list => 'mv_ch',
    lax => TRUE);
    END;
    /

    What was happening first, is that when the client try to update, insert or delete from mv_ch, changes are seen at master site 1 and when the master site 1 also update, insert or delete table "chercheurs", changes are seen at the client side. But is not the case between client and master site 2( no changes are made).
    now after disconnect between 3 site; no changes are seen between the client and the master sites..
    I afraid I'm implementing a wrong architecture.
    N.B_: I didn't copy a repeating code where I should create multiple materialized view and when I add them to multiple materialized group; I have no error message.

    Thank you

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points