4 Replies Latest reply on Feb 3, 2012 4:12 AM by 844729

    one-directional table sync in 10g

    844729
      Dear all,

      Database A 10G Enterprise 64bit on Windows 2008. ( Source: Transactional - low frequency updates e.g. less than 100 trx per hour).
      Database B 10G Standard 64bit on Windows 2008. ( Target: BI analytical / adhoc reporting - some data aggregation maybe expected ).

      I have a 5G table (w/ PK) on database A and would like to synchronize (online or even near-realtime ) this table to Database B one-way direction only. What is the efficient method to do so...? CDC? Replication or...?

      Thanks.
      Clement

      Edited by: user5473426 on Feb 1, 2012 6:29 PM
        • 1. Re: one-directional table sync in 10g
          Andy Klock
          or fast refreshes of a materialized view.

          http://docs.oracle.com/cd/B19306_01/server.102/b14226/repmview.htm
          1 person found this helpful
          • 2. Re: one-directional table sync in 10g
            844729
            Thanks very much,Andy.

            This is very helpful.
            I wonder if I should create all columns on the MV Log in the source or have this setting as default?
            And just to confirm that FAST refresh does not need the UPDATE switch at MV creation, is this correct ( as only single direction update is needed ) ?
            Lastly, I presume if did an impdp / imp into Database B from Database A ( only the table in discussion ), i just need to redo the same procedure once ?

            thanks so much.

            Clement
            • 3. Re: one-directional table sync in 10g
              Andy Klock
              No problem Clement.If there is a primary key on the table that will be sufficient for the materialized view log, otherwise you can use rowid:

              http://docs.oracle.com/cd/B19306_01/server.102/b14226/repmview.htm#i30732

              And, no, you don't need UPDATE on creation. It sounds like you only need "one-way direction" so the default READ ONLY should be sufficient for your needs. You lost me on the impdp/imp question. There is no need to exp/imp, the CREATE MATERIALIZED VIEW is going to do all of the initial heavy lifting. Depending on how you create it you can schedule the fast refresh interval with NEXT (every five minutes for example):
              CREATE MATERIALIZED VIEW catalog   
                 REFRESH FAST START WITH SYSDATE NEXT  SYSDATE + 5/24/60  
                 WITH PRIMARY KEY 
                 AS SELECT * FROM product_information@orcl1; 
              See for more details/examples:

              http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_6002.htm

              And welcome to the forums!
              • 4. Re: one-directional table sync in 10g
                844729
                That's a concise an answer! the impdp part was my assumption that the table(source) might be overwritten (impdp) in case of data recovery issues in the source ( modified). Cheers mate.