1 2 Previous Next 15 Replies Latest reply: Sep 5, 2011 9:25 AM by Hemant K Chitale RSS

    replication does not work automatically

    siegwin.port
      Hi,
      I have Master Replication Groups und on the client the same MV replication groups for update.

      Every hour a replication should be started but it does not work. The job seems to be executed.
      On the master site alle tables in teh group are generated. The status of the groups is NORMAL.

      Where is my fault?
      Who can help me?
      Regards
      Siegwin
        • 1. Re: replication does not work automatically
          Hemant K Chitale
          You should confirm if the job is running by querying DBA_JOBS.


          If it encounters errors, errors would be logged in the alert.log and trace files in background_dump_dest. See if errors are being logged.


          Hemant K Chitale
          • 2. Re: replication does not work automatically
            siegwin.port
            Hi,
            I run the following commands:
            execute dbms_repcat.do_deferred_repcat_admin('LOG_REP_GRP', FALSE);

            execute DBMS_REFRESH.REFRESH ('LOG_REP_GRP');

            But it seemed that there was no execution. I had no entry in the alert.log and no trace, but I got the message "procedure executed".

            1 record at the master site was inserted and an entry into the MLOG$ table was made.
            • 3. Re: replication does not work automatically
              Hemant K Chitale
              Did the deferred_repcat_admin complete ? It would have been executed by a dbms_repcat.do_defer_repcat_admin job so such a job must be scheduled.
              Query DBA_REPCATLOG on both sites for the status of the repcat_admin.

              Hemant K Chitale
              • 4. Re: replication does not work automatically
                siegwin.port
                Hi,
                this is the master site, I think it is ok:
                SQL> select sname,master,status,gname,rpc_processing_disabled,owner
                2 from dba_repcat;

                SNAME M STATUS GNAME R OWNER
                ------------------------------ - --------- ------------------------------ - ------------------------------
                LOG_REP_GRP Y NORMAL LOG_REP_GRP N PUBLIC
                FAQ_REP_GRP Y NORMAL FAQ_REP_GRP N PUBLIC
                This is the MV site:
                SQL> select sname,master,status,gname,rpc_processing_disabled,owner
                2 from dba_repcat;

                SNAME M STATUS GNAME R OWNER
                ------------------------------ - --------- ------------------------------ - ------------------------------
                FAQ_REP_GRP N NORMAL FAQ_REP_GRP N PUBLIC
                LOG_REP_GRP N NORMAL LOG_REP_GRP N PUBLIC

                I think it ok too.

                What else should i do?
                Regrads
                Siegwin
                • 5. Re: replication does not work automatically
                  Hemant K Chitale
                  A Materialized View is generally refreshed by a PULL mechanism so you would have a Refresh job at the MView site. Check the job and see when it last ran. (Query DBA_JOBS)

                  Hemant K Chitale
                  • 6. Re: replication does not work automatically
                    siegwin.port
                    The job seemed to run. the last time was 09:05 today. But it didn't refresh.
                    • 7. Re: replication does not work automatically
                      Hemant K Chitale
                      Maybe the new row doesn't qualify via the filter predicates of your MView definition ?


                      Hemant K Chitale
                      • 8. Re: replication does not work automatically
                        siegwin.port
                        There is no filter for the MV
                        CREATE MATERIALIZED VIEW ANGEBOTSKOEPFE REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM ANGEBOTSKOEPFE@orpc3;

                        BEGIN
                        DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
                        gname => 'LOG_REP_GRP',
                        sname => 'logistics',
                        oname => 'ANGEBOTSKOEPFE',
                        type => 'SNAPSHOT',
                        min_communication => TRUE);
                        END;
                        /

                        Itried to execute the refresh job manually by
                        exec dbms_job.run(95);
                        Result:
                        anonymer Block abgeschlossen
                        but it didn't replicate
                        • 9. Re: replication does not work automatically
                          Hemant K Chitale
                          A CREATE REPOBJECT is not a Refresh job.

                          Don't keep mixing two separate issues.

                          If the MV is created ... don't bother repeating the CREATE REPOBJECT again here.

                          An MV is refreshed by a DBMS_MVIEW.REFRESH.
                          Is job 95 the Refresh Job ?


                          Hemant K Chitale
                          • 10. Re: replication does not work automatically
                            siegwin.port
                            Hi,
                            every hour the job 95 should execute :
                            dbms_refresh.refresh('"LOGISTICS"."LOG_REP_GRP"');

                            Regards
                            Siegwin
                            • 11. Re: replication does not work automatically
                              Hemant K Chitale
                              If this Refresh job is running and doesn't error out .... then it should fetch new rows from the source if the INSERT at the source has been committed.

                              A FAST Refresh needs a MATERIALIZED VIEW LOG on the source table.


                              A Materialized View that has been created with the FOR UPDATE clause is created with the intention of propagating changes from the MV site back to the master site. Do you make changes to the MV at the replicated site ? Are these getting queued and propagated to the Master Site ? Is the propagation being applied successfully ? Can you confirm that there are no data conflicts because of this 2-way replication ?


                              Hemant K Chitale
                              • 12. Re: replication does not work automatically
                                siegwin.port
                                There is a MV log on the master site an 1 entry in this log
                                • 13. Re: replication does not work automatically
                                  Hemant K Chitale
                                  I suggest that you log an SR with Oracle Support.
                                  You have setup replication as per the documentation but it "does not work automatically", without even reporting any errors. You have a provable test case.

                                  Hemant K Chitale
                                  • 14. Re: replication does not work automatically
                                    siegwin.port
                                    Hi,
                                    please what is SR?

                                    For testing I made a new refresh group:
                                    --master site

                                    CREATE
                                    TABLE "LOGISTICS"."DEPT"
                                    (
                                    "DEPTNO" NUMBER (2, 0),
                                    "DNAME" VARCHAR2 (14 BYTE),
                                    "LOC" VARCHAR2 (13 BYTE),
                                    CONSTRAINT "DEPT_PF" PRIMARY KEY ("DEPTNO") USING INDEX PCTFREE 10
                                    INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT
                                    1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
                                    FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
                                    CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE
                                    )
                                    SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
                                    NOCOMPRESS LOGGING STORAGE
                                    (
                                    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE
                                    0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
                                    CELL_FLASH_CACHE DEFAULT
                                    )
                                    TABLESPACE "SYSTEM" ;

                                    create materialized view log on dept;


                                    execute DBMS_REPCAT.CREATE_MASTER_REPGROUP (gname => 'DEP_REP_GRP');

                                    begin
                                    DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
                                    sname =>'LOGISTICS',
                                    oname =>'DEPT',
                                    type => 'TABLE',
                                    gname => 'DEP_REP_GRP');
                                    end;

                                    begin
                                    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
                                    sname =>'LOGISTICS',
                                    oname =>'DEPT',
                                    type => 'TABLE');
                                    end;

                                    --mv site
                                    CREATE MATERIALIZED VIEW DEPT REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM DEPT@orpc3.world;

                                    begin
                                    DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
                                    gname => 'DEP_REP_GRP',
                                    master =>'ORPC3.WORLD');
                                    end;

                                    begin
                                    DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
                                    sname => 'LOGISTICS',
                                    oname => 'DEPT',
                                    type => 'SNAPSHOT',
                                    gname => 'DEP_REP_GRP');
                                    end;

                                    begin
                                    DBMS_REPCAT.REFRESH_MVIEW_REPGROUP (
                                    gname => 'DEP_REP_GRP');
                                    end;
                                    /

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

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

                                    BEGIN
                                    DBMS_DEFER_SYS.SCHEDULE_PUSH (
                                    destination => 'orpc3.world',
                                    interval => 'SYSDATE + 1/24',
                                    next_date => SYSDATE,
                                    stop_on_error => FALSE,
                                    delay_seconds => 0,
                                    parallelism => 0);
                                    END;
                                    /

                                    BEGIN
                                    DBMS_REPCAT.REGISTER_MVIEW_REPGROUP (
                                    gname => 'DEP_REP_GRP',
                                    mviewsite => 'ORATOSHI.WORLD');
                                    END;
                                    /

                                    commit;
                                    I inserted 1 record on the master site and 1 record on the mv site.
                                    It doesn't refresh automatically
                                    1 2 Previous Next