This discussion is archived
4 Replies Latest reply: Feb 2, 2012 8:12 PM by 844729 RSS

one-directional table sync in 10g

844729 Newbie
Currently Being Moderated
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
    AndyKlock Journeyer
    Currently Being Moderated
    or fast refreshes of a materialized view.

    http://docs.oracle.com/cd/B19306_01/server.102/b14226/repmview.htm
  • 2. Re: one-directional table sync in 10g
    844729 Newbie
    Currently Being Moderated
    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
    AndyKlock Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points