3 Replies Latest reply: May 26, 2012 9:19 AM by Hemant K Chitale RSS

    Basic Replication

    Stanisa
      Hi all,

      I have question about basic replication.
      I want to make read-only replication.
      The both databases are Oracle DBMS 11GR2.
      There are same schemas on both databases.
      I made on the database (db1) where are tables exists (in this example: vezbe.emp and vezbe.dept) MATERIALIZED VIEW LOG:
      CREATE MATERIALIZED VIEW LOG ON VEZBE.DEPT;
      CREATE MATERIALIZED VIEW LOG ON VEZBE.EMP;
      I created MATERIALIZED VIEW on the other database (db2):
      CREATE MATERIALIZED VIEW VEZBE.DEPT BUILD IMMEDIATE REFRESH FORCE ON DEMAND AS SELECT * FROM DEPT;
      CREATE MATERIALIZED VIEW VEZBE.DEPT BUILD IMMEDIATE REFRESH FORCE ON DEMAND AS SELECT * FROM EMP;
      Because the emp is child of deptno table and I need to refresh deptno before emp I mede refresh group as follows:

      BEGIN
      DBMS_REFRESH.MAKE(
      name => 'VEZBE.RG_VEZBE_1',
      list => '',
      next_date => SYSDATE,
      interval =>'SYSDATE + 5/1440',
      implicit_destroy => FALSE,
      lax => FALSE,
      job => 0,
      rollback_seg => NULL,
      push_deferred_rpc => TRUE,
      refresh_after_errors => TRUE,
      purge_option => NULL,
      parallelism => NULL,
      heap_size => NULL);
      END;
      /

      BEGIN
      DBMS_REFRESH.ADD(
      name => 'VEZBE.RG_VEZBE_1',
      list => 'VEZBE.DEPT',
      lax => TRUE);
      END;
      /

      BEGIN
      DBMS_REFRESH.MAKE(
      name => 'VEZBE.RG_VEZBE_2',
      list => '',
      next_date => SYSDATE,
      interval =>'SYSDATE + 10/1440',
      implicit_destroy => FALSE,
      lax => FALSE,
      job => 0,
      rollback_seg => NULL,
      push_deferred_rpc => TRUE,
      refresh_after_errors => TRUE,
      purge_option => NULL,
      parallelism => NULL,
      heap_size => NULL);
      END;
      /

      BEGIN
      DBMS_REFRESH.ADD(
      name => 'VEZBE.RG_VEZBE_2',
      list => 'VEZBE.EMP',
      lax => TRUE);
      END;

      Everything works as expected but the problem is with issuing DDL commnads.
      When I delete or add column on the table it is not shown on Materialized view!

      What I am doing wrong?

      Thanks in advance.
        • 1. Re: Basic Replication
          Hemant K Chitale
          DDLs do not get replicated.

          When you specify "SELECT * FROM DEPT" in your CREATE MVIEW definition, Oracle expands the "*" to the list of columns that were actually present in the DEPT table when the CREATE MVIEW was executed. Any new colum subsequently added to DEPT does not get added in the MVIEW.


          Hemant K Chitale
          • 2. Re: Basic Replication
            Stanisa
            Thanks for the help.

            I know that when I change structure of the table it is not propagated to MV site. My question is what to do to propagate change on the table to MV site?
            • 3. Re: Basic Replication
              Hemant K Chitale
              The simplest way is to drop and recreate the MV.


              You can explore DBMS_REPCAT.EXECUTE_DDL but that is really used in MultiMaster Replication. You have to Quiesce the Repgroup and also generate Replication Support.

              See http://docs.oracle.com/cd/E11882_01/server.112/e10707/rarrcatpac.htm#i97906

              Hemant K Chitale