10 Replies Latest reply: Jan 16, 2009 4:02 AM by Babu Baskar RSS

    Replication using Materialized View vs Streams

    521468
      Hi All,

      I am looking at create a read-only copy one of my transactional database to be used by my report users. I am currently using Oracle 10g. I have the following questions in this regard:

      1. Which among the two is a better option - Replication using read-only materialized views or Replication using Oracle Streams?
      2. Can Streams help me in replicating my entire database? Will changes to the tables and DB objects in my source DB propagate automatically to my read-only copy of DB?
      3. Form a performance perspective which option to replicate would be better? I am looking at updating my read-only copy only three times a day (in an interval of 8 hrs).

      Thanks in advance for your suggestions in this regard.
        • 1. Re: Replication using Materialized View vs Streams
          P.Forstmann
          Since Streams is a Entreprise Edition only feature, you could also evaluate Data Guard usage: especially logical standby feature which allows you to have the database open and synchronized with the source db at the same time.

          I thinks Stream can also replicate at database level but I've not used it.

          Streams or Data Guard looks better from setup point of view because you don't have to setup a database object like materialized view for each source table.
          • 2. Re: Replication using Materialized View vs Streams
            521468
            Thanks for that reply. Can you please let me know how can Dataguard help in creating a read-only copy of a database. I have never used this earlier. How is it better than Streams?

            Thanks in advance.
            • 3. Re: Replication using Materialized View vs Streams
              P.Forstmann
              The following link gives a high level comparison of Streamns vs Data Guard:
              http://www.oracle.com/technology/deploy/availability/htdocs/DataGuardStreams.html

              To build a logical standby database is also documented in http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ls.htm#g105412.
              • 4. Re: Replication using Materialized View vs Streams
                Babu Baskar
                Hello,

                As per as i know; I recommend Materialized view Replication (NOT Multimaster) this is usefull for Replication using read-only materialized views..

                To compare Data-Guard this is

                1/ Replication - Distribute env.

                2/ Replication - Performance Improvement.

                Babu
                • 5. Re: Replication using Materialized View vs Streams
                  400137
                  As Pierre Forstmann said the Data Guard Logical Standby Configuration would be the way to go (of course having in mind what you have provided as an information so far).

                  The main purpose of the logical standby database is to be used for reporting operations.

                  Setting up a materialized view based solution for the whole database could be a maintenance nightmare. Think about maintaining DDL changes...

                  Check Out Oracle® Data Guard Concepts and Administration 10g Release 2 (10.2) with a focus on Logical Standby <b>LINK</b>

                  Cheers,
                  Mihajlo
                  • 6. Re: Replication using Materialized View vs Streams
                    Babu Baskar
                    Think about maintaining DDL changes...
                    Yes. We can to DDL Changes also if we use Replication.

                    Babu
                    • 7. Re: Replication using Materialized View vs Streams
                      400137
                      Babu Baskar wrote:
                      Think about maintaining DDL changes...
                      Yes. We can to DDL Changes also if we use Replication.

                      Babu
                      Babu,

                      I never said that you can't do DDL against the master table of a materialized view, I just wanted to point out that maintaining DDL changes in such an environment could be a nightmare.

                      As you may already know a DDL change on a master table will likely require additional actions to be taken against the materialized view referencing that table.

                      For instance, if you have a read-only MV and you added a new column to the master table. Suppose you want to have this column available in the MV (and according to OPs requirements you do). What you need to do is to actually recreate the MV.

                      Refer to Metalink Note #1029823.6

                      Despite the "DDL challange", replicating the whole database (OPs requirement) using MVs just doesn't fit as a reasonable solution. (unless your database is very small and contains only few tables, which I doubt is the case here)

                      Again, Logical Standby Database can be used for Reporting Purposes and would be the best fit for OPs needs, unless the data he wants to replicate is in conflict with the Logical Standby Database data type restrictions LINK. More or less you the same restrictions apply with Streams configuration as well (both are dependant on LogMiner-based technology to when applying the changes).

                      As I said earlier, as long the OP needs to replicate the entire database and have the second instance read only that would be used for reporting purposes, Oracle Logical Standby is the way to go.

                      If the OP needs to be able to write the data, or to be able to apply much finer restrictions on what data should be replicated over then consider Oracle Streams.

                      There is a great comparative study between these two technologies in Metalink Note# 300223.1

                      Babu, I would really like to hear your arguments why do you think the MV-solution is the way to go.

                      Cheers,
                      Mihajlo
                      • 8. Re: Replication using Materialized View vs Streams
                        Babu Baskar
                        Hello Mihajlo,

                        Thanks for your response.
                        For instance, if you have a read-only MV and you added a new column to the master table. Suppose you want to have this column available in the MV (and according to OPs requirements you do). What you need to do is to actually recreate the MV.
                        Yes. I agree
                        why do you think the MV-solution is the way to go.
                        From my understanding; Advanced Replication

                        Availability

                        Replication provides fast, local access to shared data because it balances activity over multiple sites. Some users can access one server while other users access different servers, thereby reducing the load at all servers. Also, users can access data from the replication site that has the lowest access cost, which is typically the site that is geographically closest to them.

                        Performance

                        Replication provides fast, local access to shared data because it balances activity over multiple sites. Some users can access one server while other users access different servers, thereby reducing the load at all servers. Also, users can access data from the replication site that has the lowest access cost, which is typically the site that is geographically closest to them.

                        That's the reason I'm sugges Advanced Replication.

                        Babu

                        http://babudba.blogspot.com
                        • 9. Re: Replication using Materialized View vs Streams
                          400137
                          Babu,

                          I never said that MV replication cannot be used, however it is not the best solution in this case.

                          It is like comparing an old Trabant and a brand new Toyota.

                          Both will serve the purpose, however there is a huge difference in the efficiency and the maintenance cost.

                          Cheers,
                          Mihajlo
                          • 10. Re: Replication using Materialized View vs Streams
                            Babu Baskar
                            :)

                            I got form you;

                            Thanks.

                            Babu

                            PS: Nice Discussion with you.