3 Replies Latest reply: Apr 10, 2014 3:37 PM by Mimi Miami RSS

    Refresh MV from Active Data Guard standby

    Mimi Miami

      Is it possible to refresh a materialized view (either complete or fast) from an Active Data Guard standby database open in read-only w/apply mode?  Or, can I only do the refresh from the primary?

        • 1. Re: Refresh MV from Active Data Guard standby
          mseberg

          Hello;

           

          It might be possible but I doubt it would be the preferred way to do it. Since Active Data Guard is a READER database with Disaster protection I don't believe it would be possible.

           

          I would always do this on the Primary side.

           

          Best Regards

           

          mseberg

          • 2. Re: Refresh MV from Active Data Guard standby
            Mimi Miami

            Hello mseberg,

             

            You made a very good point on the very definition of a READER database.

             

            After you replied, I found in MOS:  Create a Distributed Materialized View Based on a Master Table on a Read-Only Database (Doc ID 276023.1)

             

            This explains that it is NOT possible to do a fast refresh from a RO standby database.  However, it *can* be possible to do a complete refresh from an RO standby.  This depends on the query used to define the MV.  If the RDBMS parser determines the MV is *capable* of fast refresh (even though there may not be a need for it at the present time), then you cannot do a complete refresh from the RO standby.  If the parser determines the query is NOT capable of fast refresh, then you can do a complete refresh from the RO standby.  I tested this scenario on 11.2.0.3.8 and found it to be true:

             

              CREATE MATERIALIZED VIEW MIMI.MV_TEST

              TABLESPACE USERS

              BUILD IMMEDIATE

              REFRESH COMPLETE

              AS (SELECT * FROM TEST@MIMI_ROSTBY);

             

             

              ORA-16000: database open for read-only access

              ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 364

              ORA-06512: at "SYS.DBMS_SNAPSHOT_UTL", line 446

              ORA-06512: at line 1

             

             

            Now I changed the MV query to use a "non-repeating function" (ROWNUM), which causes the query to become complex (not capable of fast refresh).  I am now able create a MV on the RO standby because the query is no longer capable of fast refresh and therefore does not require registration at the RO standby:

             

              CREATE MATERIALIZED VIEW MIMI.MV_TEST

              TABLESPACE USERS

              BUILD IMMEDIATE

              REFRESH COMPLETE

              AS (SELECT * FROM TEST@MIMI_ROSTBY WHERE ROWNUM>0);

             

              Created.

            • 3. Re: Refresh MV from Active Data Guard standby
              mseberg

              Thanks for posting the additional information.

               

              mseberg