3 Replies Latest reply: Jan 29, 2013 9:37 AM by JustinCave RSS

    What is the best method for synchronizing tables in two different DB?

    User505978 - Oracle
      We have a core banking database and this database includes our customer related tables and these tables are really huge.

      And we have other database for some applications and this database needs fresh customer data which take place in core banking database.


      Our current method is materalized views but we have some performance problems about it.

      What can give better performace for synchronizing the tables between databases?


      -oracle streams?
      -triggers?
      -dbms_comparison?

      or anything else?

      Edited by: 983327 on Jan 28, 2013 2:14 PM
        • 1. Re: What is the best method for synchronizing tables in two different DB?
          JustinCave
          Assuming that both databases are Oracle, both run relatively recent versions of Oracle, and your organization has administrative access to both systems ("core banking" databases are often run by third parties), Streams is a more efficient architecture than materialized views on the capture side (assuming that you are doing incremental refreshes with materialized views). It's more work to configure than materialized views but you get much greater flexibility and better performance.

          Oracle's direction is to move away from Streams and towards GoldenGate which requires additional licenses. Since Oracle does not plan on enhancing Streams moving forward, if this is a strategic decision for your organization, you might want to look at GoldenGate instead even if it doesn't offer any benefits today over Streams.

          Justin
          • 2. Re: What is the best method for synchronizing tables in two different DB?
            User505978 - Oracle
            Thank you for your answer.

            As i und from your words, streams is more efficient way. Also i should say this also, this synchronizing proccess should be run as much as often. Like every 5 min or less. I mean tables should be synchronized every 5 min. Streams is also useful for this strategy?
            • 3. Re: What is the best method for synchronizing tables in two different DB?
              JustinCave
              Streams can generally keep the lag between a change being made on the source system and that change being applied to the destination system to a few seconds (assuming, of course, that you have sufficient hardware resources). Keeping the lag to a few minutes shouldn't generally be a problem.

              Justin